Accesing DB2 Databse using DB2 CLI

Introduction

This is about connecting/accessing DB2 database through C++ code using DB2 call level interface. An application that uses an embedded SQL interface requires a precompiler to convert the SQL statements into code, which is then compiled, bound to the database, and executed. In contrast, a DB2 CLI application does not have to be precompiled or bound, but instead uses a standard set of functions to execute SQL statements and related services at run time.

Background

DB2 Call Level Interface (DB2® CLI) is IBM's callable SQL interface to the DB2 family of database servers. It is a 'C' and 'C++' application programming interface for relational database access that uses function calls to pass dynamic SQL statements as function arguments. It is an alternative to embedded dynamic SQL, but unlike embedded SQL, DB2 CLI does not require host variables or a precompiler.

DB2 CLI is based on the Microsoft®** Open Database Connectivity** (ODBC) specification, and the International Standard for SQL/CLI.

Using the Code

#include "sqlcli.h"
//SQL handlers 
SQLHANDLE hndEnvironment;
SQLHANDLE hndDataSource;
SQLHANDLE hndStatement;
//Declare column attributes
typedef struct COLUMN_TYPE
{
    SQLCHAR szColumnName[128];
    SQLSMALLINT iColumnNameLength;
    SQLSMALLINT iColumnType;
    SQLUINTEGER iColumnSize;
    SQLSMALLINT iColumnScale;
    SQLSMALLINT iNullable;
    SQLSMALLINT iColumnDisplaySize;
} COLUMN_TYPE;
//Declare row attributes
typedef struct ROW_TYPE
{
    SQLINTEGER iBufferLength;
    SQLINTEGER iReturnLength;
    SQLCHAR * pszData;
    SQLDOUBLE dblData;
    SQLINTEGER intData;
} ROW_TYPE;

COLUMN_TYPE * pstColumnInfo;
SQLINTEGER iNumberOfColumns;

ROW_TYPE * pstRowData;

SQLRETURN sqlRV;
SQLSMALLINT iNumCols;
//Method to fetch row data
SQLRETURN Get_Next_Row(void);

int execute_SQL(char * pszSQLStatement)
{
    if ( SQLExecDirect( hndStatement, (SQLCHAR *)pszSQLStatement, 
                         (SQLINTEGER)strlen( pszSQLStatement ) != SQL_SUCCESS )
        MessageBox(NULL,"Unable to execute Statement", "Error",MB_OK);
    return 1;
}

int _tmain(int argc, _TCHAR* argv[])
{

    char szServer[128] = "DBNAME";
    char szUser[128] = "USERNAME";
    char szPassword[128] = "PASSWORD";
    

    if (SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hndEnvironment ) != SQL_SUCCESS )
    {
        MessageBox(NULL,"Error Allocating environment handle","Error",MB_OK);
        return;
    }

    // Set some environment Variables
    if ( SQLSetEnvAttr( hndEnvironment, SQL_ATTR_ODBC_VERSION, 
 (SQLPOINTER)SQL_OV_ODBC3, 0) != SQL_SUCCESS )
    {
         MessageBox(NULL,"Error Setting environment variables","Error",MB_OK);
         return;
    }

     // allocate the Connection (Datasource) Handle
    if ( SQLAllocHandle( SQL_HANDLE_DBC, hndEnvironment, &hndDataSource ) != SQL_SUCCESS ) 
    {
        MessageBox(NULL,"Error finding data source","Error",MB_OK);
        return;
    }

    if ( SQLConnect( hndDataSource, (SQLCHAR *)szServer, SQL_NTS, 
         (SQLCHAR *)szUser, SQL_NTS, (SQLCHAR *)szPassword, SQL_NTS ) != SQL_SUCCESS ) 
    {
    
        SQLDisconnect( hndDataSource ) ;
        SQLFreeHandle( SQL_HANDLE_DBC, hndDataSource ) ;
    }
    else
        MessageBox(NULL,"Connection succeded","Info",MB_OK);

    if (SQLAllocHandle( SQL_HANDLE_STMT, hndDataSource, &hndStatement ) != SQL_SUCCESS ) 
    {
        MessageBox(NULL,"Error finding statement handle","Error",MB_OK);
        return;
    }

    char szSQLStatement[128] = ""; 
    
    sprintf_s( szSQLStatement, "SELECT * FROM TABLENAME" ); 
    
    execute_SQL(szSQLStatement);

    while(Get_Next_Row() != SQL_NO_DATA_FOUND) 
    {

        char pszReturnBuffer[200] = "";

        for(int i = 0; i < iNumCols; i++)
        {
            if ( pstColumnInfo[i].iColumnType == SQL_DOUBLE )
            {
                sprintf( pszReturnBuffer, "%f", pstRowData[i].dblData );
            }
            else if ( pstColumnInfo[i].iColumnType == SQL_INTEGER )
            {
                sprintf( pszReturnBuffer, "%d", pstRowData[i].intData );    
            }
            else
            {
                memcpy( pszReturnBuffer, pstRowData[i].pszData, pstColumnInfo[i].iColumnSize );
            }
        }
    }

     SQLDisconnect( hndDataSource ) ;
     SQLFreeHandle( SQL_HANDLE_DBC, hndDataSource ) ;
     delete pstColumnInfo;
     delete pstRowData;

    return 0;
}

