DB2 Server for VSE & VM Application Programming (Version 7 Release 5)

In a VSE environment, C comments may not start at column 1 because a “/*”

starting at column 1 will be mistaken as an End-Of-Data-File JCL command. Other

VSE restrictions may apply to the use of column 1 in C application programs.

Continuation of SQL statements and host variable declare statements across lines

can be accomplished by breaking the line anywhere a blank can occur.

Continuation of tokens (the basic syntactical units of a language) is allowed from

one line to the next, by coding a backslash (the C continuation character with hex

value X'E0') in the line to be continued immediately after the first part of the token

(leaving the remainder of the line blank), and coding the next part of the token

from column 1 on the continuation line. If column 1 on the continuation line is

blank, the token is not continued. See the DB2 Server for VSE & VM SQL Reference

manual for a discussion of tokens.

You can also use the trigraph ??/ in place of the backslash as the continuation

character. The C preprocessor treats end-of-line like a blank delimiter except when

it is in a literal.

Delimiting SQL Statements

Use delimiters on all SQL statements to distinguish them from regular C

statements. You must begin each SQL statement in your program with EXEC SQL,

and end each statement with a semicolon. EXEC and SQL must be in uppercase on

the same line, with only blanks separating them (no in-line C or SQL comments).

Also, EXEC SQL must be immediately followed by a blank, C comment, or SQL

comment, and it must be preceded by either a blank, C comment, {, }, trigraph ??<,

trigraph ??>, ), colon, or semicolon.

Elsewhere within SQL statements, C and SQL comments are allowed anywhere

that blanks are allowed. However, there must not be any comments within SQL

statements that are dynamically defined and executed.

Any SQL statement except INCLUDE can be followed on the same line by another

SQL statement, C statement, or C comment.

Identifying Rules for Case

The keywords EXEC SQL must appear in uppercase in your C program. The rest

of an SQL statement can appear in mixed case, but will be interpreted as

uppercase, except for host variable names and text within quotation marks, which

will be left in the original case.

Note: C host variables are always treated with case sensitivity by the C

preprocessor. This is true for the C compiler too, except for externals, which

C may truncate and fold to uppercase. Keep this in mind when using host

variables with external scope.

Identifying Rules for Character Constants

Remember to follow SQL, not C, conventions when coding such character constant

strings. These strings must be delimited by single quotation marks, and an

embedded backslash is not recognized as an escape character.

Using the INCLUDE Statement

To include external secondary input, specify:

EXEC SQL INCLUDE text_name

Appendix B. Using SQL in C

333

at the point in the source code where the secondary input is to be included.

DB2 Server for VM

The text_name is the file name of a CMS file with a “CCOPY” file type and

located on a CMS minidisk accessed by the user. It is always folded to

uppercase. If anything is found after an INCLUDE statement, a warning

message is issued and the input is ignored.

DB2 Server for VSE

The text_name is the member name of a “B” type source member of a VSE

library.

Use the SQL INCLUDE statement instead of the C preprocessor #include directive

to include files that contain SQL host variables or SQL statements.

Using the CONNECT Statement (DB2 Server for VSE)

The CONNECT statement is required to establish a connection between the

database manager and the program. To do an explicit connect, specify:

EXEC SQL CONNECT :userid IDENTIFIED BY :password;

Both userid and password must be host variables declared as fixed length 8

character strings.

Using the C Compiler Preprocessor

The preprocessor must run before the C compiler and its built-in preprocessor. It is

therefore not possible to contain any C preprocessor directives within an SQL

statement. The SQL INCLUDE statement should be used instead of the C #include

for files that contain SQL host variable declarations or statements.

Declaring Host Variables

You must declare all host variables in an SQL declare section. For a description of

an SQL declare section, refer to “Declaring Variables That Interact with the

Database Manager” on page 8.

Declare host variables in the source file before the first use of the variable in an

SQL statement. You can use the following types of variables in an SQL statement:

v Scalar variables

v Structure variables

v Structure elements

v Array variables

Scalar variables, structure elements, and array elements are data objects. For

information on the use of these variables in an SQL statement, refer to “Using Host

Variables” on page 55 and “Using Host Structures” on page 55.

Note: You can declare non-host variables in an SQL declare section; however,

declarations that do not conform to DB2 Server for VSE & VM declaration

rules may return errors.

The definition of a host variable is subject to the following rules:

334

Application Programming

A data object declared as a scalar variable or structure element may have any

one of the following basic C data types:

short Short integer

long Long integer

float

Floating-point

double

Double-precision floating-point

decimal

Decimal

The keyword int is optional in the declaration of a short or long integer. You

cannot use the unqualified type int when declaring a variable to be used in an

SQL statement: specify short or long.

A data object declared as an array element can have any of the following basic C

data types:

short Short integer

char Single character

You can use scalar variables and structure elements as main variables. If they are

declared with a data type of short integer, you can also use scalar variables as

indicator variables.

Character arrays hold the SQL CHARACTER data types. You should declare

character arrays with one extra character to contain the string terminating NUL.

You can use the unsigned qualifier with character array host variables. It does

not affect the way the system treats them.

An explicit constant decimal array size is required between the brackets, even if

an initializer is used on the declare. Expressions, preprocessor functions (such as

sizeof), octal or hex values, and #defined variables cannot be used as the size of

character arrays.

char value1 [5] = "TEST";

Correct

char value1 [5];

Incorrect

char value1 [ ] = "TEST";

Incorrect

char value1 [sizeof(var)];

char value1 [MAXLEN +1]

Incorrect

char value1 [015];

Incorrect

(octal)

Appendix B. Using SQL in C

335

You can only use short integer arrays as indicator arrays. The following is an

example of an indicator array:

short ind_array [10];

You cannot use indicator array elements as main or indicator variables.

You can use a structure variable as a host structure or as a varying-length string

definition. The structure declaration must be in the following form when used to

define a varying-length string:

struct tag {

short vlen;

char vstr[nnn];

} varname1;

The structure tag is optional. Any legal C names can be used for the structure

and the contained variables. The nnn, defining the length of the largest string to

be held in the structure, is specified by you.

This structure defines a VARCHAR or LONG VARCHAR host variable with the

name varnamel and a length nnn You cannot use this structure as a host

structure; you cannot use the elements of the structure as host variables.

The system does not add or expect a NUL at the end of a VARCHAR or LONG

VARCHAR string. If one is needed, you can use a character array host variable,

or you can add one after the data value has been returned, with the statement:

varname1.vstr[varname1.vlen]=’/0’;

If a NUL is required, ensure that the nnn is one larger than the maximum

allowable string, so that adding the NUL at the end will never overflow the

allocated storage.

A macro is provided to assist in the declaration of VARCHAR structures:

SQLVARCHAR(varname,nnn)

will expand to:

struct{

short sqllen;

char sqlstr[nnn];

}varname;

You can use this macro wherever a structure declaration defines a varying-length

string.

A structure variable which defines a host structure is any two-level structure,

other than a varying-length string definition, declared in an SQL declare section.

The following example is a host structure:

struct tag{char projno [7];

short actno;

long acstaff;

char acstdate [10];

char acendate [10];

}projstrct;

Note: The structure tag is optional.

This structure represents the following list of host variables when used in an

SQL statement:

projno, actno, acstaff, acstdate, acendate

In other words, the two following SQL statements are equivalent:

EXEC SQL SELECT PROJNO, ACTNO, ACTSTAFF, ACSTDATE, ACENDATE

INTO

:projstrct

FROM PROJ_ACT

WHERE PROJNO = ‘100000’

336

Application Programming

EXEC SQL SELECT PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE

INTO

:projno, :actno, :acstaff, :acstdate, :acendate

FROM PROJ_ACT

WHERE PROJNO = ‘100000’

A host structure can either be a stand-alone structure or a substructure of a more

complex structure. The following example is a complex structure that contains a

host structure:

struct tag { char empno [7];

struct taga { char firstname [13];

char midinit [1];

char lastname [16];

} empname;

char workdept [3];

char phoneno [4];

} employee;

The structure empname is a host structure.

You can use the elements of the host structure and the elements of a complex

structure containing a host structure as host variables. In the previous example,

you can use empno, firstname, midinit, lastname, workdept and phoneno as

host variables.

You can code a substructure in the host structure to represent a varying-length

string element if the substructure conforms to the rules for a varying-length

string definition. All of the rules in the description of structures that define

varying-length strings also apply in this situation. The following example is a

host structure that contains a VARCHAR element:

struct tag { struct taga { short fnlen;

char fntext [12];

} firstname;

char midinit [1];

struct tagb {short 1nlen;

char

1ntext [15];

} lastname;

} empname;

The C preprocessor interprets the structure empname as a host structure

containing 3 elements: firstname with data type VARCHAR and length 12,

midinit with data type CHAR and length 1, and lastname with data type

VARCHAR, and length 15.

Note: Any structure matching the description of a varying-length string

definition is interpreted as a VARCHAR or LONG VARCHAR variable

and cannot be used as a host structure.

Third level host structures are permitted in C to support varying-length strings.

The following is an example of varying-length string declarations in host

structures.

EXEC SQL BEGIN DECLARE SECTION;

struct

{

char last??(9??);

char first??(9??);

struct

{

short addlen;

char addtext??(200??);

} address;

} empname;

EXEC SQL END DECLARE SECTION;

main()

{

Appendix B. Using SQL in C

337

EXEC SQL SELECT LASTNAME, FIRSTNME, ADDRESS

INTO

:empname

FROM EMPLOYEE

WHERE LASTNAME = ’JOHANSON’;

}

In this example, empname is considered by the C preprocessor to be a two-level

structure because the structure of address matches that of a VARCHAR data

type. As a result, empname may be used in the SELECT statement. If, for example,

addlen was changed from short to long, the structure of address would no

longer match a VARCHAR data type and empname would be considered a

three-level structure. As a result, empname could NOT be used in a SELECT

statement.

Union, enumeration, bitfield, and void types are not supported. Typedefs are not

supported.

Auto, static, extern, const, volatile, and _Packed storage classes are supported.

The register storage class is not supported. If no class is specified, the usual C

default storage class applies (which depends on the placement of the declaration

within the source file).

The system supports any sequence of declaration keywords that is also

supported by the C compiler.

Initialization of variables on the declaration statement is supported.

You can declare multiple variables of the same type in the same C declaration

statement. For example:

static short partno, suppno, time;

static char name [16] , adr[36];

static double qonhand, qonorder, price;

Note:

You must explicitly declare all structures in C within the multi-level

structure. You cannot declare a structure for reference within another

structure (except for the SQLVARCHAR macro). In the following example,

only dates and product may be used as host structures. orderno and

custnum may be used as scalar host variables and may be qualified as

custord.orderno and ordinfo.custnum or custord.ordinfo.custnum.

EXEC SQL BEGIN DECLARE SECTION;

struct

{

char orderno??(10??);

struct

{

char custnum??(10??);

struct

{

char ordate??(7??);

char delivdte??(7??);

} dates;

} ordinfo;

struct

{

char stockno??(11??);

char quantity??(4??);

} product ;

} custord ;

EXEC SQL END DECLARE SECTION;

main()

{

EXEC SQL SELECT STOCKNO, QUANTITY

338

Application Programming

INTO :product

FROM ORDER

WHERE STOCKNO = ’1234567890’;

}

You cannot duplicate variable names in a single source file, even if they are in

different blocks or functions. The C preprocessor defines a duplicate variable

name as any name that cannot be referenced unambiguously when fully

qualified. (After a variable is declared in an SQL declare section, it is known to

SQL for all functions and blocks for the rest of the source file, regardless of the

host variable’s actual scope. Therefore, that variable, or another variable with the

same name and type, cannot be used in an SQL statement, even in a scope

outside of the original SQL declare section.) See “Using Host Variables as

Function Parameters” on page 341 for more information.

The database manager allows host variable names, statement labels, and SQL

descriptor area names of up to 256 characters in length, subject to any C

language restrictions mentioned in this appendix.

Note: Because of the restriction on the number of host variables in a statement,

host structures with greater than 256 fields will not be allowed,

You should not declare variables whose names begin with SQL, sql, RDI, or rdi

unless otherwise instructed. These names are reserved for the database manager

use.

Host variable names are case-sensitive. For example, a host variable called

partno is different from one called PartNo.

Rules for continuation are the same as those described for SQL statements.

Note that other program variables can also be declared as usual outside the SQL

declare section. The previous restrictions do not apply to non-SQL declarations.

Using Host Variables in SQL Statements

When you reference host variables, host structures, structure fields or indicator

arrays in an SQL statement, you must precede each reference by a colon (:) The

colon distinguishes these variables from SQL identifiers (such as column names).

The colon is not required outside an SQL statement.

Using the Pointer Type Attribute

Scalar host variables can be defined as pointers to any C data type that the

database manager supports. The following rules and restrictions apply:

v For the basic C data types, the variable must be declared in the same way it is

referenced in an SQL statement. For example:

short *partno;

SELECT PARTNO INTO :*partno ...

v In the case of character arrays, the array size must be explicitly defined in the

declaration. For example:

Appendix B. Using SQL in C

339

Correct

char (*v1_ptr) [5];

char (*v1_ptr) [ ];

Incorrect

char *v1_ptr ;

Incorrect

The use of parentheses is required in order for arrays to define a pointer to an

array of 5 characters, as opposed to an array of 5 pointers to characters. (See

“Using C NUL-Terminated Strings and Truncation” on page 342 for the

limitations on string lengths.)

The host variable would then be referenced as *v1_ptr in an SQL statement.

v The asterisk is considered part of the host variable name. This means:

- The asterisk is included in the 256-character length limitation for host variable

names.

- If a host variable is declared with an asterisk, it must always be used within

the SQL statement with the asterisk. If it was declared without an asterisk,

then it must never have one in an SQL statement.

v The programmer is responsible for ensuring that the pointer is set before it is

used.

There are primarily two uses for pointer types with SQL statements:

Allocating or sharing storage.

A program could contain a single SQL declare section, and use pointers for

some or all large data areas. Then, before a pointer is used in an SQL

statement, an alloc function could be used to acquire storage and set the

pointer, or the pointer could be set to a shared storage area. This allows the

program to reduce its overall storage requirement. For example:

EXEC SQL BEGIN DECLARE SECTION;

struct tag {

short vlen;

char vstr[1000];

} *vstr_ptr;

EXEC SQL END DECLARE SECTION;

vstr_ptr = (struct tag *) malloc(sizeof(struct tag));

EXEC SQL SELECT DESCRIPTION

INTO :*vstr_ptr FROM TABLE;

}

Passing variables to functions for update.

C usually passes parameters by value. This prevents the called function from

changing the caller’s version of a parameter. Passing a parameter by reference

can be accomplished by the caller explicitly passing a pointer to the data. The

called function then changes the data referenced by the pointer by using the

340

Application Programming

asterisk for indirection. If the value is to be changed with an SQL statement,

the called function must also declare the pointer value of the parameter in an

SQL DECLARE section. For example:

main()

{

EXEC SQL BEGIN DECLARE SECTION;

long int partno;

EXEC SQL END DECLARE SECTION;

getdata(&partno);

}

getdata(partno_ptr)

EXEC SQL BEGIN DECLARE SECTION;

long int *partno_ptr;

EXEC SQL END DECLARE SECTION;

{

EXEC SQL SELECT PARTNO

INTO :*partno_ptr FROM TABLE;

}

Using Host Variables as Function Parameters

Host variables with the same name can only be declared in one SQL declare

section. When passing a host variable to a function within the same file as the

calling function, the variable can be used in an SQL statement in the called

function without being redeclared in an SQL declare section. For example:

main()

{

EXEC SQL BEGIN DECLARE SECTION;

long int partno;

EXEC SQL END DECLARE SECTION;

getdata(partno);

}

getdata(partno)

long int partno;

{

EXEC SQL BEGIN DECLARE SECTION;

long int qonhand;

EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT QONHAND

INTO :qonhand FROM TABLE

WHERE PARTNO = :partno;

}

Given the SQL declaration of partno within main(), partno can be used in any SQL

statement that follows it in the file. If getdata had a different name for the partno

parameter, it would have to be included in getdata’s SQL declare section.

For information on how to allow a called function to update a parameter, refer to

“Using the Pointer Type Attribute” on page 339.

Appendix B. Using SQL in C

341

Using C Variables in SQL: Data Conversion Considerations

Host variables must be type-compatible with the columns with which they are to

be used. For example, if you want to retrieve into a program variable the

QONHAND column of the database, and the data type of QONHAND is

INTEGER, you should declare the program variable to be of type short, long, float,

or double.

The database manager considers the numeric data types compatible as well as the

character string data types (CHAR, VARCHAR, and LONG VARCHAR, including

strings of different declared lengths). Of course, an overflow condition may result

if, for example, you assign a 31-bit integer to a 15-bit integer and the current value

of the 31-bit integer is too large to fit in 15 bits. Truncation also occurs when a

decimal number having a scale greater than zero is assigned to an integer. In

general, overflow occurs when significant digits are lost, and truncation occurs

when nonsignificant digits are lost.

The system also considers the datetime data types to be compatible with character

data types (CHAR, and VARCHAR, but not long fields).

Using C NUL-Terminated Strings and Truncation

The database manager interprets a character string in C as NUL-terminated if the

length of the string is greater than 1 byte and less than 32,768 bytes.

The NUL-byte is mandatory when the database manager receives data from a

NUL-terminated string. You receive an SQLCODE -302 (SQLSTATE '22024') if the

NUL-byte is not found within the defined length of the string. This means that the

maximum number of bytes of data that can be stored in a NUL-terminated string

is one less than the defined length of the string.

When data is sent from the application server to a NUL-terminated string, a

NUL-byte is always appended to the end of the string. If the variable is not big

enough to hold the entire string (including the NUL), then a warning condition is

indicated using the SQLCA SQLWARN flags and the output indicator value, as

shown in the following chart. Truncation will occur even in the case where a

character value of actual length n is to be assigned to a C variable declared as

length n due to the NUL character being inserted at the last byte of the declared

length. When truncation occurs, that last byte is overwritten by NUL.

Table 36. Warning Flags after Character Truncation

Output Indicator

SQLCA

Variable

Condition

SQLWARN0

SQLWARN1

(if supplied)

Character string, including the

blank

(zero)

NUL, fits in the declared C

character array.

Actual data truncated. That is,

Original length of

the C variable declared as less

value (n) excluding

than or equal to n, to hold a

the NUL.

character value of actual

length n.

Calculating Dates

Date calculations can result in date durations, and the database manager converts

the result into any numeric type of a column or a host variable. However, to

342

Application Programming

involve a date duration in a calculation (for example, to add a duration to a date),

the date duration must be in DECIMAL(8,0) format. The system does not

automatically convert any numeric type of column or host variable to a decimal

value for use in a date calculation. If your C compiler does not support the fixed

decimal data format, the scalar “DECIMAL” conversion function must be used to

explicitly convert a value to decimal type. For example,

long duration=10100;

/* 1 year and 1 month */

