DB2 UDB V8.1 Family Application Development Certification:: CLI/ODBC Programming
DB2 UDB V8.1 Family Application Development Certification:: CLI/ODBC Programming
DB2 UDB V8.1 Family Application Development Certification:: CLI/ODBC Programming
11 Sep 2003
This tutorial introduces you to CLI/ODBC programming and walks you through the
basic steps used to construct a CLI/ODBC application. It also introduces you to the
process to convert one or more high-level programming language source code files
containing CLI/ODBC function calls into an executable application. This is the fourth
in a series of seven tutorials that you can use to help prepare for the DB2 UDB V8.1
Family Application Development certification exam (Exam 703).
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 1 of 32
developerWorks® ibm.com/developerWorks
You do not need a copy of DB2 Universal Database to complete this tutorial.
However, you can download a free trial version of IBM DB2 Universal Database from
the developerWorks downloads site for reference.
Although not all materials discussed in the Family Fundamentals tutorial series are
required to understand the concepts described in this tutorial, you should have a
basic knowledge of:
• DB2 instances
• Databases
• Database objects
• DB2 security
This tutorial is one of the tools that can help you prepare for Exam 703. You should
also take advantage of one or more of the Resourcesidentified at the end of this
tutorial for more information.
CLI/ODBC programming
Page 2 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
IBM, DB2, DB2 Universal Database, DB2 Information Integrator, WebSphere and
WebSphere MQ are trademarks or registered trademarks of IBM Corporation in the
United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of
others.
What is CLI/ODBC?
One of the biggest drawbacks to developing applications with embedded SQL is the
lack of interoperability that such an application affords: Embedded SQL applications
developed specifically for DB2 UDB will have to be modified (and in some cases
completely rewritten) before they can interact with other relational database
management systems (RDBMSs). Because this limitation exists in every embedded
SQL application, regardless of the RDBMS for which it is written, in the early 1990s
the X/Open Company and the SQL Access Group (SAG), now a part of X/Open,
jointly developed a standard specification for a callable SQL interface. This interface
was known as the X/Open Call-Level Interface,or X/Open CLI.Much of the X/Open
CLI specification was later accepted as part of the ISO CLI international standard.
The primary purpose of X/Open CLI was to increase the portability of database
applications by allowing them to become independent of any one database
management system's programming interface.
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 3 of 32
developerWorks® ibm.com/developerWorks
DB2's Call Level Interface(also known as DB2 CLI ) is based on the ISO CLI
international standard. It provides most of the functionality that is found in the ODBC
specification. Applications that use DB2 CLI instead of ODBC are linked directly to
the DB2 CLI load library, and any ODBC Driver Manager can load this library as an
ODBC driver. DB2 UDB applications can also use the DB2 CLI load library
independently. However, when the library is used in this manner, the application
itself will not be able to communicate with other data sources.
CLI/ODBC programming
Page 4 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
data source, at the same time. (Embedded SQL applications can only
connect to multiple data sources at the same time if Type 2 connections
are used.)
Despite these differences, there is one important concept common to both
embedded SQL applications and CLI/ODBC applications: CLI/ODBC applications
can execute any SQL statement that can be dynamically prepared in an embedded
SQL application. This is guaranteed because CLI/ODBC applications pass all of their
SQL statements directly to the data source for dynamic execution. (CLI/ODBC
applications can also execute some SQL statements that cannot be dynamically
prepared, such as compound SQL statements, but for the most part, static SQL is
not supported.)
Because the data source processes all SQL statements submitted by a CLI/ODBC
application, the portability of CLI/ODBC applications is guaranteed. This is not
always the case with embedded SQL applications, since the way SQL statements
are dynamically prepared can vary with each relational database product used. Also,
because COMMITand ROLLBACKSQL statements can be dynamically prepared by
some database products (including DB2 UDB) but not by others, they are typically
not used in CLI/ODBC applications. Instead, CLI/ODBC applications rely on the
SQLEndTran()function to terminate active transactions (when manual commit is
used). This ensures that CLI/ODBC applications can successfully end transactions,
regardless of the database product being used.
• Initialization
• Transaction processing
• Termination
The work associated with these three tasks is conducted by invoking one or more
CLI/ODBC functions. Furthermore, many of the CLI/ODBC functions used to carry
out these tasks must be called in a specific order or an error will occur. The following
illustration identifies some of the basic CLI/ODBC functions that are used to perform
initialization and termination.
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 5 of 32
developerWorks® ibm.com/developerWorks
CLI/ODBC applications can perform tasks other than the three outlined above, such
as error handling and message processing. We'll look at how errors are handled in a
CLI/ODBC application in Diagnostics and error handling .
CLI/ODBC programming
Page 6 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
Allocating resources
During initialization, resources that will be needed to process transactions are
allocated (and initialized) and connections to any data source(s) with which the
transaction processing task will interact are established. The resources used by
CLI/ODBC applications consist of special data storage areas that are identified by
unique handles.(A handle is simply a pointer variable that refers to a data object
controlled by DB2 CLI or the ODBC Driver Manager and referenced by CLI/ODBC
function calls.) By using data storage areas and handles, CLI/ODBC applications are
freed from the responsibility of allocating and managing global variables and/or data
structures like the SQLCA and SQLDA data structures that are used with embedded
SQL applications. Four different types of handles are available:
SQLHANDLE EnvHandle = 0;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle);
With CLI/ODBC applications, connections to data sources are made via connection
handles. Therefore, a connection handle must exist before a connection to any data
source can be established. Connection handles are allocated by calling the
SQLAllocHandle()function with the SQL_HANDLE_DBCoption and a valid
environment handle specified. The source code used to allocate a connection
handle looks something like:
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 7 of 32
developerWorks® ibm.com/developerWorks
SQLHANDLE ConHandle = 0;
if (EnvHandle != 0)
SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &ConHandle);
The statement handle is the real workhorse of CLI/ODBC. Statement handles are
used to process each SQL statement in an application (both user-defined SQL
statements and SQL statements that are performed behind the scenes when certain
CLI/ODBC functions are called). Notably, statement handles are used to:
SQLHANDLE StmtHandle = 0;
if (ConHandle != 0)
SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &StmtHandle);
CLI/ODBC programming
Page 8 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
It is important that DB2 CLI and/or the ODBC Driver Manager knows the
specification for which an application has been coded, because many of the return
code values (otherwise known as SQLSTATEs; we'll discuss these in more detail in
SQLSTATEs ) that are returned by a CLI/ODBC function vary from one version to
the next. Additionally, later versions of DB2 CLI and ODBC allow wild cards to be
used in some function parameters, while earlier versions do not.
• SQLConnect()
• SQLDriverConnect()
• SQLBrowseConnect()
Applications can use any combination of these functions to connect to any number
of data sources, although some data sources may limit the number of active
connections they support. (An application can find out how many active connections
a particular data source supports by calling the SQLGetInfo()function with the
SQL_MAX_DRIVER_CONNECTIONSinformation type specified.)
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 9 of 32
developerWorks® ibm.com/developerWorks
ODBC.INIfile, or the ODBC subkey in the system registry.) This function works well
for applications that need to connect to data sources that only require a user ID and
password, and for applications that want to provide their own connection interface or
that require no user interface at all.
Keyword/Value Purpose
DSN= DataSourceName Specifies the name of a data source (as returned
by the SQLDataSources()function) that a
connection is to be established with.
UID= UserID Specifies the user ID (authorization ID) of the
user attempting to establish the connection.
PWD= Password Specifies the password corresponding to the user
ID (authorization ID) specified. If a password is
not required for the specified user ID, an empty
password string ( PWD=; ) should be used.
NEWPWD= NewPassword Specifies the new password that is to be
assigned to the user ID (authorization ID)
specified. If the NEWPWDkeyword is used but no
new password is provided ( NEWPWD=; ), the
DB2 CLI driver will prompt the user to provide a
new password.
DSN=PAYROLL;UID=db2admin;PWD=ibmdb2;
Applications using the SQLDriverConnect()function can also let the driver prompt
the user for any connection information needed. For example, when the
SQLDriverConnect()function is called with an empty connection string, DB2 CLI
will display a dialog that looks something like:
CLI/ODBC programming
Page 10 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
This dialog prompts the user to select a data source from a list of data sources
recognized by DB2 CLI, and to provide a user ID along with a corresponding
password. Once this information has been provided, an appropriate connection
string is constructed and used to establish a connection to the appropriate data
source. Whether this dialog will be displayed is determined by one of the parameter
values passed to the SQLDriverConnect()function: If this function is called with
the SQL_DRIVER_PROMPT, SQL_DRIVER_COMPLETE, or
SQL_DRIVER_COMPLETE_REQUIREDoption specified, the dialog will be displayed if
the connection string provided does not contain enough information to establish a
data source connection. On the other hand, if this function is called with the
SQL_DRIVER_NOPROMPToption specified and more information is needed, an error
will be generated.
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 11 of 32
developerWorks® ibm.com/developerWorks
Transaction processing
Once the appropriate initialization has been performed by a CLI/ODBC application,
the focus shifts to processing transactions. This is where the SQL statements that
query and/or manipulate data are passed to the appropriate data source (which in
our case is typically a DB2 UDB database) by various CLI/ODBC function calls for
processing. During transaction processing, a CLI/ODBC application performs the
following five steps, in the order shown:
We'll discuss each of these steps in more detail in the next few panels.
The following illustration shows the basic steps that are performed during the
transaction processing task and identifies the CLI/ODBC function calls that are
typically used to execute each step.
CLI/ODBC programming
Page 12 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 13 of 32
developerWorks® ibm.com/developerWorks
CLI/ODBC programming
Page 14 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
...
// Define A SELECT SQL Statement That Uses A Parameter Marker
strcpy((char *) SQLStmt, "SELECT EMPNO, LASTNAME FROM ");
strcat((char *) SQLStmt, "EMPLOYEE WHERE JOB = ?");
// Prepare The SQL Statement
RetCode = SQLPrepare(StmtHandle, SQLStmt, SQL_NTS);
// Bind The Parameter Marker Used In The SQL Statement To
// An Application Variable
RetCode = SQLBindParameter(StmtHandle, 1,
SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
sizeof(JobType), 0, JobType,
sizeof(JobType), NULL);
// Populate The "Bound" Application Variable
strcpy((char *) JobType, "DESIGNER");
// Execute The SQL Statement
RetCode = SQLExecute(StmtHandle);
...
1. Determine the structure (i.e., the number of columns, column data types,
and data lengths) of the result data set produced. This is done by
executing the SQLNumResultCols(), SQLDescribeCol(), and/or the
SQLColAttributes()functions.
2. Bind application variables to the columns in the result data set using the
SQLBindCol()function (optional).
3. Repeatedly fetch the next row of data from the result data set produced
and copy it to the bound application variables. This is typically done by
repeatedly calling the SQLFetch()function within a loop. (Values for
columns that were not bound to application variables in Step 2 can be
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 15 of 32
developerWorks® ibm.com/developerWorks
In the first step, the prepared or executed SQL statement is analyzed to determine
the structure of the result data set produced. If the SQL statement was hard-coded
into the application, this step is unnecessary because the structure of the result data
set produced is already known. However, if the SQL statement was generated at
application run time, then the result data set produced must be queried to obtain this
information.
Once the structure of a result data set is known, one or more application variables
can be bound to specific columns in the result data set, just as application variables
are bound to SQL statement parameter markers. In this case, application variables
are used as output arguments rather than input arguments, and data is retrieved and
written directly to them whenever the SQLFetch() function is called. However,
because the SQLGetData()function can also be used to retrieve data from a result
data set, application variable/column binding is optional.
In the third step, data stored in the result data set is retrieved by repeatedly calling
the SQLFetch()function (usually in a loop) until data is no longer available. If
application variables have been bound to columns in the result data set, their values
are automatically updated each time SQLFetch()is called. On the other hand, if
column binding was not performed, the SQLGetData()function can be used to copy
data from a specific column to an appropriate application variable. The
SQLGetData()function can also be used to retrieve large variable length column
data values in several small pieces (which cannot be done when bound application
variables are used). All data stored in a result data set can be retrieved using any
combination of these two methods.
...
// Bind The Columns In The Result Data Set Returned
// To Application Variables
SQLBindCol(StmtHandle, 1, SQL_C_CHAR, (SQLPOINTER)
EmpNo, sizeof(EmpNo), NULL);
SQLBindCol(StmtHandle, 2, SQL_C_CHAR, (SQLPOINTER)
LastName, sizeof(LastName), NULL);
// While There Are Records In The Result Data Set
// Produced, Retrieve And Display Them
while (RetCode != SQL_NO_DATA)
{
RetCode = SQLFetch(StmtHandle);
if (RetCode != SQL_NO_DATA)
printf("%-8s %s\n", EmpNo, LastName);
}
...
CLI/ODBC programming
Page 16 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
Managing transactions
You may recall that a transaction(also known as a unit of work ) is a sequence of
one or more SQL operations that are grouped together as a single unit, usually
within an application process. A transaction is considered to be atomicbecause it is
indivisible: either all of its work is carried out or none of its work is carried out. 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. Transactions are important because the
initiation and termination of a single transaction defines points of data 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 initiated.
SQLSetConnectAttr(ConHandle, SQL_ATTR_AUTOCOMMIT,
SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);
When manual commit mode is used, transactions are started implicitly the first time
an application accesses a data source, and transactions are explicitly ended when
the SQLEndTran()function is called. This CLI/ODBC function is used to either roll
back or commit all changes made by the current transaction. Thus, all operations
performed against a data source between the time at which it is first accessed and
the time at which the SQLEndTran()function is called are treated as a single
transaction.
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 17 of 32
developerWorks® ibm.com/developerWorks
#include <stdio.h>
#include <string.h>
#include <sqlcli1.h>
int main()
{
// Declare The Local Memory Variables
SQLHANDLE EnvHandle = 0;
SQLHANDLE ConHandle = 0;
CLI/ODBC programming
Page 18 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
SQLHANDLE StmtHandle = 0;
SQLRETURN RetCode = SQL_SUCCESS;
SQLCHAR SQLStmt[255];
SQLCHAR JobType[10];
SQLCHAR EmpNo[10];
SQLCHAR LastName[25];
/*-----------------------------------------------------*/
/* INITIALIZATION */
/*-----------------------------------------------------*/
// Allocate An Environment Handle
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&EnvHandle);
// Set The ODBC Application Version To 3.x
if (EnvHandle != 0)
SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
// Allocate A Connection Handle
if (EnvHandle != 0)
SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle,
&ConHandle);
// Connect To The Appropriate Data Source
if (ConHandle != 0)
RetCode = SQLConnect(ConHandle, (SQLCHAR *) "SAMPLE",
SQL_NTS, (SQLCHAR *) "db2admin",
SQL_NTS, (SQLCHAR *) "ibmdb2",
SQL_NTS);
/*-----------------------------------------------------*/
/* TRANSACTION PROCESSING */
/*-----------------------------------------------------*/
// Allocate An SQL Statement Handle
if (ConHandle != 0 && RetCode == SQL_SUCCESS)
SQLAllocHandle(SQL_HANDLE_STMT, ConHandle,
&StmtHandle);
// Define A SELECT SQL Statement That Uses A Parameter
// Marker
strcpy((char *) SQLStmt, "SELECT EMPNO, LASTNAME FROM ");
strcat((char *) SQLStmt, "EMPLOYEE WHERE JOB = ?");
// Prepare The SQL Statement
RetCode = SQLPrepare(StmtHandle, SQLStmt, SQL_NTS);
// Bind The Parameter Marker Used In The SQL Statement To
// An Application Variable
RetCode = SQLBindParameter(StmtHandle, 1,
SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
sizeof(JobType), 0, JobType,
sizeof(JobType), NULL);
// Populate The "Bound" Application Variable
strcpy((char *) JobType, "DESIGNER");
// Execute The SQL Statement
RetCode = SQLExecute(StmtHandle);
// If The SQL Statement Executed Successfully, Retrieve
// The Results
if (RetCode == SQL_SUCCESS)
{
// Bind The Columns In The Result Data Set Returned
// To Application Variables
SQLBindCol(StmtHandle, 1, SQL_C_CHAR, (SQLPOINTER)
EmpNo, sizeof(EmpNo), NULL);
SQLBindCol(StmtHandle, 2, SQL_C_CHAR, (SQLPOINTER)
LastName, sizeof(LastName), NULL);
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 19 of 32
developerWorks® ibm.com/developerWorks
You may have noticed that a special code named SQL_NTSwas passed as a
parameter value for some of the CLI/ODBC functions used in this application.
CLI/ODBC functions that accept character string values as arguments usually
require the length of the character string to be provided as well. The value
SQL_NTScan be used in place of an actual length value to indicate that the
corresponding string is null-terminated.
CLI/ODBC programming
Page 20 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
and limitations of a particular data source and adjust its behavior accordingly. The
first of these functions, the SQLGetInfo()function, can be used to obtain
information about the various characteristics of a data source. The second function,
SQLGetFunctions(), tells an application whether or not a particular CLI/ODBC
function is supported by a data source/driver. And the last function,
SQLGetTypeInfo(), provides an application with information about the native data
types that are used by a data source. Of the three, SQLGetInfo()is probably the
most powerful; over 165 different pieces of information can be obtained by this
function alone.
• Environment attributes
• Connection attributes
• SQL statement attributes
We'll discuss each type in the next few panels.
Environment attributes
Environment attributes affect the way CLI/ODBC functions that operate under a
specified environment behave. An application can retrieve the value of an
environment attribute at any time by calling the SQLGetEnvAttr() function, and it
can change the value of an environment attribute by calling the
SQLSetEnvAttr()function. Some of the more common environment attributes
include:
Connection attributes
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 21 of 32
developerWorks® ibm.com/developerWorks
Connection attributes affect the way connections to data sources and drivers
behave. An application can retrieve the value of a connection attribute at any time by
calling the SQLGetConnectAttr()function, and it can change the value of a
connection attribute by calling the SQLSetConnectAttr()function. Some of the
more common connection attributes include:
Statement attributes
Statement attributes affect the way many SQL statement-level CLI/ODBC functions
behave. An application can retrieve the value of a statement attribute at any time by
calling the SQLGetStmtAttr()function, and it can change the value of a statement
attribute by calling the SQLSetStmtAttr()function. Some of the more common
statement attributes include:
CLI/ODBC programming
Page 22 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
Return codes
Each time a CLI/ODBC function is invoked, a special value known as a return codeis
returned to the calling application to indicate whether the function executed as
expected. If the function did not execute as expected, the return code value
generated will indicate what caused the function to fail. The following table outlines a
list of possible return codes that can be returned by any CLI/ODBC function:
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 23 of 32
developerWorks® ibm.com/developerWorks
data-at-execution ( SQL_DATA_AT_EXEC )
parameters/columns.
SQL_STILL_EXECUTING A CLI/ODBC function that was started
asynchronously is still executing.
SQL_ERROR The CLI/ODBC function failed.
SQLSTATEs
Although a return code will notify an application program if an error or warning
condition occurred, it does not provide the application (or the developer or a user)
with specific information about what caused the error or warning condition. Because
additional information about an error or warning condition is usually needed to
resolve a problem, DB2 (like other relational database products) uses a set of error
message codes known as SQLSTATEsto 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 ccindicates the
error message class and sssindicates the error message subclass. Any SQLSTATE
that has a class of 01 corresponds to a warning; any SQLSTATE that has a class of
HYcorresponds to an error that was generated by DB2 CLI; and any SQLSTATE that
has a class of IMcorresponds to an error that was generated by the ODBC Driver
Manager. (Because different database servers often have different diagnostic
message codes, SQLSTATEs follow standards that are outlined in the X/Open CLI
standard specification. This standardization of SQLSTATE values enables
application developers to process errors and warnings consistently across different
relational database products.)
Unlike return codes, SQLSTATEs are often treated as guidelines, and drivers are
not required to return them. Thus, while drivers should always return the proper
SQLSTATE for any error or warning they are capable of detecting, applications
should not count on this always happening. Because SQLSTATEs are not returned
reliably, most applications just display them to the user along with any corresponding
diagnostic message and native error code. There is rarely any loss of functionality in
taking this approach, because applications normally cannot base programming logic
on SQLSTATEs anyway. For example, suppose an application calls the
SQLExecDirect()function and the SQLSTATE 42000(Syntax error or access
violation) is returned. If the SQL statement that caused this error to occur is
hardcoded into the application or constructed at application run time, the error can
be attributed to a programming error and the source code will have to be modified.
On the other hand, if the SQL statement that caused this error to occur was provided
CLI/ODBC programming
Page 24 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
by the user at run time, the error can be attributed to a user mistake, in which case
the application has already done all that it can do by informing the user of the
problem.
#include <stdio.h>
#include <string.h>
#include <sqlcli1.h>
int main()
{
// Declare The Local Memory Variables
SQLHANDLE EnvHandle = 0;
SQLHANDLE ConHandle = 0;
SQLRETURN RetCode = SQL_SUCCESS;
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 25 of 32
developerWorks® ibm.com/developerWorks
SQLSMALLINT Counter = 0;
SQLINTEGER NumRecords = 0;
SQLINTEGER NativeErr = 0;
SQLCHAR SQLState[6];
SQLCHAR ErrMsg[255];
SQLSMALLINT ErrMsgLen = 0;
// Allocate An Environment Handle
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&EnvHandle);
// Set The ODBC Application Version To 3.x
if (EnvHandle != 0)
SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
// Allocate A Connection Handle
if (EnvHandle != 0)
SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle,
&ConHandle);
// Attempt To Connect To A Data Source Using An Invalid
// User ID (This Will Cause An Error To Be Generated)
if (ConHandle != 0)
RetCode = SQLConnect(ConHandle, (SQLCHAR *) "SAMPLE",
SQL_NTS, (SQLCHAR *) "db2_admin",
SQL_NTS, (SQLCHAR *) "ibmdb2",
SQL_NTS);
// If Unable To Establish A Data Source Connection,
// Obtain Any Diagnostic Information Available
if (RetCode != SQL_SUCCESS)
{
// Find Out How Many Diagnostic Records Are
// Available
SQLGetDiagField(SQL_HANDLE_DBC, ConHandle, 0,
SQL_DIAG_NUMBER, &NumRecords, SQL_IS_INTEGER,
NULL);
// Retrieve And Display The Diagnostic Information
// Produced
for (Counter = 1; Counter <= NumRecords; Counter++)
{
// Retrieve The Information Stored In Each
// Diagnostic Record Generated
SQLGetDiagRec(SQL_HANDLE_DBC, ConHandle, Counter,
SQLState, &NativeErr, ErrMsg, sizeof(ErrMsg),
&ErrMsgLen);
// Display The Information Retrieved
printf("SQLSTATE : %s\n", SQLState);
printf("%s\n", ErrMsg);
}
}
// Free The Connection Handle
if (ConHandle != 0)
SQLFreeHandle(SQL_HANDLE_DBC, ConHandle);
// Free The Environment Handle
if (EnvHandle != 0)
SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle);
// Return Control To The Operating System
return(0);
}
CLI/ODBC programming
Page 26 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
The following illustration outlines the basic process for converting CLI/ODBC source
code files to an executable application.
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 27 of 32
developerWorks® ibm.com/developerWorks
It is important to note that DB2 UDB is packaged with a set of special bind files that
are used to support DB2 CLI. When a database is created, these files are bound to
the database as part of the database creation process to produce a package that
facilitates CLI interaction with that database.
CLI/ODBC programming
Page 28 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
Section 7. Conclusion
Summary
This tutorial introduced you to CLI/ODBC programming and to walked you through
the basic steps used to construct a CLI/ODBC application. At this point, you should
know the difference between environment, connection, statement, and descriptor
handles, and you should know how each is used in a CLI/ODBC application. You
should also know that CLI/ODBC applications rely on functions to pass SQL
statements to a data source for processing, and that these functions must be called
in a specific order.
Finally, you should be familiar with the steps used to convert a source code file
containing CLI/ODBC function calls into an executable application.
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 29 of 32
developerWorks® ibm.com/developerWorks
Resources
Learn
• For more information on DB2 Universal Database application development:
• DB2 Version 8 Administration Guide: Implementation, International
Business Machines Corporation, 2002.
• DB2 Version 8 Application Development Guide: Programming Client
Applications, International Business Machines Corporation, 2002.
• DB2 Version 8 Application Development Guide: Programming Server
Applications, International Business Machines Corporation, 2002.
• DB2 Version 8 Application Development Guide: Building and Running
Applications, International Business Machines Corporation, 2002.
• DB2 Version 8 SQL Reference Guide, Volume 1, International Business
Machines Corporation, 2002.
• DB2 Version 8 SQL Reference Guide, Volume 2, International Business
Machines Corporation, 2002.
• For more information on the DB2 UDB V8.1 Family Application Development
Certification exam (Exam 703):
• DB2 Universal Database v8.1 Certification Exam 703 Study Guide,
Sanders, Roger E., International Business Machines Corporation, 2004.
• DB2 Universal Database v8 Application Development Certification Guide,
Martineau, David and others, International Business Machines
Corporation, 2003.
• IBM DB2 Information Management -- Training and certificationfor
information on classes, certifications available and additional resources.
• As mentioned earlier, this tutorial is just one tutorial in a series of seven to help
you prepare for the DB2 UDB V8.1 Family Application Development
Certification exam (Exam 703). The complete list of all tutorials in this series is
provided below:
2. Data Manipulation
4. ODBC/CLI Programming
5. Java Programming
6. Advanced Programming
CLI/ODBC programming
Page 30 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.
ibm.com/developerWorks developerWorks®
7. User-Defined Routines
• Before you take the certification exam (DB2 UDB V8.1 Application
Development, Exam 703) for which this tutorial was created to help you
prepare, you should have already taken and passed the DB2 V8.1 Family
Fundamentals certification exam (Exam 700). Use the DB2 V8.1 Family
Fundamentals certification prep tutorial seriesto prepare for that exam. A set of
six tutorials covers the following topics:
• DB2 planning
• DB2 security
• Accessing DB2 UDB data
• Working with DB2 UDB data
• Working with DB2 UDB objects
• Data concurrency
• Use the DB2 V8.1 Database Administration certification prep tutorial seriesto
prepare for the DB2 UDB V8.1 for Linux, UNIX and Windows Database
Administration certification exam (Exam 701). A set of six tutorials covers the
following topics:
• Server management
• Data placement
• Database access
• Monitoring DB2 activity
• DB2 utilities
• Backup and recovery
CLI/ODBC programming
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 31 of 32
developerWorks® ibm.com/developerWorks
CLI/ODBC programming
Page 32 of 32 © Copyright IBM Corporation 1994, 2007. All rights reserved.