<< Writing ClientApplication - TOC - ODBC and CSQL >>

SQLAPI (Proprietary Interface)

This section explains various interfaces in SQL API and their references along the way, which would help in writing applications to access the CSQL database.

What is SQL API

SQLAPI is CSQL’s Proprietary C++ interface. It is a highly efficient and convenient interface for CSQL main memory database. SQLAPI directly talks to the SQLEngine, It takes care of parsing the SQL statement and executing it using the storage manager which is used by DBAPI interface (Proprietary interface) . Primary sub modules of SQL engine are parser, executor, optimizer, etc.

CSQL’s implementation says that SQL API is placed in below of the ODBC and JDBC, so that ODBC and JDBC driver will use SQLAPI to access the SQLEngine. Applications shall also use SQLAPI directly, which is a proprietary C++ interface.

libcsqlsql – C++ Library

The application should link to the libcsqlsql.so library to access database. This library is available with the installation package of CSQL and is placed in ‘lib’ directory.

Important classes and Header Files

The following are some important classes and header files for SQLAPI interface.

Classes

• SqlFactory - Create appropriate implementation for SQLAPI.
• AbsSqlConnection- It represents a database connection to SQL engine.
• AbsSqlStatement - Handle to the SQL statement.

Header Files

• AbsSqlStatement.h
• SqlFactory.h
• Info.h

Error Handling

DbRetVal is one such enum which is defined in ErrorType.h file and used by SQL API. It contains all the error codes returned by all the functions.

The following are some sample error codes:

OK = 0,
ErrSysFatal = -1,
ErrSysInit = -2,
ErrNoPrivilege = -3,
ErrSysInternal = -4,
ErrNoExists = -5,
ErrNoMemory = -6,
.
.
.
SplCase = -100  

Data Type

The SQLAPI supports all primitive and non-primitive data types along with Date, Time and TimeStamp.

Primitive	Non-Primitive
typeInt	typeByteInt
typeLong	typeDate
typeLongLong	typeTime
typeFloat	typeTimeStamp
typeDouble	typeBinary
typeString

Connect to the Database

First, it needs to create the connection with the database, which is done by the following classes – SqlFactory, AbsSqlConnection

AbsSqlConnection  *con = NULL;
con = SqlFactory :: createConnection(CSql) ;
rv = con -> connect (“root” , “manager” ) ;

CSQL provides different modes to connection to it. Applications can use appropriate modes as mentioned below.

Below are the different modes and their uses in brief:

• CSql - Provides connection to the database locally.
• CSqlAdapter - Provides connection to the target database locally.
• CSqlGateway - Provides connection to the CSQL Gateway locally
• CSqlNetwork - Provides remote connection to CSQL through network
• CsqlNetworkAdapter - Provides remote connection to target DB through network
• CsqlNetworkGateway - Provides connection to the CSQL Gateway through network

Create and Set the statement for the connection

At this stage the SqlStatement object is created and set to the SqlConnection object as follows.

AbsSqlStatement*stmt = SqlFactory::createStatement(CSql);
stmt -> setConnection(con) ;

The second one sets the SqlConnection to let the SqlStatement talk to the database.

Create a table

Let us start with an example where a table is created in the CSQL database to store employee details, say

Create table EMP (
                   int empId, 
                   char name(20),
                   float sal
                 );

The following sections describe how to connect to the database, how to create a table, how to insert, update and delete records in the table and how to drop the Now the SQL statement should be prepared to create a table into the CSQL database.

Prepare the Statement

The SQL statements is prepared by the prepare() function before it executed by the execute() method.

rv = stmt->prepare(statement);

Where statement is a memory location pointing to the string holding the SQL statement that needs to be prepared before execution.

Make sure that ther is an SQL statement to prepare and execute before calling the above function. Preparation of the statement, is done by allocating memory for statement and copying the create table statement into the memory.

char statement[200] ;
strcpy (statement , “CREATE TABLE EMP(EID INT, ENAME  CHAR(20),SALARY  FLOAT);”);

Execute the Statement and release the memory

Once preparation is done, its time to execute the statement. This is done as follows – .

stmt->execute(rows);

It executes the SQL statement, internally and does the required job. The memory could be released by calling the below function.

stmt->free()

This releases all the memory that was allocated internally for the SqlStatement object. This must be done; otherwise it might lead to memory leaks for long running processes.

Insert records into the table

Now some records could be inserted into the table EMP.

Prepare the statement

First copy the INSERT SQL statement into a memory location and pass the address of that memory location to the prepare function –

strcpy( statement, “INSERT  INTO  EMP   VALUES( ?,  ?, ? ); );
.
.
.
rv = stmt -> prepare ( statement ) ;

