search

mkleehammer / pyodbc

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

Inserting numbers with bind parameters fails with Oracle 11.2 with HY000 #98

Open danbert2557 opened 8 years ago

danbert2557 commented 8 years ago

Summary

Attempting to use bind parameters for number values fails. e.g.

cursor.execute("INSERT INTO foobar (ID) VALUES (?)", [5])

gets the following response:

Traceback (most recent call last): File "", line 1, in pyodbc.Error: ('HY000', 'The driver did not supply an error!')

_I have reproduced this on two completely separate systems. _

Note: this appears to have similarities to #94 , but I can reproduce it with just a single column.

Detailed Example

Setup

Table foobar looks like: SQL> describe foobar;

Name Null? Type
ID NUMBER(10)
WORDS VARCHAR2(2048)
STUFF CLOB
CNT NUMBER(3)
TXT NVARCHAR2(32)

Create a connection in Python: import pyodbc px = pyodbc.connect("""my connection string""") pc = px.cursor()

Activity and Responses

Start with an insert statement without bind parameters.

pc.execute("insert into foobar values (1,'first row', 'little clob', 3, 'foo')") <pyodbc.Cursor object at 0x0000000002606450>

so that worked. no surprise there.

Now try a single bind parameter with a number.

pc.execute("insert into foobar values (?,'2d row', 'ltl clob', 33, 'food')",[2]) Traceback (most recent call last): File "", line 1, in pyodbc.Error: ('HY000', 'The driver did not supply an error!')

THAT ILLUSTRATES THE ERROR. Binding to a number failed.

How about binding to a string...

pc.execute("insert into foobar values (3,'2d row', 'ltl clob', 33, ?)",['woof']) <pyodbc.Cursor object at 0x0000000002606450>

That worked fine. So it is not all binds...

Same behavior with multiple binds to numbers ?

pc.execute("insert into foobar values (?,'2d row', 'ltl clob', ?, 'food')",[2,33]) Traceback (most recent call last): File "", line 1, in pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Yep, same issue with multiple binds

How about mixing binds to numbers and strings ?

pc.execute("insert into foobar values (?,'2d row', 'ltl clob', 42, ?)",[11,'baz']) Traceback (most recent call last): File "", line 1, in pyodbc.Error: ('HY000', 'The driver did not supply an error!')

That fails too. If there is a number being bound, it fails....

More platform specifics:

Oracle 11.2.0.1.0 pyodbc 3.0.10 python 3.4.0 (v3.4.0:04f714765c13, Mar 16 2014, 19:25:23) [MSC v.1600 64 bit (AMD64)] Windows 7 Professional, SR1

Open Questions

mkleehammer commented 8 years ago

I don't have a copy of Oracle to test with. Before I go down that path, can you run an ODBC trace for the failing test? Thanks.

mkleehammer commented 8 years ago

BTW, the reason I need Oracle is because this appears to be Oracle-specific. This works in PostgreSQL, SQL Server, and MySQL.

BlissfulDarkness commented 8 years ago

I can confirm this issue.

Environment: Linux CentOS 7.2.1511 Python 3.5 pyodbc 3.0.10 UnixODBC 2.3.4 Oracle 12.1 Instant Client & ODBC Oracle 11.2.0.4 DB

Per Oracle, this is likely a limitation in their ODBC driver: http://docs.oracle.com/cd/E11882_01/server.112/e10839/app_odbc.htm#UNXAR346

Oracle's ODBC driver doesn't support SQL_C_SBIGINT or SQL_C_UBIGINT

Issue exists in their latest ODBC driver as well: http://docs.oracle.com/database/121/UNXAR/app_odbc.htm#UNXAR352

The workaround is likely to force it to a Decimal or a Float before we pass it to pyodbc, but that is an ugly hack. Not sure there is a better one without sniffing Oracle's ODBC driver somehow.

The relevant trace entries in brief are below. Trace Entries: [SqlPrepareW.c][165] SQL = [][Length = 60 (SQL_NTS)] [SQL_SUCCESS] [SQLNumParams.c][144] [SQL_SUCCESS] Count -> 1 [SQLBindParameter.c][217] Param Number = 1 Param Type = 1 C Type = -25 SQL_C_SBIGINT SQL Type = -5 SQL_BIGINT Col Def = 0 Scale = 0 Rgb Value = 0x10977f0 Value Max = 0 StrLen or Ind = 0x10977d8 [SQL_SUCCESS] [SQLExecute.c][187][SQL_ERROR] [SQLGetDiagRec.c][764][SQL_NO_DATA]

mkleehammer commented 7 years ago

Can you try with version 4.0.8 or later?

prinderr commented 7 years ago

hello, i have the same issue, i 've tried with with lastest (4.0.14) and oracle 11.2

It works perfectly if i cast my values in float but sadly not in int.

regards

blthayer commented 7 years ago

Hello,

I am experiencing this problem for a few different configurations. Note that the database is Oracle 12.1.0.2:

1)

2)

3)

Note that everything is 64bit.

Here's the error message I get when trying to bind numbers (both on Windows and Linux): pyodbc.DataError: ('22018', '[22018] [Oracle][ODBC][Ora]ORA-01722: invalid number\n (1722) (SQLExecDirectW)')

Please let me know if I can provide more information, and please alert me when this issue is addressed.

tboulogne commented 4 years ago

hello @mkleehammer

I face this issue with 4.0.30. Any idea to solve this please ?

Thanks for all.

gordthompson commented 8 months ago

Users who need to access an Oracle database from Python should consider using Oracle's own python-oracledb module instead of pyodbc.