7. Understanding ODBC Connectivity : ODBC Programming : Transactions Handling
Share this page                  
Transactions Handling
This section explores how the ODBC Driver and Ingres DBMS handle transactions, and describes ODBC support for data types.
SQLSetConnectAttr()--Enable Autocommit
The Ingres DBMS supports standard transaction sessions. Standard transaction sessions begin a transaction when the first query is issued, and end when a commit or rollback command is executed. By contrast, autocommit sessions commit each insert, delete or update query in the DBMS. Standard transaction sessions delete prepared statements and cursor declarations after a commit or rollback; autocommit sessions retain prepared statements and cursor declarations.
The SQLSetConnectAttr() function enables or disables autocommit. The ODBC driver default is to enable autocommit. The following example disables autocommit and manages the transaction manually.
Example: SQLSetConnectAttr() Function
SQLHDBC hdbc;              /* Connection handle */
** Turn off autocommit.
SQLSetConnectAttr( hdbc,   /* Connection Handle */
    SQL_ATTR_AUTOCOMMIT,   /* Autocommit attribute */
    SQL_AUTOCOMMIT_OFF,    /* Autocommit disabled */
    0 );                   /* String length (n/a) */
The function SQLEndTran() commits or rolls back the transaction:
** Roll back the current transaction.
SQLEndTran(SQL_HANDLE_DBC,   /* Handle type */
    hdbc,                    /* Connection handle */
    SQL_ROLLBACK);           /* Roll back the transaction */
Simulated Autocommit for Cursors
The Ingres DBMS places a restriction on cursor declarations during autocommit in that multiple cursors cannot be declared. However, multiple cursors can be declared for standard transaction sessions.
The Ingres restriction has ramifications on the ODBC. The ODBC specification allows multiple cursor declarations regardless of whether autocommit is enabled or not.
For the ODBC Driver to support multiple cursors during autocommit, the driver internally reverts to a state named simulated autocommit. During simulated autocommit, when the ODBC driver detects that a cursor is opened, and an update, insert or delete query is to be executed, the ODBC driver internally disables autocommit. Commits are issued internally from the ODBC driver when:
The statement or connection handle is freed.
A cursor is closed, and no other cursors are open.
When all cursors are closed, the ODBC driver changes back to autocommit mode.
Simulated autocommit is not used if no cursors are open or if the only DBMS queries are fetch queries.
SQLSetStmtAttr()--Set Transaction Isolation Level
The ODBC Driver supports all transaction isolation levels available in the ODBC specification, including:
Read committed
Read uncommitted
Repeatable read
The above isolation levels are specified by the ODBC attributes SQL_ATTR_TXN_SERIALIZABLE, SQL_ATTR_TXN_READ_COMMITTED, SQL_TXN, READ_UNCOMMITTED, and SQL_TXN_REPEATABLE_READ, respectively. Transaction isolation is specified by SQLSetStmtAttr() function via the SQL_ATTR_TXN_ISOLATION connection attribute. The SQLGetStmtAttr() function returns the current isolation level when the SQL_ATTR_TXN_ISOLATION option is specified.
Other types of transaction isolation supported by Ingres, such as system, are not available in SQLSetStmtAttr(). Use SQLExecDirect() to execute the SET command directly in such cases. This is also the case when locking is specified with the SET LOCKMODE command, such as for row-level locking.
Distributed (XA) Transactions
The ODBC driver supports distributed (XA) transactions in the Windows environment using the Microsoft Distributed Transaction Coordinator. See Ingres ODBC and Distributed Transactions (Windows) in this guide for more information.
Supported Data Types
The ODBC Driver supports all ODBC data types except:
The ODBC function SQLBindParameter() allows coercion from "C" data types to SQL data types. Outside of the above exceptions, the ODBC supports all ODBC data type coercions as described in the "Converting Data from SQL to C Data Types" and "Converting Data from C to SQL Data Types" tables in the Microsoft ODBC Programmer's Reference. The following table summarizes the coercions available:
Data Type
SQL Type
All types
All types
All numeric
Timestamp, date and time
Timestamp, date and time