If the above statement is being watched, the values are given as three '?'s separated by commas. This is called “parameterize” the fields of the table in the order mentioned during the definition of the table.

Start the transaction

For any operation to take place in the table like INSERT, UPDATE, DELETE or SELECT, a transaction needs to be started. This is done as below .

con->beginTrans(); 

The default isolation level of CSQL is READ_COMMITTED, if transaction is needed to start in other modes specify it as argument to beginTrans( ) method

Parameterize the fields

The SqlStatement object should pick the values from an allocated area of memory and it is required to parameterize all the three fields in the table as per the requirement in the example. It is done as shown below –

stmt->setIntParam(1, eid);
stmt->setStringParam(2, ename);
stmt->setFloatParam(3, salary);

The 1st argument is the position of the '?' in the INSERT statement starting from 1 and the 2nd argument is the value to be picked up for respective field.

In our example, the 1st parameter to be inserted is integer, 2nd one is String and 3rd one is float type, hence the function calls are in that order.

Suppose if the statement were to be

“insert into EMP values(?, 'Kishor', 123.0);” 

There is only one field to be parameterized and that should have been the integer type with position number 1.

If the statement were to be

“insert into EMP values(eid, ?, ?);” 

There are two fields to be parameterized and that should be String and Float type with position numbers 1 and 2 respectively.

Execute the insert statement

The inserting of a row is done one row at a time by the following function.

stmt->execute(rows);

This function receives an argument rows, which is a reference variable populated by the execute function for insert statement. Since this is an insert statement and always one row is inserted at a time.

Commit the transaction

After inserting 10 rows the transaction is completed by using commit method.

con->commit();  

The release the memory by calling

stmt->free() ;

This releases all the memory that was allocated internally for the SqlStatement object. As already mentioned, if this is not done, it might lead to memory leaks.

Read the records from the table

Executing select queries using the fetch () functions in the database. Below sub sections shows the functions to do this and sample programs

Read records from the table.

Now let us read all the records that were inserted into the table. For that the statement SELECT * FROM EMP; needs to be prepared.

Prepare the statement

strcpy(statement," SELECT * FROM EMP;");
rv = stmt->prepare(statement);

Bind the fields

Using bindField() function, the select statement able to produce the table records in a database. When this function is called the field values will be copied from the database to the buffer in memory. Using fetch( ) function the field value is generated.

stmt->bindField(1, &eid);
stmt->bindField(2, ename);
stmt->bindField(3, &salary);

Since the query projects all fields using *, the entire field values are to be fetched from the table according to the sequence they are in the table. Suppose the statement was to look like select EID, ENAME from EMP; then the binding would take place for only two parameters EID and ENAME with parameter position 1 and 2 respectively.

stmt->bindField(1, &eid);
stmt->bindField(2, ename);

Suppose the statement was to look like select SALARY, ENAME from EMP; then the binding should take place for only two parameters SALARY and ENAME with parameter position 1 and 2 respectively.

stmt->bindField(1, &salary);
stmt->bindField(2, ename);

Begin transaction and execute the statement

Any DML operation needs to start a transaction and the following statement does it

con->beginTrans();

Now execute the statement by calling the following function.

stmt->execute(rows);

This will set the condition for fetching the required records and initialize the appropriate iterator for picking up the records.

Fetch the records

Calling the fetch( ) function will return the address of each record in the database. Fetch will also copy the values from the database to the bound locations.

stmt->fetch(); 

Fetch will return one record at a time that satisfies the condition set in the statement. Here all the records have been selected hence it should show all the records present in the table. After fetching all the records commit the connection using

conn->commit();

Update some records

Now let us update some of the records in table EMP.

“Update EMP SET SALARY=?, ENAME=? where EID = ?;

This statement is prepared using

stmt->prepare(statement);

Begin the transaction by calling

con->beginTrans();

Now call

stmt->setFloatParam(1, salary); 
stmt->setStringParam(2, ename);
stmt->setIntParam(3, eid);

Now call,

stmt->execute(rows); 

To execute the prepared statement, commit the transaction.

conn->commit();

After committed the transaction, the ‘rows’ variable’s value gives the number of updated rows in the table. Refer the Section 3.13.3. for the Source Code

Delete records

To delete the tuples, where EID > 1006. The statement for this one would be Delete from EMP WHERE EID > 1006;

This statement is prepared using prepare () function. The transaction would have been started using beginTrans( ) to execute the statement using execute() function in the database. After executing the statement commit the transaction using commit () function.

stmt->prepare(statement);
con->beginTrans();
stmt->execute(rows);
conn->commit();

