DB2 ODBC Introduction

Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1008yanqh/?S_TACT=105AGX52&S_CMP=reg-ccid

As an easy-to-use database connection method, ODBC is widely respected in the field of database programming because of its complete function and good portability. IBM's DB2 ODBC driver, based on the international ODBC Standard, implemented the main functions of ODBC, introduced the extension of specific functions for DB2, and made programmers develop DB2 application more handy. In this paper, based on the explanation of the basic, supplemented by a large number of vivid examples, so that the reader in the understanding of the basic principles at the same time, quickly grasp the development of DB2 ODBC application method, for the relevant personnel has a great reference value.

Develop and deploy your next application on the IBM Bluemix cloud platform.

Start your trial DB2 ODBC introduction background

The ODBC (Open database Connectivity), an open databases connection, is an application programming interface for accessing the database that is presented by Microsoft Corporation based on the X/open CLI, which mainly accomplishes the middleware functionality between the application and the database system. ODBC-based applications interact with databases through ODBC-provided APIs, which greatly enhance the portability, extensibility, and maintainability of applications while avoiding the direct manipulation of database systems by applications.

DB2 ODBC is based on ODBC standards and introduces a unique extension to the DB2 database, one of the main products of the IBM DB2 family. It provides a C/s programming interface for relational databases, which can be run on different relational databases without changing the source code, and is an effective programming interface outside of embedded SQL.

This article will mainly describe how to use DB2 ODBC for program Development on the DB2 for Z platform. difference

1. Similarities and differences with traditional ODBC

First of all, DB2 ODBC is based on the ISO CLI Standard and is able to perform very well the basic functions of database operation, most APIs are the same as ODBC3.0. But it also discards unnecessary APIs, such as Sqlcopydesc (), Sqlgetdescfield (), and so on, so when the application transitions between different ODBC versions, be aware of the compatibility of the current version with the API.

Second, DB2 ODBC provides a powerful extension to DB2 's features so that it can give full play to the benefits of DB2. It is mainly embodied in SQLCA's support for DB2 diagnostic information, LOB and lob-locator processing.

2. Similarities and differences with Embedded SQL

First, ODBC programs have better portability than Embedded SQL. The Embedded SQL program needs to specify that the pre-compiler corresponding to the database will pre-compile Embedded SQL before running. However, due to the correspondence between the pre-compiler and the database, the Embedded SQL program relies on the specific database to some extent, thus restricting the portability of the program. The ODBC program itself does not require a pre-compiler, which executes the contents of the program itself dynamically at runtime, independent of the database, and therefore has better portability.

Second, ODBC has better ease of use. In an ODBC program, the user does not need to directly define and manipulate the cursor, you can use wildcards to prepare the database operation, do not need to directly trigger a commit or roll back statement to complete the transaction commit or rollback. These encapsulation operations of DB2 ODBC allow users to focus more on program logic rather than on the language itself, so they have better usability. Advantages

1. Good portability.

The ODBC program is independent of the database, so that when the program runs on different databases, it does not need to be recompiled and bound due to the different database products.

2. Good ease of use.

ODBC provides an easy-to-understand programming interface that encapsulates a large amount of cumbersome underlying operations into the API while allowing users to focus more on business logic.

3. Powerful programming interface.

ODBC provides a powerful programming interface. The user can dynamically input and output data through wildcards, arrays, and the specified API to get the details of the program runtime, and connect multiple different databases for corresponding operations.

Back to top ODBC application structure conceptual model

Figure 2.1 is a conceptual model of a simple ODBC application: Figure 2.1 ODBC conceptual model

An ODBC program can usually be divided into three modules: initialization, database operation, and resource release. The initialization module mainly completes the database handle creation, the environment parameter setting and the connection to the database and so on; The database operation part is the main body of the whole program, the main completion of the business logic related database objects and data to increase, delete, change, check and other operations; The resource release module mainly includes database connection and resource release.

Before introducing the three modules in more detail, let's first introduce an important concept: Handle. It is a handle to a data object in an ODBC program and is used for program control. Specific can be divided into environment handle,connection handle and Statement handle (hereinafter referred to as STMT handle).

1. Environment handle: Environment handle. is a handle to the object containing the global information data that sets the overall properties of the current program, such as the version of ODBC, the maximum number of connections, and so on. There can be only one environment handle in each ODBC program. The properties that are set by environment handle affect the current entire program. In addition, you can create Connection handle for database connections by environment handle.

2. Connection Handle: Connection handle. is a handle to the data object that contains the data source connection that controls the properties of the current connection, such as transaction autocommit, connection type, isolation level, and so on, with the current connection. In a program, you can connect to multiple data sources by creating multiple Connection handle, and a Connection handle can only connect to one data source at a time. In addition, you can create Stmt handle for database operations by connection handle.

3. STMT handle: Operation handle. is a handle to the data object that contains the database operation information that is used to complete the operation of the database object and data, scoped to the current operation. Initialize

The initialization module primarily allocates and initializes system resources to prepare for later database operations. This includes the creation and initialization of environment handle and Connection handle, connections to the specified data source, settings for global and connection properties, and so on. Figure 2.2 shows a schematic of the initialization section, where the dashed line represents an option, that is, you can customize global or connection properties, or you can use the system's default values without setting. The following are described separately.

For the sake of description, the declaration of the global variables that will appear in the subsequent chapters of the example program is shown in Example 2.1: One of the HENV,HDBC,HSTMT represents environment handle,connection handle and STMT HANDLE,RC as the program After performing the operation, SQLSTMT is the SQL statement variable that is used to store the specific database operation. Figure 2.2 Initializing the module Example 2.1 global variable declaration

Sqlhenv henv = sql_null_henv; 
 Sqlhdbc hdbc = SQL_NULL_HDBC; 
 sqlhstmt hstmt = sql_null_hstmt; 
 Sqlreturn rc = sql_success; 
 SQLCHAR sqlstmt[200];

1. Allocation HANDLE:DB2 ODBC recommends using the ODBC3.0 API sqlallochandle () to uniformly assign handle, a typical example of assigning environment handle and Connection handle as an example 2.2 is shown below:

Example 2.2 assigns environment and Connection handle henv=0;                                                 

rc = 0; 
 printf ("\nsqlallochandle-allocate ENV handle"); 
 Rc=sqlallochandle ((sqlsmallint) sql_handle_env, 
 (Sqlhandle) Sql_null_handle, (Sqlhandle *) &henv); 
 printf ("\ndmlddlm-henv=%i\n", henv); 
 if (rc! = sql_success) { 
  printf ("\nallocate environment handle error\n"); 
 Goto dberror; 
 } 
 hdbc=0; 
 printf ("\nsqlallochandle-allocate connection handle"); 
 Rc=sqlallochandle ((Sqlsmallint) Sql_handle_dbc, (Sqlhandle) henv, (Sqlhandle *) &HDBC); 
 printf ("\ndmlddlm-hdbc=%i\n", HDBC); 
 if (rc! = sql_success) { 
 printf ("\nallocate connection handle error\n"); 
 Goto dberror; 
 }

The parameter description for Sqlallochandle () in example 2.2 is shown in Table 2.1: Table 2.1 sqlallochandle () parameter Description

parameter ordinal	parameter name	parameter type	parameter description
1	handletype	sqlsmallint	The type of handle to be assigned, including three types: SQ L_handle_env:environment HANDLE; sql_handle_dbc:connection HANDLE; sql_handle_stmt:stmt HANDLE.
2	inputhandle	sqlhandle	Upper level H corresponding to the currently assigned handle Andle: When assigning environment handle: Specify Sql_null_handle; when assigning Connection handle: Specify the corresponding environment handle variable; Divide With Stmt handle, specify the corresponding Connection handle variable.
3	outputhandleptr	sqlhandle *	as a handle pointer to the result of the assignment, A variable that points to the newly assigned handle.

Note: There can be only one environment handle per program, but multiple Connection handle can be assigned. In addition to using Sqlallochandle (), DB2 ODBC also supports the use of ODBC 1.0 api:sqlallocenv (), SQLAllocConnect (), and sqlallocstmt () to allocate environment, Con Nection and Stmt handle. For the maintainability and portability of the program, it is recommended that users use Sqlallochandle () to distribute handle uniformly.

2. Set and Query Global properties: DB2 ODBC uses SQLSetEnvAttr () and sqlgetenvattr () to set and query global properties. An example of setting and querying the environment handle property is shown in example 2.3: example 2.3 setting and querying environment handle properties

Sqlinteger output_nts; 
 rc = SQLSetEnvAttr (henv, Sql_attr_output_nts, (sqlpointer) sql_true, 0); 
 if (rc! = sql_success) { 
 printf ("\nset Environment handle attribute error\n"); 
  Goto dberror; 
 } 
 rc = Sqlgetenvattr (henv, sql_attr_output_nts, &output_nts, 0, 0); 
 if (rc! = sql_success) { 
  printf ("\nget Environment handle attribute error\n"); 
  Goto dberror; 
 } 
 printf ("\nnull Termination of Output strings is:"); 
 if (output_nts = = sql_true) 
 printf ("true\n"); 
 else 
  printf ("false\n");

Example 2.3 uses SQLSetEnvAttr () to set DB2 ODBC to use NULL as the output string terminator in database operations, and to query the results of the settings by Sqlgetenvattr (). The parameter description for SQLSetEnvAttr () is shown in table 2.2, and the parameter description for sqlgetenvattr () is shown in table 2.3: Table 2.2 sqlsetenvattr () parameter Description

parameter ordinal	parameter name	parameter type	parameter description