C and C++ C is an extremely flexible and very common mainframe programming language that is currently utilized for a variety of business and scientific applications. C can be used in either online or batch programs. C programs can run in any OS/390 address space, can execute under a variety of transaction managers such as CICS, and can be used as DB2 stored procedures or external user -defined functions. C API DB2 provides a C API that allows you to embed static or dynamic SQL into a C program. You can code SQL statements in a C program anywhere in which you can code an executable statement. Embedding SQL in a C Program Each SQL statement in a C program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear on the same line of the program, after which the rest of a statement can appear on any number of lines. In general, because C is case-sensitive, use uppercase letters to enter SQL words. You must keep the case of host variable names consistent throughout the program. For example, if a host variable name is in lowercase in its declaration, then it must be in lowercase in all SQL statements. Here is an example of an embedded SQL statement in a C program: EXEC SQL INSERT INTO YLA.TABLE1 VALUES(:var1, :var2, DEFAULT); A C program that contains SQL statements must include either a SQLCODE or SQLSTATE host variable, or both. The program can either define these variables or you can use an INCLUDE SQLCA statement to include the SQLCA within the program. The SQLCA structure contains both the SQLSTATE and SQLCODE variables . The C program should include the statement DECLARE TABLE to describe each table and view the program accesses . You can use the DB2 DCLGEN to generate DECLARE TABLE statements. Refer to Chapter 5 to create the DCLGEN. The INCLUDE SQL statement can be used to include table declarations, host variable declarations, and SQL statements from members of a partitioned dataset at precompile time. You should not use the C++ INCLUDE statement to include SQL statements containing C host variable declarations. Dynamic SQL statements can be coded into C programs. C programs can handle dynamic SQL statements if the data types and the number of fields returned are fixed. If you need to use variable-list SELECT statements in your C program, then you must use a SQLDA within your program. Host Variable Declarations You must explicitly declare each host variable before its first use in a SQL statement. You must precede the C statements that define the host variables with the statement BEGIN DECLARE SECTION and follow the statements with the statement END DECLARE SECTION. You can have more than one host variable declaration section in your program. A colon (:) must precede all host variable references in a SQL statement. There are numerous mappings between SQL data types and the equivalent C variable declarations. Table 12-3 lists only the most typical data type mappings. NOTE  | There is no native decimal C data type. IBM does provide a decimal.h header file to provide compatibility for C programs. This header file does not work for C++ programs. C programmers also typically write their own conversion routines to deal with the decimal data type. However, another common technique is to use the REAL or DOUBLE SQL data types in place of DECIMAL. |
NOTE | | You should always allow for an extra byte of storage for all string host variables greater than a single character to allow for the null-termination. DB2 will insert characters from variable or fixed-length character fields that are less than the length of the host variable, and then append the null-terminator. |
Table 12-3. SQL and C Data Type Mappings | SQL Data Type | C Data Type | | SMALLINT | short int | | INTEGER | long int | | DECIMAL(p,s) | decimal | | NUMERIC(p,s) | decimal | | CHAR(1) | single-character form char | | CHAR(n) | no exact equivalent; if n > 1 use a null- terminated character array of length n + 1: char[n + 1] | | VARCHAR(n) | null-terminated character array length n + 1 or Struct {short len; char data[n]; } var1; | | DATE | null-terminated character array of length 11: char[11] | | TIMESTAMP | null-terminated character array of length 26: char[26] | | REAL | float | | DOUBLE PRECISION | double | In addition to these and many other basic data type mappings, there are several special-purpose C data types, including result set locators used for processing result sets returned from stored procedures, table locators used for accessing transition tables created by a trigger and passed to a user-defined function or a stored procedure, LOB data types, LOB locators used to avoid the materialization of LOBs in an application program, and the ROWID data type. Indicator Variables 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 character is a single character, which compares equal to 0. DB2 will append the NUL on the end of any character array host variable, including those of length 0. The DB2 NULL indicates the absence of a value for a table column. Indicator variables are used in a C program to indicate a null value for an optional DB2 table column. Your C program will have to interpret or set the indicator variable in order to deal with optional columns . The indicator variable is typically set to 0 to indicate the presence of a value and onset to 1 to indicate the null value. You declare indicator variables just as you would a SMALLINT variable in your program. You reference the indicator in your SQL statements immediately following the variable for the optional column to which it relates . For example, EXEC SQL FETCH CURSOR1 INTO :var1 :var1-ind; The above statement fetches a column from a DB2 cursor into the C variable var1, and since the column in this case is optional, the indicator variable is placed in the C variable var1-ind, which is a short integer. The indicator variable can then be tested in program logic or referenced in further SQL statements to determine whether or not the value of var1 is null. Remember, this is a test of the DB2 null value. Error Handling The SQLCODE or SQLSTATE variables should be tested after each SQL statement called. The DSNTIAR program can be used to help interpret SQL errors when detected . The DSNTIAR program is called from your C program with the SQLCA as an input parameter. DSNTIAR then places an English message in an output parameter. This message can then be used as part of a printf of the error in the program. C Program Example The following simple C program processes a single DB2 cursor and displays the result: /********************************************************/ /* This simple c program reads the SYSIBM.SYSDUMMY1 DB2 */ /* catalog table, and displays the results. It also */ /* gets the current date to demonstrate the use of a */ /* null-terminated character array */ /********************************************************/ #include "stdio.h" #include "stdefs.h" void main(void) { EXEC SQL INCLUDE SQLCA; /* Include the host variables */ EXEC SQL BEGIN DECLARE SECTION; char ibmreqd; char cdate[11]; EXEC SQL END DECLARE SECTION; /* Declare the SYSDUMMY1 table */ EXEC SQL DECLARE SYSIBM.SYSDUMMY1 TABLE (IBMREQD CHAR(1) NOT NULL); /* Declare the cursor */ EXEC SQL DECLARE CURS1 CURSOR FOR SELECT IBMREQD, CURRENT DATE FROM SYSIBM.SYSDUMMY1; /* Begin the execution */ EXEC SQL OPEN CURS1; if (SQLCODE != 0) { printf("SQLCODE is %d\n",SQLCODE); return; } while (SQLCODE != 0) { EXEC SQL FETCH CURS1 INTO :ibmreqd, :cdate; if (SQLCODE == 0) { printf("ibmreqd is %c\n",ibmreqd); printf("cdate is %c\n",cdate); } if (SQLCODE != 0 and SQLCODE != 100) { printf("SQLCODE is %d\n",SQLCODE); } } EXEC SQL CLOSE CURS1; return; } | 
