<< SQLAPI - TOC - CSQL And JDBC >>
ODBC and CSQL
This chapter is intended as a guide to ODBC Programming and helps to get started.
ODBC (Open Database Connectivity) is a standard set of interfaces to SQL-Compliant databases for ODBC application and is one of the standard interfacing subsystems in CSQL database.
CSQL Provides ODBC Level 3 Driver and it indicates that most of the ODBC 3 APIs is supported and along with all-primitive data types, with the date, time and timestamp.
Header files and Library
The ODBC header files define the ODBC Functions to which program calls in the ODBC Driver, and also the data types and constants that are used with ODBC Functions. The header files are standard files provided by Microsoft. The CSQL ODBC Driver implements the functions that are specified in these header files.
The following are the listed Header files.
- Sql.h - which contains most of the definitions User’ll need
- Sqlext.h - which contains mostly additions for ODBC3
- Sqlucode.h - It is automatically included by sqlext.h
- Sqltypes.h - It is automatically included by sql.h
The CSQL provides a library libcsqlodbc.so with the install package. An application directly link to this library to access CSQL Database and this makes as a interface between an application and CSQL.
Data Types
In general ODBC uses two set of data types: SQL data types and C data types. SQL data types are used in data source and C data types are used in C code in the application.
Type Identifiers
To describe SQL and C data types, ODBC defines two sets of type identifiers and it describes the type of an SQL column or a C buffer. It is a macro is generally passed as a function argument.
The SQLBindParameter binds a variable of type SQL_DATE_STRUCT to a date parameter in an SQL statement. The C type identifier SQL_C_TYPE_DATE specifies the type of the date variable, and the SQL type identifier SQL_TYPE_DATE specifies the type of the dynamic parameter. Following code is the example,
SQL_DATE_STRUCT Date; SQLINTEGER slen = SQL_NTS; SQLBindParameter (stmt, 3, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_TYPE_DATE, 196,0, &Date, sizeof (Date), &slen);
SQL Data Types
SQL data types are the types in which data is stored in the data source.
Each database defines its own SQL data types. ODBC defines type identifier and describes the general characteristics of the SQL data types that might be mapped to each type identifier. It is completely driver-specific how each data type is mapped to an SQL type identifier of ODBC.
For Example, SQL_CHAR is the type identifier for a character column with a fixed length (between 1 and 254 characters). SQL_INTEGER for a integer column etc.
C Data Types
The CSQL ODBC driver application uses C data types to store values in application variables with their corresponding type identifiers. These are used as buffers that are bound to result set columns and statement parameter.
For Example, An application wants to retrieve the data from a result set column in character format. It have to declares a variable with the SQLCHAR * data type and binds this variable to the result set column with a type identifier of SQL_C_CHAR.
The following table describes the C data types and SQL data types that CSQL ODBC Driver provides.
CSQL ODBC Data Types
| SQL Definition | SQL Type Identifier | C Type Identifier |
| CHAR(n) | SQL_CHAR | SQL_C_CHAR |
| SMALLINT | SQL_SMALLINT | SQL_C_SSHORT |
| INTEGER | SQL_INTEGER | SQL_C_SLONG |
| REAL | SQL_REAL | SQL_C_FLOAT |
| FLOAT | SQL_FLOAT | SQL_C_FLOAT |
| DOUBLE | SQL_DOUBLE | SQL_C_DOUBLE |
| TINYINT | SQL_TINYINT | SQL_C_TINYINT |
| BIGINT | SQL_BIGINT | SQL_C_SBIGINT |
| DATE | SQL_TYPE_DATE | SQL_C_TYPE_DATE |
| TIME | SQL_TYPE_TIME | SQL_C_TYPE_TIME |
| TIMESTAMP | SQL_TYPE_TIMESTAMP | SQL_C_TYPE_TIMESTAMP |
Function Return Codes
Every ODBC function returns a return code that indicates whether the function invocation succeeded or failed.
The following table lists all possible return codes for CSQL ODBC Functions.
| Return Code | Explanation |
| SQL_SUCCESS | The function completed successfully |
| SQL_SUCCESS_WITH_INFO | The function completed successfully with a warning |
| SQL_NO_DATA_FOUND | The function returned successfully but no relevant data was found |
| SQL_ERROR | The function failed |
| SQL_INVALID_HANDLE | The function failed due to an invalid input handle |
If the function returns SQL_SUCCESS_WITH_INFO or SQL_ERROR, the application can call SQLError to retrieve additional information about the error.
Handles
ODBC application uses a small set of handles to define basic features such as database connections and SQL statements. A handle is a 32-bit value. ODBC application uses set of handles to work with DBMS, such as environment, database connections and statements. The relationship between these handles is depicted below. A program can have list of environment handles and each environment can have list of database connections and each connections can have list of statements.
TODO::DIAGRAM
In the above diagram environment handle, env1 is used to create two connection handles dbc1 and dbc2. Connection handle, dbc1 has three statements stmt1, stmt2, and stmt3 whereas dbc2 has only one statement handle namely stmt4.
The following handles are used in essentially all ODBC applications and are allocated by SQLAllocHandle () function.
The handle types required for ODBC programs are as follows:
| Item | Handle Type |
| Environment | SQLHENV |
| Connection | SQLHDBC |
| Statement | SQLHSTMT |
Environment
The environment handle provides a global context in which to access data. Every ODBC application must allocate exactly one environment handle upon starting, and must free it at the end.
The following code illustrates how to allocate an environment handle:
SQLHENV env; SQLRETURN rc; rc=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
Connection
An ODBC Driver and a data source specify a connection. An application can have several connections associated with its environment. Allocating a connection handle does not establish a connection; a connection handle must be allocated first and then used when the connection is established.
The following code illustrates how to allocate a connection handle:
SQLHDBC dbc; SQLRETURN rc; rc = SQLAllocHandle ( SQL_HANDLE_DBC, env, &dbc) ;
Statement
A statement handle provides access to a SQL statement and any information associated with it, such as result sets and parameters. Each connection can have several statements. Statements are used both for cursor operations (fetching data) and for single statement execution (e.g. INSERT, UPDATE, and DELETE).
The following code illustrates how to allocate a statement handle:
SQLHSTMT stmt; SQLRETURN rc ; rc = SQLAllocHandle( SQL_HANDLE_STMT , dbc, &stmt) ;
Connect to the Database
Allocate Environment Handle
An environment handle provides access to global information. To request an environment handle in application, call SQLAllocHandle () with the Handle Type argument set to SQL_HANDLE_ENV and the Input Handle argument set to SQL_NULL_HANDLE. CSQL ODBC allocates the environment handle and passes the value of the associated handle to the *OutputHandlePtr argument. After allocating Environment handle, the driver version needs to be set.
Setting up Driver Version
After allocating an environment handle, an application must call SQLSetEnvAttr on the environment handle to set the SQL_ATTR_ODBC_VERSION = SQL_OV_ODBC3 environment attribute. This requires that our application specify which version of the ODBC API it is using before the Connection handles are allocated. Refer the Section 4.8.22 to know details about SQLSetEnvAttr () function.
Allocate Connection Handle
A connection handle provides access to information such as the valid statement handles on the connection and an indication of whether a transaction is currently open. Using SQLAllocHandle( ) with the Handle Type argument set to SQL_HANDLE_DBC and set the Input Handle argument to the current environment handle.
This function will be called only after setting up the driver version using SQLSetAttr function.
Ways to Connect to Database
For the connection to CSQL database through ODBC Driver, Any one of the below method could be preferred.
- SQLConnect ()
- SQLDriverConnect ()
This method needs to be called after allocating connection handle and before allocate statement handle. Before starting connection the connection sting should be known to connect CSQL.
The general form of connection string is
DSN=<DSN Name>;MODE=<CSQL Mode>;SERVER=<Server Name>; PORT=<Port No>;
CSQL supports different modes in connection string, which is mentioned below.
Csql - Connect to CSQL database
Adapter - Connect to Target DB directly
Gateway - Connect to CSQL and Target DB
Network - Connect to CSQL with network
NetworkAdapter - Connect to CSQL through adapter for any operation in target DB directly with network
NetworkGateway - Connect to CSQL through gateway for any operation in target database as well as csql with network
SQLConnect ()
SQLConnect () establishes a connection to the target database. The application must supply a target SQL database.
This function needs to be call after allocating Connection handle and before allocates Statement Handle. The following code illustrates how to connect to CSQL.
ret = SQLConnect (dbc,
(SQLCHAR *) "test",
(SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root",
(SQLSMALLINT) strlen ("root"),
(SQLCHAR *) "manager",
(SQLSMALLINT) strlen (""));
Note: CSQL also accepts connection string as 2nd argument of SQLConnect ()
SQLDriverConnect ()
The following code illustrates how to connect to CSQL through SQLDriverConnect.
ret = SQLDriverConnect (dbc,
(SQLHWND)WindowHandle,
(SQLCHAR *)csqlConnectionString,
(SQLSMALLINT)StringLength,
(SQLCHAR *)OutConnectionString,
(SQLSMALLINT)BufferLength,
(SQLSMALLINT *)StringLength2Ptr,
(SQLUSMALLINT)DriverCompletion
Disconnect to the Database
Follow the steps to disconnect from CSQL
Freeing the Connection handle and Environment handle
SQLDisconnect( ) closes the connection that is associated with the database connection handle. Before SQLDisconnect( ) method is called, the SQLEndTran( ) must be called if an outstanding transaction exists on this connection.
The following code illustrates how to disconnect from CSQL.
ret = SQLDisconnect(dbc);
A sample program to Connect and Disconnect from the database.
/*
* Sample ODBC application
* This Simple ODBC application does the following using CSQL ODBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Closes the connection
*/
#include<stdio.h>
#include<stdlib.h>
#include<CSql.h>
#include<sql.h>
#include<sqlext.h>
int main()
{
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
checkrc(ret,__LINE__);
ret =SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
checkrc(ret,__LINE__);
ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc); checkrc(ret,__LINE__);
ret = SQLConnect (dbc,
(SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root", (SQLSMALLINT) strlen ("root"),
(SQLCHAR *) "manager", (SQLSMALLINT) strlen ("manager"));
if(SQL_SUCCEEDED(ret))
{
printf("\nConnect to the data source successfully\n");
}
else
{
printf("Failed to connect\n");
return 2;
}
ret = SQLDisconnect(dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
checkrc(ret,__LINE__);
return 0;
}
Using ODBC to execute SQL statements
ODBC is a widely accepted application-programming interface (API) for database access and uses Structured Query Language (SQL) as its database access language.
Creating and Dropping table
SQLExecDirect( ) prepares and executes the SQL statement in one step. It uses the current values of the parameter marker variables, if any parameter exists in the statement. The statement can only be executed once. The following code illustrates how to create a table
Creation of emp Table:
SQLHSTMT stmt;
SQLRETURN ret ;
SQLCHAR table[100]= "create table emp(eid int, ename char(20),salary float)";
ret = SQLExecDirect(stmt,table,SQL_NTS);
if (SQL_SUCCEEDED(ret))
{
printf(“Table ‘emp’ is created”);
}
Dropping of emp Table :
SQLCHAR table [100]= "drop table emp";
ret = SQLExecDirect (stmt,table,SQL_NTS);
if (SQL_SUCCEEDED(ret))
{
printf(“Table ‘emp’ is dropped”);
}
Sample Program to Create and Drop the table
/*
* Sample ODBC application
* This Simple ODBC application does the following using CSQL ODBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Create table ‘emp’ with 3 fields
* 4. Drop the table
* 5. Close the Connection.
*/
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sql.h>
#include<sqlext.h>
inline void checkrc(int rc, int line)
{
if(rc)
{
printf("ERROR %d at line %d\n",rc,line);
exit(1);
}
}
int main()
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
ret = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
checkrc(ret,__LINE__);
ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
checkrc(ret,__LINE__);
ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
checkrc(ret,__LINE__);
ret = SQLConnect(dbc,
(SQLCHAR*)"test", (SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root", (SQLSMALLINT) strlen ("root"),
(SQLCHAR *) "manager", (SQLSMALLINT) strlen ("manager"));
if(SQL_SUCCEEDED(ret))
{
printf("\connect to the data source successfully\n");
}
else
{
printf("Failed to connect\n");
return 2;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
checkrc(ret,__LINE__);
// Table creation
SQLCHAR table[100]= "create table emp(eid int, ename char(20),salary float)";
ret = SQLExecDirect(stmt,table,SQL_NTS);
checkrc(ret,__LINE__);
printf("Table 'emp' created \n");
SQLCHAR table1[50] = “drop table emp;” ;
ret = SQLExecDirect(stmt, table1,SQL_NTS);
if(SQL_SUCCEEDED(ret))
{
printf("\ndrop the ‘emp’ table \n");
}
ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
checkrc(ret,__LINE__);
ret = SQLDisconnect(dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
checkrc(ret,__LINE__);
return 0;
}
Data Manipulation
Following are the sequence of ODBC calls for inserting records into table
SQLAllocHandle - Allocate Environment Handle
SQLAllocHandle - Allocate Database Connection Handle
SQLAllocHandle - Allocate Statement Handle
SQLConnect - Connect to data source
SQLPrepare - Prepare Statement
SQLBindParameter - Bind parameters with buffers
SQLExecute- Execute statement
SQLTransact - Commit Transaction
SQLFreeHandle - Free Statement Handle
SQLDisconnect - Disconnect from data source
SQLFreeHandle - Free database connection handle
SQLFreeHandle - Free environment handle
Using SQLPrepare and SQLExecute
SQLPrepare : This function compiles a SQL Statement and stores the information in the provided statement handle. The application can reference this prepared statement by passing the statement handle to other functions.
If the statement handle is previously used with a query statement, SQLFreeStmt( ) must be called before calling SQLPrepare( ) .
The SQL statement string might contain parameter markers. A parameter marker represented by a ‘?’ character and is used to indicate a position in the statement in which an application-supplied value is to be substituted during the execution.
SQLExecute : SQLExecute () executes a statement, which is successfully prepared using SQLPrepare ( ), one or multiple times. The statement is executed using the current value of any application variables that were bound to parameter markers by SQLBindParameter( ). All parameters must be bound before calling SQLExecute ( ) .
Using SQLBindParameter Function
SQLBindParameter( ) is used to bind parameter markers in an SQL statement to application variables, for all C data types. In this case data is transferred from the application to the DBMS when SQLExecute () is called.
A parameter marker is represented by a ‘?’ character in an SQL statement and is used to indicate a position in the statement where an application –supplied value is to be substituted when the statement is executed. This value can be obtained from an application variable.
Committing Transaction
SQLTransact function is used to complete the transaction with commit or rollback. After performing all the required database operations (INSERT, UPDATE, DELETE, SELECT), application may decide to either commit the changes or rollback all the changes if any of the operation fail in a transaction. If a transaction is active on a connection, the application must call SQLTransact( ) before it can disconnect from the database.
The following code illustrates how to commit a transaction.
rc =SQLTransact(env,dbc,SQL_COMMIT); Where env is the environment handle dbc is the connection handle SQL_COMMIT specifies current transaction should be committed.
Simple program for INSERT
The records could be inserted using the functions like SQLPrepare, SQLBindParameter and SQLExecute.
/*
* Sample ODBC application
* This Simple ODBC application does the
* Following using CSQL ODBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Insert 10 rows in it.
* 4. Close the Connection.
*/
#include<stdio.h>
#include<stdlib.h>
#include<sql.h>
#include<sqlext.h>
#include<string.h>
inline void checkrc(int rc, int line)
{
if(rc)
{
printf("ERROR %d at line %d\n",rc,line);
exit(1);
}
}
int main()
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
checkrc(ret,__LINE__);
ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
checkrc(ret,__LINE__);
ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
checkrc(ret,__LINE__);
ret = SQLConnect (dbc,
(SQLCHAR*)"test",(SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root",
(SQLSMALLINT)strlen ("root"),
(SQLCHAR *) "manager",
(SQLSMALLINT) strlen (""));
if(SQL_SUCCEEDED(ret))
{
printf("\nConnect to the data source successfully\n");
}else {
printf("Failed to connect\n");
return 2;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
checkrc(ret,__LINE__);
// inserting records into the table.
int eid1 = 1000;
char ename1[20]="jitu";
float salary1 = 5500;
char ename2[10][20]= {"Praba","Kishor","Jitu","Sanjit","Sanjay","Bisi","Suman","Vikrant","Eti","Suba"};
SQLINTEGER slen=SQL_NTS;
ret = SQLPrepare(stmt,(unsigned char*)"insert into emp values(?,?,?);",SQL_NTS);
checkrc(ret,__LINE__);
ret= SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&eid1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,
SQL_CHAR,196,0,(void*)ename1,0,&slen);
checkrc(ret,__LINE__);
ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_REAL,0,0,&salary1,0,NULL);
checkrc(ret,__LINE__);
int i;
int count=0;
for( i=0;i<10;i++)
{
eid1++;
salary1 = salary1 + 1000;
strcpy(ename1,ename2[i]);
ret = SQLExecute(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
count++;
}
printf("%d rows inserted in 'emp' table\n",count);
ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
checkrc(ret,__LINE__);
ret = SQLDisconnect(dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
checkrc(ret,__LINE__);
return 0;
}
UPDATE
Using the above three functions, The records can be updated.
/*
* Sample ODBC application
*
* This Simple ODBC application does the
* following using CSQL ODBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Update 5 rows in it.
* 4. Close the Connection.
*/
#include<stdio.h>
#include<stdlib.h>
#include<sql.h>
#include<sqlext.h>
#include<string.h>
inline void checkrc(int rc, int line)
{
if(rc)
{
printf("ERROR %d at line %d\n",rc,line);
exit(1);
}
}
int main()
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
checkrc(ret,__LINE__);
ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
checkrc(ret,__LINE__);
ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
checkrc(ret,__LINE__);
ret = SQLConnect (dbc,
(SQLCHAR *) "test",
(SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root",
(SQLSMALLINT) strlen ("root"),
(SQLCHAR *) "manager",
(SQLSMALLINT) strlen (""));
if(SQL_SUCCEEDED(ret))
{
printf("\nConnect to the data source successfully\n");
}
else
{
printf("Failed to connect\n");
return 2;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
checkrc(ret,__LINE__);
// update rows in the table
ret = SQLPrepare(stmt,(unsigned char*)"update emp set eid=?,salary=? where eid = ?;",SQL_NTS);
checkrc(ret,__LINE__);
int eid1;
int eid2;
float salary1;
ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&eid1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_REAL,0,0,&salary1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&eid2,0,NULL);
int i,count=0;
eid1=5000;
for(i=0;i<5;i++)
{
eid2 = 1001 + i;
eid1++;
salary1= 20500+(500*i);
ret = SQLExecute(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
count++;
}
printf("%d rows updated in 'emp' table\n",count);
//fetch updated rows
char ename1[10]="jitu";
ret = SQLPrepare(stmt,(unsigned char*)"select * from emp where salary > 20000;",SQL_NTS);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,1,SQL_C_SLONG,&eid1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,2,SQL_C_CHAR,ename1,sizeof(ename1),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,3,SQL_C_FLOAT,&salary1,0,NULL);
checkrc(ret,__LINE__);
count=0;
ret = SQLExecute(stmt);
checkrc(ret,__LINE__);
printf("select updated rows\n");
while(SQL_SUCCEEDED(ret=SQLFetch(stmt)))
{
printf("eid=%d\tename=%s\tsal=%f\n",eid1,ename1,salary);
count++;
}
ret = SQLCloseCursor(stmt);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
checkrc(ret,__LINE__);
ret = SQLDisconnect(dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
checkrc(ret,__LINE__);
return 0;
}
DELETE
The following codes delete the rows in the table
/*
* Sample ODBC application
*
* This Simple ODBC application does the
* following using CSQL ODBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Update 5 rows in it.
* 4. Close the Connection.
*/
#include<stdio.h>
#include<stdlib.h>
#include<sql.h>
#include<sqlext.h>
#include<string.h>
inline void checkrc(int rc, int line)
{
if(rc)
{
printf("ERROR %d at line %d\n",rc,line);
exit(1);
}
}
int main()
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
checkrc(ret,__LINE__);
ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
checkrc(ret,__LINE__);
ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
checkrc(ret,__LINE__);
ret = SQLConnect (dbc,
(SQLCHAR *) "test",
(SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root",
(SQLSMALLINT) strlen ("root"),
(SQLCHAR *) "manager",
(SQLSMALLINT) strlen (""));
if(SQL_SUCCEEDED(ret))
{
printf("\nConnect to the data source successfully\n");
}
else
{
printf("Failed to connect\n");
return 2;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc,&stmt);
checkrc(ret,__LINE__);
// delete 5 rows from the table with parameter.
int eid1;
ret = SQLPrepare(stmt,(unsigned char*)"delete from emp where eid = ?;",SQL_NTS);
checkrc(ret,__LINE__);
ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&eid1,0,NULL);
checkrc(ret,__LINE__);
int i,count=0;
eid1=5000;
for(i=0;i<5;i++)
{
eid1++;
ret = SQLExecute(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
count++;
}
printf("Deleted first %d rows in 'emp' table\n,count);
// delete all the rows from the table
ret = SQLPrepare(stmt,(unsigned char*)"delete from emp;",SQL_NTS);
checkrc(ret,__LINE__);
ret = SQLExecute(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
printf("All the rows are deleted \n");
// drop the table from the database
SQLCHAR drop[30]="drop table emp;";
ret = SQLExecDirect(stmt,drop,SQL_NTS);
checkrc(ret,__LINE__);
printf("Table 'emp'dropped successfully\n");
ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
checkrc(ret,__LINE__);
ret = SQLDisconnect(dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
checkrc(ret,__LINE__);
return 0;
}
Retrieving Records
Following are the sequence of ODBC calls for retrieving records from table
SQLAllocHandle - Allocate Environment Handle
SQLAllocHandle - Allocate Database Connection Handle
SQLAllocHandle - Allocate Statement Handle
SQLConnect - Connect to data source
SQLPrepare - Prepare Statement
SQLBindParameter - Bind parameters with buffers if any
SQLBindCol – Bind application buffers to retrieve field values
SQLExecute- Execute statement
SQLFetch – Fetch all the records by calling this function in loop
SQLCloseCursor – Close the cursor
SQLTransact - Commit Transaction
SQLFreeHandle - Free Statement Handle
SQLDisconnect - Disconnect from data source
SQLFreeHandle - Free database connection handle
SQLFreeHandle - Free environment handle
When SQL statements is issued to the database, the select statements is required which could be Prepared by the SQLPrepare.
SQLBindCol, SQLFetch & SQLCloseCursor Function
SQLBindCol:
When records are retrieved from DBMS, it needs to be stored in program variables so that program can process that. SQLBindCol function is used to bind application buffer to ODBC driver so that field values are populated in these application buffers during result set fetch. It binds one column at a time.
This function is also used to specify any data conversion required. It is called once for each column in the result set that the application needs to retrieve.
SQLPrepare( ) is usually called before SQLBindCol( ) which would be called before SQLFetch( ) to transfer data to the storage buffers specified by this call.
The following code illustrates how to bind application buffer to an already prepared SELECT statement.
int eid; rc = SQLBindCol( stmt, 1, SQL_C_SLONG, &eid1, 0, NULL );
SQLFetch:
When a SELECT statement is executed using SQLExecute () function, the ODBC driver internally created a result set object internally, which contains all the records of the SELECT query. SQLFetch () function advances the cursor to the next row of the result set, and retrieves any bound columns. It retrives the data directly into variables you specify with SQLBindCol( ) When there are no records, it returns SQL_NO_DATA_FOUND or SQL_ERROR.
The following code illustrates how to retrieve all the records from the result set.
while (SQL_SUCCEEDED(rc = SQLFetch(stmt)))
{
//Field values are copied to binded buffers
//work with the record here
}
It takes statement handle as its argument.
SQLCloseCursor:
This function closes the open cursor on a statement handle. When SQLExecute function is called, it creates a result set and opens the cursor in case of SELECT statement. After finished working with result set, the cursor should be closed and the memory should be freed by using this Function. Refer the Section 4.8.12 to know about these functions and their arguments.
The following code illustrates how close the cursor to result set. It takes statement handle as its argument.
rc = SQLCloseCursor(stmt);
A sample CSQL ODBC Program for SELECT
/*
* Sample ODBC application
*
* This Simple ODBC application does the following using CSQL ODBC Driver.
*
* 1. Loading the Driver
* 2. Connects to CSQL using Driver
* 3. Select all the rows from ‘emp’table
* 4. Close the Connection.
*/
#include<stdio.h>
#include<stdlib.h>
#include<sql.h>
#include<sqlext.h>
#include<string.h>
inline void checkrc(int rc, int line)
{
if(rc)
{
printf("ERROR %d at line %d\n",rc,line);
exit(1);
}
}
int main()
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
checkrc(ret,__LINE__);
ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
checkrc(ret,__LINE__);
ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
checkrc(ret,__LINE__);
ret = SQLConnect (dbc,
(SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
(SQLCHAR *) "root", (SQLSMALLINT) strlen ("root"),
(SQLCHAR *) "manager", (SQLSMALLINT) strlen ("manager"));
if(SQL_SUCCEEDED(ret))
{
printf("\nConnect to the data source successfully\n");
}
else
{
printf("Failed to connect\n");
return 2;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
checkrc(ret,__LINE__);
// fetch the rows from the table
int eid1=100;
char ename1[20]="jitu";
float salary1 = 2000;
int j,count=0;
ret = SQLPrepare(stmt,(unsigned char*)"select * from emp;",SQL_NTS);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,1,SQL_C_SLONG,&eid1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,2,SQL_C_CHAR,ename1,sizeof(ename1),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,3,SQL_C_FLOAT,&salary1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLExecute(stmt);
checkrc(ret,__LINE__);
printf("Fetching starts on 'emp' table\n ");
while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
{
printf("eid=%d\tename=%s\tsalary=%f\n", eid1, ename1, salary1);
count++;
}
ret = SQLCloseCursor(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
printf("Total row fetched=%d\n",count);
ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
checkrc(ret,__LINE__);
ret = SQLDisconnect(dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
checkrc(ret,__LINE__);
ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
checkrc(ret,__LINE__);
return 0;
}
Closing transaction using COMMIT and ROLLBACK
For completing a transaction, commit or rollback is used. Issuing Commit end the transaction permanently which is opposite of Rollback.
SQLTransact function is used to complete the commit or rollback the operations performed. If a transaction is active on a connection, the application must call SQLTransact( ) before it can disconnect from the database.
ret = SQLTransact(env,dbc,SQL_COMMIT);
<< SQLAPI - TOC - CSQL And JDBC >>