Cannot parameterize SQL with strings using pyodbc

[mtslaugh]

What happens?

Executing parameterized SQL with pyodbc fails when the parameters are provided as python strings, but works as expected when parameters are provided as numeric values.

To Reproduce

Execute a parameterized query via pyodbc and specify the parameter as a string:

import pyodbc

cnxn = pyodbc.connect('DSN=DuckDB')
cursor = cnxn.cursor()

cursor.execute("CREATE TABLE fuu (j STRING)")
cursor.execute("INSERT INTO fuu VALUES (?)", 'Hello')
cursor.execute("SELECT * FROM fuu")
result = cursor.fetchall()
print(result)

This produces the following error:

pyodbc.Error: ('01000', '[01000] ODBC_DuckDB->GetParamValues\nFailed to set parameter value (0) (SQLExecDirectW)')

To confirm that it does work with numeric values:

import pyodbc

cnxn = pyodbc.connect('DSN=DuckDB')
cursor = cnxn.cursor()

cursor.execute("CREATE TABLE fuu (i INTEGER)")
cursor.execute("INSERT INTO fuu VALUES (?)", 42)
cursor.execute("SELECT * FROM fuu")
result = cursor.fetchall()
print(result)

Outputs [(42,)] as expected.

OS:

Windows 11 x64 version 10.1.26100

DuckDB Version:

v1.1.3 (nightly build as of 2024-12-29)

DuckDB Client:

ODBC

Hardware:

Intel Core i7-9750H CPU @ 2.6GHz × 6 , 16 GB Memory

Python environment:

Anaconda Python version 3.13.1, pyodbc version 5.2.0

Name:

Matthew T. Slaughter

Affiliation:

Kaiser Permanente