boud parameter to NCHAR column in Oracle

[mdob]

Hi everyone, 

There's IMO unusual behavior in Oracle when using bound parameters on NCHAR column. 

from sqlalchemy import create_engine
from sqlalchemy.sql import text


e = create_engine('oracle://chinook:p4ssw0rd@localhost/xe')


result = e.execute(
    text("select * from nchartable where id = '1'"),
).fetchall()


print 'hardcoded:', list(result)


result = e.execute(
    text('select * from nchartable where id = :id'),
    {'id': '1'}
).fetchall()


print 'trimmed:', list(result)




result = e.execute(
    text('select * from nchartable where id = :id'),
    {'id': '1   '}  # padded with spaces
).fetchall()


print 'padded:', list(result)

output

hardcoded: [(u'1   ',)]

trimmed: []

padded: [(u'1   ',)]

When value is hardcoded or bound parameter is padded the statement works as expected, but it doesn't when value is trimmed. Although the value in where statement is just like in hardcoded case.

As explained on cxOracle mailing list https://sourceforge.net/p/cx-oracle/mailman/message/36775002/ it's a deliberate decision to bind values str to VARCHAR2 and unicode to NVARCHAR2. Unfortunately that doesn't work correctly for filtering on CHAR and NCHAR. 

I know it's more cxOracle matter but how do you deal with this in SQLAlchemy? Expecially when working with text SQL statement which may be complex e.g. use stored procedures. 

Thanks for any clues, 

Michal

[Victor Olex]

I'd add that other dialects such as pyodbc+mssql work as expected, which is to ignore trailing white space in fixed width character fields for comparison purposes.

[Jonathan Vanasco]

I'm sure Mike will have a better answer, but I immediately thought "events!" and that led me to some documentation in the Oracle driver for dealing with another issue, but it looks like you could use that same hook...

https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DialectEvents.do_setinputsizes

https://docs.sqlalchemy.org/en/13/dialects/oracle.html#cx-oracle-setinputsizes

you could probably use another event too... but i think events should catch all the `text()` constructs that a TypeDecorator would miss.

[Mike Bayer]

hey there -

you should apply typing behavior which should be safe to apply to any CHAR like this:

    class PaddedChar(TypeDecorator):

        impl = NCHAR

        def process_bind_param(self, value, dialect):

            if value is not None:

                value = value + (" " * (self.impl.length - len(value)))

            return value

    result = conn.execute(

        text("select * from nchartable where id = :id").bindparams(

            bindparam("id", type_=PaddedChar(4))

        ),

        {"id": "1"},

    ).fetchall()

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com.

[Mike Bayer]

With cx_Oracle, you really should likely be using typing for everything as cx_Oracle is pretty sensitive as well to the cursor.setinputsizes() settings, which SQLAlchemy will do for you if you send in typed bound parameters; additionally, you can set typing information for result columns also (use text().columns())  which SQLAlchemy uses in order to set up cursor.outputtypehandler.   Both of these are cx_Oracle things that are unfortunately unique to this DBAPI and they are hugely important; even the django ORM has to use outputtypehandler.       

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c313d214-d5a2-455c-b661-3a7cdfb379f2%40www.fastmail.com.

[Victor Olex]

A bit of advocacy from my side on cx_Oracle: https://github.com/oracle/python-cx_Oracle/issues/365

Mike, there's something you might want to look at. We have this monkey patch on SQLAlchemy in our recent code, which was necessary to get the correct behavior for NCHAR columns.

from sqlalchemy.dialects.oracle import cx_oracle

from sqlalchemy.sql sqltypes

class _OracleNChar(sqltypes.NCHAR):

    def get_dbapi_type(self, dbapi):

        return dbapi.FIXED_NCHAR

cx_oracle._OracleNChar = _OracleNChar

cx_oracle.dialect.colspecs[sqltypes.NCHAR] = _OracleNChar

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

On Fri, Oct 11, 2019, at 3:56 PM, Victor Olex wrote:

A bit of advocacy from my side on cx_Oracle: https://github.com/oracle/python-cx_Oracle/issues/365

Mike, there's something you might want to look at. We have this monkey patch on SQLAlchemy in our recent code, which was necessary to get the correct behavior for NCHAR columns.

from sqlalchemy.dialects.oracle import cx_oracle

from sqlalchemy.sql sqltypes

class _OracleNChar(sqltypes.NCHAR):

    def get_dbapi_type(self, dbapi):

        return dbapi.FIXED_NCHAR

cx_oracle._OracleNChar = _OracleNChar

cx_oracle.dialect.colspecs[sqltypes.NCHAR] = _OracleNChar

it seems likely that should be in the codebase as there is already:

class _OracleChar(sqltypes.CHAR):

    def get_dbapi_type(self, dbapi):

        return dbapi.FIXED_CHAR

can you open a bug report ?

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/94e3b8da-415e-434d-bf6a-dbd1a671e576%40googlegroups.com.

[Victor Olex]

That's for CHAR not NCHAR. I will open a bug report.