Programming C and C++ applications that issue SQL statements

You can code SQL statements in a C or C++ program wherever you can use executable statements.

Each SQL statement in a C or C++ program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.

In general, because C is case sensitive, use uppercase letters to enter all SQL keywords. However, if you use the FOLD precompiler suboption, DB2® folds lowercase letters in SBCS SQL ordinary identifiers to uppercase. For information about host language precompiler options, see Table 1.

You must keep the case of host variable names consistent throughout the program. For example, if a host variable name is lowercase in its declaration, it must be lowercase in all SQL statements. You might code an UPDATE statement in a C program as follows:

EXEC SQL
   UPDATE DSN8A10.DEPT
   SET MGRNO = :mgr_num
   WHERE DEPTNO = :int_dept;

Comments

You can include C comments (/* ... */) within SQL statements wherever you can use a blank, except between the keywords EXEC and SQL. You can use single-line comments (starting with //) in C language statements, but not in embedded SQL. You can use SQL comments within embedded SQL statements. You can nest comments.

To include EBCDIC DBCS characters in comments, you must delimit the characters by a shift-out and shift-in control character; the first shift-in character in the DBCS string signals the end of the DBCS string.

Continuation for SQL statements

You can use a backslash to continue a character-string constant or delimited identifier on the following line. However, EBCDIC DBCS string constants cannot be continued on a second line.

Delimiters

Delimit an SQL statement in your C program with the beginning keyword EXEC SQL and a Semicolon (;).

Declaring tables and views

Your C program should use the DECLARE TABLE statement to describe each table and view the program accesses. You can use the DB2 declarations generator (DCLGEN) to generate the DECLARE TABLE statements. For more information, see DCLGEN (declarations generator).

Including SQL statements and variable declarations in source code that is to be processed by the DB2 precompiler

To include SQL statements or C host variable declarations from a member of a partitioned data set, add the following SQL statement to the source code where you want to include the statements:

EXEC SQL INCLUDE member-name;

You cannot nest SQL INCLUDE statements. Do not use C #include statements to include SQL statements or C host variable declarations.

Margins

Code SQL statements in columns 1 through 72, unless you specify other margins to the DB2 precompiler. If EXEC SQL is not within the specified margins, the DB2 precompiler does not recognize the SQL statement. The margin rules do not apply to the DB2 coprocessor. The DB2 coprocessor allows variable length source input.

Names

You can use any valid C name for a host variable, subject to the following restrictions:

Do not use DBCS characters.
Do not use external entry names or access plan names that begin with 'DSN', and do not use host variable names or macro names that begin with 'SQL' (in any combination of uppercase or lowercase letters). These names are reserved for DB2.

Nulls and NULs

C and SQL differ in the way they use the word null. The C language has a null character (NUL), a null pointer (NULL), and a null statement (just a semicolon). The C NUL is a single character that compares equal to 0. The C NULL is a special reserved pointer value that does not point to any valid data object. The SQL null value is a special value that is distinct from all non-null values and denotes the absence of a (nonnull) value. NUL (or NUL-terminator) is the null character in C and C++, and NULL is the SQL null value.

Sequence numbers

The DB2 precompiler generates statements without sequence numbers. (The DB2 coprocessor does not perform this action, because the source is read and modified by the compiler. )

Statement labels

You can precede SQL statements with a label.

Trigraph characters

Some characters from the C character set are not available on all keyboards. You can enter these characters into a C source program using a sequence of three characters called a trigraph. The trigraph characters that DB2 supports are the same as those that the C compiler supports.

WHENEVER statement

The target for the GOTO clause in an SQL WHENEVER statement must be within the scope of any SQL statements that the statement WHENEVER affects.

Special C/C++ considerations

Using the C/370™ multi-tasking facility, in which multiple tasks execute SQL statements, causes unpredictable results.
Except for the DB2 coprocessor, you must run the DB2 precompiler before running the C preprocessor.
Except for the DB2 coprocessor, DB2 precompiler does not support C preprocessor directives.
If you use conditional compiler directives that contain C code, either place them after the first C token in your application program, or include them in the C program using the #include preprocessor directive.

Refer to the appropriate C documentation for more information about C preprocessor directives.

To use the decimal floating-point host data type, you must do the following:

Use z/OS® 1.10 or above (z/OS V1R10 XL C/C++ ).
Compile with the C/C++ compiler option, DFP.
Specify the SQL compiler option to enable the DB2 coprocessor.
Specify C/C++ compiler option, ARCH(7). It is required by the DFP compiler option if the DFP type is used in the source.
Specify 'DEFINE(__STDC_WANT_DEC_FP__)' compiler option.

Handling SQL error return codes in C or C++

You can use the subroutine DSNTIAR to convert an SQL return code into a text message. DSNTIAR takes data from the SQLCA, formats it into a message, and places the result in a message output area that you provide in your application program. For concepts and more information about the behavior of DSNTIAR, see Displaying SQLCA fields by calling DSNTIAR.

You can also use the MESSAGE_TEXT condition item field of the GET DIAGNOSTICS statement to convert an SQL return code into a text message. Programs that require long token message support should code the GET DIAGNOSTICS statement instead of DSNTIAR. For more information about GET DIAGNOSTICS, see Checking the execution of SQL statements by using the GET DIAGNOSTICS statement.

DSNTIAR syntax

rc = DSNTIAR(&sqlca, &message, &lrecl);

The DSNTIAR parameters have the following meanings:

&sqlca

An SQL communication area.

&message

An output area, in VARCHAR format, in which DSNTIAR places the message text. The first halfword contains the length of the remaining area; its minimum value is 240.

The output lines of text, each line being the length specified in &lrecl, are put into this area. For example, you could specify the format of the output area as:

#define data_len 132
#define data_dim 10
int length_of_line = data_len ; 
struct error_struct {
   short int error_len;
   char error_text[data_dim][data_len];
   } error_message = {data_dim * data_len};
⋮
rc = DSNTIAR(&sqlca, &error_message, &length_of_line);

where error_message is the name of the message output area, data_dim is the number of lines in the message output area, and data_len is the length of each line.

&lrecl

A fullword containing the logical record length of output messages, between 72 and 240.

To inform your compiler that DSNTIAR is an assembler language program, include one of the following statements in your application.

For C, include:

#pragma linkage (DSNTIAR,OS)

For C++, include a statement similar to this:

extern "OS" short int DSNTIAR(struct sqlca *sqlca,
                              struct error_struct *error_message,
                              int *data_len);

Examples of calling DSNTIAR from an application appear in the DB2 sample C program DSN8BD3 and in the sample C++ program DSN8BE3. Both are in the library DSN8A10.SDSNSAMP. See Sample applications supplied with DB2 for z/OS for instructions on how to access and print the source code for the sample programs.

CICS: If your CICS application requires CICS storage handling, you must use the subroutine DSNTIAC instead of DSNTIAR. DSNTIAC has the following syntax:

rc = DSNTIAC(&eib, &commarea, &sqlca, &message, &lrecl);

DSNTIAC has extra parameters, which you must use for calls to routines that use CICS commands.

&eib: EXEC interface block
&commarea: communication area

For more information on these parameters, see the appropriate application programming guide for CICS. The remaining parameter descriptions are the same as those for DSNTIAR. Both DSNTIAC and DSNTIAR format the SQLCA in the same way.

You must define DSNTIA1 in the CSD. If you load DSNTIAR or DSNTIAC, you must also define them in the CSD. For an example of CSD entry generation statements for use with DSNTIAC, see job DSNTEJ5A.

The assembler source code for DSNTIAC and job DSNTEJ5A, which assembles and link-edits DSNTIAC, are in the data set prefix.SDSNSAMP.

C and C++ programming examples
You can write DB2 programs in C and C++. These programs can access a local or remote DB2 subsystem and can execute static or dynamic SQL statements. This information contains several such programming examples.
Defining the SQL communications area, SQLSTATE, and SQLCODE in C
C and C++ programs that contain SQL statements can include an SQL communications area (SQLCA) to check whether an SQL statement executed successfully. Alternatively, these programs can declare individual SQLCODE and SQLSTATE host variables.
Defining SQL descriptor areas in C
If your program includes certain SQL statements, you must define at least one SQL descriptor area (SQLDA). Depending on the context in which it is used, the SQLDA stores information about prepared SQL statements or host variables. This information can then be read by either the application program or DB2.
Declaring host variables and indicator variables in C
You can use host variables, host variable arrays, and host structures in SQL statements in your program to pass data between DB2 and your application.
Equivalent SQL and C data types
When you declare host variables in your C programs, the precompiler uses equivalent SQL data types. When you retrieve data of a particular SQL data type into a host variable, you need to ensure that the host variable is of an equivalent data type.

Related concepts:

Using host variable arrays in SQL statements

Including dynamic SQL in your program

Handling SQL error codes

Setting limits for system resource usage by using the resource limit facility