DB2 Server for VSE & VM: Application Programming

Rules for Using SQL in C

Placing and Continuing SQL Statements

All statements in your C program, including SQL statements, must be contained in columns 1 through 72 of your source file. Columns 73 through 80 can also be used if the NOSEQuence C preprocessor option is specified; if NOSEQuence is not specified, or if SEQuence is specified, these columns will be ignored by the preprocessor. If NOSEQuence is used, the NOSEQ and MARGINS(1,80) C compiler options must be used to compile the application program.

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

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.

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:

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" and "Using Host Structures".
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:

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:

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

  1. 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;
 
    ...
}
  2. 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 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.

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.

Figure 111. Warning Flags after Character Truncation

 


Condition
SQLCA
SQLWARN0
SQLCA
SQLWARN1
Output Indicator
Variable
(if supplied)

Character string, including the NUL, fits in the declared C character array. blank blank 0 (zero)
Actual data truncated. That is, the C variable declared as less than or equal to n, to hold a character value of actual length n. W W Original length of value (n) excluding the NUL.

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 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).

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 Figure 112. 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.

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.

Figure 112. 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
    #define SQLVARCHAR(varname,nnn) \
       struct {                     \
               short sqllen;        \
               char  sqlstr[nnn];   \
       } varname
  #endif

Macro that can be used by the application to simplify the C program. The definition of this macro can be changed by including a #define statement before the first non-precompiler directive C statement or SQL statement in your program. 
  typedef struct {
                 short CALLTYPE;
                 char AUTHOR[8];
                 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;

typedefs of allocated control blocks used when translating executable SQL statements into C function calls. 
static long SQLTIE[12];
  static char RDIRDBN[16];
  static struct {
          char RDIEXTEC[8];
          long RDIEXTFLR;
          char *RDIDBNMP;
          char *RDICONSP;
          char *RDIBPOPT;
          char *RDIXPTRS[6];
  } SQLRDIX;

Communication areas used to save information about the state of the C program between run-time calls to the database manager.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]