-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On May 15, 2012, at 7:51 PM, Adam Tauno Williams wrote:
> Intuitively, this should return a row with the integer numbers 0 and
> 1, but Informix can't execute the query because the parameters need to
> be bound to a particular data type on the SQL side, and no SQL type
> information can be inferred from the query.
>
> I see two different approaches around this issue:
> * Use literal values instead of bound parameters in the projection
> clause.
> * If you must use bound parameters, use type casts for the parameters
> that are used in the projection clause. For example: CASE WHEN
> (xrefr.xr_supersede = :1) THEN :2::int ELSE :3::int
> </quote>
>
> I assume this same kind of issue must be addressed in other dialects???
We have this same kind of issue in very rare circumstances - with Firebird, and
with SQL server when certain drivers are used, though not with a plain CASE
statement. Some DB/DBAPIs require that it can infer the type from a given
expression, so "somecol = ?" is OK, whereas "? = ?" is not. Using bound
parameters in the columns clause of a SELECT can be a problem on some SQL
Server drivers - like "SELECT ? AS foo".
So in this case, you can always bypass bound values using a construct like
literal_column("'somevalue'"). SQLAlchemy also has limited, and not very
publicly exposed, ability to turn a bound parameter into an in-place literal
directly - I'm very nervous about this feature as if it is mis-used, it amounts
to a security vulnerability. It seems like you've just used literal_column()
so that is probably the best way to go here.
>
>> On May 15, 2012, at 11:36 AM, Adam Tauno Williams <awill...@whitemice.org>
>> wrote:
>>> I'm use the Informix dialog with SQLalchemy 0.7.7 (the latest 0.7.x
>>> release).
>>> It works.
>>> But now I'm getting this error -
>>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) SQLCODE -201 in
>>> PREPARE:
>>> 42000: Syntax error or access violation
>>> 'SELECT xrefr.xr_serial_no AS xrefr_xr_serial_no, xrefr.xr_stock_no AS
>>> xrefr_xr_stock_no, xrefr.xr_desc AS xrefr_xr_desc, xrefr.xr_oem_code AS
>>> xrefr_xr_oem_code, xrefr.xr_oem_partcode AS xrefr_xr_oem_partcode,
>>> xrefr.xr_vend_code AS xrefr_xr_vend_code, xrefr.xr_vendr_part_code AS
>>> xrefr_xr_vendr_part_code, xrefr.xr_vendr_stock_no AS
>>> xrefr_xr_vendr_stock_no, xrefr.xr_min_qty AS xrefr_xr_min_qty,
>>> xrefr.xr_weight AS xrefr_xr_weight, xrefr.xr_quality_code AS
>>> xrefr_xr_quality_code, xrefr.xr_net_price AS xrefr_xr_net_price,
>>> xrefr.xr_list_price AS xrefr_xr_list_price, xrefr.xr_supersede AS
>>> xrefr_xr_supersede, CASE WHEN (xrefr.xr_supersede = :1) THEN :2 ELSE :3
>>> END AS anon_1 \nFROM xrefr \nWHERE xrefr.xr_oem_code = :4 AND
>>> xrefr.xr_vend_code = :5 AND CASE WHEN (xrefr.xr_supersede = :6) THEN :7
>>> ELSE :8 END IN (:9)' ('S', 1, 0, '35X', 'A78', 'S', 1, 0, 0)
>>> Only issue is that this is a *legitimate* statement. If I cut-n-paste
>>> this into a query tool [like DbVisualizer] this statement runs without
>>> issue. Any tips or suggestions on how to determine what is actually
>>> wrong?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (Darwin)
Comment: GPGTools - http://gpgtools.org
iQEcBAEBAgAGBQJPs6sKAAoJEDMCOcHE2v7hjC8H/jZWGwUbggXpfX8CulPAUI6z
UUE1uN6ZTkIik/yamPo1zIGZZcGd5zPmQwIS6Ms9P77Ut2xV0SlYWysAVg820Bf6
6E7Bbyxn9r3TNdubP1HALY7YjIt4Ft1FCyBNjMbM8hLZMS0ZkJ1jKYW2hmJ9xMH0
16YGagUlTf6ac1GXXZy9Ej+SJs6mOTYJmwpFwZ47GKitMdskRqp/7IpF2HZo/3u9
2uHoEaGjG6YRdEOAvUIcVVYw4nxU0IntCwW/rItnlUJevVzGqrCG15ma87Odmv9v
jOO7L5W0bYisMUZnIzVaFtsnxXHQN0tN9RM5wKwS0cyDWDZaFoLNDEHujjh+yGg=
=h8oD
-----END PGP SIGNATURE-----
--
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.
