Error handling in embedded SQL for C/C++ on IBM z/ OS systems Francesco Cassullo ([email protected]) Software Developer IBM 08 January 2013 Igor Todorovski ([email protected]) Software Developer IBM This guide to detecting SQL errors in C/C++ applications with embedded SQL on the z/OS platform includes sample code for performing simple error-checking and a more advanced technique for more in-depth error analysis. All material is based on and tested with IBM z/OS DB2 Versions 8 to 10 and z/OS XL C/C++ compilers V1R11 to V1R13. Introduction to SQL error handling The SQL communication area (SQLCA) is essential to determine the status of any SQL statement. The SQLCA is a C-style struct that stores data on the execution status of an SQL statement. The IBM® DB2® database populates it with data on the most recently executed SQL statement. To declare SQLCA into your application, you must include this statement: EXEC SQL INCLUDE SQLCA; From the members of the SQLCA struct, you can extract details of the most recently executed SQL statement. SQLCODE and SQLSTATE are some of the more important members. SQLCODE stores the return code of the SQL statement. This value represents the error code of an SQL statement reported by DB2. The codes follow the pattern shown in Table 1. Table 1. SQLCODE representations Error codes Behavior SQLCODE < 0 Execution failed SQLCODE = 0 Execution succeeded SQLCODE > 0 Execution succeeded with a warning DB2 places each SQL error into specific groups. SQLSTATE stores which group the error belongs to. As with SQLCODE, a value of 0 means that the SQL statement executed successfully. © Copyright IBM Corporation 2013 Error handling in embedded SQL for C/C++ on IBM z/OS systems Trademarks Page 1 of 9 developerWorks® ibm.com/developerWorks/ SQLCA stores data only on the most recently executed SQL statement, and it gets updated with each subsequent SQL statement. Therefore, to validate the status of your SQL statements, you need to check the status of SQLCODE and SQLSTATE after each call. Listing 1 shows an example of how to do this. Listing 1. Basic use of SQLCODE and SQLSTATE EXEC SQL INSERT INTO PRODUCT VALUES (11, 'RED', '0098'); if (sqlca.sqlcode != 0) { printf("SQL ERROR CODE = %d\n", sqlca.sqlcode); printf("SQL ERROR CLASS = %s\n", sqlca.sqlstate); } If, for example, this INSERT statement is executed and table PRODUCT does not exist, you will see this output: SQL ERROR CODE = -204 SQL ERROR CLASS = 42704 The "DB2 codes information center" cited in Resources for z/OS state that SQL error - 204 indicates that PRODUCT does not exist in the database. The description for SQLSTATE 42704 also describes this type of error. Using the first two digits of the SQLSTATE, you can extract the error class. In this case, the class is 42 and represents a Syntax Error or Access Rule Violation. The Access Rule Violation is applicable because PRODUCT does not exist. See the "DB2 application programming and SQL" guide cited in Resources for details on other members of SQLCA that you might find useful. Listing 2. Sample code for basic error handling #include <stdio.h> EXEC SQL INCLUDE SQLCA; int main(void) { int status = 55; /* Create Table */ EXEC SQL CREATE TABLE TABLE_1 ( COLOUR CHAR(6) NOT NULL, ID INTEGER NOT NULL ) IN DATABASE DSNUCOMP; /* Check for SQL error on Create Table */ if (sqlca.sqlcode != 0) { printf(">> SQLCODE = %d SQLSTATE = %s: in file %s on line #%d -- %s <<\n", \ sqlca.sqlcode, sqlca.sqlstate, __FILE__, __LINE__, "Failed to CREATE"); status = -1; } /* Insert row into table. Note this SQL command is purposely wrong to trigger an SQL error at run-time. */ EXEC SQL INSERT INTO TABLE_1 VALUES ('RED'); /* Check for SQL error on Insert */ if (sqlca.sqlcode != 0) Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 2 of 9 ibm.com/developerWorks/ developerWorks® { printf(">> SQLCODE = %d SQLSTATE = %s: in file %s on line #%d -- %s <<\n", \ sqlca.sqlcode, sqlca.sqlstate, __FILE__, __LINE__, "Failed to INSERT"); status = -1; } /* Drop Table */ EXEC SQL DROP TABLE TABLE_1; /* Check for SQL error on Drop Table */ if (sqlca.sqlcode != 0) { printf(">> SQLCODE = %d SQLSTATE = %s: in file %s on line #%d -- %s <<\n", \ sqlca.sqlcode, sqlca.sqlstate, __FILE__, __LINE__, "Failed to DROP"); status = -1; } return status; } A better way to handle errors The previous method is easy to use but provides the programmer with only an error code, which is not very helpful unless you look it up in the DB2 codes information center (see Resources). A more complete error handler gives the programmer more feedback on the error, such as the error message. The DSNTIAR DB2 routine is a module that uses the SQLCA struct to produce a complete SQL diagnostic of an erroneous SQL statement. It provides the SQLCODE, SQLSTATE, error message, and so on. For more information on DSNTIAR, see the "SQL statements in C programs" topic in the DB2 application programming instructions. This is the basic form for calling it: rc = DSNTIAR(&sqlca, &error_message, &line_length); Table 2. Description of DSNTIAR function arguments Variable Description sqlca The SQLCA struct error_message A special struct that stores the complete SQL diagnostic message line_length The row length of error_message error_message is a special struct that contains two members: length and msg: It must be defined by the user in a manner that conforms to this structure for compliance: msg A two-dimensional character array that stores the message output from DB2 length The size of the msg array, which informs DB2 to write a maximum of length characters into msg The dimensions of msg are controlled by data_len and data_dim. data_len is the length of each line and data_dim is the number of lines to store. The sample code in Listing 3 uses 200 and 10 for data_len and data_dim respectively. These can be manipulated to user preference, but keep in Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 3 of 9 developerWorks® ibm.com/developerWorks/ mind data_len must be between 72 and 240 characters. Therefore, the length of msg is data_len * data_dim. Listing 3. Defining storage for the error messages #define data_len 200 #define data_dim 10 struct SQL_error_block { short int length; char text[data_len][data_dim]; } error_message = {data_len * data_dim}; For the linker to find the DSNTIAR module, you need to include the code in Listing 4. Listing 4. Including DSNTIAR module into your application #ifdef __cplusplus extern "OS" #endif short int DSNTIAR(struct sqlca *sqlca, struct SQL_error_block *error_message, \ int *msg_len); #ifndef __cplusplus #pragma linkage (DSNTIAR,OS) #endif error_message requires some massaging to attain a user-friendly format. The sample header in Listing 5 defines an easy-to-use function that checks the status of an SQL statement and outputs a well-formatted diagnostic message if it fails. Listing 5. Sample header for reporting detailed SQL error messages #include <stdio.h> #include <string.h> #include <ctype.h> EXEC SQL INCLUDE SQLCA; void trim_sql_error_message(char *sql_full_msg); #define data_len 200 #define data_dim 10 int sql_rc = 55; struct SQL_error_block { short int length; char text[data_len][data_dim]; } error_message = {data_len * data_dim}; #ifdef __cplusplus extern "OS" #endif short int DSNTIAR(struct sqlca *sqlca, struct SQL_error_block *error_message, \ int *msg_len); #ifndef __cplusplus #pragma linkage (DSNTIAR,OS) #endif /* Checks the return code of the SQL command and fetches the SQL message if erroneous ARG1 is the SQLCA struct ARG2 is used to store the formatted SQL message Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 4 of 9 ibm.com/developerWorks/ developerWorks® Returns 0 if there is no error Returns -1 if there is an SQL error that is properly handled Returns -2 if DSNTIAR function fails */ int error_handler(struct sqlca *sqlca, char *sql_full_msg) { short rc = 0; int lrecl = data_len; if (sqlca->sqlcode != 0) { rc = DSNTIAR(sqlca, &error_message, &lrecl); if (rc != 0) { printf("DSNTIAR ERROR: call failed with RC = %d\n", rc); return -2; } error_message.text[data_len - 1][data_dim - 1] = '\0'; trim_sql_error_message(sql_full_msg); return -1; } else return 0; } #define CHECK_ERROR(sqlca, error_msg) \ if (error_handler(sqlca, error_msg) != 0) { \ printf("ERROR: in file %s on line %d\n", __FILE__, __LINE__); \ printf("%s\n", error_msg); \ sql_rc = -1; \ } /* Format the SQL message block into a readable string ARG1 is used to store the formatted message */ void trim_sql_error_message(char *sql_full_msg) { int i = 0, j = 0, pos = 0, space_count = 0; for (i = 0; i < data_len; i++) { for (j = 0; j < data_dim; j++) { if ( isspace(error_message.text[i][j]) ) space_count++; else space_count = 0; if (space_count <= 2) { sql_full_msg[pos] = error_message.text[i][j]; pos++; } } } } The sample program in Listing 6 demonstrates how to check for SQL errors and call the error handler function. Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 5 of 9 developerWorks® ibm.com/developerWorks/ Listing 6. Sample code for calling the detailed SQL error handler function #include <stdio.h> #include "dsntiar.h" #define BUF_SIZE 2000 int main() { char sql_error_log[BUF_SIZE]; /* Create Table */ EXEC SQL CREATE TABLE COLOUR_TABLE ( COLOUR CHAR(6) NOT NULL, ID INTEGER NOT NULL ) IN DATABASE DSNUCOMP; /* Check for and output any SQL errors from attempting to create the table */ CHECK_ERROR(&sqlca, sql_error_log); /* Insert row into table. Note this SQL command is purposely wrong to trigger an SQL error at runtime. */ EXEC SQL INSERT INTO COLOUR_TABLE VALUES ('RED'); /* Check for SQL error on Insert */ CHECK_ERROR(&sqlca, sql_error_log); /* Drop Table */ EXEC SQL DROP TABLE COLOUR_TABLE; /* Check for and output any SQL errors from attempting to drop the table */ CHECK_ERROR(&sqlca, sql_error_log); return sql_rc; } Acknowledgements The authors thank the following individuals who helped make this article possible: Zibi Sarbinowski, Rajan Bhakta, and Kobi Vinayagamoorthy. Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 6 of 9 ibm.com/developerWorks/ developerWorks® Downloads Description Name Size DSNTIAR dsntiar.zip 7KB SQL code sqlcode.zip 5KB Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 7 of 9 developerWorks® ibm.com/developerWorks/ Resources Learn • For relevant DB2 database documentation, see: • DB2 10 for z/OS Application programming and SQL information center (SC19-2969-04), including the SQL statements in C programs topic • DB2 10 for z/OS Codes (GC19-2971-04) • For details on the compiler, see "Using DB2 Universal Database" in the XL C/C++ Programming Guide (SC09-4765-12). • For details on the C/C++ language, see XL C/C++ Language Reference (SC09-4815-11). • Explore the Rational software area on developerWorks for technical resources, best practices, and information about Rational collaborative and integrated solutions for software and systems delivery. • Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics. • Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools, as well as IT industry trends. • Watch developerWorks on-demand demos, ranging from product installation and setup demos for beginners to advanced functionality for experienced developers. Get products and technologies • Download a free trial version of Rational software. • Evaluate other IBM software in the way that suits you best: Download it for a trial, try it online, use it in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement service-oriented architecture efficiently. Discuss • Check out the Rational C/C++ Café community. • Get connected with your peers and keep up on the latest information in the Rational community. • Join the Rational software forums to ask questions and participate in discussions. • Ask and answer questions and increase your expertise when you get involved in the Rational forums, cafés, and wikis. • Rate or review Rational software. It's quick and easy. Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 8 of 9 ibm.com/developerWorks/ developerWorks® About the authors Francesco Cassullo Francesco Cassullo is a software developer in the IBM XL Compilers group. He has been at IBM since 2008 and has worked on Fortran, C/C++, and COBOL compilers. Igor Todorovski Igor Todorovski is a software developer in the IBM XL Compilers group. He has been at IBM since 2008 and specializes in z/OS C/C++ compilers. © Copyright IBM Corporation 2013 (www.ibm.com/legal/copytrade.shtml) Trademarks (www.ibm.com/developerworks/ibm/trademarks/) Error handling in embedded SQL for C/C++ on IBM z/OS systems Page 9 of 9