SQLTransact requests a commit or rollback operation for all active operations on all hstmts associated with a connection. SQLTransact can also request that a commit or rollback operation be performed for all connections associated with the henv.

When SQLTransact returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLTransact and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

If hdbc is SQL_NULL_HDBC and henv is a valid environment handle, then the Driver Manager will attempt to commit or roll back transactions on all hdbcs that are in a connected state. The Driver Manager calls SQLTransact in the driver associated with each hdbc. The Driver Manager will return SQL_SUCCESS only if it receives SQL_SUCCESS for each hdbc. If the Driver Manager receives SQL_ERROR on one or more hdbcs, it will return SQL_ERROR to the application. To determine which connection(s) failed during the commit or rollback operation, the application can call SQLError for each hdbc.

If hdbc is a valid connection handle, henv is ignored and the Driver Manager calls SQLTransact in the driver for the hdbc.

If hdbc is SQL_NULL_HDBC and henv is SQL_NULL_HENV, SQLTransact returns SQL_INVALID_HANDLE.

If fType is SQL_COMMIT, SQLTransact issues a commit request for all active operations on any hstmt associated with an affected hdbc. If fType is SQL_ROLLBACK, SQLTransact issues a rollback request for all active operations on any hstmt associated with an affected hdbc. If no transactions are active, SQLTransact returns SQL_SUCCESS with no effect on any data sources.

If the driver is in manual-commit mode (by calling SQLSetConnectOption with the SQL_AUTOCOMMIT option set to zero), a new transaction is implicitly started when an SQL statement that can be contained within a transaction is executed against the current data source.

To determine how transaction operations affect cursors, an application calls SQLGetInfo with the SQL_CURSOR_ROLLBACK_BEHAVIOR and SQL_CURSOR_COMMIT_BEHAVIOR options.

If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_DELETE, SQLTranact closes and deletes all open cursors on all hstmts associated with the hdbc and discards all pending results. SQLTransact leaves any hstmt present in an allocated (unprepared) stated; the appliation can reuse them for subsequent SQL requests or can call SQLFreeStmt to deallocate them.

If the SQL_CURSOR_ROLLBACK_BEHAVIOR or SQL_CURSOR_COMMIT_BEHAVIOR value equals SQL_CB_PRESERVE, SQLTransact does not affect open cursors associated with the hdbc. Cursors remain at the row they pointed to prior to the call to SQLTransact.

For drivers and data sources that support transactions, calling SQLTransact with either SQL_COMMIT or SQL_ROLLBACK when no transaction is active will return SQL_SUCCESS (indicating that there is no work to be committed or rolled back) and have no effect on the data source.

Drivers or data sources that do not support transactions (SQLGetInfo fOption SQL_TXN_CAPABLE is 0) are effectively always in autocommit mode. Therefore, calling SQLTransact with SQL_COMMIT will return SQL_SUCCESS. However, calling SQLTransact with SQL_ROLLBACK will result in SQLSTATE S1C00 (Driver not capable), indicating that a rollback can never be performed.

See SQLParamOptions.

ODBC Router

ODBC Router transparently makes all ODBC drivers on a central Windows Server useable by your network's iPhone/iPod, iPad, Linux, Mac and Windows systems. Years in the making here in the USA, ODBC Router has saved its customers millions of dollars in DLL installations and support costs for less than the price of a new PC and a few minutes installation time. ODBC Router provides a low cost, turnkey database network with enterprise class IT support.

Ditching the ODBC Administrator Control Panel:

By linking your application with the free ODBC Router client-side SDK instead of the ODBC Driver Manager, the need for your end-users to deal with driver installation or an ODBC Control Panel is gone! We even provide a setup function your code may call to display a "network browser" that enables your customer to "find" their ODBC Router server on the network, then "choose" the exact database they want to work with and finally return to your application with a fully-formed ODBC connection-string that may be stored and passed back to SQLConnect or SQLDriverConnect anytime your user wants to initiate a connection that data source. No more walking your customers through the process of adding data sources to ODBC Control Panel or, in the case of non-Windows computers, no need to worry about whether or not a compatible third-party ODBC Driver Manager has been installed for use with your code. (Remember that ODBC Router supports iPhone/iPod, iPad, Mac OS 9, Mac OS X, Linux and Windows.) If there ever are any client-side ODBC support issues, AugSoft can handle them directly with the customer freeing you to focus on the application.

JDBC?

JDBC-in-ODBC drivers (like some 'actual' ODBC drivers, lol) launch a CPU-intensive virtual machine in the background on your machine, which is bad for battery powered laptops, high-volume web servers or entry level desktops (that typically have slow busses and drives). As the world shifted to laptops and shared servers, the whole "virtual machine" concept became a support nightmare and so these days good Java apps are compiled to run as native (not emulated) code. Java developers may use the operating system's native ODBC support from within the JDBC class library using the sun.jdbc.odbc.JdbcOdbcDriver driver with a URL as shown below.

jdbc:odbc:dsn[;key=value]*

Example:

jdbc:odbc:finance;UID=cfo;

IT techs may then complete the database connection on the Customer's machine using ODBC Router or the database vendor's official ODBC driver.

NOTE: By creating ODBC data sources with ODBC Router, your apps will enjoy native speed and database independent connections from either Java/C/C++/C#/ObjC or PHP/PERL/Python/Ruby/BASIC on Linux, Macintosh and Windows. Also be aware that using ODBC Router with the Mac platform is an especially good idea because database vendors have not kept their Mac drivers in sync with Windows and there are actual third-party vendors who wrap freeware and JDBC drivers inside of ODBC "shells" without warning their customers! This problem is of great concern to developers because fake drivers almost always fly past the IT guys who test with speed deamon desktops, but fail the enterprise when user laptops and iMacs take too long to run queries or slowly corrupt the database when they do. IT guys often chalk this up to "network problems" leaving users with poison drivers to avoid their database. ODBC Router addresses this issue by enabling official vendor supported Windows ODBC drivers (on a Windows Server) to be accessed from all platforms, network wide.

ODBC 3.x?

It's not here yet. Even in 2010, most ODBC drivers are ODBC 1.x and 2.x. The ODBC Driver Manager translates between 3.x and 2.x or 2.x and 1.x ODBC calls. Therefore, if you don't need UNICODE, it's a bad idea to use ODBC 3.x API calls. That said, UNICODE is a Good Thing and there are actually at least three databases that natively support it now, so look for 3.x to be here soon.

Need ODBC API Help?

We really know ODBC and we routinely provide code-level ODBC help to our customers. Our low cost ODBC Router systems are available now at our online store and if your site buys them, you may open a ticket to ask ODBC development questions --we offer both E-Mail and On-Call support options in seven of the G8's timezones! Be sure to test your ODBC Router and ask for any needed installation help before purchase because we aren't Fry's --no refunds please, our prices are too low for such nonsense and we're too busy supporting real Customers!