search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.89k stars 562 forks source link

Question marks not detected as parameter markers for CREATE TABLE in Vertica #1059

Closed maciek16180 closed 2 years ago

maciek16180 commented 2 years ago

Environment

Issue

Expected behavior: question marks will work in all queries as parameter markers.

Observed behavior: in some queries question marks are not detected.

I use Python to connect to Vertica database, create a cursor, and try to run parameterized query.

This works:

In [17]: cursor.execute("SELECT 1 WHERE 1=?", 1)
Out[17]: <pyodbc.Cursor at 0x7f2c897f2230>

But if I wrap it into CREATE TABLE, is stops working:

In [18]: cursor.execute("CREATE TABLE a AS (SELECT 1 WHERE 1=?)", 1)
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-18-1e79b89b9f86> in <module>
----> 1 cursor.execute("CREATE TABLE a AS (SELECT 1 WHERE 1=?)", 1)

ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')
v-chojas commented 2 years ago

Could you post an ODBC trace?

It is more likely this is a bug and/or limitation in the ODBC driver, not pyODBC.

gordthompson commented 2 years ago

Your sample code works fine for me with the "PostgreSQL Unicode" ODBC driver, so this looks like an issue with the Vertica driver.

maciek16180 commented 2 years ago

@v-chojas Here is my ODBC trace:

