SOLID Light Client Programmer's Guide

SOLID Light Client Programmer's Guide

This document gives a brief overview on writing applications on SOLID Light Client API. The objective of this document is to provide the reader with sufficient knowledge for connecting to SOLID Server through Light Client library and completing the basic database operations. The reader is expected to have the basic knowledge on the following subjects:

C programming language
C development environments
SQL database language
relational database technology in general, SOLID Server in particular
ODBC in general

This document contains a general description of the services provided by the Light Client API and some code examples using the functions.

Background
Using SOLID Light Client
Running SQL Statements on Light Client
Special Notes about SOLID Server and SOLID Light Client
Notes for Programmers Familiar with ODBC

Background

SOLID Light Client API provides full access to SOLID Server relational database. It provides functions for controlling database connections, executing SQL statements, retrieving result sets, committing transactions and other SOLID Server functionality. SOLID Light Client API has a strong resemblance to ODBC, which is SOLID Server's native database API on all platforms. See SOLID Server Programmer's Guide and Reference for more information about the ODBC API.

Using SOLID Light Client

Getting Started

Getting started with using SOLID Light Client consists of following steps, each of which is briefly described below.

Setting up the TCP/IP infrastructure
Setting up the development environment and building the sample application
Connecting to database using the sample application

Setting up the TCP/IP Infrastructure

For more details see SOLID Light Client Installation Instructions and your platform specific documentation.

Setting up the Development Environment and Building a Sample Program

Building a program using SOLID Light Client library goes exactly like building any normal C/C++ program.If necessary, check your development environment documentation on the following:

insert the library file to your project
include header file
compile the source code
link the program

The first two issues are described in more detail below.

Insert the library file into your project.

Check your development environment's documentation on how to link a library to a program.

In DOS environment link the library slcdos23.lib
In NT environment link the library slcw3223.lib

Include header files

Following line needs to be included in a Light Client program.

#include "cli0lcli.h"

Other necessary Light Client headers are included by this header file. Insert the directory containing all the Light Client headers into your development environment's include directories setting.

Verifying the development environment setup

The easiest way to do this is to build a Light Client sample program. This enables you to verify your development and running environment without writing any code.

Note 1: In NT environment the TCP/IP services are provided by standard DLL wsock32.dll. To link these services into your project add wsock32.lib into linker's lib file list.
Note 2: In NT environment some development tools link odbc32.lib providing standard ODBC service as a default library to any project. Because (most of) the functions have similar names and interfaces the program may be linked to use ODBC instead of Light Client. Remove odbc32.lib from the linker's file list.

Connecting to Database using the Sample Application

Establishing a connection to database using SOLID Light Client library is very similar to establishing connections using ODBC. An application needs to obtain an environment handle, allocate space for a connection and establish a connection. Run the sample program to check whether it can obtain a connection to SOLID Server in your environment.

The following code establishes a connection to SOLID Server database running in a machine 192.168.1.111 and listening to tcp/ip at port 1313. User account "DBA" with password "DBA" has been defined in the database.

HENV henv;    /* pointer to environment object         */
HDBC hdbc;    /* pointer to database connection object */
RETCODE rc;   /* variable for return code              */

rc = SQLAllocEnv(&henv);
if (SQL_SUCCESS != rc)
{
    printf("SQLAllocEnv fails.\n");
    return;
}

rc = SQLAllocConnect(henv,&hdbc);
if (SQL_SUCCESS != rc)
{
    printf("SQLAllocConnect fails.\n");
    return;
}

rc = SQLConnect(hdbc,(UCHAR*)"192.168.1.111 1313",SQL_NTS, (UCHAR*)"DBA",SQL_NTS, (UCHAR*)"DBA", SQL_NTS);
if (SQL_SUCCESS != rc)
{
printf("SQLConnect fails.\n");
return;
}

The connection established above can be cleared using the code below. To make it easier to read no return code checking is included.

SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);

Running SQL Statements on Light Client

This chapter describes briefly how to do basic database operations with SQL. The following operations are presented here

Executing statements through SOLID Light Client
- Simple statement
- Statement with parameters
Reading result sets
Transactions and autocommit mode
Handling Database errors

