No result for SQL-select if character field in where-clause does not match in length using parameter (ORACLE)

[ap-0]

Hello,

I am using PyODBC 4.0.14 with Python 3.4.4 on Windows 7 64-Bit and connect to a Oracle 11gR2 database running on a SLES12 machine. The ODBC Driver I am using comes with the Client installation of Oracle: 11.02.00.01

When I execute a select-Statement that contains a string parameter (NCHAR in Oracle) I do not get any results if the length of the parameter and the length of the database field do not exactly match.

See this minimal example:

""" create table foo
    (
        colnum    number(20)    not null,
        colchar1  nchar(6)      not null,
        colchar2  nchar(20)     not null
    );

    insert into foo values (1, 'foobar', 'foobar');
"""

import pyodbc

conn = pyodbc.connect("...")
curs = conn.cursor()


curs.execute("select * from foo;")
print(curs.fetchall()) # Works

curs.execute("select * from foo where colnum = ? and colchar1 = ?;", [ 1, "foobar"] )
print(curs.fetchall()) # Works

curs.execute("select * from foo where colnum = ? and colchar2 = ?;", [ 1, "foobar"] )
print(curs.fetchall()) # Does not work

curs.execute("select * from foo where colnum = ? and colchar2 = ?;", [ 1, "foobar".ljust(20) ] )
print(curs.fetchall()) # Works

This does only happen for Oracle Databases. I have tested this with Postgres 9.6.2 and the issue does not come up. I have yet to test this on a Linux machine and maybe try Oracle 12c - but I don't expect changes.

[skillian]

Does it work if you pad the right side of the string with spaces?

…

On Mar 8, 2017 6:14 AM, "ap-0" ***@***.***> wrote: Hello, I am using *PyODBC 4.0.14* with *Python 3.4.4* on Windows 7 64-Bit and connect to a *Oracle 11gR2* database running on a SLES12 machine. The ODBC Driver I am using comes with the Client installation of Oracle: *11.02.00.01* When I execute a select-Statement that contains a string parameter (NCHAR in Oracle) I do not get any results if the length of the parameter and the length of the database field do not exactly match. See this minimal example: """ create table foo ( colnum number(20) not null, colchar1 nchar(6) not null, colchar2 nchar(20) not null ); insert into foo values (1, 'foobar', 'foobar'); """ import pyodbc conn = pyodbc.connect("...") curs = conn.cursor() curs.execute("select * from foo;") print(curs.fetchall()) # Works curs.execute("select * from foo where colnum = ? and colchar1 = ?;", [ 1, "foobar"] ) print(curs.fetchall()) # Works curs.execute("select * from foo where colnum = ? and colchar2 = ?;", [ 1, "foobar"] ) print(curs.fetchall()) # Does not work This does only happen for Oracle Databases. I have tested this with Postgres 9.6.2 and the issue does not come up. I have yet to test this on a Linux machine and maybe try Oracle 12c - but I don't expect changes. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#212>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AIP311tRfysqGHmRXo5RbCG9plRRujv7ks5rjo2YgaJpZM4MWqOv> .

[skillian]

(My point being "does 'foobar ' == 'foobar'," not "does making it 20 characters make it work").

…

On Mar 8, 2017 7:07 AM, "Sean Killian" ***@***.***> wrote: Does it work if you pad the right side of the string with spaces? On Mar 8, 2017 6:14 AM, "ap-0" ***@***.***> wrote: > Hello, > > I am using *PyODBC 4.0.14* with *Python 3.4.4* on Windows 7 64-Bit and > connect to a *Oracle 11gR2* database running on a SLES12 machine. The > ODBC Driver I am using comes with the Client installation of Oracle: > *11.02.00.01* > > When I execute a select-Statement that contains a string parameter (NCHAR > in Oracle) I do not get any results if the length of the parameter and the > length of the database field do not exactly match. > > See this minimal example: > > """ create table foo > ( > colnum number(20) not null, > colchar1 nchar(6) not null, > colchar2 nchar(20) not null > ); > > insert into foo values (1, 'foobar', 'foobar'); > """ > > import pyodbc > > conn = pyodbc.connect("...") > curs = conn.cursor() > > > curs.execute("select * from foo;") > print(curs.fetchall()) # Works > > curs.execute("select * from foo where colnum = ? and colchar1 = ?;", [ 1, "foobar"] ) > print(curs.fetchall()) # Works > > curs.execute("select * from foo where colnum = ? and colchar2 = ?;", [ 1, "foobar"] ) > print(curs.fetchall()) # Does not work > > This does only happen for Oracle Databases. I have tested this with > Postgres 9.6.2 and the issue does not come up. I have yet to test this on a > Linux machine and maybe try Oracle 12c - but I don't expect changes. > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <#212>, or mute the thread > <https://github.com/notifications/unsubscribe-auth/AIP311tRfysqGHmRXo5RbCG9plRRujv7ks5rjo2YgaJpZM4MWqOv> > . >

[ap-0]

Yes, if the string is filled with whitespace to the same length I get an result.

curs.execute("select * from foo where colnum = ? and colchar2 = ?;", [ 1, "foobar".ljust(20) ] )
print(curs.fetchall()) # Works

[mkleehammer]

So, it sounds like it is working? That is how nchar fields work. If you change the field type to nvarchar then it would work like you expect, I think.

