OpenClient++: C++ wrapper for Sybase Open Client library

• Download demo project - 140 Kb
• Documentation - 988 Kb

Introduction

Sybase Open Client is the interface (API) between client applications and Sybase database server. In fact, it allows client applications to communicate to any database server which supports Tabular Data Stream (TDS) protocol. Currently, Microsoft SQL server and Sybase database server support TDS protocol. Open Client API can be thought of having two components.

1. Programming Interface component implemented in C programming language which allows a user to build applications to communicate to Server. It contains Client-Library, DB-Library and CS-Library.
   • DB-Library, a collection of routines for use in writing client applications. DB-Library includes a bulk copy library and the two-phase commit special library.
   • Client-Library, a collection of routines for use in writing client applications. Client-Library is a new library, designed to accommodate cursors and other advanced features in the SYBASE 10.0 product line.
   • CS-Library, a collection of utility routines that are useful to both client and server applications. All Client-Library applications will include at least one call to CS-Library, because Client-Library routines use a structure which is allocated in CS-Library.
2. Open Client Network services includes network library which provides support for specific network protocols such as TCP/IP or DCENet. For more information, see www.sybase.com.

Tabular Data Stream protocol was developed by Sybase to provide client-server communication between Sybase client and Server. You can get protocol specifications from Sybase or see www.freetds.org.

OpenClient++

I wanted to learn the Open Client library to access Sybase ASE server (12_5) from my client application. The best way I found was to implement C++ wrapper which can provide thread safe, cross platform interface similar to MySQL++ or other database library. Current version of OpenClient++ library provides following features using Client-Library (CT_LIB and CS_LIB routines):

• C++ API
• Thread Safe access
• Cross platform (Windows and Linux)
• Error Handling: it provides error handling using C++ Exception handling as well as traditional way where return values of function call is checked. Exception handling can be enabled/disabled at library level or individual class level.
• Following SQL commands are supported:
  1. SELECT
  2. UPDATE
  3. DELETE
  4. INSERT
  5. COMPUTE: SUM, COUNT, MIN, MAX, AVG
• Good documentation generated using DOxygen.

Interface Design:

Following classes are implemented to develop this library.

1. Context:
   • Context: It is a singleton class which provides the instance of itself. It stores CS_CONTEXT structure and provides thread safe access context of client-library. It provides methods to set the debug level, enabled/disable exception handling, and call back function settings for error handling.
   • ContextException: It is an exception class for Context class. It is derives from the standard "Exception" class.
2. Connection:
   • Connection: It�s a wrapper class for CS_CONNECTION structure. It stores connection parameters and establishes connection with server.
   • ConnectException: It is an exception class for Connection class. It is derived from the standard "Exception" class.
3. Command:
   • Command: It�s a wrapper class for CS_COMMAND structure. It stores the SQL command string and type of command. It also builds and executes the commands using ct_command() and ct_send() functions of Client-Library.
   • CommandException: It is an exception class for Command class. It is derived from the standard "Exception" class.
4. Query:
   • Query: It executes the command string, processes the result received from the server, and stores it into a RecordSet.
   • QueryException: It is an exception class for Query class. It is derived from the standard "Exception" class.
5. Record Set:
   • Record: It�s a wrapper class for COLUMN_DATA structure. It stores information about columns and its value.
   • RecordSet: It stores the Record class objects, number of row updates, query success/failure, transaction ID and command ID.
6. Utility classes:
   • Mutex: It provides the synchronized access to Context class object. It is implemented in two different header files.
     1. Mutex.h: Mutex class implementation for Linux.
     2. MutexWin.h: Mutex class implementation for Windows.
   • template<class T> Lock: It provides the guard lock functionality for Mutex class which avoids indefinite mutex locking in error condition.

Here is the diagram showing relationship among the classes:

Usage:

The simple usage of this library involves the following steps:

• Set up the environment variables: get the instance of Context class which is a singleton class and set the environment variables like debug level, exception handling and error display. It provides the default call back functions to display the errors on the terminal. You can write your own custom call back functions and set it to context.
• Connect to the server: connection class provides multiple constructor and connect() methods to pass connection information.
• Create a Query Object: Connection class provides the method GetQuery() which creates the Query object by setting this as member variable.
• Send a Command: Query object provides the method Execute() to send a command to the Server, it processes the result received from the server, stores into RecordSet, and returns the RecordSet object.
• Result Processing: RecordSet object provides GetRecordsCount() method which returns total number of Records available. It provides GetRecord(index) method to retrieve the Record class object using which you can retrieve field name and value.

int main()
{
    try {
        // Get the context instance

        Context* pInstance = Context::GetInstance();
        std::cout << "Initialized the Context" << std::endl;
        
    }catch(ContextException &e) {
        std::cout << e.what() << std::endl;
        return -1;
    }
    try {
        // Set the debug level to all

        pInstance->SetDebugLevel(CS_DBG_ALL);
        // Set the call back handles for CS, CT and SRV libs

        pInstance->SetCallbackHandler(Context::CS_LIB,csmsg_callback);
        pInstance->SetCallbackHandler(Context::CT_LIB,ctmsg_callback);
        pInstance->SetCallbackHandler(Context::SRV_LIB, servermsg_callback);
    }catch(ContextException &e) {
        std::cout << e.what() << std::endl;
    }
    
    // Initialize the connection object

    Connection oConn("TestDB", "username", "password", "servername");
    try {
        // connect to server

        oConn.Connect();
    }catch (ConnectionException &e) {
        std::cout << e.what() << std::endl;
        return -1;
    }
    
    // Get the instance of Query class

    Query q = oConn.GetQuery();
    
    try {
        char sCommandBuff[256] = "";
        string sCmd;
        // execute the command untill userinput is "q"

        while(sCmd != "q") {
            std::cout << ">";
            std::cin.getline(sCommandBuff, 256);
            string sCmd = sCommandBuff;
            if(sCmd == "q")
                break;
            
            // Execute the query

            RecordSet &result = q.Execute(sCmd);
            if(result.IsSuccess()) {
                for(int i =0; i < result.GetRecordsCount(); i++) {
                    // Get the record

                    Record *pRecord = result.GetRecord(i);
                    if(!pRecord)
                      continue;
                    for(int j =0 ; j < pRecord->GetFieldsCount(); j++) {
                        std::cout << "Column:"
                                  << result.GetFieldName(j) 
                                  << "    Value:" 
                                  << pRecord->GetFieldValue(j) 
                                  << std::endl;
                    }
                 }
                 
                 std::cout << result.GetMessage()<< std::endl;
                 std::cout << "Result:"<<(bool)result
                            << " NumRows:" << result.GetRowsRead() 
                            << " CmdNum:" << result.GetCommandId()
                            << "Transaction Id:" 
                            << result.GetTransState() << std::endl;
             }else {
                 // print failure condition

                 std::cout << result.GetMessage() << std::endl;
                 std::cout << "Result:"<<(bool)result
                            << " NumRows:" << result.GetRowsRead() 
                            << " CmdNum:" << result.GetCommandId()
                            << "Transaction Id:" 
                            << result.GetTransState() << std::endl;
             }
                    
        } // while end

     
     }catch (QueryException &e) {
         std::cout << e.what() << std::endl;
    }
      return 0;
}

References:

• Sybase.
• Open Client Client-Library/C Reference Manual.
• FreeTDS.

Your comments, suggestions and rating for this article are appreciated.