Use CLI to develop DB2 Stored Procedure instances (1)

The stored procedure is one of the most important issues to consider when using a large relational database DB2 for development. This article describes how to use CLI to develop the DB2 stored procedure and attaches a development instance for your reference.

• Actual operations for viewing DB2 dynamic SQL and Stored Procedure SQL
• What code is used for writing DB2 stored procedures?
• What types of abnormal processors are involved in DB2 stored procedures?
• Quick Start to DB2 Stored Procedure"
• Errors frequently encountered when creating a stored procedure in DB2 are:

■ DB2 Stored Procedure

Stored Procedure Store Procedure) is a programming method in the Client/Server application. It is mainly used to solve the network overload problems that may occur when the Client application accesses the database.

In general, DB2 provides three methods for writing stored procedures, not directly using the DB2 Store Procedure Builder generation method): the first is embedded SQL programming; the second is CLI programming, and the third is pure SQL programming. This article focuses on CLI programming.

■ CLI Programming

CLICall Level Interface) programming is a way that DB2 provides to write a dynamic database read/write program. In CLI programming, all SQL statements are executed through a set of C language functions provided by DB2, so they can be directly developed in Visual Studio. Next, we first need to briefly introduce the working process and method of CLI.

The CLI program consists of three parts: Environment Variable initialization, statement execution, and environment variable release. We will use a specific example to discuss these three processes.

■ Development Instance

The database in this example is a Sample database automatically generated by DB2. The operation completed in this example is to obtain the employee name whose ID is equal to the input parameter value from the database. The entire process of this example is very simple, however, it includes the basic ideas and steps for CLI programming.

1. initialize Environment Variables

There are three handles in CLI: Environment handle, connection handle, and statement handle. During initialization, you must first obtain the first two handles one by one. After successful retrieval, you can connect to the database and obtain the third handle.

SQLRETURN ret;

SQLHENV henv; // environment handle

SQLHDBC hdbc; // connection handle

SQLHSTMT hstmt; // statement handle

Sqlchar dbname [] = "SAMPLE"; // Database Name