[ODBC][7765][1652428547.592640][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x56035e5d5610
[ODBC][7765][1652428547.592664][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0x56035e5d5610
                        Attribute = SQL_ATTR_ODBC_VERSION
                        Value = 0x3
                        StrLen = 4
[ODBC][7765][1652428547.592671][SQLSetEnvAttr.c][381]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.592678][SQLAllocHandle.c][377]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x56035e5d5610
[ODBC][7765][1652428547.592684][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x56035e5dd860
[ODBC][7765][1652428547.592937][SQLDriverConnectW.c][290]
                Entry:
                        Connection = 0x56035e5dd860
                        Window Hdl = (nil)
                        Str In = [DRIVER={Vertica};SERVER=************;DATABASE=*******;UID=******;PWD=**************][length = **** (SQL_NTS)]
                        Str Out = (nil)
                        Str Out Max = 0
                        Str Out Ptr = (nil)
                        Completion = 0
                UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][7765][1652428547.879266][SQLDriverConnectW.c][912]
                Exit:[SQL_SUCCESS]                    
                        Connection Out [[NULL]]
[ODBC][7765][1652428547.879289][SQLSetConnectAttr.c][396]
                Entry:
                        Connection = 0x56035e5dd860
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = (nil)
                        StrLen = -5
[ODBC][7765][1652428547.905078][SQLSetConnectAttr.c][859]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.905122][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x56035e5dd860
                        Info Type = SQL_DRIVER_ODBC_VER (77)
                        Info Value = 0x7ffec3ad7ad0
                        Buffer Length = 20
                        StrLen = 0x7ffec3ad7a8c
[ODBC][7765][1652428547.905137][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x56035e5dd860
                        Info Type = SQL_DESCRIBE_PARAMETER (10002)
                        Info Value = 0x7ffec3ad7a8e
                        Buffer Length = 2
                        StrLen = 0x7ffec3ad7a8c
[ODBC][7765][1652428547.905146][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x56035e5dd860
                        Info Type = SQL_NEED_LONG_DATA_LEN (111)
                        Info Value = 0x7ffec3ad7a8e
                        Buffer Length = 2
                        StrLen = 0x7ffec3ad7a8c
[ODBC][7765][1652428547.905153][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e5dd860
[ODBC][7765][1652428547.905192][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x56035e6efa50
[ODBC][7765][1652428547.905199][SQLGetTypeInfo.c][168]
                Entry:
                        Statement = 0x56035e6efa50
                        Data Type = SQL_VARCHAR
[ODBC][7765][1652428547.938403][SQLGetTypeInfo.c][321]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.938419][SQLFetch.c][162]
                Entry:
                        Statement = 0x56035e6efa50
[ODBC][7765][1652428547.938435][SQLFetch.c][352]
                Exit:[SQL_NO_DATA]
[ODBC][7765][1652428547.938442][SQLFreeStmt.c][144]
                Entry:
                        Statement = 0x56035e6efa50
                        Option = 0
[ODBC][7765][1652428547.938457][SQLFreeStmt.c][266]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.938467][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e6efa50
[ODBC][7765][1652428547.938483][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.938490][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e5dd860
[ODBC][7765][1652428547.938517][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x56035e6f5ad0
[ODBC][7765][1652428547.938524][SQLGetTypeInfo.c][168]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Data Type = Unknown(-9)
[ODBC][7765][1652428547.971421][SQLGetTypeInfo.c][321]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.971436][SQLFetch.c][162]
                Entry:
                        Statement = 0x56035e6f5ad0
[ODBC][7765][1652428547.971447][SQLFetch.c][352]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.971454][SQLGetData.c][237]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Column Number = 3
                        Target Type = 4 SQL_INTEGER
                        Buffer Length = 4
                        Target Value = 0x7ffec3ad7a94
                        StrLen Or Ind = (nil)
[ODBC][7765][1652428547.971473][SQLGetData.c][545]
                Exit:[SQL_SUCCESS]                
                        Buffer = [65000]
                        Strlen Or Ind = NULLPTR
[ODBC][7765][1652428547.971480][SQLFreeStmt.c][144]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Option = 0
[ODBC][7765][1652428547.971498][SQLFreeStmt.c][266]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.971508][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e6f5ad0
[ODBC][7765][1652428547.971526][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428547.971534][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e5dd860
[ODBC][7765][1652428547.971560][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x56035e6f5ad0
[ODBC][7765][1652428547.971567][SQLGetTypeInfo.c][168]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Data Type = SQL_VARBINARY
[ODBC][7765][1652428548.004731][SQLGetTypeInfo.c][321]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.004746][SQLFetch.c][162]
                Entry:
                        Statement = 0x56035e6f5ad0
[ODBC][7765][1652428548.004756][SQLFetch.c][352]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.004764][SQLGetData.c][237]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Column Number = 3
                        Target Type = 4 SQL_INTEGER
                        Buffer Length = 4
                        Target Value = 0x7ffec3ad7a98
                        StrLen Or Ind = (nil)
[ODBC][7765][1652428548.004773][SQLGetData.c][545]
                Exit:[SQL_SUCCESS]                
                        Buffer = [65000]                
                        Strlen Or Ind = NULLPTR
[ODBC][7765][1652428548.004779][SQLFreeStmt.c][144]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Option = 0
[ODBC][7765][1652428548.004794][SQLFreeStmt.c][266]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.004803][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e6f5ad0
[ODBC][7765][1652428548.004815][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.004823][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e5dd860
[ODBC][7765][1652428548.004849][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x56035e6f5ad0
[ODBC][7765][1652428548.004856][SQLGetTypeInfo.c][168]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Data Type = SQL_TYPE_TIMESTAMP
[ODBC][7765][1652428548.037964][SQLGetTypeInfo.c][321]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.037979][SQLFetch.c][162]
                Entry:
                        Statement = 0x56035e6f5ad0
[ODBC][7765][1652428548.037989][SQLFetch.c][352]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.037997][SQLGetData.c][237]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Column Number = 3
                        Target Type = 4 SQL_INTEGER
                        Buffer Length = 4
                        Target Value = 0x7ffec3ad7a9c
                        StrLen Or Ind = (nil)
[ODBC][7765][1652428548.038006][SQLGetData.c][545]
                Exit:[SQL_SUCCESS]                
                        Buffer = [26]                
                        Strlen Or Ind = NULLPTR
[ODBC][7765][1652428548.038012][SQLFreeStmt.c][144]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Option = 0
[ODBC][7765][1652428548.038027][SQLFreeStmt.c][266]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.038037][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e6f5ad0
[ODBC][7765][1652428548.038049][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428548.038063][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x56035e5dd860
[ODBC][7765][1652428548.038089][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x56035e6f5ad0
[ODBC][7765][1652428594.957511][SQLFreeStmt.c][144]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Option = 0
[ODBC][7765][1652428594.957546][SQLFreeStmt.c][266]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428594.957590][SQLPrepareW.c][165]
                Entry:
                        Statement = 0x56035e6f5ad0
                        SQL = [CREATE TABLE a AS (SELECT 1 WHERE 1=?);][length = 39]
[ODBC][7765][1652428595.009846][SQLPrepareW.c][346]
                Exit:[SQL_SUCCESS]
[ODBC][7765][1652428595.009863][SQLNumParams.c][144]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Param Count = 0x7ffec3ad8106
[ODBC][7765][1652428595.009872][SQLNumParams.c][231]
                Exit:[SQL_SUCCESS]
                        Count = 0x7ffec3ad8106 -> 0
maciek16180 commented 2 years ago

I updated the initial post with Vertica version.

v-chojas commented 2 years ago 
[ODBC][7765][1652428595.009863][SQLNumParams.c][144]
                Entry:
                        Statement = 0x56035e6f5ad0
                        Param Count = 0x7ffec3ad8106
[ODBC][7765][1652428595.009872][SQLNumParams.c][231]
                Exit:[SQL_SUCCESS]
                        Count = 0x7ffec3ad8106 -> 0   *** HERE ***

That proves it's the ODBC driver which is claiming the query has no parameters. pyODBC isn't the problem here. Ask Vertica about that.

maciek16180 commented 2 years ago

Ok, thanks for checking.