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
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com.
