A simple C inventory program using CLI - IBM DB2

To illustrate the basic fundamentals of building a C/C++ application using the CLI interface, let us develop a simple program and work with the INVENTORY table in the SAMPLE database again. This application will print a list of products, which are low in quantity, The user must supply the minimum acceptable quantity value. Any products with quantity less than the acceptable value need to be restocked.

Sample output of CLI program

Sample output of CLI program

In the above run, the user tells the application that we need a list of all products, which have a quantity of less than 50. The application returns a list of two items in the database satisfying this requirement.

CLI header files
To begin a CLI program, we need to ensure one of the following header files is included:

  • sqlcli.h - Contains CLI constants, function prototypes and data structures
  • sqlcli1.h - Contains everything in sqlcli.h as well as SQL extensions in sqlext.h

Allocating handles
There are various CLI APIs we can use to allocate different types of handles. we list the APIs below.

APIs to allocate handles

APIs to allocate handles

We use SQLAllocHandle in our sample CLI application. This is the syntax of the function, as outlined in the DB2 documentation:

In the above syntax, HandleType is the type of handle, InputHandle is the context for the new handle, and OutputHandlePtr is a buffer storing the newly allocated handle data structure.

To begin the application, we need to define and allocate an environment and connection handle. We declare two variables to store the data structure of the handles and then call the SQLAllocHandle() API. When environment handles are allocated, there is no context for that handle type, so we use SQL_NULL_HANDLE as the context. When connection handles are allocated, they need to be under the context of an environment handle.

Using SQLAllocHandle()

Freeing handles
At the end of an application, we need to ensure we free the resources associated with each handle. There are various CLI APIs we can use to free up the different types of handles.we list the APIs below.

APIs to free handles

APIs to free handles

Generally, we can use the SQLFreeHandle() API to free environment, connection, and statement handles. We use SQLFreeHandle() API in our sample CLI application. To end our sample application, we need to free the connection and environment handles. We show how to do this with the SQLFreeHandle() API, giving a handle type and handle for each call in below code

Using SQLFreeHandle()

Connecting and disconnect to and from a database
Lists some APIs that can be used in working with a connection to a database below.

Connection-related CLI APIs

Connection-related CLI APIs

There are various APIs to choose from for connecting to a database. The SQLDriverConnect() API expands the functionality of SQLConnect() by allowing extra connection parameters and the ability to get connection information from the user. The SQLBrowseConnect() is an iterative way of connecting to the database. In our application, we use the SQLConnect() API:

In our code, we add the following lineto establish a default database connection and later to disconnect from the database.

Using SQLConnect() & SQLDisconnect()

Processing SQL statements
After connecting to the database, we need to allocate a statement handle to manage our SQL statement. Once this is done, we can execute an SQL statement by either preparing the statement or executing the statement directly.This process shown below.

Processing SQL statements

Processing SQL statements

In either case, if parameter markers exist in the SQL statement, SQLBindParameter() must be called to associate each parameter marker to an application buffer or a LOB locator. If a SELECT statement is run and a result set expected, a cursor is automatically opened. So, unlike embedded SQL, cursors neither have to be declared nor opened. To fetch the results, SQLBindCol() is first called to bind the columns of a result set to application variables, then SQLFetch() is called to fetch the rows.

After the processing of an SQL statement is complete, the statement handle can be freed. we show how to prepare, bind, and execute a SELECT statement and fetch the results. Some CLI APIs that you can use for processing an SQL statement lists below.

Some CLI APIs for SQL processing

Some CLI APIs for SQL processing

Complete CLI Inventory Program
In our example CLI inventory program , we connect to the database, prepare a SELECT statement, and then bind the parameter marker. Because QUANTITY is of type INTEGER in the database, we bind the parameter marker with a C type of SQL_C_LONG and SQL type of SQL_INTEGER. In binding a parameter marker, the SQL data type and the symbolic C data type need to be known as input to the CLI API. We also call SQLBindCol() for each column of the result set. Because QUANTITY and PID data values are returned from the INVENTORY table, we call SQLBindCol() to bind both columns to application buffers. Once we finish processing the result set, we disconnect from the database.

For simplicity and ease of readability, we have omitted error checking in our example CLI application.

CLIinventory.c

/****************************************************** ** Source File Name: CLIinventory.c ** ** This simple CLI program will print a list of ** products that need to be restocked. ** The user needs to supply the minimum acceptable ** quantity value before a product is considered to be ** low in stock. ** *******************************************************/

Error handling
After each API call, we should be checking the return code status to ensure that no errors have occurred. Lists possible CLI function return codes below.

CLI function return codes

CLI function return codes

If a function call resulted in an unexpected return code, diagnostic records are produced and associated with the handle that executed the API. To retrieve the diagnostic information, applications can make use of functions as shown below.

Some diagnostic CLI APIs

Some diagnostic CLI APIs

Quick SQLGetDiagRec() example
Let us see an example of how to use the SQLGetDiagRec() API. We first define the variables within a CLI application needed for the function call:

Now let us try connecting to an invalid database and see what is provided by the diagnostic function. We run a program with the following lines:

This produces the following output to let us know the database name was incorrectly specified:

SQLState: 08001 Native Error: -1013 Error Message Text: [IBM][CLI Driver] SQL1013N The database alias name or database name "SAMDPLE" could not be found. SQLSTATE=42705

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics