search

blue-yonder / turbodbc

Turbodbc is a Python module to access relational databases via the Open Database Connectivity (ODBC) interface. The module complies with the Python Database API Specification 2.0.
http://turbodbc.readthedocs.io/en/latest
MIT License
614 stars 86 forks source link

Failure on parameter discovery in the presence of column functions #50

Closed gary-cowell closed 7 years ago

gary-cowell commented 7 years ago 
$ pip freeze | grep turbo
turbodbc==1.0.2

I have a result set 'rows' that has come from an Oracle cusror/fetchall

Some of the columns are Oracle VARCHAR2 types, which need to have the empty string. As we know, Oracle can't store the empty string in VARCHAR2, so these columns are coming back as NULL (None in the tuple) and my SQL Server database column is NOT NULL (expecting empty string).

I tried to put COALESCE in my SQL Server insert, but I get the following error:

mscursor.executemany(stmt, rows)

stmt is:
insert into TAEMENUL ( MENU, SEQ, LINE_MENU, PRG, DESCR, DFLTS, URL, EXE, LINE_MODULE_CODES ) values( ? ,  ? ,  ? ,  ? ,  ? ,  ? , coalesce( ? ,''), coalesce( ? ,''),  ? )

result:
MS insert Error ODBC error
state: 42000
native error code: 11502
message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The type for parameter '@P7' cannot be deduced in this context.

So this shows my INSERT statement with placeholders, and the two placeholders within the COALESCE functions.

This worked when I used pymssql , but was much too slow, hence coming to turbodbc (my migration program now runs in 12 minutes, instead of 3 hours)

But, how can I get this to work in turbodbc to convert the NULLs to empty string?

MathMagique commented 7 years ago

Hi Gary! Glad to hear that turbodbc helps to speed up your workflow.

Removing the context from your issue, the following remains: When an ODBC driver cannot determine a parameter type (due to a call to the SQLDescribeParam() ODBC function), an error message is raised.

I recently fixed a related issue (#48, here the driver did not support SQLDescribeParam() at all). I will try to use the same fallback in your situation for the next release. You should then be able to use the query as you gave above.

MathMagique commented 7 years ago

I just pushed a change that fixes this to master. I'm not sure yet when I'll make the release, I am thinking of fixing a few more MSSQL compatibility issues first.

gary-cowell commented 7 years ago

have tested this with my project and the issue is resolved, thank you