cx_Oracle returns empty query when using bindings

Question

I'm facing a strange problem when doing queries in my sql application. I'm working with python3 and cx_Oracle 5.1.2. My test table is organized as it follows:

CREATE TABLE people ( sin CHAR(15), name VARCHAR(40), PRIMARY KEY (sin))

with the following values inserted (sin, name):

('1','a'), ('2','b'), ('3','c')

When I do a simple select using an unsafe query:

curs.execute("select name from people where sin = '1'")

The result is 'a', as expected, but if I use bindings:

curs.execute("select name from people where sin = :v", v='1')

The result is empty. I already tried to change this to the positional '?' parameter, set the size of 'v' through setinputsizes(v=15) but nothing appears to work.

Is there something that I am missing?

Thanks,

Not sure - have you tried curs.execute("select name from people where sin = :v", {'v':'1'})? — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 17, 2014 at 4:41

Marco Baldelli · Accepted Answer · 2014-03-17 09:25:36Z

The problem lies in your use of the CHAR datatype instead of VARCHAR2.

You can observe the difference even in SQL*Plus.

If we bind a VARCHAR2 variable then no rows are selected:

SQL> variable v varchar2(15)
SQL> exec :v := '1';

PL/SQL procedure successfully completed.

SQL> select name from people where sin = :v;

no rows selected

If instead we bind a CHAR variable, which is the same data type as the column, then one row is selected:

SQL> variable v char(15)
SQL> exec :v := '1';

PL/SQL procedure successfully completed.

SQL> select name from people where sin = :v;

NAME
----------------------------------------
a

Therefore you either need to change the column data type from CHAR to VARCHAR2 (by the way, VARCHAR is obsolete as well) or instruct cx_Oracle to user a FIXED_CHAR data type:

>>> v = curs.var(cx_Oracle.FIXED_CHAR, 15)
>>> v.setvalue(0, '1')
>>> print v
<cx_Oracle.FIXED_CHAR with value '1'>

>>> result = curs.execute("select name from people where sin = :sin", sin=v)
>>> for r in result: print r

('a',)

Collectives™ on Stack Overflow

cx_Oracle returns empty query when using bindings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related