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:  

Summary

This tutorial was designed to introduce you to embedded SQL programming and to walk you through the steps that are used to develop an embedded SQL application. Structured Query Language (SQL) is a standardized language used to manipulate database objects and the data they contain. Because SQL is nonprocedural in nature, it is not a general-purpose programming language. Therefore, database applications are usually developed by combining the decision and sequence control of a high-level programming language with the data storage, manipulation, and retrieval capabilities of SQL. Several methods are available for merging SQL with a high-level programming language, but the simplest approach is to embed SQL statements directly into the high-level programming language source code files that are used to create an application.

One of the drawbacks to developing applications using embedded SQL is that high-level programming language compilers do not recognize, and therefore cannot interpret, SQL statements embedded in a source code file. Because of this, source code files containing embedded SQL statements must be preprocessed (by a process known as precompiling) before they can be compiled and linked to produce an executable application. To facilitate this preprocessing, each SQL statement embedded in a high-level programming language source code file must be prefixed with the keywords EXEC SQL and terminated with either a semicolon (in C/C++) or the keyword END-EXEC (in COBOL). Preprocessing is performed by a special tool known as the SQL precompiler; when the SQL precompiler encounters the EXEC SQL keywords, it replaces the text that follows (until it encounters a semicolon (;) or the keyword END-EXEC) with a DB2-specific function call that forwards the SQL statement encountered to the DB2 Database Manager for processing.

Likewise, the DB2 Database Manager cannot work directly with high-level programming language variables. Instead, it must use special variables known as host variables to move data between an application and a database. Host variables look like any other high-level programming language variable; so, to be set apart, they must be defined in a special section known as a declare section. Also, in order for the SQL precompiler to distinguish host variables from other text in an SQL statement, all references to host variables must be preceded by a colon (:).

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. During the connection process, information needed to establish a connection -- such as the 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.

Embedded SQL applications are comprised of static and dynamic SQL statements. Static SQL statements are well suited for high-performance applications that execute predefined operations against a known set of database objects. Dynamic SQL statements are well suited for applications that interact with a rapidly changing database or that allow users to define and execute ad-hoc queries. 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.

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

  2. Open the cursor. This will cause the corresponding query to be executed and a result data set to be produced.

  3. Retrieve (fetch) each row stored in the result data set, one by one, until an end-of-data condition occurs.

  4. Close the cursor. This action will cause the result data set that was produced when the corresponding query was executed to be deleted.

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; whenever possible, they should be provided with diagnostic information sufficient to allow them to locate and correct the problem.

Once a source code file has been written, the following steps must be performed, in the order shown, before an application that interacts with a DB2 database will be created:

  1. All source code files containing embedded SQL statements must be precompiled.

  2. Once a source code file containing embedded SQL statements has been processed by the SQL precompiler, the source code file produced -- along with any additional source code files needed -- must be compiled by a high-level programming language compiler.

  3. After all source code files needed to build an application have been successfully compiled, the resulting object modules must be linked with high-level programming language libraries and DB2 libraries to create an executable program.

  4. If the packages for the files that were processed by the SQL precompiler have not already been bound to the appropriate database, they must be bound using the bind files produced during the precompile process. This is done using a tool known as the DB2 Binder (or simply the Binder).

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