Support native parameterized prepared statements

[nuno-faria]

Environment

• Python: 3.8.10
• pyodbc: 4.0.32
• OS: Ubuntu 20.04 LTS

Issue

Right now, it does not seem to be possible to prepare statements using native commands when the statement involves custom parameters. For instance, the following code works properly:

cursor.execute('PREPARE SELECT * FROM Table WHERE id = 1')
cursor.execute('PREPARE SELECT * FROM Table WHERE id = 2')
cursor.execute('EXECUTE 0()') # 0 is the first prepared statement with id=1
cursor.fetchall()
cursor.execute('EXECUTE 1()') # 1 is the second prepared statement with id=2
cursor.fetchall()

However, when we try to introduce parameters, the pyodbc driver considers it as a statement it has to prepare, so it appends a prepare to the existing one, resulting in a syntax error:

cursor.execute('PREPARE SELECT * FROM Table WHERE id = ?')
> syntax error, unexpected PREPARE in: "prepare prepare"

We need this to optimize large queries that are constantly called by the application. As I understand, pyodbc currently only caches the previous statement, as per #214 , which is not enough for our use case.

Is there a possible workaround or plans to prevent this behavior by passing, for example, a prepare=False to the cursor.execute?

[v-chojas]

pyODBC does not alter your queries; it simply passes them on to the ODBC driver. What ODBC driver and DB are you using?

[nuno-faria]

DB: MonetDB Driver: libMonetODBC with unixODBC 2.3.6

[v-chojas]

I'm not familiar with that one, but I would consider this a bug in the ODBC driver.

[nuno-faria]

Thank you for your help. I will mark this issue as closed.