SQLRETURN Get_Next_Row(void)
{
    
    if ( SQLNumResultCols( hndStatement, &iNumCols  != SQL_SUCCESS )
    {
        MessageBox(NULL,"Error finding number of columns","Error",MB_OK);
        return;
    }

    if ( pstColumnInfo != 0x00 )
            delete pstColumnInfo;

    pstColumnInfo = new COLUMN_DESCRIPTION_TYPE[iNumCols];
    for ( int i = 0; i < iNumCols; i++ )
        memset( pstColumnInfo[i].szColumnName, 0x00, 128 );

    if ( pstRowData != 0x00 )
        delete pstRowData;

    pstRowData = new ROW_DATA_TYPE[iNumCols];

    for ( int i = 0; i < iNumCols; i++ )
    {
        if ( SQLDescribeCol( hndStatement,
                        ( SQLSMALLINT )(i + 1),
                        pstColumnInfo[i].szColumnName,
                        sizeof(pstColumnInfo[i].szColumnName),
                        &pstColumnInfo[i].iColumnNameLength,
                        &pstColumnInfo[i].iColumnType,
                        &pstColumnInfo[i].iColumnSize,
                        &pstColumnInfo[i].iColumnScale,
                    &pstColumnInfo[i].iNullable ) != SQL_SUCCESS )
        {
            MessageBox(NULL, "Unable to get Column Description", "Error",MB_OK);
            return;
        }

        if ( SQLColAttribute( hndStatement,
                            ( SQLSMALLINT )(i + 1),
                            SQL_DESC_DISPLAY_SIZE,
                            NULL,
                            0,
                            NULL,
                            &pstColumnInfo[i].iColumnDisplaySize ) != SQL_SUCCESS )
        {
            MessageBox(NULL, "Unable to get Column Attributes", "Error",MB_OK);
            return;
        }
     
        pstRowData[i].iBufferLength = pstColumnInfo[i].iColumnSize + 1;
        pstRowData[i].pszData = new SQLCHAR[pstRowData[i].iBufferLength + 1];
        memset( pstRowData[i].pszData, 0x00, pstRowData[i].iBufferLength + 1);

        switch( pstColumnInfo[i].iColumnType )
        {
            case SQL_DOUBLE :
                if ( SQLBindCol( hndStatement,
                                    ( SQLSMALLINT ) ( i + 1 ),
                                    SQL_C_DOUBLE,
                                    &pstRowData[i].dblData,
                                    pstRowData[i].iBufferLength,
                                    &pstRowData[i].iReturnLength )  != SQL_SUCCESS )
                {
                    MessageBox(NULL, "Unable to bind Data Columns", "Error",MB_OK);
                    return;
                }
                    
                break;

            case SQL_BLOB :
            case SQL_CLOB :
                if ( SQLBindCol( hndStatement,
                                    ( SQLSMALLINT ) ( i + 1 ),
                                    SQL_C_BINARY,
                                    pstRowData[i].pszData,
                                    pstRowData[i].iBufferLength,
                                    &pstRowData[i].iReturnLength ) != SQL_SUCCESS )
                {
                    MessageBox(NULL, "Unable to bind Data Columns", "Error",MB_OK);
                    return;
                }
                break;
            case SQL_INTEGER:
                if (SQLBindCol( hndStatement,
                                    ( SQLSMALLINT ) ( i +1 ),
                                    SQL_C_LONG,
                                    &pstRowData[i].intData,
                                    pstRowData[i].iBufferLength,
                                    &pstRowData[i].iReturnLength ) != SQL_SUCCESS )
                {
                    MessageBox(NULL, "Unable to bind Data Columns", "Error",MB_OK);
                    return;
                }
                break;
            default :
                if ( SQLBindCol( hndStatement,
                                    ( SQLSMALLINT ) ( i + 1 ),
                                    SQL_C_CHAR,
                                    pstRowData[i].pszData,
                                    pstRowData[i].iBufferLength,
                                    &pstRowData[i].iReturnLength ) != SQL_SUCCESS )
                {
                    MessageBox(NULL, "Unable to bind Data Columns", "Error",MB_OK);
                    return;
                }
                break;
            }
        }
        sqlRV = SQLFetch( hndStatement );
        return sqlRV;
    }