Skip to main content

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

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

All information submitted is secure.

  • Close [x]

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.

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

All information submitted is secure.

  • Close [x]

developerWorks Community:

  • Close [x]

DB2 9 Application Development exam 733 prep, Part 4: Embedded SQL programming

Build applications that interact with DB2

Roger E. Sanders (rsanders@netapp.com), Senior Manager - IBM Alliance Engineering, Network Appliance, Inc.
Author1 photo
Roger E. Sanders is a Senior Manager - IBM Alliance Engineering at Network Appliance, Inc. He has been designing and developing databases and database applications for more than 20 years and has been working with DB2 Universal Database since it was first introduced with OS/2 1.3 Extended Edition. He has written articles for IDUG Solutions Journal, Certification Magazine, and developerWorks, presented and taught classes at IDUG and RUG conferences, participated in the development of the DB2 certification exams, writes a regular column for DB2 Magazine and is the author of 9 books on DB2 UDB.
(An IBM developerWorks Master Author, Level 2)

Summary:  This tutorial introduces you to embedded SQL programming and walks you through how to construct an embedded SQL application. This tutorial introduces the process for converting one or more high-level programming language source code files containing embedded SQL into an executable application. This is the fourth in a series of nine tutorials designed to help you prepare for the DB2 Application Developer Certification exam (Exam 733).

View more content in this series

Date:  15 Feb 2007
Level:  Introductory PDF:  A4 and Letter (521 KB | 33 pages)Get Adobe® Reader®

Comments:  

Diagnostics and error handling

Using the WHENEVER statement

Earlier, we saw that the SQL Communications Area (SQLCA) data structure contains a collection of elements that are updated by the DB2 Database Manager each time an SQL statement is executed. One element of that structure, the sqlcode element, is assigned a value that indicates the success or failure of the SQL statement executed. (A value of 0 indicates successful execution, a positive value indicates successful execution with warnings, and a negative value indicates that an error occurred.) At a minimum, an embedded SQL application should always check the sqlcode value produced (often referred to as the SQL return code) immediately after an SQL statement is executed. If an SQL statement fails to execute as expected, users should be notified that an error or warning condition occurred; in addition, whenever possible they should be provided with diagnostic information sufficient to allow them to locate and correct the problem.

As you might imagine, checking the SQL return code after each statement is executed can add additional overhead to an application, especially one that contains a large number of SQL statements. However, since every SQL statement coded in an embedded SQL application must be processed by the SQL precompiler, it is possible to have the precompiler automatically generate the source code needed to check SQL return codes. This is accomplished by embedding one or more forms of the WHENEVER SQL statement in a source code file.

When used, the WHENEVER statement tells the SQL precompiler to generate source code that evaluates SQL return codes and branches to a specified label whenever an error, warning, or out-of-data condition occurs. (If the WHENEVER statement is not used, the default behavior is to ignore SQL return codes and continue processing as if no problems have been encountered.) Four forms of the WHENEVER statement are available, one for each of the three different types of error/warning conditions the WHENEVER statement can be used to check for, and one to turn error checking off:

  • WHENEVER SQLERROR GOTO [Label]: Instructs the precompiler to generate source code that evaluates SQL return codes and branches to the label specified whenever a negative sqlcode value is generated.

  • WHENEVER SQLWARNING GOTO [Label]: Instructs the precompiler to generate source code that evaluates SQL return codes and branches to the label specified whenever a positive sqlcode value (other than the value 100) is generated.

  • WHENEVER NOT FOUND GOTO [Label]: Instructs the precompiler to generate source code that evaluates SQL return codes and branches to the label specified whenever an sqlcode value of 100 or an sqlstate value of 02000 is generated. (The value 100 is used to indicate that no records were found that matched the selection criteria specified or that the end of a result data set has been reached.)

  • WHENEVER [SQLERROR | SQL WARNING | NOT FOUND] CONTINUE: Instructs the precompiler to ignore the SQL return code and continue with the next instruction in the application.

A source code file can contain any combination of these four forms of the WHENEVER statement, and the order in which the first three forms appear is insignificant. However, once any form of the WHENEVER statement is used, the SQL return codes of all subsequent SQL statements executed will be evaluated and processed accordingly until the application ends or until another WHENEVER statement alters this behavior.

Listing 8, written in the C programming language, illustrates how the WHENEVER statement can be used to trap and process out-of-data errors.


Listing 8. Handling errors with the WHENEVER statement
 
...
// Include The SQLCA Data Structure Variable
EXEC SQL INCLUDE SQLCA;

// Set Up Error Handler
EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND_HANDLER;

// Connect To The Appropriate Database 
EXEC SQL CONNECT TO sample USER db2admin USING ibmdb2;     
     
// Execute A SELECT INTO SQL Statement (If A "DATA NOT FOUND" Situation Occurs, 
// The Code Will Branch To The NOT_FOUND_HANDLER Label)
EXEC SQL SELECT empno INTO :EmployeeNo 
    FROM rsanders.employee
    WHERE job = 'CODER';
...

// Disable All Error Handling
EXEC SQL WHENEVER NOT FOUND CONTINUE;

// Prepare To Return To The Operating System
goto EXIT;
 