For more detailed description on these subjects see other SOLID Server documentation.

Executing Statements with SOLID Light Client API

Simple Statement

The code below executes a simple SQL statement INSERT INTO TESTTABLE (I,C) VALUES (100,'HUNDRED').
The code expects a valid HENV henv and a valid HDBC hdbc to exist and variable rc of type RETCODE to be defined. The code also expects a table TESTTABLEwith columns I and C to exist in the database.

rc = SQLAllocStmt(hdbc, &hstmt);

if (SQL_SUCCESS != rc)
{
printf("SQLAllocStmt failed \n");
}

rc = SQLExecDirect(hstmt,(UCHAR*)"INSERT INTO TESTTABLE (I,C) VALUES (100,'HUNDRED')",SQL_NTS);
if (SQL_SUCCESS != rc)
{
printf("SQLExecDirect failed \n");
}

rc = SQLTransact(SQL_NULL_HENV,hdbc,SQL_COMMIT);
if ((SQL_SUCCESS != rc))
{
printf("SQLTransact failed \n");
}

rc = SQLFreeStmt(hstmt,SQL_DROP);
if ((SQL_SUCCESS != rc))
{
printf("SQLFreeStmt failed \n");
}

Statement with parameters

The code example below prepares a simple statement INSERT INTO TESTTABLE (I,C) VALUES (?,?)to be executed several times with different parameter values. Note, that the Light Client does not provide ODBC-like parameter binding. Instead, the values for parameters need to be assigned using the SQLSetParamValue function. Following variable definitions are expected.

char buf[255];
SDWORD dwPar;

As above, the code also expects a valid HENV henv and a valid HDBC hdbc to exist and variable rcof type RETCODE to be defined and a table TESTTABLE with columns I and C to exist in the database.

rc = SQLAllocStmt(hdbc, &hstmt);

if (SQL_SUCCESS != rc) {
    printf("Alloc statement failed. \n");
   }

rc = SQLPrepare(hstmt,(UCHAR*)"INSERT INTO TESTTABLE(I,C) VALUES (?,?)",SQL_NTS);

if (SQL_SUCCESS != rc) {
    printf("Prepare failed. \n");
}

for (i=1;i<100;i++)
{
    dwPar = i;
    sprintf(buf,"line%i",i);

    rc = m_lc->LC_SQLSetParamValue( hstmt,1,SQL_C_LONG,SQL_INTEGER,0,0,&dwPar,NULL );
    if (SQL_SUCCESS != rc) {
      printf("(SetParamValue 1 failed) \n");
      return 0;
    }

    rc = m_lc->LC_SQLSetParamValue( hstmt,2,SQL_C_CHAR,SQL_CHAR,0,0,buf,NULL );
    if (SQL_SUCCESS != rc) {
      printf("(SetParamValue 1 failed) \n");
      return 0;
    }

    rc = m_lc->LC_SQLExecute(hstmt);

    if (SQL_SUCCESS != rc) {
      printf("SQLExecute failed \n");
    }
}

rc = SQLFreeStmt(hstmt,SQL_DROP);
if ((SQL_SUCCESS != rc)) {
printf("SQLFreeStmt failed. \n");
}

Reading Result Sets

The following code excerpt prepares an SQL Statement "SELECT I,C FROM TESTTABLE", executes it and fetches all the rows the database returns.
The example code below expects valid definitions for rc, hdbc, hstmt and henv.

rc = SQLAllocStmt(hdbc, &hstmt);

if (SQL_SUCCESS != rc) {
    printf("SQLAllocStmt failed. \n");
   }

rc = SQLPrepare(hstmt,(UCHAR*)"SELECT I,C FROM TESTTABLE",SQL_NTS);

if (SQL_SUCCESS != rc) {
    printf("SQLPrepare failed. \n");
}

rc = SQLExecute(hstmt);

if (SQL_SUCCESS != rc) {
printf("SQLExecute failed. \n");
}

rc = SQLFetch(hstmt);

if ((SQL_SUCCESS != rc) && (SQL_NO_DATA_FOUND != rc)) {
printf("SQLFetch returned an unexpected error code . \n");
}

