Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Cannot parameterize SQL with strings using pyodbc #49

Open
mtslaugh opened this issue Dec 30, 2024 · 5 comments · May be fixed by #68
Open

Cannot parameterize SQL with strings using pyodbc #49

mtslaugh opened this issue Dec 30, 2024 · 5 comments · May be fixed by #68

Comments

@mtslaugh
Copy link

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
@staticlibs
Copy link
Contributor

It seems that the driver cannot accept SQL_WVARCHAR supplied by PyODBC:

python  test    4e0-59c	ENTER SQLBindParameter 
		HSTMT               0x000000000000000X
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       -8 <SQL_C_WCHAR>
		SWORD                       -9 <SQL_WVARCHAR>
		SQLULEN                    6
		SWORD                        0 
		PTR                0x000000000000000X
		SQLLEN                     6
		SQLLEN *            0x000000000000000X

python  test    4e0-59c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               0x000000000000000X
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       -8 <SQL_C_WCHAR>
		SWORD                       -9 <SQL_WVARCHAR>
		SQLULEN                    6
		SWORD                        0 
		PTR                0x000000000000000X
		SQLLEN                     6
		SQLLEN *            0x000000000000000X (6)

python  test    4e0-59c	ENTER SQLExecute 
		HSTMT               0x000000000000000X

python  test    4e0-59c	EXIT  SQLExecute  with return code -1 (SQL_ERROR)
		HSTMT               0x000000000000000X

		DIAG [01000] ODBC_DuckDB->pbi1->GetParamValues
Failed to set parameter value (0)

This needs to be fixed. With current driver the workaround is to force PyODBC to pass string parameters in encoded (non-unicode) form, this only works with ASCII data though:

cnxn.setencoding(encoding="utf-8")

@mtslaugh
Copy link
Author

Thanks for looking into this! I've confirmed that this workaround works for me as well. Would be great to see a fix.

staticlibs added a commit to staticlibs/duckdb-odbc that referenced this issue Feb 23, 2025
@staticlibs
Support wide char params binding
5a1ae8c 
Before this change the binding for `SQL_WCHAR` parameters was declared
in `ParameterDescriptor::SetValue` but was not actually implemented (no
encoding conversion).

This change implements support for getting incoming
`SQL_WCHAR`/`SQL_WVARCHAR` parameters as normal UTF-8 values. Incoming
UCS-2 (on Windows) or UTF-32 (on other platforms) parameter data is
converted to UTF-8 using C++ stdlib.

Testing: added a test that inserts non-ASCII data as literal and then
uses wide char parameter binding to match this data in `SELECT`.

Fixes: duckdb#49
@staticlibs staticlibs linked a pull request Feb 23, 2025 that will close this issue
Open
@staticlibs
Copy link
Contributor

@mtslaugh

I've submitted a fix in #68, it should allow PyODBC to use string parameters including non-ASCII ones.

staticlibs added a commit to staticlibs/duckdb-odbc that referenced this issue Feb 24, 2025
@staticlibs
Allow to pass SQL_WVARCHAR in SQLBindParameter
2a896d7 
This change adds only a nominal support for passing query parameters
as `SQL_WVARCHAR`, it uses existing logic that is already there for
`SQL_WCHAR` parameters.

It allows to use parameters from PyODBC, but in current impl only ASCII
data can be passed in these parameters.

Testing: none, test suite needs to be extended to test unicode through
the driver manager.

Fixes: duckdb#49
@staticlibs
Copy link
Contributor

@mtslaugh

it should allow PyODBC to use string parameters including non-ASCII ones

Just FYI, this appeared not to be the case, only ASCII is working correctly.

I cannot see any easy way to enable support for non-ASCII data, filed #69 for that.
So the only thing #68 is doing now is allowing to pass ASCII parameters from PyODBC without requiring cnxn.setencoding(encoding="utf-8"). It works for me now (with ASCII params) on Windows. And I cannot make it work on Linux (input conversion is different there) going to follow up as part of #69.

@mtslaugh
Copy link
Author

@staticlibs thanks for the heads up. This fix should be helpful for me even if it's not as complete a solution as previously intended.

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Nominal support for wide char params binding staticlibs/duckdb-odbc
2 participants
@staticlibs @mtslaugh