long result_dt;

EXEC SQL SELECT START_DATE+DECIMAL(:duration,8,0)

INTO :result_dt FROM TABLE;

Using Trigraphs

A trigraph is a sequence of three characters that you write in place of a C source

character that your input device does not generate. The following trigraphs are

supported by the C preprocessor in an SQL declare section:

??(

[

(left bracket)

??)

]

(right bracket)

??<

{

(left brace)

??>

}

(right brace)

The following trigraph is supported in an SQL statement only when used as a

continuation character:

??/

(backslash)

Using DBCS Characters in C

The rules for the format and use of DBCS characters in SQL statements are the

same for C as for other host languages supported by the system. For a discussion

of these rules, see “Using a Double-Byte Character Set (DBCS)” on page 51.

The C language does not provide a way to define graphic host variables. If you

want to add graphic data to or retrieve it from DB2 Server for VSE & VM tables,

you must execute the affected statements dynamically. By doing so, the data areas

that are referenced by each statement can be described in an SQLDA. In the

SQLDA, you must set the data type of the areas containing graphic data to one of

the graphic data types. For a discussion of the SQLDA, refer to the DB2 Server for

VSE & VM SQL Reference manual.

Considering Preprocessor-Generated Statements

When preprocessing an SQL C program, every executable SQL statement is

translated into control block declarations, assignment statements, and a function

call to pass the control block to the preprocessor at run time. To simplify the

generation of this code during preprocessing, a number of typedef and

communication area definitions are placed just after any initial C compiler

directives or comments.

In addition, to assist the application programmer, the SQLVARCHAR macro is

inserted with the typedefs and communication area definitions.

The preprocessor-generated statements are described in Table 37 on page 344.

These statements are inserted immediately before the first line in the source

program that is not a blank line, a C comment, or a C precompiler directive.

Appendix B. Using SQL in C

343

The C preprocessor imposes two restrictions on the coding of C precompiler

directives:

1. You may not use the #INCLUDE precompile directive to include the main

function of a C program.

2. Conditional precompiler directives that contain C code must come after the first

non-precompiler directive.

Table 37. C Preprocessor-Generated Statements

Generated Code

Purpose

#pragma linkage (ARIPRDI,OS)

To establish correct addressability and

parameter passing conventions with

the system at run time.

#ifndef SQLVARCHAR

Macro that can be used by the

#define SQLVARCHAR(varname,nnn) \

application to simplify the C program.

struct {

The definition of this macro can be

short sqllen;

changed by including a #define

char sqlstr[nnn];

statement before the first

} varname

non-precompiler directive C statement

#endif

or SQL statement in your program.

344

Application Programming

Table 37. C Preprocessor-Generated Statements

(continued)

Generated Code

Purpose

typedef struct

{

typedefs of allocated control blocks

short CALLTYPE;

used when translating executable SQL

char AUTHOR[8];

statements into C function calls.

short PROG_NAMEL;

char PROG_NAME[8];

short SECTION_NUM;

short CLASS_SECTION;

char *CODEPTR;

char *VPARAMPTR;

char *AUXPARAMPTR;

char *SQLTIEPTR;

char SPECIALCALL;

char CALLFLAG;

char WAITFLAG;

char RELEASEFLAG;

char VPARAMIND;

char AUXPARAMIND;

char ERRORFLAG;

char RDIDESCFLAG;

long MAILBOXLEN;

char RDIRELNO;

char RDICISL;

char RDIDATE;

char RDITIME;

long RDIFDBCK;

char *RDIEXTP;

char RDIRESV1[2];

char RDIRDB16;

char RDIRESV2;

} SQL_RDIIN;

typedef struct

{

char *RDIPTR01;

char *RDIPTR02;

} SQL_RDIPT;

typedef struct

{

short DATA_TYPE;

short LEN;

char *DATA_PTR;

short *INFOPTR;

short NAMEL;

char NAME[30];

} SQL_PVELMS;

typedef SQL_PVELMS *SQL_PVLMP;

typedef struct {

short CURSRLEN;

char CURSRNAM[18];

} SQL_RDICURAR;

typedef union {

long rdicnstl[2];

char rdicnstc[8];

} SQL_RDICONST;

static long SQLTIE[12];

Communication areas used to save

static char RDIRDBN[16];

information about the state of the C

static struct {

program between run-time calls to the

char RDIEXTEC[8];

database manager.

long RDIEXTFLR;

char *RDIDBNMP;

char *RDICONSP;

char *RDIBPOPT;

char *RDIXPTRS[6];

} SQLRDIX;

Appendix B. Using SQL in C

345

Handling SQL Errors

A return code structure (the SQLCA) must be in scope for each executable SQL

statement. You can define one by coding the following statement in your source

program:

EXEC SQL INCLUDE SQLCA;

The preprocessor replaces this statement with the declaration of the SQLCA

structure, and a set of #defines to make referring to the error codes and flags

easier. These are shown in Figure 81.

#ifndef SQLCODE

struct sqlca

{

unsigned char sqlcaid[8];

long sqlcabc;

long sqlcode;

short sqlerrml;

unsigned char sqlerrmc[70];

unsigned char sqlerrp[8];

long sqlerrd[6];

unsigned char sqlwarn[11];

unsigned char sqlstate[5];

};