[ap-0]

I see your point but when accessing the database directly through sqlplus in a terminal the statement
select * from foo where colnum = 1 and colchar2 = 'foobar';
returns the desired data rows. Also Tools like Toad for Oracle behave the same. It even works with PyODBC if the column values are formatted into the statement string. It just does not work using parameter.

But I would rather avoid formatting the statement string and use parameter to avoid sql injeciton. Unfortunately changing columns to varchar will not be possible.

Now I am wondering why PostgreSQL does not care about the whitespace padding.
(Point is to use ANSI SQL to the application is interchangeable with different databases)

[keitherskine]

It seems Oracle does follow the ANSI 92 standard of ignoring trailing spaces when comparing strings (https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/b_char.htm#175), but the issue here may be the result of using parameter values. This StackOverflow answer suggests that the parameterized values are sent with specific typing that force trailing spaces to be included in the comparison.

[mkleehammer]

I wonder if the fix would be, in this case, to provide the parameters as CHAR instead of VARCHAR. I'm very concerned about what problems that would cause when comparing to actual varchar columns.

This is related to a couple of other issues where we need to know the SQL type of the parameters according to the SQL, not the values we're providing. For example, if we're passing the number '7' that is being used to compare against a bigint field, it would be better if we passed as bigint rather than int. We don't know until we ask the driver, but that requires a compilation.

The current version of pyodbc always prepares SQL if parameters are provided, so it is possible that the performance would not change. I was planning on eliminating the prepare, but we could use the flag to keep it.

[gordthompson]

How about something like this? (I used some SQL Server terminology to describe the resulting actions, but the actual ODBC implementation would presumably be agnostic.):

# (1) as now, straight SQL Batch execute
crsr.execute(sql_string)
# ... no need to change existing client code

# (2) RPC call to sp_executesql with current parameter handling
params = (param1, param2,)
crsr.execute(sql_string, params)
# ... also no need to change existing client code

# (3) new explicit prepare/execute
crsr.prepare(sql_string)  # new method
# ... sends SQL command text to server to be compiled
# ... and lets us retrieve the expected parameter types/sizes
params = (param1, param2,)
crsr.executeprepared(params)  # new method
# ... does RPC call to sp_execute

[mkleehammer]

It is doable, but I'm not sure how that would help. Are you suggesting you'd change the parameters to match the sizes? Once we've prepared, pyodbc can match up the sizes automatically.

[gordthompson]

I wasn't thinking of changing the params (in Python) after the .prepare. If pyodbc can take care of optimizing the parameters based on the information it gets back from the server then that's great.

It's more about having control over when a prepare takes place, sort of a continuation of my comment here. So not only could an explicit .prepare be used to avoid sending the command text over and over (via sp_prepexec, as it does now), it could also let pyodbc get the information it needs to manage the parameters more effectively.

[mkleehammer]

Ah. It isn't strictly necessary, though it still might be a good idea from a usability point of view.

ODBC only allows one prepared statement per HSTMT (Cursor), so you can simply create one cursor for each SQL statement you want to prepare. They are prepared automatically if there are parameters and reused if you execute with the same (exactly the same) SQL.

It might be useful for two reasons: (1) some developers are familiar with prepared statements in other languages and (2) you don't have to keep the Cursor and the SQL, just the cursor.

If I have to prepare anyway to get the parameter type info, it might be better to maintain the old API to keep the surface area down.

[gordthompson]

I guess my point is that we won't necessarily need to prepare in every case (simply to optimize parameters), and by giving users the option to explicitly request a prepare - e.g., when their intent is to do a large number of repetitive operations, or when char vs. string matters - you don't have to give up #214 completely.

[mkleehammer]

OK - I hate to have to ask this, but is there an easy way to setup Oracle on CentOS? I've installed Oracle XE in a VM but cannot get anything to connect. Do I still need all of the listener **** if I'm using Oracle instant client? (Honestly, after using PostgreSQL, MySQL, and SQL Server do they not consider making it work out of the box? I get you need to pay a real DBA a million dollars but it isn't helping in situations like this where I don't really care about how optimized it is.)

[ap-0]

Usually a listener is installed along with the database. You can check whether the listener is up and running by using $ORACLE_HOME/bin/lsnrctl status as the oracle user.

For my Oracle installation on Suse Linux I have installed a pre-installation package. It creates a oracle user and sets up the required environment to run the database. AFAIK Oracle provides a RPM for other distributions too.

[mkleehammer]

I did install from an RPM, but I still can't connect to it with isql or pyodbc. I also can't figure out what the original database is actually named - XE, xe, test, etc. I'll look for more docs, but I spent a few hours on the weekend and was hoping there was a simple doc somewhere.

[ap-0]

You can try ps -ef | grep pmon. If a database is currently up the result should show ora_pmon_<databasename>. /etc/oratab should also contain one line per database.

If you have found the database name (if the installation created one) you can connect to it using sqlplus sys@ORACLE_SID. ORCLE_SID is the service name configured in the tnsnames.ora file. See here.

This links describes the full installation but maybe there are some hints on what you might have missed.

[sidnaik03]

Any solution for this ? I am facing same issue with NCHAR datatype.

[keitherskine]

Closed due to inactivity. Feel free to re-open with current information if necessary.