Temp table is not available to cursor when arguments are passed into the cursor.execute method

[hjb417]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.9.6
• pyodbc: 4.0.32
• OS: Windows 10.0.19044.1415
• DB: SQL Server 15.0.4123.1
• driver: ODBC Driver 17 for SQL Server 2017.177.02.01

Issue

When creating temp tables, I can no longer access them if the cursor.execute function that created it was passed in arguments.

For the 3 test cases below, 1st one passes and the other two fail

def drain_cursor(cursor):
    has_next_set = True
    while has_next_set:
        while cursor.description is not None and cursor.fetchone():
            continue
        if cursor.messages:
            for msg in cursor.messages:
                print(msg)
        has_next_set = cursor.nextset()

import pyodbc
conn_str = "DRIVER=ODBC Driver 17 for SQL Server;SERVER=localhost;Trusted_Connection=yes"
conn = pyodbc.connect(conn_str)
try:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #SomeTable1 (SomeCol INT NOT NULL);")
    drain_cursor(cursor)
    cursor.execute("SELECT * FROM #SomeTable1")
    cursor.fetchall()
    drain_cursor(cursor)
    print("WORKS")
except:
    print("FAIL")
conn.close()

conn = pyodbc.connect(conn_str)
try:
    cursor = conn.cursor()
    cursor.execute("DECLARE @SomeVar VARCHAR(MAX)=?;CREATE TABLE #SomeTable (SomeCol INT NOT NULL);", ["TEST"])
    drain_cursor(cursor)
    cursor.execute("SELECT * FROM #SomeTable")
    cursor.fetchall()
    drain_cursor(cursor)
    print("WORKS")
except:
    print("FAIL")
conn.close()

conn = pyodbc.connect(conn_str)
try:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #SomeTable2 (SomeCol INT NOT NULL);INSERT INTO #SomeTable2 VALUES(?)", [3])
    drain_cursor(cursor)
    cursor.execute("SELECT * FROM #SomeTable2")
    cursor.fetchall()
    drain_cursor(cursor)
    conn.close()
    print("WORKS")
except:
    print("FAIL")
conn.close()

[keitherskine]

When parameters are used in a SQL statement, the SQL is effectively executed in its own transaction, so the temporary table is dropped immediately afterwards. You could try using global temporary tables instead (e.g. ##SomeTable). Or just create the local temp table in its own non-parameterized SQL, e.g. as a replacement for your third example:

conn = pyodbc.connect(conn_str)
try:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #SomeTable2 (SomeCol INT NOT NULL)")
    drain_cursor(cursor)
    cursor.execute("INSERT INTO #SomeTable2 VALUES(?)", [3])
    drain_cursor(cursor)
    cursor.execute("SELECT * FROM #SomeTable2")
    drain_cursor(cursor)
    print("WORKS")
except Exception as e:
    print(e)
    print("FAIL")
conn.close()

[hjb417]

All the executes are part of the same transaction because autocommit was implicitly set to False... Had it been set to True, all test cases would have failed.

But regardless, this is a bug... most likely in pyodbc that hopefully the author or someone else can fix.

[v-chojas]

This is the normal behaviour as Keith explains above.

[hjb417]

Where is this documented? The only difference I can see is pyodbc uses SQLExecute when parameters are passed in and SQLExecDirect when parameters aren't. And the documentation for both say "If the data source is in manual-commit mode (requiring explicit transaction initiation) and a transaction has not already been initiated, the driver initiates a transaction before it sends the SQL statement.".

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlexecute-function?view=sql-server-ver15#comments

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlexecdirect-function?view=sql-server-ver15#comments