while (SQL_NO_DATA_FOUND != rc)
{
     rc = SQLGetCol(hstmt,1,SQL_C_LONG,&lbuf,sizeof(lbuf),NULL);
    if (SQL_SUCCESS == rc)
    {
      printf("LC_SQLGetCol(1) returns %d \n",lbuf);
     }
    else printf("Error in SQLGetCol(1) \n");

    rc = SQLGetCol(hstmt,2,SQL_C_CHAR,buf,sizeof(buf),NULL);

    if (SQL_SUCCESS == rc)
    {
      printf("SQLGetCol(2) returns %s \n",buf);
    }
    else printf("Error in SQL_GetCol(2) \n");

    rc = SQLFetch(hstmt);
}

rc = m_lc->LC_SQLFreeStmt(hstmt,SQL_DROP);
if ((SQL_SUCCESS != rc))
{
    printf("SQLFreeStmt failed. ");
}

Also the following Light Client API functions may be useful when processing result sets:

SQLDescribeCol
SQLGetCursorName
SQLNumResultCols
SQLSetCursorName

Transactions and Autocommit Mode

All SOLID Light Client connections have the autocommit option set off. There is no method in Light Client to set the option on.
Every transaction has to be committed explicitly. This can be achieved by calling the function SQLTransact.

To commit the transaction, call the function as follows

rc = SQLTransact(SQL_NULL_HENV,hdbc,SQL_COMMIT);

To roll the transaction back, call it as follows.

rc = SQLTransact(SQL_NULL_HENV,hdbc,SQL_ROLLBACK);

Handling Database Errors

Typically, after any Light Client API function has returned SQL_ERROR or SQL_SUCCESS_WITH_INFO more information about the error or warning can be obtained by calling the SQLError function. If the following code is run against a database where no table TESTTABLE is defined it will produce the appropriate error information.

As usually, the code expects a valid HENV henv and a valid HDBC hdbc to exist and variable rcof type RETCODE to be defined .

rc = SQLPrepare(hstmt,(UCHAR*)"SELECT I,C FROM TESTTABLE",SQL_NTS);

if (SQL_SUCCESS != rc)
{
char buf[255];
RETCODE rc;

    char szSQLState[255];
    char szErrorMsg[255];
    SDWORD nativeerror = 0;
    SWORD maxerrmsg = 0;

    memset(szSQLState,0,sizeof(szSQLState));
    memset(szErrorMsg,0,sizeof(szErrorMsg));

rc = SQLError( SQL_NULL_HENV,hdbc,hstmt,(UCHAR*)szSQLState,&nativeerror,
(UCHAR*)szErrorMsg,sizeof(szErrorMsg),&maxerrmsg);

    if (SQL_ERROR == rc)
    {
      printf("SQLError failed \n.");
    }
    else
    {
      printf("Error information dump begins:-------------\n");
      printf("SQLState '%s' \n",szSQLState);
      printf("nativeerror %i \n",nativeerror);
      printf("Errormsg '%s' \n", szErrorMsg);
      printf("maxerrmsg %i \n",maxerrmsg);
      printf("Error information dump ends:---------------\n");
    }
}

Check SOLID Server Programmer's Guide and Reference Appendix A for possible error codes.

Special Notes about SOLID Server and SOLID Light Client

Network Traffic in Fetching Data

SOLID Light Client communication does not support SOLID Server's RowsPerMessage setting. Every Light Client call to SQLFetch causes a network message to be sent between client and server. This affects performance when fetching large amounts of data.

Notes for Programmers Familiar with ODBC

Migrating ODBC Applications to using Light Client API

Migration to using SOLID Light Client from standard ODBC database interface requires some programming, if you are using ODBC functions not provided in the Light Client API. Roughly, the migration steps are as follows.

Rewiew how your application uses ODBC and estimate whether Light Client API functionality is sufficient for you. Some minor changes in your own code are to be expected, basically:
- Calls to ODBC Extension Level 1 functions should be converted to ODBC Core level functions
- Usually this means rewriting the application without SQLBindParameter and SQLBindCol
Download SOLID Light Client package.
Verify your environment using SOLID Light Client samples.
Modify the ODBC calls in your own code, rebuild and test your program.