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:  

Constructing an embedded SQL application

Declaring host variables

Earlier, we saw that the DB2 Database Manager relies on host variables to move data between an application and a database. We also saw that host variables are defined in a special section known as a declare section, and that this is what distinguishes them from other high-level programming language variables. So just how are declare sections written?

The beginning of a declare section is defined by the SQL statement BEGIN DECLARE SECTION, while the end is defined by the statement END DECLARE SECTION. Thus, a typical declare section in a C/C++ source code file looks something like this:

...
// Define The SQL Host Variables Needed 	
EXEC SQL BEGIN DECLARE SECTION;
    char    EmployeeID[7];
    char    WorkDept[4];
    char    Job[9];
    char    Sex[2];
    double  Salary;
    double  Bonus;
    double  Commision;
EXEC SQL END DECLARE SECTION;
...
        

A declare section can be coded anywhere high-level programming language variable declarations can be coded in a source code file. And although a source code file typically contains only one declare section, multiple declare sections are allowed.

Host variables that transfer data to a database are known as input host variables, while host variables that receive data from a database are known as output host variables. Regardless of whether a host variable is used for input or output, its attributes must be appropriate for the context in which it is used. Thus, you must define host variables such that their data types and lengths are compatible with the data types and lengths of the database objects they are intended to work with. Also, each host variable used in an application must be assigned a unique name -- duplicate names in the same file are not allowed, even when multiple declare sections are used. A tool known as the Declaration Generator can be used to generate host variable declarations for the columns of a given table in a database. This tool creates embedded SQL declaration source code files, which can then be inserted into C/C++, Java language, COBOL, and FORTRAN applications. For more information about this utility, refer to the db2dclgn command in the DB2 Command Reference.

So how are host variables used to move data between an application and a database? The easiest way to answer this question is by examining a simple embedded SQL source code fragment, such as the one in Listing 1.


Listing 1. Proper use of host variables
...
// Define The SQL Host Variables Needed
EXEC SQL BEGIN DECLARE SECTION;
    char     EmployeeNo[7];
    char     LastName[16];
EXEC SQL END DECLARE SECTION;
...

// Retrieve A Record From The Database
EXEC SQL SELECT empno, lastname 
    INTO :EmployeeNo, :LastName 
    FROM employee 
    WHERE empno = '000100';
  
// Do Something With The Results
...
	

In this example, when the SQL statement SELECT empno, lastname FROM employee WHERE empno = '000100' is executed, the results are transfered to the host variables EmployeeNo and LastName. The actual commands and functions used to make this transfer happen are added when the source code file is precompiled.


Declaring indicator variables

By default, columns in a DB2 database table can contain null values. And because null values are not stored the same way in which conventional data is stored, special provisions must be made if an application intends to work with null data. Null values cannot be retrieved and copied to host variables in the same manner that other data values can. Instead, a special flag must be examined to determine whether a specific value is meant to be null. And in order to obtain the value of this flag, a special host variable known as an indicator variable (or null indicator variable) must be associated with a host variable that has been assigned to a nullable column.

Because indicator variables must be accessible by both the DB2 Database Manager and the application program, they too must be defined inside a declare section. Furthermore, they must be assigned a data type that is compatible with the DB2 SMALLINT data type. Thus, the code used to define an indicator variable in a C/C++ source code file will look something like this:

// Define The SQL Host Variables Needed 	
EXEC SQL BEGIN DECLARE SECTION;
    short   SalaryNullIndicator;
EXEC SQL END DECLARE SECTION;
        

An indicator variable is associated with a specific host variable when it follows the host variable in an SQL statement. And once associated with a host variable, an indicator variable can be examined as soon as its corresponding host variable has been populated. If the indicator variable contains a negative value, that indicates that a null value was found and that the value of the corresponding host variable should thus be ignored. Otherwise, the value of the corresponding host variable is valid.

Again, in order to understand how indicator variables are used, it helps to look at a source code fragment. Listing 2, written in the C programming language, shows one example of how indicator variables are defined and used.


Listing 2. Proper use of indicator variables
...
// Define The SQL Host Variables Needed
EXEC SQL BEGIN DECLARE SECTION;
    char     EmployeeNo[7];
    double   Salary;    // Salary - Used If SalaryNI Is Positive ( >= 0 )
    short    SalaryNI;  // Salary NULL Indicator - Used
                        // To Determine If Salary
                        // Value Should Be NULL
EXEC SQL END DECLARE SECTION;
...

// Declare A Static Cursor
EXEC SQL DECLARE cursor1 CURSOR FOR SELECT empno, DOUBLE(salary)
    FROM employee;

// Open The Cursor
EXEC SQL OPEN cursor1;
    
// If The Cursor Was Opened Successfully, Retrieve And
// Display All Records Available
while (sqlca.sqlcode == SQL_RC_OK)
{
    // Retrieve The Current Record From The Cursor
    EXEC SQL FETCH cursor1 INTO :EmployeeNo, :Salary :SalaryNI;

    // If The Salary Value For The Record Is NULL, ...
    if (SalaryNI < 0)
    {
        printf("No salary information is available for ");
        printf("employee %s\n", EmployeeNo);
    }  
}
  
// Close The Open Cursor
EXEC SQL CLOSE C1;
...
	

Indicator variables can also be used to send null values to a database when an insert or update operation is performed. When processing INSERT and UPDATE SQL statements, the DB2 Database Manager examines the values of any indicator variables provided first; if one or more indicators contain a negative value, it assigns a null value to the appropriate column, provided the column is nullable. (If the indicator variable is set to zero or contains a positive number, or if no indicator variable is used, the DB2 Database Manager assigns the value stored in the corresponding host variable to the appropriate column instead.) Thus, the code used in a C/C++ source code file to assign a null value to a column in a table would look something like this:

ValueInd = -1;
EXEC SQL INSERT INTO tab1 VALUES (:Value :ValueInd);
        


The SQLCA data structure

So far, we've only looked at how host variables and indicator variables are used to move data between embedded SQL applications and database objects. However, there are times when an embedded SQL application needs to communicate with the DB2 Database Manager itself. Two special SQL data structures are used to establish this vital communication link: the SQL Communications Area (SQLCA) data structure and the SQL Descriptor Area (SQLDA) data structure.

The SQLCA data structure contains a collection of elements that are updated by the DB2 Database Manager every time an SQL statement or a DB2 administrative API function is executed. This data structure must exist before the DB2 Database Manager can populate it. Therefore, any application that contains embedded SQL or that calls one or more administrative APIs must define at least one SQLCA data structure variable. In fact, such an application will not compile successfully if an SQLCA data structure variable does not exist.

So just what does an SQLCA data structure look like? Table 1 lists the elements that make up an SQLCA data structure variable.


