Click here to Skip to main content
Click here to Skip to main content
Go to top

Accesing DB2 Databse using DB2 CLI

, 4 Oct 2012
Rate this:
Please Sign up or sign in to vote.
C++ code to connect/access DB2 database using DB2 call level interface(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;
    } 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ritesh_Singh
Software Developer (Senior) Nihilent Technologies Pvt Ltd
South Africa South Africa
I am working with Nihilent Technologies Pvt Ltd.I have worked on Banking and Finance domain, mainly in Cash Management and Repo Trading.
Technologies:-
My technological forte is VC++, C++, Win32, MFC, ATL/COM, C#.NET Windows Application, C, WebSphere MQ and DB2.
Specialties
Domain:-
BFS (Cash Management and Repo Trading)
Technologies:-
C, C++, VC++, Win32, MFC, ATL/COM, WebSphere MQ, DB2

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 4 Oct 2012
Article Copyright 2012 by Ritesh_Singh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid