volx wrote:
>
> cx_Oracle actually has thread on that a topic at
> http://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.00E5.0%40uwinnipeg.ca
>
> It boils down to having to specify a type for input parameter. Is that
> something I could "plug-in" as a decorator or would that need to be
> hacked on SqlAlchemy level?
here is the thing. We call setinputsizes() with oracle. So perhaps
OracleChar() needs to have dbapi.FIXED_CHAR established as its
get_dbapi_type(). would you care to try monkeypatching this (in 0.5, the
fix for 0.6 would be different) ?
class OracleChar(sqltypes.CHAR):
def get_col_spec(self):
return "CHAR(%(length)s)" % {'length' : self.length}
def get_dbapi_type(self, dbapi):
return dbapi.FIXED_CHAR
from sqlalchemy.databases import oracle
oracle.OracleChar = OracleChar
>
> Here's how:
>
> In [70]: query = "select * from price_sources where description
> = :someparam"
> In [71]: cursor.setinputsizes(someparam=cx_Oracle.FIXED_CHAR)
> Out[71]: {'someparam': <cx_Oracle.FIXED_CHAR with value None>}
> In [72]: cursor.execute(query, dict(someparam='EJV')).fetchall()
> Out[72]:
> [(149,
>
> 'EJV
> ')]
>
> Any help would be appreciated.
>
>
> On Sep 30, 2:36 pm, volx <victor.o...@gmail.com> wrote:
>> I have just now and it looks that this post probably belongs on
>> cx_Oracle mailing list.
>>
>> In [47]: cursor.execute("select * from price_sources where desciption
>> = :someparam", dict(someparam='EJV')).fetchall()
>> Out[47]: []
>> In [49]: cursor.execute("select * from price_sources where desciption
>> = :someparam", dict
>> (someparam='EJV
>> ')).fetchall()
>> Out[49]:
>> [(149,
>>
>> 'EJV
>> ')]
>>
>> On Sep 30, 2:18 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>>
>> > On Sep 30, 2:07 pm, volx <victor.o...@gmail.com> wrote:
>>
>> > > Hello all:
>>
>> > > Consider table:
>>
>> > > CREATE TABLE PRICE_SOURCES (
>> > > ID decimal(22) PRIMARY KEY NOT NULL,
>> > > DESCRIPTION char(100) NOT NULL
>> > > )
>>
>> > > and this screen dump from ipython session:
>>
>> > > In [28]: import cx_Oracle
>> > > In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup
>> > > In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
>> > > In [31]: cursor = con.cursor()
>> > > In [32]: cursor.execute("select * from price_sources where
>> description
>> > > = 'EJV'").fetchall()
>> > > Out[32]:
>> > > [(149,
>>
>> > > 'EJV
>> > > ')]
>>
>> > > Note that the result has padding in the column "description" as it
>> is
>> > > of type CHAR(100), with is fixed length.
>>
>> > > Now, let's do the same in SqlAlchemy.
>>
>> > > In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb')
>> > > In [34]: soup.price_sources.filter_by(description='EJV').all()
>> > > Out[34]: []
>>
>> > > As you can see it returns zero rows. Only after I pad the parameter
>> > > for where clause will I get my row back:
>>
>> > > In [35]: soup.price_sources.filter_by
>> > > (desciption='EJV
>> > > ').all()
>> > > Out[35]: [MappedPrice_sources(pricesource=Decimal
>> > > ('149'),desciption='EJV
>> > > ')]
>>
>> > > The same behavior happens even if I use ORM and regardless whether
>> the
>> > > column metadata is defined with String, OracleString or CHAR.
>>
>> > > I can understand the query result being padded as it is consistent
>> > > with behavior everywhere but the where clasues must not. It is a big
>> > > problem if you try to use session.merge() because you have to pad
>> > > contents of object fields otherwise the ORM will insert a duplicate.
>>
>> > > Please help. I hope I am doing something wrong or missing something.
>>
>> > that's a little strange. have you tried:
>>
>> > In [28]: import cx_Oracle
>> > In [30]: con = cx_Oracle.connect('myuser/myp...@mydb')
>> > In [31]: cursor = con.cursor()
>> > In [32]: cursor.execute("select * from price_sources where description
>> > = :someparam", dict(someparam='EJV')).fetchall()
>>
>> > ?
>>
>> > since the usage of bind parameters is how it actually runs. If same
>> > behavior there then it's a cx_oracle behavior. you can verify this
>> > by turning on SQL echoing. As a workaround you can build yourself a
>> > TypeDecorator that elongates/truncates incoming strings though that
>> > seems a little inconvenient.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