#define SQLCODE sqlca.sqlcode

#define SQLWARN0 sqlca.sqlwarn[0]

#define SQLWARN1 sqlca.sqlwarn[1]

#define SQLWARN2 sqlca.sqlwarn[2]

#define SQLWARN3 sqlca.sqlwarn[3]

#define SQLWARN4 sqlca.sqlwarn[4]

#define SQLWARN5 sqlca.sqlwarn[5]

#define SQLWARN6 sqlca.sqlwarn[6]

#define SQLWARN7 sqlca.sqlwarn[7]

#define SQLWARN8 sqlca.sqlwarn[8]

#define SQLWARN9 sqlca.sqlwarn[9]

#define SQLWARNA sqlca.sqlwarn[10]

#define SQLSTATE sqlca.sqlstate

#endif

struct sqlca sqlca;

Figure 81. SQLCA Structure (in C)

Note: SQLCA character array variables are not NUL-terminated. They cannot be

directly used by C string manipulation functions.

The SQLCA must not be declared within the SQL declare section. It may be

declared outside all functions in the module, which gives it global scope, or

separately within each function that contains executable SQL statements.

Instead of using the SQL INCLUDE SQLCA statement, the SQLCA can be coded

directly, or #included from a header file.

You may find that the only variable in the SQLCA that you really need is

SQLCODE. If this is the case, declare just the SQLCODE variable, and invoke

NOSQLCA support at preprocessor time.

The number of SQLCODE declarations is not limited by the DB2 Server for VSE &

VM preprocessor. If a stand-alone SQLCODE is specified, the code inserted by the

preprocessor into the C code to expand an EXEC SQL statement will refer to the

address of that SQLCODE. The C compiler determines if multiple declarations

346

Application Programming

within a program section are not acceptable. In addition, the C compiler

determines which region of the code an SQLCODE declaration refers to.

Using Dynamic SQL Statements in C

You must declare an SQLDA structure to execute dynamically defined SQL

statements. You can have the database manager include the structure definition

automatically, by specifying the following statement in your source code:

EXEC SQL INCLUDE SQLDA;

You can also include the structure definition by directly coding it as shown in

Figure 82.

#ifndef SQLDASIZE

struct sqlda {

unsigned char sqldaid[8];

long sqldabc;

short sqln;

short sqld;

struct sqlvar {

short sqltype;

short sqllen;

unsigned char *sqldata;

short *sqlind;

struct sqlname {

short length;

unsigned char data[30];

} sqlname;

} sqlvar[1];

};

#define SQLDASIZE(n)

(sizeof(struct sqlda)+((n)-1)*

sizeof(struct sqlvar))

#endif

Figure 82. SQLDA Structure (in C)

Note: The SQLDA character array variables sqldaid and sqlname.data are not

NUL-terminated. They cannot be directly used by C string manipulation

functions.

The SQLDA must not be declared within the SQL declare section.

Using the defined preprocessor function SQLDASIZE, your program can

dynamically allocate an SQLDA of adequate size for use with each EXECUTE

statement. For example, the code fragment below allocates an SQLDA that is

adequate for five fields, and uses it in an EXECUTE of statement S3:

struct sqlda *daptr;

daptr = (struct sqlda *)malloc(SQLDASIZE(5));

daptr->sqln=5;

/* Add code to set the rest of values and

pointers in the SQLDA

EXEC SQL EXECUTE S3 USING DESCRIPTOR *daptr;

Note: The variable that points to the SQLDA is not defined in an SQL declare

section. Its context within an SQL statement (following INTO or USING

DESCRIPTOR) is enough to identify it.

Appendix B. Using SQL in C

347

You can use a similar technique to allocate an SQLDA for use with a DESCRIBE

statement. The following program fragment illustrates the use of SQLDA with

DESCRIBE for three fields and a “prepared” statement S1:

struct sqlda *daptr;

EXEC SQL DECLARE C1 CURSOR FOR S1;

daptr = (struct sqlda *)malloc(SQLDASIZE(3));

daptr->sqln=3;

EXEC SQL DESCRIBE S1 INTO *daptr;

if (daptr->sqld > daptr->sqln)

--get a bigger one

Set sqldata and sqlind

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 USING DESCRIPTOR *daptr;

There is no standard C type to support packed decimal data. If you want to get

data in packed decimal format, the SQLDA must be filled in with an SQLTYPE of

484 and with the appropriate values for precision and scale in SQLLEN. The C

program would then have to deal with the data in its packed format.

See the DB2 Server for VSE & VM SQL Reference manual for more information on

the individual fields within SQLDA.

Defining DB2

Server for VSE & VM Data Types for C

Table 38. DB2 Server for VSE & VM Data Types for C

DB2 Server for VSE

& VM

Equivalent C

Description

Keyword

Declaration

A binary integer of 31 bits, plus sign.

INTEGER or INT

long or

long int

A binary integer of 15 bits, plus sign.

SMALLINT

short or

short int

A packed decimal number, precision p, scale s

DECIMAL[(p[,s])]

decimal(p,s)

(1≤p≤31 and 0≤s≤p). In storage, the number occupies

a m aximum of 16 bytes. Precision is the total number

or DEC[(p[,s])]

If your version of the C

of digits. Scale is the number of those digits that are

compiler does not provide

to the right of the decimal point.

support for the decimal

data type, C short, long,

float and double host

variables are supported

for conversion to and

from DECIMAL columns.

To preserve decimal places:

p<7 use float;

else use double.

A single-precision (4-byte) floating-point number, in

REAL or

FLOAT

short System/390 floating-point format.

FLOAT(p),

1 ≤ p ≤ 21

348

Application Programming

Table 38. DB2 Server for VSE & VM Data Types for C

(continued)

DB2 Server for VSE

& VM

Equivalent C

Description

Keyword

Declaration

A double-precision (8-byte) floating-point number, in

FLOAT or

DOUBLE

long System/390 floating-point format.

FLOAT(p),

22 ≤ p ≤ 53

or DOUBLE

PRECISION

A fixed-length character string of length 1.

CHARACTER[(1)] or

char or char ..[1]

CHAR[(1)]

A NUL-terminated character string of maximum

VARCHAR(n)

char ..[n+1]

defined length n. Range of n is 1 ≤ n ≤ 254. The

terminating NUL is mandatory upon input.

A NUL-terminated character string of maximum

LONG VARCHAR

char ..[n+1]

defined length of 32 767 bytes, subject to certain

usage limitations. Range of n is 255 ≤ n ≤ 32 766. The

terminating NUL is mandatory upon input.

A varying-length character string of maximum length

VARCHAR(n)

struct { short ..; char ..[n]; }

n. If n > 254 or ≤ 32 767, this data type is considered

a long field. See “Using Long Strings” on page 45 for

more information.

A varying-length character string of maximum length

LONG VARCHAR

struct { short ..; char ..[n]; }

32 767 bytes, subject to certain usage limitations.

A fixed-length string of n DBCS characters where 0 <

GRAPHIC[(n)]

Not supported

n ≤ 127.

A varying-length string of n DBCS characters. If n >

VARGRAPHIC(n)

Not supported

127 or ≤ 16 383, this data type is considered a long

field. See “Using Long Strings” on page 45 for more

information.

A varying-length string of DBCS characters of

LONG VARGRAPHIC

Not supported

maximum length 16 383, subject to certain usage

limitations.

A NUL-terminated or varying-length character string

DATE

see VARCHAR(n)

representing a date.

A NUL-terminated or varying-length character string

TIME

see VARCHAR(n)

representing a time.

A NUL-terminated or varying-length character string

TIMESTAMP

see VARCHAR(n)

representing a timestamp.

Notes:

1. NUMERIC is a synonym for DECIMAL, and may be used when creating or

altering tables. In such cases, however, the CREATE or ALTER function will

establish the column (or columns) as DECIMAL.

2. For a NUL-terminated string, the declared length should be one more than the

maximum length of a datetime to allow for the terminating NUL-byte, which is

mandatory input. Refer to the DB2 Server for VSE & VM SQL Reference manual

for information on minimum and maximum lengths.

Appendix B. Using SQL in C

349

Using Reentrant C Programs

A reentrant program has the characteristic of dynamic allocation of space for data

and save areas. This characteristic can be employed in C programs. In this case, the

data and save areas are dynamically allocated in a “static” area by the IBM C

Program Product Compiler.

Using Stored Procedures

Figure 83 on page 351 shows how to define the parameters in a stored procedure

that uses the GENERAL linkage convention.

v argv contains an array of pointers to the parameters that were passed to the

stored procedure.

- argv[0] is a special entry containing the address of the stored procedure name

- argv[1] contains the address of parameter 1

- argv[2] contains the address of parameter 2

v argc contains the number of parameters that were passed to the stored

procedure, plus one to account for the procedure name which is passed in

argv[0].

350

Application Programming

#pragma options(RENT)

#pragma runopts(PLIST(OS))

#include <stdlib.h>

#include <stdio.h>

/*****************************************************************/

/* Code for a C language stored procedure that uses the

/* GENERAL linkage convention.

/*****************************************************************/

main(argc,argv)

int argc;

/* Number of parameters passed */

char *argv[];

/* Array of strings containing */

/* the parameter values

{

long int locv1;

/* Local copy of V1

char locv2[10];

/* Local copy of V2

(null-terminated)

/***************************************************************/

/* Get the passed parameters.

/***************************************************************/

if(argc==3)

/* Should get 3 parameters:

{

/* procname, V1, V2

locv1 = *(int *) argv[1];

/* Get local copy of V1

strcpy(argv[2],locv2);

/* Assign a value to V2

}

Figure 83. Stored Procedure - Using GENERAL Linkage Convention

Figure 84 on page 352 shows how to define the parameters in a stored procedure

that uses the GENERAL WITH NULLS linkage convention. In this case:

v argv[0] contains the address of the stored procedure name

v argv[1] contains the address of parameter 1

v argv[2] contains the address of parameter 2

v argv[n] contains the address of parameter n

v argv[n+1] contains the address of the indicator variable array

Appendix B. Using SQL in C

351

#pragma runopts(PLIST(OS))

#include <stdlib.h>

#include <stdio.h>

/*****************************************************************/

/* Code for a C language stored procedure that uses the

/* GENERAL WITH NULLS linkage convention.

/*****************************************************************/

main(argc,argv)

int argc;

/* Number of parameters passed */

char *argv[];

/* Array of strings containing */

/* the parameter values

{

long int locv1;

/* Local copy of V1

char locv2[10];

/* Local copy of V2

(null-terminated)

short int locind[2];

/* Local copy of indicator

/* variable array

short int *tempint;

/* Used for receiving the

/* indicator variable array

/***************************************************************/

/* Get the passed parameters.

/***************************************************************/

if(argc==4)

