PostgreSQL missing datatype on prepared statements

[aparcar]

Environment

• Python: 3.6.3
• pyodbc: 4.0.21
• OS: Debian Testing
• DB: PostgreSQL
• driver: PostgreSQL Unicode

Issue

I'm trying to prepare some functions in a prepare-postgres.sql and execute the file on just after the database connection.

The functions are called via cursor.execute("execute function_name") or coursor.execute("execute function_name_param(?)", parameter1). While the parameter-less version works as expected the second function results in an error:

Error pyodbc.ProgrammingError: ('42P18', '[42P18] ERROR: could not determine data type of parameter $1;\n Error while preparing parameters (1) (SQLExecDirectW)')

Prepare code

prepare get_releases(varchar) as select distro, release from releases where distro like $1;

Python code

self.c.execute("execute get_releases (?)", "foobar")

I tried the same with psycopg2 and it works as expected.

[aparcar]

Checking the Postgres log it tries to run execute get_releases ($1) (replacing the ? with a $1) which can't work. Is that a problem of pyodbc or the driver?

[v-chojas]

pyODBC does not do any manipulation of the query passed to execute(), but only hands it down to the ODBC driver.

You can temporarily enable tracing in unixODBC to see what ODBC calls pyODBC makes.

[aparcar]

@v-chojas thanks for you quick response! I tried to connect with isql and performed the same task, it works just fine.

[aparcar]

So it seems like the driver tries to prepare a prepare statement, filling it with $1 and so produce the error. I guess that's a problem of the SQL driver or does pyodbc support manual preparation which is not optimized by odbc?

[v-chojas]

Could you post an ODBC trace? Seeing what pyODBC is doing to the driver can determine whether the problem is in the driver or pyODBC.

[mkleehammer]

First, I'm not getting that far in my tests - I get an error when I try to prepare:

cursor.execute("prepare get_releases(varchar) as select distro, release from releases where distro like $1;")

This causes:

pyodbc.Error: ('07002', '[07002] The # of binded parameters < the # of parameter markers (32) (SQLExecDirectW)')

How are you preparing?

Second, you might not need to prepare something manually. SQL with parameters is prepared under the covers anyway and reused if you execute the statement multiple times in a row. (Though this statement doesn't look like something you'd do that with.)

[aparcar]

@mkleehammer

Thanks for trying! Here it runs with no error:

db = utils.database.Database(config)
db.c.execute("drop table if exists test")
db.c.execute("create table test (distro varchar, release varchar);")
db.c.execute("prepare get_releases(varchar) as select distro, release from test where distro like $1;")
db.c.execute("execute get_releases ('foobar')")     # <- works
db.c.execute("execute get_releases (?)", "foobar")  # <- fails

This is just the simplest example I could find, other stuff is more complex and I'd like to hide it with a prepare statement, putting all SQL to .sql files.

[aparcar]

@v-chojas I hope that helps http://termbin.com/uc53

[v-chojas]

I am not familiar with PostGres but from the trace you provided, it appears that pyODBC is calling the ODBC driver correctly. Perhaps the driver has some limitations on how it uses ODBC parameters?

[aparcar]

@v-chojas I'll write a mail to their list and ask about this behaviour

[mboeringa]

@aparcar,

Did you get any further with this, potentially finding a workaround? I am now running into exactly the same issue with a prepared statement and parameters failing with a "could not determine data type of parameter", even though, like you, I actually specify the data type of the input parameter in the PREPARE SQL statement.

As you observed, the same code runs fine in psycopg2, only failing in pyodbc. And both are using the same official PostgreSQL ODBC driver (https://www.postgresql.org/ftp/odbc/versions/).

[aparcar]

I never looked into this again and stopped using ODBC in general, sorry.

[mboeringa]

I never looked into this again and stopped using ODBC in general, sorry.

Thanks @aparcar

One weird thing I have now also noticed is that in a slightly different failure mode, I had a random failure with this error message on a single row, instead of totally blocking at the first instance of processing as I first observed. I use a custom written Python multi-threading application, that can use either pyodbc or psycopg2 as database connector.

For some reason, one of the threads emitted a warning about a single record failing to be processed with this error being emitted (some custom error coding I did to catch such failures and continue processing others).

This is weird, I would have expected it to either fail on all records, as in the first observed failure case, or none, not just a random one among millions of others on dozens of threads, especially considering the type of error concerned.