Table 1. SQLCA data structure elements
Element nameData typeDescription
sqlcaidCHAR(8)An eye catcher for storage dumps. To help visually identify the data structure, this element normally contains the value "SQLCA". If line number information is returned from parsing an SQL procedure body, the sixth byte contains the character L.
sqlcabcINTEGERThe size, in bytes, of the SQLCA data structure itself. This element should always contain the value 136.
sqlcodeINTEGERThe SQL return code value. A value of 0 indicates successful execution, a positive value indicates successful execution with warnings, and a negative value indicates an error. Refer to the DB2 Message Reference, Volumes 1 and 2 product manuals (see Resources for a link) to obtain more information about specific SQL return code values.
sqlerrmlSMALLINTThe size, in bytes, of the data stored in the sqlerrmc element of this structure. This value can be any number between 0 and 70; a value of 0 indicates that no data has been stored in the sqlerrmc field.
sqlerrmcCHAR(70)One or more error message tokens, separated by the value "0xFF", that are to be substituted for variables in the descriptions of warning or error conditions. This element is also used when a successful connection is established.
sqlerrpCHAR(8)A diagnostic value that represents the type of DB2 server currently being used. This value begins with a three-letter code identifying the product version and release, and is followed by five digits that identify the modification level of the product. For example, SQL09010 means DB2 Version 9, release 1, modification level 0. If the sqlcode element contains a negative value, this element will contain an eight-character code that identifies the module that reported the error.
sqlerrdINTEGER ARRAYAn array of six integer values that provide additional diagnostic information when an error occurs. (Refer to Table 2 for more information about the diagnostic information that can be returned in this element.)
sqlwarnCHAR(11)An array of character values that serve as warning indicators; each element of the array contains either a blank or the letter W. If compound SQL is used, this field will contain an accumulation of the warning indicators that were set for all substatements executed in the compound SQL statement block. (Refer to Table 3 for more information about the types of warning information that can be returned in this element.)
sqlstateCHAR(5)The SQLSTATE value that identifies the outcome of the most recently executed SQL statement. (We'll discuss these in more detail in the section below entitled SQLSTATEs.")

Table 2 outlines the elements of the sqlca.sqlerrd array.


Table 2. sqlerrd array elements
Array elementDescription
sqlerrd[0]

If a connection has successfully been established, this element will contain the expected difference in length of mixed character data (CHAR data types) when it is converted from the application code page used to the database code page used. A value of 0 or 1 indicates that no expansion is anticipated; a positive value greater than 1 indicates a possible expansion in length; and a negative value indicates a possible reduction in length.

If an SQL procedure has successfully been executed, this element will contain the return status of the procedure.

sqlerrd[1]

If a connection has successfully been established, this element will contain the expected difference in length of mixed character data (CHAR data types) when it is converted from the database code page used to the application code page used. A value of 0 or 1 indicates that no expansion is anticipated; a positive value greater than 1 indicates a possible expansion in length; and a negative value indicates a possible reduction in length.

If the SQLCA data structure contains information for a NOT ATOMIC compound SQL statement, this element will contain the number of substatements that encountered one or more errors (if any).

sqlerrd[2]

If the SQLCA data structure contains information for a CONNECT SQL statement that executed successfully, this element will contain the value 1 if the connected database is updatable and the value 2 if the connected database is read-only.

If the SQLCA data structure contains information for a PREPARE SQL statement that executed successfully, this element will contain an estimate of the number of rows that will be returned in a result data set when the prepared statement is executed.

If the SQLCA data structure contains information for an INSERT, UPDATE, or DELETE SQL statement that executed successfully, this element will contain a count of the number of rows that were affected by the operation.

If the SQLCA data structure contains information for an OPEN SQL statement that executed successfully and the corresponding cursor used to perform insert, update, and delete operations, this element will contain a count of the number of rows that were affected by the operation.

If the SQLCA data structure contains information for a CREATE PROCEDURE SQL statement that encountered errors when parsing the SQL procedure body, this element will contain the line number where the parsing error was encountered.

If the SQLCA data structure contains information for a compound SQL statement, this element will contain a count of the number of rows that were affected by the substatements in the compound SQL statement block.

sqlerrd[3]

If the SQLCA data structure contains information for a CONNECT SQL statement that executed successfully, this element will contain the value 0 if one-phase commit from a down-level client is being used, the value 1 if one-phase commit is being used, the value 2 if one-phase, read-only commit is being used, and the value 3 if two-phase commit is being used.

If the SQLCA data structure contains information for a PREPARE SQL statement that executed successfully, this element will contain a relative cost estimate of the resources needed to prepare the statement specified.

If the SQLCA data structure contains information for a compound SQL statement, this element will contain a count of the number of substatements in the compound SQL statement block that executed successfully.

sqlerrd[4]

If the SQLCA data structure contains information for a CONNECT SQL statement that executed successfully, this element will contain the value 0 if server authentication is being used; the value 1 if client authentication is being used; the value 2 if authentication is being handled by DB2 Connect; the value 4 if SERVER_ENCRYPT authentication is being used; the value 5 if DB2 Connect with encryption authentication is being used; the value 7 if KERBEROS authentication is being used; the value 9 if GSSPLUGIN authentication is being used; the value 11 if DATA_ENCRYPT authentication is being used; and the value 255 if the way in which authentication is being handled cannot be determined.

If the SQLCA data structure contains information for anything else, this element will contain a count of the total number of rows that were inserted, updated, or deleted as a result of the DELETE rule of one or more referential integrity constraints or the activation of one or more triggers.

If the SQLCA data structure contains information for a compound SQL statement, this element will contain a count of all such rows for each substatement in the compound SQL statement block that executed successfully.

sqlerrd[5]For partitioned databases, this element contains the partition number of the partition that encountered an error or warning. If no errors or warnings were encountered, this element will contain the partition number of the partition that serves as the coordinator node.

And finally, Table 3 outlines the elements of the of the sqlca.sqlwarn array.


Table 3. sqlwarn array elements
Array elementDescription
sqlwarn[0]This element is blank if all other elements in the array are blank; it contains the character W if one or more of the other elements available is not blank.
sqlwarn[1]

This element contains the character W if the value for a column with a character string data type was truncated when it was assigned to a host variable.

This element contains the character N if the null-terminator for a character string was truncated.

This element contains the character A if a CONNECT or ATTACH operation was successful and the authorization name for the connection is longer than 8 bytes.

This element contains the character P if the PREPARE statement relative cost estimate stored in sqlerrd[3] exceeds the amount that can be stored in an INTEGER data type or was less than 1, and either the CURRENT EXPLAIN MODE or the CURRENT EXPLAIN SNAPSHOT special register is set to a value other than NO.

sqlwarn[2]

This element contains the character W if null values were eliminated from the arguments passed to a function.

If the SQLCA data structure contains information for a CONNECT SQL statement that executed successfully, this element will contain the character D if the database is in a quiesced state, or the character I if the instance is in a quiesced state.

sqlwarn[3]

This element contains the character W if the number of values retrieved does not equal the number of host variables provided.

This element contains the character Z if the number of result data set locators specified with an ASSOCIATE LOCATORS SQL statement is less than the actual number of result data sets returned by a called procedure.

sqlwarn[4]This element contains the character W if an UPDATE or DELETE SQL statement that does not contain a WHERE clause was prepared.
sqlwarn[5]This element contains the character E if an error was tolerated during SQL statement execution.
sqlwarn[6]This element contains the character W if the result of a date calculation was adjusted to avoid an invalid date value.
sqlwarn[7]This element contains the character E if the SQLCA data structure contains information for a CONNECT SQL statement that executed successfully, and the DYN_QUERY_MGMT database configuration parameter is enabled.
sqlwarn[8]This element contains the character W if a character that could not be converted was replaced with a substitution character.
sqlwarn[9]This element contains the character W if one or more errors in an arithmetic expression were ignored during column function processing.
sqlwarn[10]This element contains the character W if a conversion error occurred while converting a character data value in another element of the SQLCA data structure variable.

Both an SQLCA data structure variable and an SQLDA data structure variable (which we will look at next) can be created by embedding the appropriate form of the INCLUDE SQL statement (INCLUDE SQLCA and INCLUDE SQLDA, respectively) in an embedded SQL source code file.


The SQLDA data structure

The SQL Descriptor Area (SQLDA) data structure contains a collection of elements that are used to provide detailed information to PREPARE, OPEN, FETCH, and EXECUTE SQL statements. This data structure consists of a header followed by an array of structures, each of which describes a single host variable or a single column in a result data set. Table 4 lists the elements that make up an SQLDA data structure variable.


Table 4. SQLDA data structure elements
Element nameData typeDescription
sqldaidCHAR(8)An eye catcher for storage dumps. To help visually identify the data structure, this element normally contains the value "SQLDA". The seventh byte of this field is a flag named SQLDOUBLED; the DB2 Database manager sets this byte to 2 if two sqlvar entries have been created for each column, or if any host variable being described is a structured type, or to BLOB, CLOB, or DBCLOB data type.
sqldabcINTEGERThe size, in bytes, of the SQLDA data structure itself. The value assigned to this element is determined using the equation sqldabc = 16 + (44 * sqln) (for 32-bit operating systems) or sqldabc = 16 + (56 * sqln) (for 64-bit operating systems).
sqlnSMALLINTThe total number of elements in the sqlvar array.
sqldSMALLINTEither the number of columns in the result data set returned by a DESCRIBE or PREPARE SQL statement, or the number of host variables described by the elements in the sqlvar array.
sqlvarSTRUCTURE ARRAYAn array of data structures that contain information about host variables or columns in a result data set.

In addition to this basic information, an SQLDA data structure variable contains an arbitrary number of occurrences of sqlvar data structures (also referred to as SQLVAR variables). The information stored in each SQLVAR variable is dependent upon the location where the corresponding SQLDA data structure variable is used: when used with a PREPARE or a DESCRIBE SQL statement, each SQLVAR variable will contain information about a column that will exist in the result data set produced when the prepared SQL statement is executed. (If any of the columns have a large object (LOB) or user-defined data type, the number of SQLVAR variables used will be doubled and the seventh byte of the character string value stored in the sqldaid element of the SQLDA data structure variable will be assigned the value 2.) On the other hand, when the SQLDA data structure variable is used with an OPEN, FETCH, or EXECUTE SQL statement, each SQLVAR variable will contain information about a host variable whose value is to be passed to the DB2 Database Manager during statement execution.

Two types of SQLVAR variables are used: base SQLVARs and secondary SQLVARs. Base SQLVARs contain basic information (such as data type code, length attribute, column name, host variable address, and indicator variable address) for result data set columns or host variables. The elements that make up a base SQLVAR data structure variable are shown in Table 5.


Table 5. SQLVAR data structure elements
Element nameData typeDescription
sqltypeSMALLINTThe data type of a host variable/parameter marker used, or the data type of a column in the result data set produced.
sqllenSMALLINTThe length, in bytes, of a host variable used, or the size of a column in the result data set produced.
sqldataPointerA pointer to a location in memory where the data for a host variable used is stored, or a pointer to a location in memory where data for a column in the result data set produced is to be stored.
sqlindPointerA pointer to a location in memory where the data for the null indicator variable associated with a host variable used is stored, or a pointer to a location in memory where the data for the null indicator variable associated with a column in the result data set produced is to be stored.
sqlnameVARCHAR(30)The unqualified name of a host variable or a column in the result data set produced.

Secondary SQLVARs, on the other hand, contain either the distinct data type name for distinct data types or the length attribute of the column or host variable and a pointer to the buffer that contains the actual length of the data for LOB data types. Secondary SQLVAR entries are only present if the number of SQLVAR entries is doubled because LOBs or distinct data types are used: if locators or file reference variables are used to represent LOB data types, secondary SQLVAR entries are not needed.

The information stored in an SQLDA data structure variable, along with the information stored in any corresponding SQLVAR variables, may be placed there manually (using the appropriate programming language statements), or it can be generated automatically by executing the DESCRIBE SQL statement.


Establishing a database connection

In order to perform any type of operation against a database, you must first establish a connection to that database. With embedded SQL applications, database connections are made (and in some cases are terminated) by executing the CONNECT SQL statement. (The RESET option of the CONNECT statement is used to terminate a connection.) During the connection process, information needed to establish a connection -- such as an authorization ID and a corresponding password of an authorized user -- is passed to the appropriate database for validation. Often, this information is collected at application runtime and forwarded to the CONNECT statement by way of one or more host variables. Thus, the code used to establish a database connection in a C/C++ source code file typically looks something like Listing 3.


Listing 3. Establishing a database connection
...
// Define The SQL Host Variables Needed	
EXEC SQL BEGIN DECLARE SECTION;
    char   DataSource[129] = {"SAMPLE"};
    char   UserID[129] = {"USER1"};
    char   Password[129] = {"User1PWD"};
EXEC SQL END DECLARE SECTION;

...
// Connect To The Appropriate Database
EXEC SQL CONNECT TO :DataSource USER :UserID USING :Password;
...

// Terminate The Database Connection
EXEC SQL CONNECT RESET;
...
	

Embedded SQL applications can use two types of connection semantics. These types, known simply as Type 1 and Type 2, support two very different behaviors: Type 1 connections support only one database connection per transaction (referred to as a remote unit of work) while Type 2 connections support multiple database connections per transaction (referred to as an application-directed distributed unit of work). Essentially, when Type 1 connections are used, an application can only be connected to one database at a time. Once a connection to a database is established and a transaction is started, that transaction must be committed or rolled back before another database connection can be established. On the other hand, when Type 2 connections are used, an application can be connected to several different databases at the same time, and each database connection will have its own set of transactions.

The actual type of connection semantics used by an embedded SQL application is determined by the value assigned to the CONNECT, SQLRULES, DISCONNECT, and SYNCPOINT SQL precompiler options when the application is precompiled.


Preparing and executing SQL statements

When static SQL statements are embedded in an application program, they are executed as they are encountered. However, when dynamic SQL statements are used, they can be processed in one of two ways:

  • Prepare and execute: This approach separates the preparation of the SQL statement from its actual execution and is typically used when an SQL statement is to be executed repeatedly. This method is also used when an application needs advance information about the columns that will exist in the result data set produced when a SELECT SQL statement is executed. The SQL statements PREPARE and EXECUTE are used to process dynamic SQL statements in this manner.

  • Execute immediately: This approach combines the preparation and the execution of an SQL statement into a single step and is typically used when an SQL statement is to be executed only once. This method is also used when the application does not need additional information about the result data set that will be produced, if any, when the SQL statement is executed. The SQL statement EXECUTE IMMEDIATE is used to process dynamic SQL statements in this manner.

Dynamic SQL statements that are prepared and executed (using either method) at runtime are not allowed to contain references to host variables. They can, however, contain parameter markers in place of constants and expressions. Parameter markers are represented by the question mark character (?), and they indicate where in the SQL statement the current value of one or more host variables (or elements of an SQLDA data structure variable) are to be substituted when the statement is executed. Therefore, parameter markers are typically used where a host variable would be referenced if the SQL statement being executed were static. Two types of parameter markers are available: typed and untyped.

A typed parameter marker is any parameter marker that is specified along with its target data type when it is referenced in an SQL statement. Typed parameter markers have this form:

CAST( ? AS DataType )
        

This notation does not imply that a function is called; rather, it promises that the data type of the value replacing the parameter marker at application runtime will be either the data type specified or a data type that can be converted to the data type specified. For example, consider the following SQL statement:

UPDATE employee SET lastname = CAST(? AS VARCHAR(12)) WHERE empno = '000050'
        

In this example, the value for the LASTNAME column is provided at application runtime, and the data type of that value will be either a VARCHAR(12) data type or a data type that can be converted to a VARCHAR(12) data type.

An untyped parameter marker, on the other hand, is specified without a target data type and has the form of a single question mark (?). The actual data type for an untyped parameter marker is determined by the context in which it is used. For example, in the following SQL statement, the value for the LASTNAME column is provided at application runtime, and it is assumed that the data type of that value will be compatible with the data type that has been assigned to the LASTNAME column of the EMPLOYEE table.

UPDATE employee SET lastname = ? WHERE empno = '000050'
	

When parameter markers are used in embedded SQL applications, values that are to be substituted for them must be provided, as additional parameter values, to the EXECUTE or the EXECUTE IMMEDIATE SQL statement when either is used to execute the SQL statement specified. Listing 4, written in the C programming language, illustrates how actual values might be provided for parameter markers that have been coded in a simple UPDATE SQL statement.


Listing 4. Using parameter markers
          
...
// Define The SQL Host Variables Needed
EXEC SQL BEGIN DECLARE SECTION;
    char   SQLStmt[80];
    char   JobType[10];
EXEC SQL END DECLARE SECTION;
...
	     
// Define A Dynamic UPDATE SQL Statement That Uses A Parameter Marker
strcpy(SQLStmt, "UPDATE employee SET job = ? ");
strcat(SQLStmt, "WHERE job = 'DESIGNER'");
	         
// Populate The Host Variable That Will Be Used In Place Of The Parameter Marker
strcpy(JobType, "MANAGER");
	         
// Prepare The SQL Statement
EXEC SQL PREPARE SQL_STMT FROM :SQLStmt;
	         
// Execute The SQL Statement
EXEC SQL EXECUTE SQL_STMT USING :JobType;
...
	


Retrieving and processing results

Regardless of whether static SQL statements or dynamic SQL statements are used in an embedded SQL application, once a statement has been executed, any results produced will need to be retrieved and processed. If the SQL statement was anything other than a SELECT or a VALUES statement, the only additional processing required after execution is a check of the SQLCA data structure variable to ensure that the statement executed as expected. However, if a query was executed, especially a query that returns multiple rows, additional steps may be required in order to retrieve data from the result data set produced.

When multiple rows are returned to an application by a query, DB2 can use a mechanism known as a cursor to retrieve values from the result data set produced. The name cursor probably originated from the blinking cursor found on early computer screens, and just as that cursor indicated the current position on the screen and identified where typed words would appear next, a DB2 cursor indicates the current position in a result data set (i.e., the current row) and identifies the row of data that will be returned to the application next. The following steps must be performed, in the order shown, if a cursor is to be incorporated into an embedded SQL application:

  1. Declare (define) a cursor along with its type (read-only or updatable), and associate it with the desired query. This is done by executing the DECLARE CURSOR statement.

  2. Open the cursor. This will cause the corresponding query to be executed and a result data set to be produced. This is done by executing the OPEN statement.

  3. Retrieve (fetch) each row stored in the result data set, one by one, until an end-of-data condition occurs. This is done by repeatedly executing the FETCH statement; host variables or an SQLDA data structure variable are used in conjunction with a FETCH statement to extract a row of data from a result data set. Each time a row is retrieved from the result data set, the cursor is automatically moved to the next row.

  4. If appropriate, modify or delete the current row -- provided the cursor is an updatable cursor. This is done by executing the UPDATE statement or the DELETE statement.

  5. Close the cursor. This action will cause the result data set that was produced when the corresponding query was executed to be deleted. This is done by executing the CLOSE statement.

Now that you have seen the steps that must be performed in order to use a cursor, you can take a look at how these steps are coded in an embedded SQL application. Listing 5, written in the C programming language, illustrates how a cursor would be used to retrieve the results of a simple query.


Listing 5. Retrieving results using a cursor
  
...
// Declare The SQL Host Memory Variables
EXEC SQL BEGIN DECLARE SECTION;
    char      EmployeeNo[7];
    char      LastName[16];
EXEC SQL END DECLARE SECTION;
...

// Declare A Cursor
EXEC SQL DECLARE cursor1 CURSOR FOR
    SELECT empno, lastname
    FROM employee
    WHERE job = 'DESIGNER';

// Open The Cursor
EXEC SQL OPEN cursor1;

// Fetch The Records
while (sqlca.sqlcode == SQL_RC_OK)
{
    // Retrieve A Record
    EXEC SQL FETCH cursor1
    INTO :EmployeeNo, :LastName;

    // Process The Information Retrieved
    if (sqlca.sqlcode == SQL_RC_OK)
        ...
}

// Close The Cursor
EXEC SQL CLOSE cursor1;
...
	

If you know in advance that only one row of data will be produced in response to a query, you can copy the contents of that row directly to host variables without using a cursor by executing either the SELECT INTO SQL statement or the VALUES INTO statement. Like the SELECT statement, the SELECT INTO statement can be used to construct complex queries. However, unlike the SELECT statement, the SELECT INTO statement requires a list of valid host variables to be supplied as part of its syntax, and cannot be used dynamically. Additionally, if the result data set produced when the SELECT INTO statement is executed contains more than one record, the operation will fail and an error will be generated. (If the result data set produced is empty, a NOT FOUND warning will be generated instead.)

Like the SELECT INTO statement, the VALUES INTO statement can be used to retrieve the data associated with a single record and copy it to one or more host variables. And, like the SELECT INTO statement, when the VALUES INTO statement is executed, all data retrieved is stored in a result data set. If this result data set contains only one record, the first value in that record is copied to the first host variable specified, the second value is copied to the second host variable specified, and so on. If the result data set produced contains more than one record, the operation will fail and an error will be produced. (Again, if the result data set produced is empty, a NOT FOUND warning will be generated.)


Managing transactions

A transaction (also known as a unit of work) is a sequence of one or more SQL operations grouped together as a single unit, usually within an application process. A given transaction can be comprised of any number of SQL operations, from a single operation to many hundreds or even thousands, depending upon what is considered a single step within your business logic.

The initiation and termination of a single transaction defines points of consistency within a database: either the effects of all operations performed within a transaction are applied to the database and made permanent (committed), or the effects of all operations performed are backed out (rolled back) and the database is returned to the state it was in before the transaction was started. In most cases, transactions are initiated the first time an executable SQL statement is executed after a connection to a database has been established, or immediately after a pre-existing transaction has been terminated. Once initiated, transactions can be implicitly terminated using the autocommit feature or explicitly by executing either the COMMIT or the ROLLBACK SQL statement. If the autocommit feature is enabled, each executable SQL statement is treated as a single transaction; if the statement executes successfully, any changes made by the statement are applied to the database; if the statement fails, all changes are discarded.


Putting it all together

Now that we have examined some of the basic components that are used to construct embedded SQL applications, let's see how they all come together to produce an embedded SQL application that interacts with a DB2 database. A simple embedded SQL application, written in the C programming language, that obtains and prints employee identification numbers, last names, and salaries for all employees who have the job title "DESIGNER", using static SQL, would look something like Listing 6.


Listing 6. A simple embedded SQL application that uses static SQL
 
  
  #include <stdio.h>
  #include <string.h>
  #include <sql.h>
  
  int main()
  {
      // Include The SQLCA Data Structure Variable
      EXEC SQL INCLUDE SQLCA;
  
      // Define The SQL Host Variables Needed
      EXEC SQL BEGIN DECLARE SECTION;
          char     EmployeeNo[7];
          char     LastName[16];
          double   Salary;
          short    SalaryNI;
      EXEC SQL END DECLARE SECTION;
      
      // Connect To The Appropriate Database
      EXEC SQL CONNECT TO sample USER db2admin USING ibmdb2;
      
      // Declare A Static Cursor
      EXEC SQL DECLARE cursor1 CURSOR FOR
          SELECT empno,
                 lastname,
                 DOUBLE(salary)
          FROM employee
          WHERE JOB = 'DESIGNER';
          
      // Open The Cursor
      EXEC SQL OPEN cursor1;
      
      // If The Cursor Was Opened Successfully, Retrieve And
      // Display All Records Available
      while (sqlca.sqlcode == SQL_RC_OK)
      {
          // Retrieve The Current Record From The Cursor
          EXEC SQL FETCH cursor1
              INTO :EmployeeNo,
                   :LastName,
                   :Salary :SalaryNI;
      
          // Display The Record Retrieved
          if (sqlca.sqlcode == SQL_RC_OK) 
          { 
          	printf("%-8s %-16s ", EmployeeNo,
      	        LastName);
          	if (SalaryNI >= 0)  
      	        printf("%lf\n", Salary); 
          	else
      	        printf("Unknown\n"); 
          }
      }
      
      // Close The Open Cursor
      EXEC SQL CLOSE cursor1;
  
      // Commit The Transaction
      EXEC SQL COMMIT;
  
      // Terminate The Database Connection
      EXEC SQL CONNECT RESET;
      
      // Return Control To The Operating System
      return(0);
  }    
        

On the other hand, a simple embedded SQL application written in the C programming language that uses dynamic SQL to change the job titles for all users with the title of "DESIGNER" to "MANAGER" might look something like Listing 7.


Listing 7. A simple embedded SQL application that uses dynamic SQL
 
	
#include <stdio.h>
#include <string.h>
#include <sql.h>

int main()
{
    // Include The SQLCA Data Structure Variable
    EXEC SQL INCLUDE SQLCA;

    // Define The SQL Host Variables Needed
    EXEC SQL BEGIN DECLARE SECTION;
        char     DataSource[129] = {"SAMPLE"};
        char     UserID[129] = {"USER1"};
        char     Password[129] = {"User1PWD"};
        char     SQLStmt[80];
        char     JobType[10];
    EXEC SQL END DECLARE SECTION;
    
    // Connect To The Appropriate Database
    EXEC SQL CONNECT TO :DataSource USER :UserID USING :Password;
       
    // Define A Dynamic UPDATE SQL Statement That Uses A
    // Parameter Marker
    strcpy(SQLStmt, "UPDATE employee SET JOB = ? ");
    strcat(SQLStmt, "WHERE job = 'DESIGNER'");
    
    // Populate The Host Variable That Will Be Used In
    // Place Of The Parameter Marker
    strcpy(JobType, "MANAGER");
    
    // Prepare The SQL Statement
    EXEC SQL PREPARE SQL_STMT FROM :SQLStmt;
    
    // Execute The SQL Statement
    EXEC SQL EXECUTE SQL_STMT USING :JobType;
    
    // Commit The Transaction
    EXEC SQL COMMIT;

    // Terminate The Database Connection
    EXEC SQL DISCONNECT CURRENT;
    
    // Return Control To The Operating System
    return(0);
}   
     
        

3 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=