/* Should get 4 parameters:

{

/* procname, V1, V2,

/* indicator variable array

locv1 = *(int *) argv[1];

/* Get local copy of V1

tempint = argv[3];

/* Get pointer to indicator

/* variable array

locind[0] = *tempint;

/* Get 1st indicator variable

locind[1] = *(++tempint);

/* Get 2nd indicator variable

if(locind[0]<0)

/* If 1st indicator variable

{

/* is negative, V1 is null

}

strcpy(argv[2],locv2);

/* Assign a value to V2

*(++tempint) = 0;

/* Assign 0 to V2’s indicator

/* variable

}

Figure 84. Stored Procedure - Using GENERAL WITH NULLS Linkage Convention

352

Application Programming

Appendix C. Using SQL in COBOL

A Sample COBOL Program

354

Invoking COPYBOOKs (DB2 Server for VSE)

360

Rules for Using SQL in COBOL

354

Using the COBRC Parameter

361

Placing and Continuing SQL Statements . . . 354

Using the TRUNC Compiler Option

361

Delimiting SQL Statements

355

Using the INCLUDE Statement

361

Identifying Rules for Case

355

Using COBOL Variables in SQL: Data

Declaring Host Variables

356

Conversion Considerations

361

Using Host Variables in SQL Statements . . . 359

Other Coding Considerations

362

Using Long VARCHAR Host Variables (DB2

Using DBCS Characters in COBOL

362

Server for VSE)

359

Handling SQL Errors

363

Using Preprocessor Options

359

Using Dynamic SQL Statements in COBOL . . 364

Using the QUOTE Parameter

359

Defining DB2 Server for VSE & VM Data Types

Using the COB2 Parameter (DB2 Server for

for COBOL

366

VSE)

360

Using Reentrant COBOL Programs

368

Using the COB2 Parameter (DB2 Server for

Using the DYNAM Compiler Option

369

VM)

360

Using Stored Procedures

369

353

A Sample COBOL Program

ARIS6CBD is a COBOL language sample program for VSE systems that is shipped

with the DB2 Server for VSE product. ARIS6CBC is a COBOL sample language

program for VM systems that is shipped with the DB2 Server for VM product. It

resides on the production disk for the base product. You may find it useful to print

this sample program before going through this appendix as the hard copy will

provide an illustration for many of the topics discussed here.

Here is a summary of the program by COBOL Divisions:

v Identification and Environment Divisions

You do not have to do anything different in either of these divisions for DB2

Server for VSE & VM applications.

v Data Division

In the Data Division of any COBOL application, you must declare all host

variables and the SQLCA structure.

The only SQL statements allowed in the Data Division are those shown in the

sample program and the INCLUDE statement; all others must be placed in the

Procedure Division.

The COBOL PICTURE clauses for the host variables are determined by referring

to Table 40 on page 366 which gives the COBOL representation for each of the

DB2 Server for VSE & VM data types. When you are coding your own

applications, you will need to obtain the data types of the columns that your

host variables interact with. This can be done by querying the catalog tables,

which are described in the DB2 Server for VSE & VM SQL Reference manual.

v Procedure Division

The program must explicitly connect to the application server. WHENEVER

statements should be coded to provide for error handling. Near the logical end

of the program, the database changes are rolled back, to ensure that the database

remains consistent for each use of the sample program. (For your own

applications, of course, you will enter a COMMIT statement.)

Rules for Using SQL in COBOL

In this appendix, the term COBOL implies OS/VS COBOL, VS COBOL II, IBM

COBOL for MVS and VM, or VSE IBM COBOL for VSE.

Placing and Continuing SQL Statements

Table 39 shows how SQL statements can be coded

Table 39. Coding SQL Statements in COBOL Program Sections

SQL Statement

Program Section

BEGIN DECLARE SECTION

WORKING STORAGE or

END DECLARE SECTION

LINKAGE SECTION or

FILE SECTION

INCLUDE SQLCA

WORKING-STORAGE SECTION

INCLUDE text_file_name

PROCEDURE DIVISION or

DATA DIVISION

354

Application Programming

Table 39. Coding SQL Statements in COBOL Program Sections (continued)

SQL Statement

Program Section

Other

PROCEDURE DIVISION SQL statements are

coded between columns 12 and 72 inclusive.

The system checks that SQL statements are not used in nested programs. Also if

one program immediately follows another program, the second program must not

contain SQL statements.

The rules for continuation of tokens from one line to the next are the same as the

COBOL rules for the continuation of words and constants. If a string-constant is

continued from one line to the next, the first non-blank character in that next line

must be a single quotation mark (') or a double quotation mark ("). If a delimited

SQL identifier (such as “EMP TABLE”) is continued from one line to the next, the

first non-blank character in that next line must be a double quotation mark.

COBOL comment lines, identified by an asterisk * in column 7, can be coded

within an embedded statement.

Delimiting SQL Statements

Delimiters are required on all SQL statements to distinguish them from regular

COBOL statements. You must precede each SQL statement with EXEC SQL, and

terminate each one with END-EXEC. Any desired COBOL punctuation, such as a

period, can be placed after the END-EXEC. For example, suppose an SQL

statement occurs as one of several statements nested inside a COBOL IF-statement.

In this instance, the SQL statement should not be followed by a period.

EXEC SQL must be specified within one line; the same is true for END-EXEC. A

separator (such as a blank space, SQL comment, or end-of-line) must precede the

END-EXEC that terminates an SQL statement; however, no punctuation is required

after the END-EXEC.

If an SQL statement appears within an IF sentence such that a COBOL ELSE clause

immediately follows the SQL statement, the clause must begin with the word

ELSE. In addition, this ELSE must be contained entirely on one line. (No

continuation is allowed for the word ELSE).

SQL WHENEVER and DECLARE CURSOR statements should not be the only

contents of COBOL IF or ELSE clauses as the preprocessor does not generate

COBOL code for these statements.

If an SQL statement terminates a COBOL IF sentence, a period should immediately

follow END-EXEC with no intervening blanks. A blank should follow the period.

Because a COBOL statement can be immediately preceded by a paragraph name,

so can an embedded SQL statement. Similarly, an embedded SQL statement in the

Procedure Division can be immediately followed by a separator period.

Identifying Rules for Case

Mixed case can be used in your COBOL program. The SQL preprocessor will

change the lowercase into uppercase, except for text within quotation marks, which

will be left in the original case.

Appendix C. Using SQL in COBOL

355

Declaring Host Variables

You must declare all host variables in an SQL declare section. For a description of

an SQL declare section, refer to “Declaring Variables That Interact with the

Database Manager” on page 8.

Declare host variables in the source file before the first use of the variable in an

SQL statement. All SQL declare sections must be located in the Working-Storage

Section, the File Section, or the Linkage Section of the Data Division. You can use

the following types of variables in an SQL statement:

v Elementary items (independent or subordinate of a group item)

v Group items

v Tables

For information on the use of these variables in an SQL statement, refer to “Using

Host Variables” on page 55 and “Using Host Structures” on page 55.

Note: You can declare non-host variables in an SQL declare section; however,

declarations that do not conform to DB2 Server for VSE & VM declaration

rules may return errors.

The declaration of a host variable is subject to the following rules:

All elementary items that are declared in an SQL declare section can be used as

main variables. If these items are declared with a data type of short integer, they

can also be used as indicator variables.

The only tables accepted by the COBOL preprocessor are tables of short integer

elements. These may only be used as indicator arrays. The following example is

an indicator array declaration:

IND_ARRAY.

05 IND-ELEMENT OCCURS 15 TIMES PIC S9(4) COMP.

The COBOL preprocessor recognizes IND-ELEMENT as the indicator array.

You cannot use indicator array elements as main variables or indicator variables.

You can use a group item as a host structure or as a varying-length string

definition. The structure must take the following form when used to define a

varying-length string:

VARCHAR-FIELD.

49 LEN-FIELD

PIC S9(4) COMP.

49 TXT-FIELD

PIC X(25).

This structure defines a VARCHAR host variable with the name

VARCHAR-FIELD and a length of 25. You cannot use this group item as a host

structure; you cannot use the elementary items in the structure as host variables.

For the rules for varying-length string variables, refer to Table 40 on page 366.

A group item which defines a host structure is any two-level structure declared

in an SQL declare section. The following example is a host structure:

PROJ-STRCT.

05 PROJNO

PIC X(6).

05 ACTNO

PIC S9(4) COMP.

05 ACSTAFF

PIC S9(9) COMP.

05 ACSTDATE

PIC X(10).

05 ACENDATE

PIC X(10).

This structure represents the following list of host variables when used in an

SQL statement:

PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE

The two following SQL statements are equivalent:

356

Application Programming

EXEC SQL SELECT PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE

INTO

:PROJ-STRCT

FROM PROJ_ACT

WHERE PROJNO = ‘100000’

EXEC SQL SELECT PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE

INTO :PROJNO, :ACTNO, : ACSTAFF, :ACSTDATE, :ACENDATE

FROM PROJ_ACT

WHERE PROJNO = ‘100000’

A host structure can be a stand-alone group item or a substructure of a more

complex group item. The following example is a complex group item that

contains a host structure:

EMPLOYEE.

05 EMPNO

PIC X(6).

05 EMPNAME.

10 FIRSTNAME

PIC X(12).

10 MIDINIT

PIC X(1).

10 LASTNAME

PIC X(15).

05 WORKDEPT

PIC X(3).

05 PHONENO

PIC X(4).

The group item EMPNAME is a host structure.

You can use the elementary items in the host structure and the elementary items

in the group item containing a host structure as host variables. In the previous

example, the following elementary items can be used as host variables:

EMPNO, FIRSTNAME, MIDINIT, LASTNAME, WORKDEPT, PHONENO

You can include a subordinate group item in the host structure to represent a

varying-length string element if that group item conforms to the rules for a

varying-length string definition. All of the rules previously stated for the

definition of varying-length strings also apply in this situation. The following

example is a host structure that contains a VARCHAR element:

EMPNAME.

05 FIRSTNAME.

49 FNLEN

PIC S9(4) COMP.

49 FNTEXT

PIC X(12).

05 MIDINIT

PIC X(1).

05 LASTNAME.

49 LNLEN

PIC S9(4)COMP.

49 LNTEXT

PIC X(15).

The COBOL preprocessor interprets the structure EMPNAME as a host structure

containing three elements: FIRSTNAME with data type VARCHAR and length 12,

MIDINIT with data type CHAR and length 1, and LASTNAME with data type

VARCHAR and length 15.

Note: Any structure that matches the description of a varying-length string

definition is interpreted as a varying-length definition and cannot be used

as a host structure.

Third-level host structures are permitted in COBOL to support varying-length

strings. The following is an example of varying-length string declarations in host

structures:

WORKING-STORAGE SECTION.

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

EMPNAME.

FIRST-NM PIC X(8).

LAST-NM

PIC X(8).

ADDRESS.

ADD-LEN PIC S9(4) COMP.

ADD-TXT PIC X(200).

EXEC SQL END DECLARE SECTION END-EXEC.

PROCEDURE DIVISION.

Appendix C. Using SQL in COBOL

357

EXEC SQL SELECT FIRSTNAME, LASTNAME, ADDRESS

INTO

:EMPNAME

FROM EMPLOYEE

WHERE LASTNAME = ’JOHANSON’

END-EXEC

In this example, empname is considered by the COBOL preprocessor to be a

two-level structure because the structure of address matches that of a

VARCHAR data type. As a result, empname may be used in the SELECT

statement. If, for example, addlen was changed from "PIC S9(4) COMP" to "PIC

S9(9) COMP", the structure of address would no longer match a VARCHAR data

type and empname would be considered a three-level structure. As a result,

empname could NOT be used in a SELECT statement.

A host structure field in an SQL statement must be qualified as

structurename.fieldname instead of fieldname OF structurename or fieldname IN

structurename.

In the declaration below, only DATES and PRODUCT may be used as host structures.

ORDERNO and CUSTNUM may be used as scalar host variables, and may be qualified

as CUSTORD.ORDERNO and ORDINFO.CUSTNUM or CUSTORD.ORDINFO.CUSTNUM.

WORKING-STORAGE SECTION.

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

CUSTORD.

ORDERNO

PIC X(10).

ORDINFO.

CUSTNUM PIC X(10).

DATES.

ORDDATE PIC X(6).

DELIVDTE PIC X(6).

PRODUCT.

STOCKNO

PIC X(10).

QUANTITY

PIC X(3).

EXEC SQL END DECLARE SECTION END-EXEC.

PROCEDURE DIVISION.

EXEC SQL SELECT STOCKNO, QUANTITY

INTO :PRODUCT

FROM ORDER

WHERE STOCKNO = ’1234567890’

END-EXEC.

The following restrictions apply to level numbers:

1. Independent elementary items must have a level number of 01 or 77.

2. Subordinate elementary items must have a level number from 02 to 49.

3. The outermost group item must have a level number of 01.

4. Subordinate group items must have a level number from 02 to 49.

5. Elementary items in a varying-length string definition must have a level

number of 49.

6. Level 66 and level 88 items will be ignored by the preprocessor.

Except for an indicator array, FILLER is permitted as the name of an elementary

item. If FILLER is used as the name of an elementary item, the item will be

ignored.

In addition to the clauses discussed in Table 40 on page 366, the COBOL

preprocessor supports the following clauses in declarations imbedded in the

SQL declare section:

358

Application Programming

GLOBAL

EXTERNAL

SYNCHRONIZED

VALUE

v Rules for continuation of variable names and COBOL keywords in declaration

statements are the same as those described for SQL statements.

v The database manager allows host variable names, statement labels, and SQL

descriptor area names of up to 256 characters in length, subject to any COBOL

language restrictions mentioned in this appendix.

Note: Due to the restriction on the number of host variables in a statement, host

structures with greater than 256 fields will not be allowed,

v You should not give any variable a name beginning with SQL or RDI. These

names are reserved for database manager use.

v Comma separators are supported between the clauses of a declaration statement.

Using Host Variables in SQL Statements

When you reference host variables, host structures, structures fields, or indicator

arrays in an SQL statement, you must precede each reference by a colon (:). The

colon distinguishes these variables from SQL identifiers (such as column names).

The colon is not required outside an SQL statement.

Using Long VARCHAR Host Variables (DB2 Server for VSE)

When you code on-line command level application programs in COBOL, be aware

of the following CICS/VSE restriction. The length of the working storage plus the

length of the TGT (TARGET GLOBAL TABLE) must not exceed 64K bytes.

This restriction only applies when using Long VARCHAR Host Variables because

the length of a single long VARCHAR host variable can be up to 32K bytes.

Using Preprocessor Options

Using the QUOTE Parameter

DB2 Server for VSE

If the COBOL compiler QUOTE option is used or if the QUOTE option has

been specified in the CBL statement of COBOL, then the QUOTE (or Q)

option of the preprocessor should also be specified. You should use a single

quotation mark (') to delineate constants used in embedded SQL statements,

regardless of the COBOL compiler QUOTE option.

DB2 Server for VM

If the COBOL compiler QUOTE option is used, the QUOTE (or Q) option of

the preprocessor should also be specified. Use a single quotation mark (') to

delineate constants used in embedded SQL statements, regardless of the

COBOL compiler QUOTE option.

Appendix C. Using SQL in COBOL

359

Using the COB2 Parameter (DB2 Server for VSE)

When the COB2 parameter is specified, certain functions supported by the COBOL

II Release 3 compiler, and later, are also supported by the database manager. These

functions include:

v Literals can be 160 characters long.

v ENDIF will be generated where appropriate when expanding code for the SQL

WHENEVER statement.

In order to make use of these features, you must specify the COB2 option when

preprocessing your application. Existing applications that use these features must

be repreprocessed and recompiled.

Using the COB2 Parameter (DB2 Server for VM)

When the COB2 parameter is specified, certain functions supported by the COBOL

II Release 3 compiler, and later, are also supported by the database manager. These

functions include:

COBOL keywords can be in mixed case. For example, “Data Division” is

allowed.

The COBOL picture clause enhancements:

- Can be in mixed case. Thus, “Picture” is allowed.

- Can end in either a period or a comma. For example, “Pic x(10)..”, and “pic

x(10),.” are both valid.

DB2 Server for VM numeric column types are compatible with the COBOL

variables

Picture S9(4) USAGE BINARY

and

Picture S9(p)[V9(s)] USAGE PACKED-DECIMAL

where “p” is the precision and “s” is the scale.

The COBOL FILLER is optional. Thus, the following example is valid even

though the fourth field is blank:

01 HEADING2.

03 FILLER

Pic x(6) VALUE ’ITEM NUMBER’.

03 FILLER

Pic x(5) VALUE SPACES.

03 FILLER

Pic x(11) VALUE ’DESCRIPTION’.

Pic x(4) VALUE SPACES.

03 FILLER

Pic x(8) VALUE ’QUANTITY’.

Literals can be 160 characters long.

The system checks that SQL statements are not used in nested programs. Also, if

one program immediately follows another, the second program must not contain

SQL statements.

ENDIF will be generated where appropriate when expanding code for the SQL

WHENEVER statement.

In order to make use of these features, you must specify the COB2 option when

preprocessing your application. Existing applications that are to make use of these

features must be repreprocessed and recompiled.

Invoking COPYBOOKs (DB2 Server for VSE)

You should not use the COBOL COPY verb to invoke COPYBOOKS that involve

SQL host variables. Instead, use the SQL INCLUDE statement to invoke such

COPYBOOKs. This arrangement is necessary because the preprocessor is run

before the COBOL compiler.

360

Application Programming

Using the COBRC Parameter

When the COBRC parameter is specified, the preprocessor will generate the

statement 'MOVE ZEROS TO RETURN-CODE' after it generates a call to ARIPRDI.

This solves the problem of unexpected or invalid return codes being reported after

a COBOL II (IBM COBOL for MVS and VM or IBM COBOL for VSE) application

ends. For example, a REXX EXEC may contain several steps which each execute

based on a return code from the previous step. If the application programmer has

not set the COBOL special register, RETURN-CODE, the return code is not reliable.

This new parameter may be used instead of explicitly setting the special register.

Limitations:

1. If the user’s COBOL compiler does not support the special register,

RETURN-CODE, the application will not compile successfully. COBOL II

supports it and new versions of COBOL support it, but old versions do not.

2. If the application sets the special register, RETURN-CODE, and then does an

SQL call, the value is not preserved.

MOVE 4 TO RETURN-CODE.

EXEC SQL INSERT INTO MYTABLE VALUES (1,2).

STOP RUN.

The application will end with return code 0 instead of 4 because when the

EXEC SQL statement is expanded the last line generated is 'MOVE ZEROS TO

RETURN-CODE'.

3. If the user’s compiler does not support the special register, RETURN-CODE,

but they have declared a variable called RETURN-CODE, the variable will be

updated which can cause unexpected results for the application.

Using the TRUNC Compiler Option

For Version 3.2 of COBOL II or later, use the TRUNC(BIN) compiler option,

because the system is half-word boundary sensitive. Under this option, receiving

fields are truncated only at halfword, fullword, or doubleword boundaries.

Using the INCLUDE Statement

To include the external secondary input, specify the following at the point in the

source code where the secondary input is to be included:

EXEC SQL INCLUDE text_file_name END-EXEC.

The text_file_name is a C-Type source member of a VSE library. Text_file_name is

the file name of a CMS file, with a “COBCOPY” file type, located on a CMS

minidisk accessed by the user.

The INCLUDE statement can appear anywhere within the File, Linkage, or

Working Storage Sections of the Data Division, and anywhere within the Procedure

Division, including the Declaratives Section, if one is used. Note that the

INCLUDE statement is the only type of SQL statement that is allowed within the

Declaratives Section of a Procedure Division.

Using COBOL Variables in SQL: Data Conversion Considerations

COBOL variables used in SQL statements must be type-compatible with the

columns of the tables with which they are to be used (stored, retrieved, or

compared). Of course, an overflow condition may occur if, for example, an

INTEGER data item is retrieved into a PICTURE S9(4) variable, and its current

value is too large to fit.

Appendix C. Using SQL in COBOL

361

The database manager recognizes the DISPLAY SIGN LEADING SEPARATE

(DSLS) attribute for COBOL host variables. It converts input host variables in the

DSLS format to the required column format, and output host variables from the

column format to DSLS format.

The character data types CHAR, VARCHAR, and LONG VARCHAR are

considered compatible. The graphic data types GRAPHIC, VARGRAPHIC, and

LONG VARGRAPHIC are considered compatible. A varying-length string is

automatically converted to a fixed-length string, and a fixed-length string is

automatically converted to a varying-length string when necessary. If a

varying-length string is converted to a fixed-length string, it is truncated or

padded on the right with blanks to the correct length. The system also truncates or

pads with blanks if a fixed-length string is assigned to another fixed-length string

of a different size (for example, a variable of PICTURE X(12) is stored in a column

of type CHAR(18)).

The system also considers the datetime data types to be compatible with character

data types (fixed or varying, but not LONG VARCHAR and VARCHAR > 254).

Refer to “Converting Data” on page 48 for a data conversion summary.

Other Coding Considerations

You may want to consider the following points when coding SQL statements and

host variable declarations:

v The preprocessor scans past COBOL NOTE-type comments and line comments

defined by an asterisk (*) in column 7. It does not recognize line comments

identified by a slash (/) in column 7.

v An SQL comment entered in a static SQL statement must be preceded by a

blank.

v When performing subtraction in an SQL statement, delimit the minus sign (-)

with blanks:

blanks

| |

V V

QUANT - :ORDER-AMOUNT

v COBOL keywords can be coded in mixed case. For example, Data Division is

allowed.

Using DBCS Characters in COBOL

DB2 Server for VSE

If your program contains DBCS characters, the following sequence of

processing is necessary:

v DB2 Server for VSE COBOL Preprocessor

v COBOL Kanji Preprocessor

v CICS/VSE Translator, if necessary

v COBOL Compiler.

The rules for the format and use of DBCS characters in SQL statements are the

same for COBOL as for other host languages supported by the system. For a

discussion of these rules, see “Using a Double-Byte Character Set (DBCS)” on page

51.

362

Application Programming

When coding graphic constants in SQL statements, use the SQL format of the

graphic constant:

G’<▌XXXX▐>’

Note: N is a synonym for G.

See “Using Graphic Constants” on page 58 for a discussion of graphic constants.

The COBOL preprocessor does not support options for changing the encoding for

the < and > characters.

Handling SQL Errors

You can declare the SQLCA return code structure that is required for the system in

two ways:

1. You may write:

EXEC SQL INCLUDE SQLCA END-EXEC.

in the Working-Storage Section of your source program. The preprocessor

replaces this with a declaration of the SQLCA structure.

2. You may declare the SQLCA yourself in the Working-Storage Section, as shown

in Figure 85 on page 363.

01 SQLCA.

05 SQLCAID

PIC X(8).

05 SQLCABC

S9(9) COMPUTATIONAL.

05 SQLCODE

PIC S9(9) COMPUTATIONAL.

05 SQLERRM.

49 SQLERRML PIC S9(4) COMPUTATIONAL.

49 SQLERRMC PIC X(70).

05 SQLERRP

PIC X(8).

05 SQLERRD

OCCURS 6 TIMES

PIC S9(9) COMPUTATIONAL.

05 SQLWARN.

10 SQLWARN0

PIC X(1).

10 SQLWARN1

PIC X(1).

10 SQLWARN2

PIC X(1).

10 SQLWARN3

PIC X(1).

10 SQLWARN4

PIC X(1).

10 SQLWARN5

PIC X(1).

10 SQLWARN6

PIC X(1).

10 SQLWARN7

PIC X(1).

10 SQLWARN8

PIC X(1).

10 SQLWARN9

PIC X(1).

10 SQLWARNA

PIC X(1).

05 SQLSTATE

PIC X(5).

Figure 85. SQLCA Structure (in COBOL)

A COBOL program containing SQL statements must have a Working-Storage

Section. The meanings of the fields within the SQLCA are discussed in the DB2

Server for VSE & VM SQL Reference manual.

In COBOL, the object of a GO TO in the SQL WHENEVER statement must be a

section name or an unqualified paragraph name.

You may find that the only variable in the SQLCA you really need is SQLCODE. If

this is the case, declare just the SQLCODE variable and invoke NOSQLCA support

at preprocessor time.

Appendix C. Using SQL in COBOL

363

The number of SQLCODE declarations is not limited by the preprocessor. If a

stand-alone SQLCODE is specified, the code inserted by the preprocessor into the

COBOL code to expand an EXEC SQL statement will refer to the address of that

SQLCODE. The COBOL compiler determines if multiple declarations within a

program section are not acceptable. In addition, the COBOL compiler determines

which region of the code an SQLCODE declaration refers to.

DB2 Server for VSE & VM does not pass the return code in register 15 on

completion of SQL statement processing. The return code and any other

information is passed in the SQLCA. Furthermore, if the COBRC preprocessor

parameter was not specified, DB2 Server for VSE & VM does not set the return

code to zeros on completion of SQL statement processing. If IBM COBOL for MVS

and VM, IBM COBOL for VSE, or COBOL II is being used, this can cause register

15 to be uninitialized and can contain unpredictable data. This appears as very

large return codes when the COBOL application ends. This does not occur with the

DOS/VS COBOL compiler. It is the responsibility of the application programmer to

set the return code to something meaningful. The COBOL special register

RETURN-CODE should be set before the application program ends.

The simplest method is to code the following lines just before a STOP RUN or a

GOBACK statement.

MOVE ZERO TO RETURN-CODE.

STOP RUN.

Any return code meaningful to the application can be set. It can also be set to the

SQLCODE if desired.

Using Dynamic SQL Statements in COBOL

The COBOL preprocessor lets you use a descriptor area, the SQLDA, to execute

dynamically defined SQL statements. (See Chapter 7, “Using Dynamic Statements,”

on page 215 for more information on dynamic SQL statements and for more

information on dynamic SQL statements and the SQLDA.) However, the COBOL

preprocessor will not replace the statement EXEC SQL INCLUDE SQLDA with a

declaration of the SQLDA structure, as is done with the SQLCA. Instead, EXEC SQL

INCLUDE SQLDA would just include the secondary input file SQLDA, as described in

“Using the INCLUDE Statement” on page 361.

Before you can use the descriptor area you must properly allocate and initialize it,

and you must manage all its address variables. The following example shows how

you could define a descriptor area in the COBOL Working-Storage section for five

fields:

364

Application Programming

01 DASQL.

02 DAID

PIC X(8) VALUE ’SQLDA

’.

02 DABC

PIC S9(8) COMP VALUE 13216.

02 DAN

PIC S9(4) COMP VALUE 5.

02 DAD

PIC S9(4) COMP VALUE 0.

02 DAVAR

OCCURS 1 TO 300 TIMES

DEPENDING ON DAN.

03 DATYPE

PIC S9(4) COMP.

03 DALEN

PIC S9(4) COMP.

03 FILLER REDEFINES DALEN.

SQLPRCSN

PIC X.

SQLSCALE

PIC X.

03 DADATA

POINTER.

03 DAIND

POINTER.

03 DANAME.

49 DANAMEL

PIC S9(4) COMP.

49 DANAMEC

PIC X(30).

Note: DOS/VS COBOL 3.1 users cannot use the ″USAGE IS POINTER″ clause

implied in this example for the DADATA and DAIND areas. Instead, these areas

must be defined with the characteristics of PIC X(4).

The descriptor area must not be declared within the SQL declare section.

The following pseudocode illustrates a use of the descriptor area, adequate for

three fields:

- allocate storage for a Descriptor Area of at least size = 3

- set DAN = 3

(number of fields)

- set DAD = 3

- set the rest of the values and pointers in the Descriptor Area

EXEC SQL EXECUTE S1 USING DESCRIPTOR dasql

When decimal data is used, the values of the SQLPRCSN and SQLSCALE field can

be determined by declaring additional variables. For example:

01 PRCSNN

PIC S9(4) COMP.

01 PRCSNC

REDEFINES PRCSNN.

FILLCHAR1

PIC X.

PRCSNCHAR

PIC X.

01 SCALEN

PIC S9(4) COMP.

01 SCALEC

REDEFINES SCALEN.

FILLCHAR2

PIC X.

SCALECHAR

PIC X.

The following MOVE statements would move the precision and scale of the nth

selected item into PRCSNN and SCALEN, respectively:

MOVE SQLPRCSN(n) TO PRCSNCHAR.

MOVE SQLSCALE(n) TO SCARECHAR.

For COBOL, the string-spec in PREPARE and EXECUTE IMMEDIATE must be in

the same format as the SQL VARCHAR data type (you must set the proper length)

or a quoted string. If a quoted string is used, its length is limited to 120 characters

(the maximum length allowed for COBOL constants). In addition, you cannot use a

single (') or double (") quotation mark within a COBOL constant that is the object

of a PREPARE or EXECUTE IMMEDIATE statement.

Appendix C. Using SQL in COBOL

365

Defining DB2 Server for VSE & VM Data Types for COBOL

Table 40. DB2 Server for VSE & VM Data Types for COBOL

DB2 Server for VSE

& VM

Equivalent COBOL

Description

Keyword

Declaration

A binary integer of 31 bits, plus sign.

INTEGER or INT

01 PICTURE S9(9)

COMPUTATIONAL.

A binary integer of 15 bits, plus sign.

SMALLINT

01 PICTURE S9(4)

COMPUTATIONAL.

A packed decimal number, precision p, scale s

DECIMAL[(p[,s])]

01 PICTURE S9(x)[V9(y)]

(1 ≤ p ≤ 31 and 0 ≤ s≤ p). In storage the

COMPUTATIONAL-3.

number occupies a maximum of 16 bytes.

or DEC[(p[,s])]

Precision is the total number of digits. Scale is

01 PICTURE S9(x)[V9(y)]

the number of those digits that are to the

PACKED-DECIMAL.

right of the decimal point.

01 PICTURE S9(x)[V9(y)]

DISPLAY SIGN LEADING SEPARATE

Where x + y = p and

y = s

A single-precision (4-byte) floating-point

REAL or

COMPUTATIONAL-1.

number, in short System/390 floating-point

format.

FLOAT(p), 1 ≤ p ≤ 21

A double-precision (8-byte) floating-point

FLOAT or

COMPUTATIONAL-2.

number, in long System/390 floating-point

format.

FLOAT(p), 22 ≤ p ≤

or DOUBLE

PRECISION

A fixed-length character string of length n

CHARACTER[(n)]

01 S PICTURE X(n).

where 0 < n ≤ 254.

or CHAR[(n)]

A varying-length character string of maximum

VARCHAR(n)

01 S.

length n. If n > 254 or ≤ 32 767, this data type

49 S-LENGTH

is considered a long field. (See “Using Long

PICTURE S9(4)

Strings” on page 45 for more information.)

COMPUTATIONAL.

(Only the actual length is stored in the

49 S-VALUE

database.)

PICTURE X(n).

A varying-length character string of maximum

LONG VARCHAR

01 S.

length 32 767 bytes.

49 S-LENGTH

PICTURE S9(4)

COMPUTATIONAL.

49 S-VALUE

PICTURE X(n).

A fixed-length string of n DBCS characters

GRAPHIC[(n)]

01 GNAME PICTURE G(n)

where 0 < n ≤ 127.

[DISPLAY-1].

366

Application Programming

Table 40. DB2 Server for VSE & VM Data Types for COBOL (continued)

DB2 Server for VSE

& VM

Equivalent COBOL

Description

Keyword

Declaration

A varying-length string of n DBCS characters.

VARGRAPHIC(n)

01 GNAME.

If n > 127 or ≤ 16383, this data type is

49 GGLEN

considered a long field. (See “Using Long

PICTURE S9(4)

Strings” on page 45 for more information.)

COMPUTATIONAL.

49 GGVAL

PICTURE G(n)

[DISPLAY-1].

A varying-length string of DBCS characters of

LONG VARGRAPHIC

01 XNAME.

maximum length 16383.

XNAMLEN

PICTURE S9(4)

COMPUTATIONAL.

XNAMVAL

PICTURE G(n)

[DISPLAY-1].

A fixed or varying-length character string

DATE

01 S PICTURE X(n).

representing a date. The minimum and

maximum lengths vary with both the format

01 S.

used and whether it is an input or output

S-LENGTH

operation. See the DB2 Server for VSE & VM

PICTURE S9(4)

SQL Reference manual for more information.

COMPUTATIONAL.

S-VALUE

PICTURE X(n).

A fixed or varying-length character string

TIME

01 S PICTURE X(n).

representing a time. The minimum and

maximum lengths vary with both the format

01 S.

used and whether it is an input or output

S-LENGTH

operation. See the DB2 Server for VSE & VM

PICTURE S9(4)

SQL Reference manual for more information.

COMPUTATIONAL.

S-VALUE

PICTURE X(n).

A fixed or varying-length character string

TIMESTAMP

01 S PICTURE X(n).

representing a timestamp. The lengths can

vary on input and output. See the DB2 Server

01 S.

for VSE & VM SQL Reference manual for more

S-LENGTH

information.

PICTURE S9(4)

COMPUTATIONAL.

S-VALUE

PICTURE X(n).

Notes:

1. USAGE or USAGE IS is optional before COMPUTATIONAL, BINARY,

PACKED-DECIMAL, and DISPLAY-1.

2. The word IS can follow PICTURE or PIC.

3. COMPUTATIONAL can be abbreviated COMP. PICTURE can be abbreviated

PIC.

Appendix C. Using SQL in COBOL

367

COMPUTATIONAL-4. or USAGE BINARY can be substituted for

COMPUTATIONAL for DB2 Server for VM.

The following synonyms are supported:

v COMPUTATIONAL-4 for COMPUTATIONAL

v BINARY for COMPUTATIONAL

v PACKED-DECIMAL for COMPUTATIONAL-3

v N(n) for G(g)

INTEGER and SMALLINT data types can have sliding ranges. For example, if

you want to declare a SMALLINT variable that you know will remain very

small, you could use S9(2) instead of S9(4). Or, you could declare an integer

with a range of S9(7) instead of S9(9). However, only the ranges shown in the

above table allow for the largest possible values of SMALLINT and INTEGER.

Truncation may occur if you declare smaller ranges.

For COMPUTATIONAL types, 9’s may be repeated rather than using the

repetition factors in parentheses (that is, 9999 instead of 9(4)). The same is true

for the X’s in the character types and the G’s in the graphic character types.

In DECIMAL data types, precision is the total number of digits. Scale is the

number of digits to the right of the decimal point.

NUMERIC is a synonym for DECIMAL and, can be used when you are

creating or altering tables. In such cases, however, the CREATE or ALTER

function will establish the column (or columns) as DECIMAL.

10.

When a VALUE clause is used for host variables of the form “PIC S9(4)

COMP”, the highest value accepted by COBOL is 9999. If you specify the

COBOL NOTRUNC option, however, a value up to 32 767 can be moved into

the host variable. If host variables are to contain long fields where the length

exceeds 9999, the NOTRUNC option must be set.

Using Reentrant COBOL Programs

A reentrant program has the characteristic of dynamic allocation of space for data

and save areas. This reentrant characteristic can be used in COBOL programs that

use the database manager.

DB2 Server for VSE

Such programs must follow the COBOL compiler’s rules for producing

reentrant programs, and must be repreprocessed, recompiled, and relinked

with the OBJECT file ARIPADR4.

Existing COBOL programs may continue to use ARIPADR until they are

recompiled. Thereafter, they must link-edit the OBJECT file ARIPADR4.

DB2 Server for VM

Such programs must follow the COBOL compiler’s rules for producing

reentrant programs, and must be repreprocessed, recompiled, and relinked

with the TEXT file ARIPADR4.

Existing COBOL programs (preprocessed prior to SQL/DS Version 2 Release

2) may continue to use ARIPADR until they are recompiled. Thereafter, they

must link-edit the TEXT file ARIPADR4.

After programs are recompiled, ARIPADR4 must be in their link or load step.

368

Application Programming

Using the DYNAM Compiler Option

The DYNAM option of the IBM COBOL for MVS and VM, IBM COBOL for VSE,

and VS COBOL II compilers can be used by applications.

If the DYNAM option is used, then it is not necessary to include any of the linkage

modules listed for COBOL programs in “Link-Editing and Loading the Program”

on page 142 (DB2 Server for VM) or “Link-Editing and Loading the Program” on

page 180 (DB2 Server for VSE.

DB2 Server for VSE

Applications using the DYNAM option must have access to the DB2 Server

for VSE production library at run time.

CICS/VSE programs do not support the DYNAM option; they must continue

to be link-edited with the required extra linkage modules.

DB2 Server for VM

COBOL applications that use the DYNAM option must have access to the

DB2 Server for VM production disk at run time.

Using Stored Procedures

The following example shows how to define the parameters in a stored procedure

that uses the GENERAL linkage convention.

IDENTIFICATION DIVISION.

DATA DIVISION.

LINKAGE SECTION.

01 PARM1 ...

01 PARM2

PROCEDURE DIVISION USING PARM1, PARM2.

Figure 86. Stored Procedure - Using GENERAL Linkage Convention

The following example shows how to define the parameters in a stored procedure

that uses the GENERAL WITH NULLS linkage convention.

Appendix C. Using SQL in COBOL

369

Appendix D. Using SQL in Fortran

A Fortran Sample Program

372

Using DBCS Characters in Fortran

376

Rules for Using SQL in Fortran

372

Using the INCLUDE Statement

377

Placing and Continuing SQL Statements . . . 372

Using Fortran Variables in SQL: Data

Placing Data Statements

373

Conversion Considerations

377

Using Fortran Common Areas (DB2 Server for

Handling SQL Errors

377

VSE)

373

Handling Program Interrupts

378

Identifying Rules for Case

373

Using Dynamic SQL Statements in Fortran . . . 378

Declaring Host Variables

373

Restrictions When Using the Fortran Preprocessor

379

Embedding SQL Statements

375

Defining DB2 Server for VSE & VM Data Types for

Using Host Variables in SQL Statements . . . 375

Fortran

380

Using Variable Length Character Strings . . . 375

371

A Fortran Sample Program

ARIS6FTD is a Fortran language sample program for VSE systems that is shipped

with the DB2 Server for VSE product. ARIS6FTC is a Fortran language sample

program for VM systems that is shipped with the DB2 Server for VM product. It

resides on the production disk for the base product. You may find it useful to print

this sample program before going through this appendix as the hard copy will

provide an illustration for many of the topics discussed here.

Note, for example, how the program satisfies the requirements of the application

prolog and epilog. Near the beginning of the program all the host variables are

declared, and error handling is defined. Near the logical end of the program, the

database changes are rolled back, to assure the database remains consistent for

each use of the sample program. For your own applications, of course, you will

enter a commit. the host variables are declared, and error handling is defined.

The data description statements for the host variables are determined by referring

to Table 41 on page 380. When you are coding your own applications you will

need to obtain the data types of the columns that your host variables interact with.

This can be done by querying the catalog tables. See the DB2 Server for VSE & VM

SQL Reference manual for more information on catalog tables.

Rules for Using SQL in Fortran

The Fortran SQL preprocessor supports programs written for the VS Fortran

compiler with the LANGLVL (77) option specified. Only FIXED-FORM source

statements are supported.

If Fortran labels are placed on SQL declarative statements, the label will be

removed and an information message given.

The Fortran preprocessor supports a maximum of 255 program units per input

source file (254 subprograms in addition to the main program).

DB2 Server for VM

All the restrictions that apply to extended dynamic statements apply to all

Fortran programs.

Placing and Continuing SQL Statements

All SQL statements must be placed in columns 7 to 72. Columns 73 to 80 may

contain sequence numbers and information; columns 1 to 5 may also contain

statement numbers.

The rules for continuation of tokens from one line to the next are the same as the

Fortran rules for the continuation of words and constants.

An SQL statement may use up to 124 continuation lines in addition to the first line

(for a total of 125 lines including blanks and comments). A continuation line can

be:

v A continued line (that is, a line that does not have a blank or zero in column 6).

v A blank line

v A comment line.

372

Application Programming

These lines must fall between the start of the SQL statement and the next

statement.

Notes:

1. The maximum length of an SQL statement is 8 192 characters

2. This restriction also applies to Fortran IF and ELSE statements

3. A statement is terminated by another statement or by end-of-file.

Placing Data Statements

The Fortran Release 3.0 compiler restricts the placement of data statements in

Fortran programs or subroutines. Some precaution is necessary in order to

eliminate the following warning message during compilation of the program or

subroutine:

WARNING MSGIFX1935I

DATA STATEMENT PRECEDES AN EXPLICIT TYPE STATEMENT

During preprocessing, the Fortran preprocessor places inline calls at the end of the

DB2 Server for VSE & VM declare section, if one exists; otherwise, the calls are

placed at the beginning of the program or subroutine. These calls contain data

statements that must be preceded by all declares.

If an SQL declare section does not exist, place the following dummy SQL declare

section after all other program declares to avoid the above warning message:

EXEC SQL BEGIN DECLARE SECTION

EXEC SQL END DECLARE SECTION

Since the preprocessor replaces EXEC SQL INCLUDE SQLCA with the declaration of the

SQLCA structure, the SQLCA must be included before the declare section.

The Fortran preprocessor does not recognize a FUNCTION keyword if it is

preceded by a type declaration. The FUNCTION keyword must, therefore, be the

first word in the FUNCTION statement.

Using Fortran Common Areas (DB2 Server for VSE)

For VSE single user mode, items in a Fortran COMMON statement must be

initialized in a BLOCK DATA subroutine and the COMMON statement must be

assigned a name.

Identifying Rules for Case

Mixed case can be used in your Fortran program. The SQL preprocessor will

change the lowercase into uppercase, except for text within quotation marks, which

will be left in the original case.

Declaring Host Variables

Host variables must be explicitly declared to be used in SQL statements. The

following example shows an SQL declare section for a Fortran program:

EXEC SQL BEGIN DECLARE SECTION

(at beginning of section)

(Data description entries for host variables)

EXEC SQL END DECLARE SECTION

(at end of section)

Appendix D. Using SQL in Fortran

373

Place the data description entries for all the host variables within the SQL declare

sections. You may use the variables appearing in these SQL declare sections in

regular Fortran statements as well as in SQL statements.

A host variable declared within the SQL DECLARE SECTION may not be

continued. The host variable declaration must appear on a single line in order to

be recognized by the preprocessor.

You can also place data description entries for non-host variables in the SQL

declare section as the Fortran preprocessor ignores data description entries within

the SQL declare section that it does not recognize as valid host variable

declarations. No error message is generated; instead, the statement is left for the

Fortran compiler to process. Thus it is possible, though not recommended, to place

all data description entries within an SQL declare section.

The rules for declaring variables within SQL declare sections are:

Host variables must be valid Fortran variable names according to the version of

the Fortran compiler that is being used. Fortran host variable names are

restricted to a length of 18 bytes.

Variables named in the SQL declare sections must have data descriptions like

those in Table 41 on page 380.

Variables cannot be any of the following:

- Vector or array declarations

- Constants defined by a PARAMETER statement

- Any declarations that use expressions to define the length of the variables

- Character variables declared with an undefined length, such as

CHARACTER*(*).

You should not give any variable a name beginning with SQL, because these

names are reserved for database manager use.

When host variables are declared as INTEGER, and you are using the

OPTIMIZE(2) or OPTIMIZE(3) compile option, the host variables should be

declared as COMMON.

Under OPTIMIZE 2 or 3, Fortran may make register assignments to the program

variables if they are not in COMMON storage. Under some circumstances, this

can result in the database manager using an inaccurate variable value.

In the following example, NUM must be declared as COMMON if OPTIMIZE 2

or 3 is specified:

EXEC SQL DECLARE CURSOR C1 FOR INSERT INTO T1 VALUES (:NUM)

EXEC SQL OPEN C1

DO 20

NUM=1,10

EXEC SQL PUT C1

CONTINUE

EXEC SQL CLOSE C1

Only the NONE value of the AUTODBL Fortran compile option is supported.

AUTODBL changes the precision of declared variables without altering the

source code. The preprocessor runs before the Fortran compiler and interprets

variable types based strictly on their declaration.

A host variable must be declared earlier than the first use of the variable in an SQL

statement in the program.

374

Application Programming

Embedding SQL Statements

You must precede each SQL statement in your program with EXEC SQL. No

delimiter should be used at the end of each statement.

Fortran source statements and SQL statements cannot be contained on the same

line or within the same continued statement, except when an SQL statement is

used as the imperative statement of a logical IF. Also, only one SQL statement can

be contained in a single line, or within the same continued statement.

Using Host Variables in SQL Statements

When you place host variables within an SQL statement, you must precede each

one by a colon (:), to distinguish it from the SQL identifiers (such as a column

name). When you place a host variable outside of an SQL statement, do not use a

colon.

A host variable can represent a data value, but not an SQL identifier. For example,

you cannot assign a character constant such as ‘MUSICIANS’ to a host variable,

and then use that host variable in a CREATE TABLE statement to represent the

table name. This pseudocode sequence is invalid:

IT = ' MUSICIANS '

Incorrect

CREATE TABLE :TT (NAME ...

Using Variable Length Character Strings

Fortran does not support variable length character strings (VARCHAR, LONG

VARCHAR). However, it is possible to circumvent this restriction in the following

way:

1. Declare INTEGER*2 to contain the length of the string

2. Declare a CHARACTER*(length) string of data

3. Declare a CHARACTER*(2 + length of string)

4. Declare a COMMON block containing (1) and (2) above

5. Use the EQUIVALENCE statement (name of (1) above, name of (3) above)

6. Specify a DATA BLOCK subroutine to initialize (1) and (2) above

7. When referencing the string in an SQL statement, use (3) above.

8. After preprocessing the Fortran program (but before compilation), change all

occurrences of the data code for the variable(s) in the input or output data

structure(s) from the CHARACTER data code to the corresponding VARCHAR

or LONG VARCHAR data code. For information on how to interpret the data

codes returned in SQLTYPE, see the DB2 Server for VSE & VM SQL Reference

manual.

Figure 88 on page 376 shows an example of how to INSERT a row into the

INVENTORY table using a VARCHAR variable for description.

Note: It is necessary to set the length field (STRNGL) to the corresponding length

of the character string (STRING) before the insert statement is executed.

When the character string is fetched, the first two bytes of the string (STRNGW)

contain the length. The variable STRNGL determines the length.

Appendix D. Using SQL in Fortran

375

C*** DB2 Server for VSE & VM STATEMENT ***

EXEC SQL BEGIN DECLARE SECTION

CHARACTER ID*8

CHARACTER PW*8

INTEGER*2

STRNGL

CHARACTER*24

STRING

CHARACTER*26

STRNGW

COMMON /SDATA/ STRNGL,STRING

EQUIVALENCE

(STRNGL,STRNGW)

C*** DB2 Server for VSE & VM STATEMENT ***

EXEC SQL END DECLARE SECTION

C*** DB2 Server for VSE & VM STATEMENT ***

EXEC SQL INSERT INTO SQLDBA.ACTIVITY

VALUES(190, ’TSTSYS’,:STRNGW)

SQI002(

1) =

SQI002(

2) = 452 * SQSHHW +

---> Change

452

448

SQI002(

2) = SQLADD(STRNGW)

SQI002(

2) = 0

SQCALL = ’EXECUTE ’

SQSTMT =

−1

SQLTYP = ’0’

SQLCTL(1) = SQLADD ( SQCALL )

SQLCTL(2) = SQLADD ( SQCOLL )

SQLCTL(3) = SQLADD ( SQPROG )

SQLCTL(4) = SQLADD ( SQSTMT )

SQLCTL(5) = SQLADD ( SQI002 )

SQLCTL(6) = 0

SQLCTL(7) = 0

SQLCTL(8) = 8

SQLCTL(9) = SQLADD ( SQLISL )

SQLCTL(10) = SQLADD ( SQLDAT )

SQLCTL(11) = SQLADD ( SQLTIM )

SQLCTL(12) = SQLADD ( SQLCNT )

SQLCTL(13) = SQLADD ( SQLTYP )

CALL ARIFOR ( SQLCTL )

END

*********************************************************************

BLOCK DATA SUBROUTINE

*********************************************************************

BLOCK DATA

COMMON /SDATA/ STRGNL,STRING

INTEGER*2 STRGNL/3/

CHARACTER*24 STRING/’SYSTEM TESTING’/

END

Figure 88. Using a VARCHAR Variable

Using DBCS Characters in Fortran

The rules for the format and use of DBCS characters in SQL statements are the

same for Fortran as for other host languages supported by the system. For a

discussion of these rules, see “Using a Double-Byte Character Set (DBCS)” on page

51.

376

Application Programming

Fortran does not provide a way to define graphic host variables. If you want to

add graphic data to or retrieve it from DB2 Server for VSE & VM tables, you must

execute the affected statements dynamically. By doing so, the data areas that are

referenced by each statement can be described in an SQLDA. In the SQLDA, you

must set the data type of the areas containing graphic data to one of the graphic

data types. (For a discussion of the SQLDA, refer to the DB2 Server for VSE & VM

SQL Reference manual.)

Using the INCLUDE Statement

To include the external secondary input, specify the following at the point in the

source code where the secondary input is to be included:

EXEC SQL INCLUDE text_name

Text_name is the G-Type source member of a VSE library. Text_name is the file name

of a CMS file (with a “FORTCOPY” file type) located on a CMS minidisk accessed

by the user.

Using Fortran Variables in SQL: Data Conversion

Considerations

Host variables must be type-compatible with the columns with which they are to

be used.

A column of type INTEGER, SMALLINT, or DECIMAL is compatible with a

Fortran variable of INTEGER, INTEGER*2, or INTEGER*4. Of course, an overflow

condition may occur if, for example, an INTEGER data item is retrieved into an

INTEGER*2 variable, and its current value is too large to fit.

Fixed-length and varying-length character data (CHAR, VARCHAR, and LONG

VARCHAR) are considered compatible. A varying-length string is automatically

converted to a fixed-length string, and a fixed-length string is automatically

converted to a varying-length string, when necessary. If a varying-length string is

converted to a fixed-length string, it is truncated or padded on the right with

blanks to the correct length.

The database manager also considers the datetime data types to be compatible

with character data types (CHAR and VARCHAR, but not LONG VARCHAR and

VARCHAR > 254).

Refer to “Converting Data” on page 48 for a data conversion summary.

Handling SQL Errors

There are two ways to declare the return code structure (called SQLCA):

1. You may write:

EXEC SQL INCLUDE SQLCA

in your source program. The preprocessor replaces this with the declaration of

the SQLCA structure.

2. You may declare the SQLCA structure directly, as shown in Figure 89 on page

378.

Appendix D. Using SQL in Fortran

377

INTEGER*4

SQLCOD,

SQLERR(6),

SQLTXL*2

COMMON /SQLCA1/ SQLCOD,SQLERR,SQLTXL

CHARACTER

SQLERP*8,

SQLWRN(0:10),

SQLTXT*70,

SQLSTT*5

COMMON /SQLCA2/ SQLERP,SQLWRN,SQLTXT,SQLSTT

Figure 89. SQLCA Structure (in Fortran)

The SQLCA must not be declared within the SQL declare section. The meanings of

the fields within the SQLCA are discussed in the DB2 Server for VSE & VM SQL

Reference manual.

You may find that the only variable in the SQLCA you really need is SQLCODE. If

this is the case, declare just the SQLCOD variable and invoke NOSQLCA support

at preprocessor time.

Note: Fortran requires SQLCOD instead of SQLCODE.

The number of SQLCOD declarations is not limited by the preprocessor. If a

stand-alone SQLCOD is specified, the code inserted by the preprocessor into the

Fortran code to expand an EXEC SQL statement will refer to the address of that

SQLCOD. The Fortran compiler determines if multiple declarations within a

program section are not acceptable. In addition, the Fortran compiler determines

which region of the code an SQLCOD declaration refers to.

Handling Program Interrupts

If a program interrupt occurs and the database manager is unaware of it, you may

get unexpected results. To allow the system to process the interrupt, specify the

run time options NOSTAE and NOSPIE. These options are only available in

Version 2 of Fortran.

Using Dynamic SQL Statements in Fortran

The Fortran preprocessor lets you use a descriptor area, the SQLDA, to execute

dynamically defined SQL statements. (See Chapter 7, “Using Dynamic Statements,”

on page 215 for information on dynamic SQL statements and the SQLDA.)

However, the Fortran preprocessor will not replace the statement EXEC SQL INCLUDE

SQLDA with a declaration of the SQLDA structure, as is done with the SQLCA.

Instead EXEC SQL INCLUDE SQLDA would just include the secondary input file

SQLDA, as described in the section “Using the INCLUDE Statement” on page 377.

Before you can use the descriptor area you must properly allocate and initialize it,

and you must manage all its address variables. The following example shows how

you could define the descriptor area in Fortran for three fields:

378

Application Programming

CHARACTER*8

DAID

INTEGER*4

DABC

INTEGER*2

DASQLN,

DAD,

DATYPE_1, DATYPE_2, DATYPE_3,

DALEN_1, DALEN_2, DALEN_3,

DANLN_1, DANLN_2, DANLN_3

INTEGER*4

DADATA_1, DADATA_2, DADATA_3,

DAIND_1, DAIND_2, DAIND_3

CHARACTER*30 DANAME_1, DANAME_2, DANAME_3

COMMON /DASQL/ DAID, DABC, DAN, DAD,

* DATYPE_1, DALEN_1, DADATA_1, DAIND_1, DANLN_1, DANAME_1,

* DATYPE_2, DALEN_2, DADATA_2, DAIND_2, DANLN_2, DANAME_2,

* DATYPE_3, DALEN_3, DADATA_3, DAIND_3, DANLN_3, DANAME_3

The descriptor area must not be declared within the SQL declare section.

The following pseudocode illustrates the use of a descriptor area, adequate for

three fields:

- allocate storage for a Descriptor Area of at least size = 3

- set DAN = 3

(number of fields)

- set DAD = 3

- set the rest of the values and pointers in the Descriptor Area

EXEC SQL EXECUTE S1 USING DESCRIPTOR dasql

Restrictions When Using the Fortran Preprocessor

The Fortran preprocessor is an extended dynamic preprocessor that uses the

NOMODIFY and DESCRIBE options of the extended CREATE PACKAGE

statement. The other extended CREATE PACKAGE options that are used are taken

from the parameters specified when invoking the preprocessor.

Fortran programs are preprocessed and executed using extended dynamic SQL.

Those that are preprocessed with the DB2 Server for VSE & VM Fortran

preprocessor must, therefore, comply with the same restrictions that apply to

extended dynamic SQL, or programs preprocessed or executed using extended

dynamic SQL.

The following is a partial list of restrictions when using the Fortran preprocessor.

v The BIND preprocessing parameter is ignored by the Fortran preprocessor. (DB2

Server for VSE)

v When declaring a dynamic cursor, if you are using the following format of the

PREPARE statement, you must code it in your program before the DECLARE

CURSOR statement:

PREPARE statement_name FROM string_constant

This restriction does not apply when using the following format of the

PREPARE statement:

PREPARE statement_name FROM host_variable

v When using DRDA protocol, the following statements are not supported:

SELECT INTO

Positioned UPDATE

Appendix D. Using SQL in Fortran

379

Positioned DELETE

When switching between SQLDS protocol and DRDA protocol, you cannot do

the following:

- Preprocess a program using one protocol and then execute it using another

protocol.

- Preprocess a DB2 Server for VM program using one protocol, and then

repreprocess the program using another protocol. If the original program is

dropped with the DROP PACKAGE statement, you can repreprocess the

program using a different protocol.

DB2 Server for VM

Note: If the PROTOCOL option on the application requester is set to AUTO,

the system uses SQLDS protocol to communicate with another DB2

Server for VM application server, and uses DRDA protocol to

communicate with unlike application servers. The system uses DRDA

protocol to communicate with another DB2 Server for VM application

server only when the PROTOCOL option on the application requester

is set to DRDA protocol. The PROTOCOL option is set and queried

using the SQLINIT command.

Refer to “Mapping Extended Dynamic Statements to Static and Dynamic

Statements” on page 253 for details about mapping extended dynamic

statements to non-extended dynamic statements. Refer to the DB2 Server for

VSE & VM SQL Reference for a discussion of DRDA restrictions.

Defining DB2 Server for VSE & VM Data Types for Fortran

Table 41. DB2 Server for VSE & VM Data Types for Fortran

DB2 Server for VSE

Equivalent Fortran

Description

& VM Keyword

Declaration

A binary integer of 31 bits, plus sign.

INTEGER or INT

INTEGER

INTEGER*4

A binary integer of 15 bits, plus sign.

SMALLINT

INTEGER*2

A packed decimal number, precision p, scale s (1 ≤

DECIMAL[(p[,s])]

Not supported.

p ≤ 31 and 0 ≤ s ≤p). In storage the number

occupies a maximum of 16 bytes. Precision is the

or DEC[(p[,s])]¹

total number of digits. Scale is the number of those

digits that are to the right of the decimal point.

A single-precision (4- byte) floating-point number,

REAL or

REAL

in short System/390 floating-point format.

FLOAT(p),

REAL*4

1 ≤ p ≤ 21

A double-precision (8- byte) floating-point number,

FLOAT or

REAL*8

in long System/390 floating-point format.

FLOAT(p), 22 ≤ p ≤ 53

DOUBLE PRECISION

or DOUBLE PRECISION

DOUBLEPRECISION

A fixed-length character string of length n where 0

CHARACTER[(n)]

CHARACTER

< n ≤ 254.

or CHAR[(n)]

CHARACTER*n

380

Application Programming

Table 41. DB2 Server for VSE & VM Data Types for Fortran (continued)

DB2 Server for VSE

Equivalent Fortran

Description

& VM Keyword

Declaration

A varying-length character string of maximum

VARCHAR(n)

Not supported.

length n. If n > 254 but ≤ 32767, this data type is

considered a long field. (See “Using Long Strings”

on page 45 for more information.)

A varying-length character string of maximum

LONG VARCHAR

Not supported.

length 32765 bytes (two bytes less than the DB2

Server for VSE & VM maximum, because of the

length field). (Character strings ≥ 255 are not

supported in Fortran releases prior to Release 1.3.)

A fixed-length string of n DBCS characters where 0

GRAPHIC[(n)]

Not supported.

< n ≤ 127.

A varying-length string of n DBCS characters. If n

VARGRAPHIC(n)

Not supported.

> 127 but ≤ 16383, this data type is considered a

long field. (See “Using Long Strings” on page 45

for more information.)

A varying-length string of DBCS characters of

LONG VARGRAPHIC

Not supported.

maximum length 16383.

A fixed-length character string representing a date.

DATE

CHARACTER

The minimum and maximum lengths vary with

both the format used and whether it is an input or

CHARACTER*n

output operation. See the DB2 Server for VSE & VM

SQL Reference manual for more information.

No varying-length equivalent

is supported.

A fixed-length character string representing a time.

TIME

CHARACTER

The minimum and maximum lengths vary with

both the format used and whether it is an input or

CHARACTER*n

output operation. See the DB2 Server for VSE & VM

SQL Reference manual for more information.

No varying-length equivalent

is supported.

A fixed-length character string representing a

TIMESTAMP

CHARACTER

timestamp. The lengths can vary on input and

output. See the DB2 Server for VSE & VM SQL

CHARACTER*n

Reference manual for more information.

No varying-length equivalent

is supported.

Notes:

1. NUMERIC is a synonym for DECIMAL and can be used when creating or

altering tables. In such cases, however, the CREATE or ALTER function

establishes the column (or columns) as DECIMAL.

An * length specification can also be used to override a length specification

associated with the initial keyword. The following are examples:

Specification

Valid

Invalid (ignored)

INTEGER VAR001,VAR002(2)

VAR001 4 bytes

VAR002

INTEGER*2 VAR001*4,VAR002

VAR001 4 bytes VAR002 2

bytes

INTEGER*4 VAR001*2/10/,VAR002*4

VAR001 2 bytes VAR002 4

bytes

Appendix D. Using SQL in Fortran

381

Specification

Valid

Invalid (ignored)

INTEGER*5 VAR001*2,VAR002*4

VAR001,VAR002

REAL VAR001*8,VAR002

VAR001 8 bytes VAR002 4

bytes

REAL*8 VAR001,VAR002*4,VAR003

VAR001 8 bytes VAR002 4

bytes VAR003 8 bytes

DOUBLE PRECISION VAR001,VAR002*4

VAR001 8 bytes VAR002 4

bytes

REAL*8 VAR001(10,10)*4,VAR002

VAR002 8 bytes

VAR001

REAL*16 VAR001,VAR002*4,VAR003*8

VAR002 4 bytes VAR003 8

VAR001

bytes

CHARACTER VAR1,VAR2*80

VAR1

1 byte

VAR2

80 bytes

CHARACTER*10 VAR1,VAR2*80

VAR1 10 bytes VAR2 80

bytes

CHARACTER*500 VAR1(5),VAR2*1

VAR2 1 byte

VAR1

382

Application Programming

DB2 Server for VSE & VM Application Programming (Version 7 Release 5) - page 8