PreparedStatements with DDL and SQL Server

[vashek]

I am trying to create a view where the view definition is parameterized, such as:

cursor.execute("CREATE VIEW schema.viewname AS SELECT t.col1 AS col1, JSON_VALUE(t.col2, ?) AS col3 FROM schema.table t", (123,)) (corrected, originally my SQL was bad)

This works fine with PostgreSQL and psycopg2 and also with Microsoft SQL Server and pymssql, but using pyodbc, it throws a ProgrammingError:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'VIEW'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Unfortunately (don't take it personally ;)), I need pyodbc because pymssql doesn't support integrated authentication (Trusted_Connection=yes), as far as I can see.

This answer says that "Databases do not allow you to prepare DDL", which I wonder how true that is in general, but I assume it is at least true for MSSQL and is the cause of my issue. (If so, I'm guessing that pymssql must be interpolating the parameters before sending the command to SQL Server.)

One obvious solution is to do the interpolation myself, escaping single-quotes. I can't help but wonder if I'm missing a better solution though - surely I'm not the only one with this need.

Environment

• Python: Python 3.11.3 (tags/v3.11.3:f3909b8, Apr 4 2023, 23:49:59) [MSC v.1934 64 bit (AMD64)] on win32
• pyodbc: 4.0.35
• OS: Windows 2019
• DB: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)
• driver: ODBC Driver 13 for SQL Server

[v-chojas]

That's not valid syntax for CREATE VIEW. See here for more information:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

Also, this has nothing to do with pyODBC.

[vashek]

Ah, sorry, my bad, of course the command was incomplete. I'll edit the report. As for "nothing to do with pyODBC" -- as I mentioned, it works fine with pymssql and psycopg2, so if nothing else I'd say it warrants a mention in the documentation.

[v-chojas]

That's just DBMS/driver-specific variation ,which is normal for something as generic as ODBC.

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/statement-parameters?view=sql-server-ver16 "Generally, parameters are valid only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements."

[gordthompson]

I'm guessing that pymssql must be interpolating the parameters before sending the command to SQL Server.

Yes, that is true. For

crsr.execute("CREATE TABLE table_name (lastname varchar(%s))", (50, ))

pymssql sends

CREATE TABLE table_name (lastname varchar(50))

whereas with the corresponding .execute() from pyodbc, SQL Server ODBC sends

exec sp_prepexec @p1 output,N'@P1 int',N'CREATE TABLE table_name (lastname varchar(@P1))',50

and SQL Server rejects it with

[ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102)