Dev-Picayune

For a utility we are writing at work we needed to be able to insert and delete records from a temporary flat file/table on our AS/400. For reference, we are connecting via IBM’s iSeries Access ODBC drivers. The SELECT statements were working fine but we were getting the infamous SQL7008 errors on all DELETE and INSERT statements. I hunted frantically for a solution and everything of course explains that unjournaled files on an AS/400 won’t support transactions and that the driver should be set to an isolation level of 0. The problem is that there appears to be no way to actually set something called an “isolation level” within the IBM driver.

After some experimentation and a little logic, I discovered that the solution is rather simple. In mxODBC you need to specify the clear_auto_commit=0 as part of the connection parameters. And the solution for pyodbc is similar, you need to specify the new autocommit=True as a named parameter for the connect method. The tricky part about the pyodbc is that the new autocommit parameter didn’t exist until February 1st, 2007 (version 2.0.33). I was floundering along in version 2.0.30 and was stuck as to why the documentation didn’t seem to match the actual library. After a quick download and install of the latest version, I was in business. It’s nice to see the pyodbc project maturing and being so actively developed.

Technorati Tags: python, pyodbc, mxodbc, AS/400, DB/400, DB2