Programming Interfaces Guide
   CHAPTER 4. ODBC Programming     

ODBC fundamentals

Database access in ODBC is carried out using SQL statements passed as strings to ODBC functions.

The following fundamental objects are used for every ODBC program. Each object is referenced by a handle.

• Environment     Every ODBC application must allocate exactly one environment using SQLAllocEnv, and must free it at the end of the application using SQLFreeEnv. An environment handle is of type HENV.

• Connections     An application can have several connections associated with its environment. Each connection must be allocated using SQLAllocConnect and freed using SQLFreeConnect. Your application can connect to a data source using either SQLConnect, SQLDriverConnect or SQLBrowseConnect, and disconnect using SQLDisconnect. A connection handle is of type HDBC.

• Statements     Each connection can have several statements, each allocated using SQLAllocStmt and freed using SQLFreeStmt. Statements are used both for cursor operations (fetching data) and for single statement execution (e.g. INSERT, UPDATE and DELETE). A statement handle is of type HSTMT.

All access to these objects is through function calls; the application cannot directly access any information about the object from its handle. In the Windows and Windows NT environments, all the function calls are described in full detail in the ODBC API help file, which is part of the ODBC SDK.

  Compiling and linking an ODBC application

Every C source file using ODBC functions must include one of the following lines:

• Windows 95 and NT     #include "ntodbc.h"

• Windows 3.x     #include "winodbc.h"

• OS/2     #include "os2odbc.h"

• QNX     #include "qnxodbc.h"

These files all include the main ODBC include file odbc.h, which defines all of the functions, data types and constant definitions required to write an ODBC program. The file odbc.h and the environment-specific include files are installed in the h subdirectory of your Adaptive Server Anywhere installation directory.

Once your program has been compiled, you must link with the appropriate import library file to have access to the ODBC functions:

• Windows 95 and NT     wodbc32.lib, which defines the entry points into the Driver Manager odbc32.dll. If you connect to a database in Windows NT, odbc32.dll will load the ODBC driver, dbodbc6.dll.

• Windows 3.x     wodbc.lib, which defines the entry points into the Driver Manager odbc.dll. If you connect to a database in Windows 3.x, odbc.dll will load the Adaptive Server Anywhere ODBC driver, dbodbc6w.dll.

• OS/2     wodbc2.lib, which defines the entry points into the Adaptive Server Anywhere ODBC driver, dbodbc62.dll.

• UNIX     dbodbc.lib, which defines the entry points into the Adaptive Server Anywhere ODBC driver, dbodbc6.so.

  A first example

The following is a simple ODBC program:

{
   HENV   env;
   HDBC   dbc;
   HSTMT   stmt;
   
   SQLAllocEnv( &env );
   SQLAllocConnect( env, &dbc );
   SQLConnect( dbc, "asademo", SQL_NTS,
               "dba", SQL_NTS, "sql", SQL_NTS );
   SQLSetConnectOption( dbc, SQL_AUTOCOMMIT, FALSE );
   SQLAllocStmt( dbc, &stmt );
   
   /* Delete all the order items for order 2015 */
   SQLExecDirect( stmt,
      "DELETE FROM sales_order_items WHERE id=2015",
       SQL_NTS );
   
   /* Use rollback to undo the delete */
   SQLTransact( env, dbc, SQL_ROLLBACK );
   SQLFreeStmt( stmt, SQL_DROP );
   SQLDisconnect( dbc );
   SQLFreeConnect( dbc );
   SQLFreeEnv( env );
}

Notes 

• SQL_NTS     Every string passed to ODBC has a corresponding length. If the length is unknown, you can pass SQL_NTS indicating that it is a Null Terminated String whose end is marked by the null character (\0).

• SQLSetConnectOption     By default, ODBC operates in auto-commit mode. This mode is turned off by setting SQL_AUTOCOMMIT to false.

• SQLExecDirect     Executes the SQL statement specified in the parameter.

• SQLTransact     Used to perform COMMIT and ROLLBACK statements marking the end of a transaction. You should not use SQLExecDirect to perform COMMIT or ROLLBACK.

  Threads in ODBC applications

You can develop multi-threaded ODBC applications for Adaptive Server Anywhere. It is recommended that you use a separate connection for each thread.

You can use a single connection for multiple threads. However, the database server does not allow more than one active request for any one connection at a time. If one thread executes a statement that takes a long time, all other threads must wait until the request is complete.