This section describes how to develop an application with ODBC functions.
For the sample code, we assume a database called basketball. The basketball database contains one table called players, which contains a player's name and team.
Create the table by issuing the following SQL statements:
-- create the players table CREATE TABLE players( name varchar(25), team varchar(50) ); |
Populate the table with the following data:
-- insert 3 records into the players table
INSERT into players VALUES ('Michael Jordan', 'Washington Wizards');
INSERT into players VALUES ('Tim Duncan', 'San Antonio Spurs');
INSERT into players VALUES ('Vince Carter', 'Toronto Raptors'); |
The include files necessary for the ODBC functions, iodbc.h and isqlext.h, can be found under /usr/include/pgsql/iodbc/ for a standard Red Hat Database installation. The library libpsqlodbc.a is located under /usr/lib/.
When using the ODBC driver, the first step is to allocate the environment handle, which is an environment storage area. The allocation of the environment handle must be the first routine called by the application using the ODBC interface. After an environment handle is allocated, it is used to allocate a connection handle. The declarations for the environment handle and connection handle are as follows:
HENV henv; /* environment handle */ HDBC hdbc; /* connection handle */ |
To allocate the environment handle:
SQLAllocEnv(&henv); |
To allocate the connection handle associated with the environment:
SQLAllocConnect(henv, &hdbc); |
When the SQLAllocEnv or SQLAllocConnect are called, the driver manager allocates a structure for storing the information about the environment and the connection.
To connect to a database, specify the DSN, userid and password (if they are not specified in the DSN definition in the .odbc.ini file) when calling the function SQLConnect. The particular DSN definition must exist in the .odbc.ini file. If the sample .odbc.ini file in the Access Information File section is used, the following will connect to the data source RHDB as user postgres and password postgres:
SQLCHAR DSN[10] = "RHDB"; SQLCHAR DSN_userid[9] = "postgres"; SQLCHAR DSN_password[9] = "postgres"; SQLConnect(hdbc, DSN, SQL_NTS, DSN_userid, SQL_NTS, DSN_password, SQL_NTS); |
When SQLConnect is called, the driver manager looks for the driver to be used and checks to see if a driver has been loaded for the particular connection. If no driver is loaded in the environment for the connection, the driver manager loads the driver and allocates the environment handle.
To perform a query, a statement handle is required and it has to be allocated before it is used. The following shows the declaration and allocation of a statement handle:
HSTMT hstmt; SQLAllocStmt(hdbc, &hstmt); |
After the statement handle is allocated, it has to be prepared so that the SQL statement is sent to the data source and compiled for execution. The actual SQL query can be stored in a SQLCHAR array as follows:
SQLCHAR SelectStmt[255]; strcpy ((char *) SelectStmt, "SELECT * FROM players"); SQLPrepare(hstmt, SelectStmt, SQL_NTS); |
After the SQL statement is successfully prepared, the SQLExecute routine executes the SQL statement. Assume there is a table named players in the database specified in the RHDB DSN, the following shall execute successfully:
SQLExecute(hstmt); |
By changing the content of SelectStmt, updates and deletes to the tables can be performed. The following shows the deletion of a record from the same table:
strcpy ((char *) SelectStmt, "DELETE FROM players WHERE "); strcat ((char *) SelectStmt, "name=\'Michael Jordan\'"); SQLExecDirect(hstmt, SelectStmt, SQL_NTS ); |
The effect of SQLExecDirect is equivalent to issuing SQLPrepare and SQLExecute.
After a SELECT has been performed, results will be returned to the application. In order to traverse a query result set, one needs to bind all the columns returned with variables in the application. When the bindings occur, the application describes the variable to the driver, these information are stored in the structure for the statement handle, and are used to extract the value from the columns when a row is fetched. Assume the table has 2 columns, both of type character, the following will bind columns 1 and 2 in the result set to variables cname1 and cname2 in the application respectively:
SQLBindCol(hstmt, 1, SQL_C_CHAR, cname1, sizeof (cname1), NULL); SQLBindCol(hstmt, 2, SQL_C_CHAR, cname2, sizeof (cname2), NULL); |
After the columns are successfully bind to variables in the application, performing SQLFetch will retrieve the next result from the result set. The following will traverse the result set until no more data is found:
res = SQLFetch(hstmt);
while (res != SQL_NO_DATA_FOUND) {
printf("%s %s \n", cname1, cname2);
res = SQLFetch(hstmt);
} |
So far, each SQL statement has been atomic, that is, each statement can stand on its own and if one statement fails, others are not affected and the database is left in a consistent state. By default, ODBC is in "auto-commit" mode, where each statement is either committed as soon as it succeeds or rolled back if it fails.
There are times when multiple statements need to be grouped together into one atomic action or transaction. The SQLSetConnectOption function is used to switch between auto-commit or manual-commit mode. When auto-commit mode is turned off, SQL statements are executed in manual-commit mode, the application controls when to commit or rollback. The first statement executed after turning off auto-commit mode or after committing or rolling back a transaction starts a new transaction. The SQLTransact function commits or rolls back a transaction.
The following shows how to turn auto-commit off:
SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); |
After auto-commit is turned off, the transaction begins with the next executable statement. When all the statements that are grouped together have been executed, the transaction can either be committed or rolled back. To commit or rollback all the transactions associated with the connection handle:
SQLTransact(henv, hdbc, SQL_COMMIT); |
Replace SQL_COMMIT by SQL_ROLLBACK to terminate all the transactions and remove the changes made within those transactions associated with the connection handle.
All transactions must be ended before the connection to the data source is terminated.
Closing a connection is fairly simple: all that is required is disconnect and free the explicitly allocated handles. To disconnect from the database and release all the handles and the memory associated with them:
SQLFreeStmt(hstmt, SQL_CLOSE); /* free the statement handle */ SQLDisconnect(hdbc); /* disconnect from the database */ SQLFreeConnect(hdbc); /* free the connection handle */ SQLFreeEnv(henv); /* free the environment handle */ |