SqlAutmn library homepage

SqlAutmn library 2.0

Introduction

The SqlAutmn library is a set of C++ classes capable of supporting different RDBMS native APIs. It was specially designed for using along with Oracle8. Besides it provides uniform database application development and minimizes efforts of porting applications to different database server platforms.

Supported platforms

Oracle8 using OCI calls;

Key Features

Easy dynamic binding of program variables;
Buffering result set: several rows of a result set can be cached to decrease the number of fetching requests;
Support of BLOB data type;
Error-handling mechanism using exceptions or user handler;
The library was design to be maximum portable between different database platforms and C++ compilers, though hard testing is required;

Planning Features

Support of Centura SQLBase Server 7+;
Fully thread-safe;
Stored commands/procedures/functions;
Better support of date/time data type;

Comprehensive documentation!

Other abilities can be implemented on request. Remember that these features are being worked on while you are reading this document!

Requirements

SqlAutmn library requires Win32 platform and Oracle8 client software.

Using SqlAutmn library

Distribution Package

The distribution contains the following subdirectories:

BIN. It contains DLLs needed for your application.
DOCS. Contains documentation.
INCLUDE. It contains C++ header files required for using SqlAutmn library. You are supposed to include only SqlAutmn.h file. Others are for those who decide to you platform-specific features.
LIB. It contains the library that needs to be linked into your application.
SAMPLES. Several examples that shows you how to use the library.

Principles of the library

The SqlAutmn library logic is based on terms Connection and Cursor. Connection is a single user session. It allows to create Cursors, sets various attributes

Creating a connection object

The following example demonstrates, how to open database connection:

char sConnectString[32];
printf("Enter connect string in form of DATABASE/USERNAME/PASSWORD");
scanf("%s", sConnectString);
SQL_Connection *pConnection = CreateConnection(
                  sConnectionString, SQL_Connection::PT_Oracle8);

Opening a connection

In order to perform database operations one must open a connection using:

pConnection->Connect();

Closing an opened connection

pConnection->Disconnect();

Destroying a connection object

SQL_DestroyConnection(pConnection);

Making another connection

Very often it is needed to have more than one connections in order to perform simultaneous and independent transactions.

SQL_Connection *pAnotherConnection = pConnection->CreateConnection(true);

SQL_Connection::CreateConnection method also can be used to create a connection of the same database platform. Though it can be done as follow:

SQL_Connection *pAnotherConnection = SQL_CreateConnection(
               pConnection->GetURL(), pConnection->GetPlatformType());

But both cases still require manual opening connection:

pAnotherConnection->Connect();

Creating a cursor object

There are two was of creating a cursor:

SQL_Cursor *pCursor = SQL_CreateCursor(pConnection);

SQL_Cursor *pCursor = pConnection->CreateCursor();

Opening a cursor

This call is required before any database operations using this SQL_Cursor object.

pCursor->Connect();

Closing a cursor

pCursor->Disconnect();

Destroying a cursor object

SQL_DestroyCursor(pCursor);

Processing SQL statement

The following examples shows how to perform SQL statements:

pCursor->ExecuteSQL("create table A (I integer, DT date, L blob)");
pCursor->Commit();

Last line commits a transaction associated with the connection that opened the given SQL_Cursor object.

Binding variables

*pCursor << 100 << "1/1/99";
pCursor->ExecuteSQL("insert into A (I, DT) values (:1, :2)");

Operator << binds variables by numbers according to the ordinal number of a variable when calling <<. So the value 100 has bind name ':1', "1/1/99" has the bind value ':2' and so on. To change the bind names one can use manipulator set_id:

*pCursor << set_id("NUMBER") << 100 << set_id(1) << "1/1/99";
pCursor->ExecuteSQL("insert into A (I, DT) values (:NUMBER, :1)");

After using set_id("BINDNAME") the position of the next parameter will start from 1, so the last example can be modified as follow:

*pCursor << set_id(2) << 50 << set_id("NUMBER") << 50 << "1/1/99";
pCursor->ExecuteSQL("insert into A (I, DT) values (:2+:NUMBER, :1)");

Binding BLOB data type

The BLOB data type requires different scheme of binding:

char sBuffer[BUFFER_SIZE];
...
SQL_Long BlobValue("L");
*pCursor << 100 << "1/1/99" << BlobValue;
pCursor->ExecuteSQL("insert into A values(:1, :2, :3)");
pCursor->LongWrite(BlobValue, sBuffer, sizeof(sBuffer));
pCursor->LongEnd(BlobValue);
pCursor->LongComplete();

Step-by-step description of the sample:

Variable of type SQL_Long must be created with the column name containing BLOB field as a parameter for the SQL_Long constructor.
All bindings must be performed, including the binding of the created SQL_Long variable.
An SQL statement must be executed using method ExecuteSQL.
In this step multiple BLOB write operations can be performed using LongWrite method. The method LongEnd must be call at the end.
The LongComplete method must be invoked to complete BLOB write operations.

Processing queries

This call is required before any database operations using this SQL_Cursor object.

pCursor->ExecuteSQL("select * from A");
SQL_Row row_;
while (pCursor->FetchNext(&row_))
{
    int int_value_ = row_[1].IsNull() ? -1 : row_[1];
    const char *date_value_ = row_[2].IsNull() ? "NULL" : row_[2];
    printf("I = %4d  DT = %s", int_value_, date_value_);
}

SQL_Cursor::operator [] returns an object of type SQL_Field, which has a method IsNull for determining weather the column in the a row contains a NULL value.

Fetching BLOB data type

The BLOB value can be fetched as follow:

pCursor->ExecuteSQL("select * from A");
SQL_Row row_;
while (pCursor->FetchNext(&row_))
{
    SQL_Long BlobValue = row_[3];
    pCursor->LongRead(BlobValue, sBuffer, sizeof(sBuffer));
    pCursor->LongEnd(BlobValue);
    ...
}

After sequence of LongRead calls it is needed to call LongEnd method.

Transaction control

There are two methods of controlling transactions:

pCursor->Commit();

and

pCursor->Rollback();

The first one commits the changes made by the cursors associated with the single connection. Rollback method discards all such changes.

Error-handling mechanism

The SqlAutmn library has unified error-handling mechanism. When some error occures, the method HandleError is called. Both of SQL_Connection and SQL_Cursor has this method. The default behavior of SQL_Cursor::HandleError is to call SQL_Connection::HandleError. The last one generates C++ exception of type (SQL_Connection *). If your compiler doesn't support exception-handling mechanism used in Visual C++ 6.0 you can override that behavior to throw it manually by setting user error-handling function:

void MyErrorHandler( int Code, const char *sText,
                     SQL_Connection *pConnection, SQL_Cursor *pCursor)
{
    throw pConnection;
}
...
pConnection->SetErrorHandler(MyErrorHandler);
try
{
    pConnection->Connect();
    pCursor->Connect();
    ...
}
catch (SQL_Connection *)
{
    char sText[256];
    pConnection->GetErrorText(sText, sizeof(sText));
    printf("\nERROR: %s", sText);
}

Note: HandleError method is not called in constructors, destructors and global functions SQL_* to prevent from memory leaks.

Contact address: mailto:sqlautmn@mail.ru

Chat.ru ТЕЛПНЕОДХЕФ: ФПЧБТЩ ЙЪ лЙФБС ОБ УБКФЕ Asia.ru!