All the required fields are deleted based on the condition. Here ‘rows’ variable’s value gives the number of deleted rows from the table.

Check for the deleted values by creating another select statement after freeing that statement.

Sample Programs

Illustrating sample programs using SQL APIs.

Connect and Disconnect to CSQL

/*
 *	Sample SQL API application
 *
 *	This Simple C++ application does the
 *   following using CSQL SQL APIs.
 *
 *     1. Connects to the CSQL
 *     2. Disconnect from the database
 *     
 */

#include<SqlStatement.h>
int main()
{
DbRetVal rv = OK;
   	SqlConnection *con = new SqlConnection();
   	rv = con->connect("root", "manager");

if (rv != OK) return 1;
	printf(“Connection established\n”);

delete con;
printf(“Connection closed”);

return 0;
}

Insert and select

/*
 *	Sample SQL API application
 *
 *	This Simple C++ application does the
 *   following using CSQL SQL APIS.
 *
 *     1. Connects to the CSQL
 *     2. Created table EMP(EID INT,ENAME CHAR(20),SALARY FLOAT
 *     3. Performing INSERT statement,
 *     4. Using parameterized value
 *
 *     5.  Creates SELECT query. 
 *     6.  Fetches and dumps all the rows.   
 *           
 */

#include<SqlFactory.h>

int main()
{
DbRetVal rv = OK;

AbsSqlConnection *con = SqlFactory::createConnection(CSql);

rv = con->connect("root", "manager");
if(rv != OK) return 1;

AbsSqlStatement *stmt = SqlFactory::createStatement(CSql);

stmt->setConnection(con);
char statement[200];

strcpy(statement, "CREATE TABLE EMP(EID INT,ENAME CHAR(20),SALARY FLOAT);");

int rows = 0;

rv = stmt->prepare(statement);
if(rv != OK) { delete stmt; delete con; return 2; }

rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; delete con; return 3; }
stmt->free();
printf("Table Created\n");

strcpy(statement,” INSERT INTO EMP VALUES(?,?,?);");

int eid = 1000;
char ename[20] = "fasd";

char ename1[10][20]={"praba","kishor","jitu", "biswa",”suman","arin","arabi","sanjit","sanjay","rajesh"};

float salary = 0;
int count = 0;

rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 4; }

rv = con->beginTrans();
for(int i = 0; i < 10; i++) 	
{
   		eid++;
        	salary = salary + 1000;
        	strcpy(ename, ename1[i]);
          stmt->setIntParam(1, eid);
        	stmt->setStringParam(2, ename);
        	stmt->setFloatParam(3, salary);

     	rv = stmt->execute(rows); if(rv != OK) break;
        	count++;
}
     con->commit();
   	stmt->free();

   	printf("Total rows Inserted = %d\n", count);


//fetching the rows from EMP Table

 	strcpy(statement, "SELECT * FROM EMP;");
    	rv = stmt->prepare(statement);
    	if(rv!=OK) { delete stmt; delete con; return 5; }
   	stmt->bindField(1, &eid);
   	stmt->bindField(2, ename);
   	stmt->bindField(3, &salary);
   	count=0;


   	rv = con->beginTrans(); if(rv != OK) return 6;
   	stmt->execute(rows);

   	printf("\ninserted values are as follows\n");
   	printf("EmpId | name\t| salary\n");
   	printf("--------------------------\n");
   	while (stmt->fetch() != NULL)

printf("%d  | %s\t| %6.2f\n", eid, ename, salary);

 	rv = con->commit();
if(rv != OK){delete stmt; delete con; return 7; }

stmt->close();
stmt->free();
   	delete stmt;
delete con;
    	return 0;
}

Update and Select

/*
 *	Sample SQL API application
 *
 *	This Simple C++ application does the
 *   following using CSQL SQL APIS.
 *
 *     1. Conencts to CSQL using SQL API
 *     2. Open the ‘EMP’ table
 *
 *     3. Performing UPDATE statement,
 *        Where EID = 1001, 1003, 1005
 *
 *     4.  Creates SELECT query. 
 *     5.  Fetches and dumps all the rows.   
 *           
 */

#include<SqlFactory.h>
#include <AbsSqlStatement.h>

