when using contextlib wrapper, cursor.execute fails when cursor is assigned to a variable before usage

[GoogleCodeExporter]

What steps will reproduce the problem?
> run the attached script

What is the expected output? What do you see instead?
> expect to see the output as documented in attached module. Actually the
output is as expected, but an unhandled win32 error is thrown as well,
which kills the process.

What version of the product are you using? On what operating system?
> python 2.6.4 on WinXP-SP2, pyodbc-2.1.6

Please provide any additional information below.
> Basically I have a little context-aware wrapper around pyodbc.connect so
that it can be used with with_statement as described at
http://docs.python.org/library/contextlib.html.

The problem using it does not work in a very strange situation where the
difference between works/does-not-work is narrowed down to the difference
between following lines:

1-works:
...
db_conn.cursor().execute(Test_pyodbc_connect._SQL_INSERT, (1,))
...

2-does not work:
...
curs = db_conn.cursor()
curs.execute(Test_pyodbc_connect._SQL_INSERT, (1,))
...

Original issue reported on code.google.com by ivan.mel...@gmail.com on 21 Dec 2009 at 4:33

Attachments:

• test_with_pyodbc.py

[GoogleCodeExporter]

Another finding.
> It only fails when the insert statement is executed with the parameter. If no
parameters are passed to cursor.execute(). Therefore following works as 
expected:
    def test_with_works_noparam(self):
        """ Use contextmanager and immediatelly execute sql on cursor.  """
        logger = logging.getLogger('test_with_works_noparam')
        with pyodbc_connect(self._db_conn_str) as db_conn:
            for row in range(2):
                logger.info("Inserting row %d ...", row)
                curs = db_conn.cursor()
                curs.execute("INSERT INTO test_pyodbc_table (SomeUniqueValue) VALUES
(1)")
                logger.info("Inserted  row %d ...", row)
        return True

Original comment by ivan.mel...@gmail.com on 21 Dec 2009 at 5:12

[GoogleCodeExporter]

There is definitely a bug and I will fix it, but I can also provide a work 
around.  The crash only occurs when you 
are creating multiple cursors, which you are doing by putting the cursor() call 
inside the loop.

When I take it out of the loop (and change "(1,)" to "(row,"), the crash does 
not occur:

    def test2(self):
        logger = logging.getLogger('test_with_fails')
        with pyodbc_connect(self._db_conn_str) as db_conn:
            curs = db_conn.cursor()
            for row in range(2):
                logger.info("Inserting row %d ...", row)
                curs.execute(Test_pyodbc_connect._SQL_INSERT, (row,))
                logger.info("Inserted  row %d ...", row)
        return True

As an FYI, you can pass parameters without putting them into a tuple if you 
want:

  curs.execute("insert ...", (1,))  --> curs.execute("insert ...", 1)

Original comment by mkleehammer on 22 Dec 2009 at 7:41

• Changed state: Investigating
• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

RE: bug > great, thanks
RE: parameters > I know and it is cool, but it is not part of DB-API so I try 
not to
use it for (appearance of) portability.

Original comment by ivan.mel...@gmail.com on 22 Dec 2009 at 8:04

[GoogleCodeExporter]

Fixed in e998b116d8341c552cc8a2d2dea02026d00504f3, which will go into 2.1.7

During parameter cleanup, the cursor's HSTMT was being used after the 
connection's HDBC was already freed.

It is strange that I was unable to create a test case for this other than using 
the context.

As a side note, I never use contexts since Python objects are deleted at the 
end of scope unlike garbage 
collection in Java or C#.  This means you can use something simple like:

def f():
  cnxn = connect()
  cursor = cnxn.cursor()
  cursor.execute(...)
  cnxn.commit()

The cursor and connection are automatically closed at the end of the function.  
(Note that I create a new 
connection every time.  ODBC connection pooling is very fast on Windows.)

Connections perform an automatic rollback when they are closed, so if  the 
function doesn't make it to the 
commit due to an exception or early exit, everything is cleaned up 
appropriately.

Original comment by mkleehammer on 31 Dec 2009 at 6:27

• Changed state: Verified

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:45

• Changed state: Complete