Error handling in embedded SQL for C/C++ on IBM z/OS systems

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.

Share:

Francesco Cassullo (cassullo@ca.ibm.com), Software Developer, IBM

Author photoFrancesco 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 (itodorov@ca.ibm.com), Software Developer, IBM

Author1 photoIgor 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.



08 January 2013

Also available in Vietnamese

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

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

  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.

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.


Downloads

DescriptionNameSize
DSNTIARdsntiar.zip7KB
SQL codesqlcode.zip5KB

Resources

Learn

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=853013
ArticleTitle=Error handling in embedded SQL for C/C++ on IBM z/OS systems
publish-date=01082013