// Define A Generic "Data Not Found" Handler    
NOT_FOUND_HANDLER:
    printf("NOT FOUND: SQL Code = %d\n", sqlca.sqlcode);
    EXEC SQL ROLLBACK;
    goto EXIT;

EXIT:
    
// Terminate The Database Connection
EXEC SQL CONNECT RESET;
       
// Return Control To The Operating System
return(0);	
        

Unfortunately, the code that is generated when the WHENEVER SQL statement is used relies on GO TO branching instead of call/return interfaces to transfer control to the appropriate error-handling section. As a result, when control is passed to the source code that is used to process errors and warnings, the application has no way of knowing where control came from, nor does it have any way of knowing where control should be returned to after the error or warning has been properly handled. For this reason, about the only thing an application can do when control is passed to a WHENEVER statement error-handling label is to display the error code generated, roll back the current transaction, and return control to the operating system.


The Get Error Message API

Among other things, most editions of DB2 contain a rich set of functions, referred to as the administrative APIs (application programming interfaces), that are designed to provide services other than the data storage, manipulation, and retrieval functionality that SQL provides to DB2 applications. Essentially, any database operation that can be performed from the Command Line Processor by executing a DB2 command can be performed from within an application by calling the appropriate administrative API.

The value assigned to the sqlcode element of the SQLCA data structure variable each time an SQL statement is executed is actually a coded number. Furthermore, a special administrative API, called the Get Error Message API, can be used to translate this coded number into a meaningful description that can then be displayed to the user. The basic syntax used to call this API from a C/C++ high-level programming language source code file is as follows:

sqlaintp (char          *pBuffer,
          short         sBufferSize,
          short         sLineWidth,
          struct sqlca  *pSQLCA);

And the syntax used to call this API from other high-level programming language source code files is:

sqlgintp (short         sBufferSize,
          short         sLineWidth,
          struct sqlca  *pSQLCA,
          char          *pBuffer);
	

Let's take a closer look at the components of the syntax for this API:

  • pBuffer: Identifies a location in memory where the Get Error Message API is to store any message text retrieved.

  • sBufferSize: Identifies the size, in bytes, of the memory storage buffer to which any message text retrieved should be written.

  • sLineWidth: Identifies the maximum number of characters that one line of message text should contain before a line break is inserted. A value of 0 indicates that the entire message text is to be returned without line breaks.

  • pSQLCA: Identifies a location in memory where an SQL Communications Area (SQLCA) data structure variable is stored.

Each time the Get Error Message API is called, the value stored in the sqlcode element of the SQLCA data structure variable provided is used to locate and retrieve appropriate error message text from a message file that is packaged with DB2. Listing 9, written in the C programming language, illustrates how the Get Error Message API would typically be used to obtain and display the message text associated with any SQL return code generated.


Listing 9. Handling errors with the Get Error Message API
         
	
...
// Include The SQLCA Data Structure Variable
EXEC SQL INCLUDE SQLCA;
    
// Declare The Local Memory Variables
long  RetCode = SQL_RC_OK;
char  ErrorMsg[1024];
...

// Perform Some SQL Operation    
...
     
// If An Error Occurred, Obtain And Display Any Diagnostic Information Available
if (sqlca.sqlcode != SQL_RC_OK)
{ 
    // Retrieve The Error Message Text For The Error Code Generated
    RetCode = sqlaintp(ErrorMsg, sizeof(ErrorMsg), 70, &sqlca);
    switch (RetCode)
    {
    case -1:
        printf("ERROR : Insufficient memory.\n");
        break;
    case -3:
        printf("ERROR : Message file is inaccessible.\n");
        break;
    case -5:
        printf("ERROR : Invalid SQLCA, bad buffer, ");
        printf("or bad buffer length specified.\n");
        break;
    default:
        printf("%s\n", ErrorMsg);
        break;
    }
}
...	
	

As you can see in this example, when the Get Error Message API is called, it returns a value that indicates whether or not it executed successfully. In this example, the return code produced is checked; if an error occurred, a message that explains why the API failed is returned to the user. If the API was successful, the appropriate message text is retrieved and returned to the user instead.


SQLSTATEs

In addition to SQL return codes, DB2 (as well as other relational database products) uses a set of error message codes known as SQLSTATEs to provide supplementary diagnostic information for warnings and errors. SQLSTATEs are alphanumeric strings that are five characters (bytes) in length and have the format ccsss, where cc indicates the error message class and sss indicates the error message subclass. Like SQL return code values, SQLSTATE values are written to an element (the sqlstate element) of the SQLCA data structure variable used each time an SQL statement is executed. And just as the Get Error Message API can be used to convert any SQL return code value into a meaningful description, another API -- the Get SQLSTATE Message API -- can be used to convert an SQLSTATE value into a meaningful description as well. By including either (or both) of these APIs in your embedded SQL applications, you can always return meaningful information to the end user whenever error and/or warning conditions occur.

4 of 8 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=195578
TutorialTitle=DB2 9 Application Development exam 733 prep, Part 4: Embedded SQL programming
publish-date=02152007
author1-email=rsanders@netapp.com
author1-email-cc=