int main()
{

DbRetVal rv = OK;
AbsSqlConnection *con = SqlFactory ::              

rv = con->connect("root","manager");
if(rv!=OK)return 1;

AbsSqlStatement*stmt= SqlFactory :: createStatement(CSql);

stmt->setConnection(con);
char statement[200];

int rows = 0;

// UPDATE EMP WITH WHERE CLAUSE(EID<1006)
strcpy(statement, "UPDATE EMP SET SALARY=?, ENAME=? WHERE EID=?;" );

rv = stmt->prepare(statement);
if(rv != OK) { delete stmt; delete con; return 3; }

int eid = 1001;
char ename[20];
float salary;
strcpy(ename, "Mani");

char *name[20]= { "Ravi", "Kiran", "Ganesh", "Yogesh", "Vishnu" };

int i = 0;
while(true) 
{
  eid = 1001 + 2 * i;
  if (eid > 1005) break;
  strcpy(ename, name[i]);
  salary = 1111.00 * (1 + i);
  rv = con->beginTrans();
  stmt->setFloatParam(1,salary);
  stmt->setStringParam(2,ename);
  stmt->setIntParam(3, eid);
  if(rv != OK){ delete stmt; delete con;return 4; }

  rv = stmt->execute(rows);
  rv = con->commit();
  if(rv != OK){delete stmt; delete con; return 5;}
  i++;
 }
 stmt->free();


  	//fetching the rows from EMP Table

   	strcpy(statement, "SELECT * FROM EMP;");
   	rv = stmt->prepare(statement);
   	if(rv != OK) { delete stmt; delete con; return 6; }

   	int count=0;

    stmt->bindField(1, &eid);
    stmt->bindField(2, ename);
    stmt->bindField(3, &salary);

    printf("updated values are as follows\n");
    printf("EmpId | name\t| salary\n");
    printf("--------------------------\n");

    rv = con->beginTrans();
    if(rv != OK) { delete stmt; delete con; return 6; }
    stmt->execute(rows);
    while(stmt->fetch() != NULL)

    printf("%d  | %s\t| %6.2f\n", eid, ename, salary);
   	rv = con->commit();

    if(rv != OK) { delete stmt; delete con; return 7; }
   	stmt->close();
   	stmt->free();
   	delete stmt;
   	delete con;
   	return 0;
}

Delete Records

/*
 *	Sample SQL API application
 *
 *	This Simple C++ application does the
 *   following using CSQL SQL APIS.
 *
 *     1. Conencts to CSQL using Driver
 *     2. Open the ‘EMP’ table
 *     3. Performing DELETE statement
 *     4. Where EID > 1005
 *
 *     5.  Creates SELECT query. 
 *     6.  Fetches and dumps all the rows.   
 */   

#include<SqlFactory.h>
int main()
{
    DbRetVal rv = OK;
AbsSqlConnection *con = SqlFactory ::   createConnection(CSql);

 	rv = con->connect("root","manager");
    if(rv!=OK)return 1;

AbsSqlStatement *stmt = SqlFactory :: createStatement(CSql);

 	stmt->setConnection(con);
     if(rv!=OK)return 2;
     char statement[200];
    int rows = 0;

//    deleteing tuples from EMP;

    int eid = 1000;
    char ename[20]="smith";
    float salary = 2000;

strcpy(statement,"DELETE FROM EMP WHERE EID > 1005;");

 	rv = stmt->prepare(statement);
     if(rv!=OK) { delete stmt; delete con; return 3; }
   	rv = con->beginTrans();
   	if(rv!=OK) { delete stmt; delete con; return 4; }
   	rv = stmt->execute(rows);
   	if(rv!=OK) { delete stmt; delete con; return 5; }
   	printf("Rows Deleted successfully\n");
   	rv = con->commit();
   	if(rv!=OK) { delete stmt; delete con; return 6; }
   	stmt->free();

   	// fetching the rows from EMP Table
   	strcpy(statement,"SELECT * FROM EMP;");
   	rv = stmt->prepare(statement);
   	if(rv!=OK) { delete stmt; delete con; return 7; }

   	stmt->bindField(1,&eid);
   	stmt->bindField(2,ename);
   	stmt->bindField(3,&salary);

     printf("EmpId | name\t| salary\n");
   	printf("--------------------------\n");

    int count=0;
    rv = con->beginTrans();
    if(rv!=OK)return 6;
    stmt->execute(rows);
    while(stmt->fetch() !=NULL)

    printf("%d  | %s\t| %6.2f\n", eid, ename, salary);
    rv = con->commit();
    if(rv!=OK) { delete stmt; delete con; return 7; }
    stmt->close();
    stmt->free();

    strcpy(statement,"DROP TABLE EMP;");
    rv = stmt->prepare(statement);
    if(rv!=OK) { delete stmt; delete con; return 9; }
    rv = stmt->execute(rows);
    if(rv!=OK) { delete stmt; delete con; return 10;  }
    stmt->free();
    printf("Table dropped\n");
    delete stmt;
    delete con;
    return 0;
}

<< Writing ClientApplication-TOC - ODBC and CSQL >>

Page last modified on October 13, 2009, at 04:30 AM