Re: Manual commit ODBC transactions?

Posted in 2004

Sheshnarayan Agrawal — Thu, 29 Apr 2004 10:37:20 -0500 — source: Usenet: comp.databases.informix

--0__=08BBE416DFC79EE78f9e8a93df938690918c08BBE416DFC79EE7 Content-type: multipart/alternative; Boundary="1__=08BBE416DFC79EE78f9e8a93df938690918c08BBE416DFC79EE7" --1__=08BBE416DFC79EE78f9e8a93df938690918c08BBE416DFC79EE7 Content-type: text/plain; charset=US-ASCII Hi Rob, I am not very familiar with ODBCExpress. However, I would like to give you an idea about how you can do the same using small ODBC application. First of all you have to make sure what type of database you are dealing with i.e. logging, bufferd logging or ANSI. For more info pl. refer your Server Administrator's Guide. http://publib.boulder.ibm.com/epubs/pdf/ct1ucna.pdf You might also want to refer IBM Informix ODBC Programmers's manual, see page #56-57. http://publib.boulder.ibm.com/epubs/pdf/ct1ufna.pdf For example lets assume you are working with ANSI database, your ODBC C app would look like as follows to perform the manual commit. Create your DSN with default settings. That means let the "Auto Commit Optimization" check box be checked. 1. Allocate required handles. 2. SQLConenct/SQLDriverConnect ( ...."connect with ANSI database" .....); 3. Set SQL_ATTR_AUTOCOMMIT attribute to SQL_AUTOCOMMIT_OFF using SQLSetConnectAttr. 4. Allocate statement handle. 5. Execute "Begin work;" using SQLExecDirect(); // This will start the transaction. 6. Do whatever you want ....select, insert, update etc. 7. Execute SQLEndTran either with SQL_ROLLBACK or SQL_COMMIT. // This will end the transaction after performing the necessary operation. 8. Do necessary cleanup. I am hoping if you are connecting to ANSI database then even using ODBCExpress you should be able to run it successfully, if you are sure that the above steps are being taken care under the cover. HTH Thanks -Shesh Rob <nospam@nowhere.c To: informix-list@iiug.org om> cc: Sent by: Subject: Manual commit ODBC transactions? owner-informix-li st@iiug.org 04/27/2004 07:44 AM Please respond to Rob Hello, When attempting a manual commit, I receive a "Driver not capable" error message. Is it possible to use manual transactions with Informix v9.4 and ODBC driver v3.82 ? I tried the ODBC advanced setup option "Auto commit optimization" both ways with the same result. I am using ODBCExpress with StartTransact(), Commit(), and EndTransact() sequence to manually manage transactions. This works great with MSSQL. Is there a better ODBC driver available? Rob. --1__=08BBE416DFC79EE78f9e8a93df938690918c08BBE416DFC79EE7 Content-type: text/html; charset=US-ASCII Content-Disposition: inline <html><body> Hi Rob, I am not very familiar with ODBCExpress. However, I would like to give you an idea about how you can do the same using small ODBC application. First of all you have to make sure what type of database you are dealing with i.e. logging, bufferd logging or ANSI. For more info pl. refer your Server Administrator's Guide. <a href="http://publib.boulder.ibm.com/epubs/pdf/ct1ucna.pdf">http://publib.boulder.ibm.com/epubs/pdf/ct1ucna.pdf</a> ; You might also want to refer IBM Informix ODBC Programmers's manual, see page #56-57. <a href="http://publib.boulder.ibm.com/epubs/pdf/ct1ufna.pdf">http://publib.boulder.ibm.com/epubs/pdf/ct1ufna.pdf</a> ; For example lets assume you are working with ANSI database, your ODBC C app would look like as follows to perform the manual commit. Create your DSN with default settings. That means let the "Auto Commit Optimization" check box be checked. 1. Allocate required handles. 2. SQLConenct/SQLDriverConnect ( ...."connect with ANSI database" .....); 3. Set SQL_ATTR_AUTOCOMMIT attribute to SQL_AUTOCOMMIT_OFF using SQLSetConnectAttr. 4. Allocate statement handle. 5. Execute "Begin work;" using SQLExecDirect(); // This will start the transaction. 6. Do whatever you want ....select, insert, update etc. 7. Execute SQLEndTran either with SQL_ROLLBACK or SQL_COMMIT. // This will end the transaction after performing the necessary operation. 8. Do necessary cleanup. I am hoping if you are connecting to ANSI database then even using ODBCExpress you should be able to run it successfully, if you are sure that the above steps are being taken care under the cover. HTH Thanks -Shesh <img src="cid:10__=08BBE416DFC79EE78f9e8a93df938@us.ibm.com" width="16" height="16" alt="Inactive hide details for Rob <nospam@nowhere.com>">Rob <nospam@nowhere.com> <table V5DOTBL=true width="100%" border="0" cellspacing="0" cellpadding="0"> <tr valign="top"><td width="1%"><img src="cid:20__=08BBE416DFC79EE78f9e8a93df938@us.ibm.com" border="0" height="1" width="72" alt=""> </td><td style="background-image:url(cid:30__=08BBE416DFC79EE78f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " width="1%"><img src="cid:20__=08BBE416DFC79EE78f9e8a93df938@us.ibm.com" border="0" height="1" width="225" alt=""> <ul> <ul> <ul> <ul>Rob <nospam@nowhere.com> Sent by: owner-informix-list@iiug.org 04/27/2004 07:44 AM Please respond to Rob</ul> </ul> </ul> </ul> </td><td width="100%"><img src="cid:20__=08BBE416DFC79EE78f9e8a93df938@us.ibm.com" border="0" height="1" width="1" alt=""> To: informix-list@iiug.org cc: Subject: Manual commit ODBC transactions?</td></tr> </table> Hello, When attempting a manual commit, I receive a "Driver not capable" error message. Is it possible to use manual transactions with Informix v9.4 and ODBC driver v3.82 ? I tried the ODBC advanced setup option "Auto commit optimization" both ways with the same result. I am using ODBCExpress with StartTransact(), Commit(), and EndTransact() sequence to manually manage transactions. This works great with MSSQL. Is there a better ODBC driver available? Rob. </body></html> --1__=08BBE416DFC79EE78f9e8a93df938690918c08BBE416DFC79EE7-- --0__=08BBE41