By accident I found out that the SQLAlchemy Function class has an
attribute 'packagenames'. If you set this attribute manually, the
query is executed properly.
It would be great if this attribute is filled automatically by
splitting the function name on every dot.
>>> session.query(Function('SDO_GEOMETRY', 'POINT(0 0)', 4326,
>>> packagenames=['MDSYS'])).select_from(table('dual')).first()
2010-05-06 13:35:31,546 INFO sqlalchemy.engine.base.Engine.0x...a8ac
SELECT "SDO_GEOMETRY_1"
FROM (SELECT "SDO_GEOMETRY_1", ROWNUM AS ora_rn
FROM (SELECT MDSYS.SDO_GEOMETRY(:SDO_GEOMETRY_2, :SDO_GEOMETRY_3) AS
"SDO_GEOMETRY_1"
FROM dual)
WHERE ROWNUM <= :ROWNUM_1)
WHERE ora_rn > :ora_rn_1
2010-05-06 13:35:31,547 INFO sqlalchemy.engine.base.Engine.0x...a8ac
{'ROWNUM_1': 1, 'SDO_GEOMETRY_3': 4326, 'SDO_GEOMETRY_2': 'POINT(0
0)', 'ora_rn_1': 0}
(<cx_Oracle.OBJECT object at 0x8c11da0>,)
On May 6, 10:37 am, Tobias <[email protected]> wrote:
> Hi,
>
> I am using SQLAlchemy 0.6 together with cx_Oracle and I am receiving
> an error message when a database function is inside a package and has
> to be called like "package.functionname".
>
> For example the following query can reproduce the error (beside that
> this query makes not much sense):
>
> >>> session.query(table('spots',
> column('spot_location'))).filter(getattr(func, 'MDSYS.SDO_GEOMETRY')
> ('POINT(0 0)', 4326) == text('spot_location')).first()
>
> The output is:
>
> 2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
> SELECT spots_spot_location
> FROM (SELECT spots_spot_location, ROWNUM AS ora_rn
> FROM (SELECT spots.spot_location AS spots_spot_location
> FROM spots
> WHERE
> MDSYS.SDO_GEOMETRY(:"MDSYS.SDO_GEOMETRY_1", :"MDSYS.SDO_GEOMETRY_2") =
> spot_location)
> WHERE ROWNUM <= :ROWNUM_1)
> WHERE ora_rn > :ora_rn_1
> 2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
> {'ora_rn_1': 0, 'ROWNUM_1': 1, u'"MDSYS.SDO_GEOMETRY_2"': 4326,
> u'"MDSYS.SDO_GEOMETRY_1"': 'POINT(0 0)'}
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
>
> [..]
>
> File .../env/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/
> sqlalchemy/engine/default.py", line 277, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01036: illegal
> variable name/number
> 'SELECT spots_spot_location \nFROM (SELECT spots_spot_location,
> ROWNUM AS ora_rn \nFROM (SELECT spots.spot_location AS
> spots_spot_location \nFROM spots \nWHERE
> MDSYS.SDO_GEOMETRY(:"MDSYS.SDO_GEOMETRY_1", :"MDSYS.SDO_GEOMETRY_2") =
> spot_location) \nWHERE ROWNUM <= :ROWNUM_1) \nWHERE ora_rn> :ora_rn_1'
> {'ora_rn_1': 0, 'ROWNUM_1': 1, u'"MDSYS.SDO_GEOMETRY_2"':
>
> 4326, u'"MDSYS.SDO_GEOMETRY_1"': 'POINT(0 0)'}
>
> Something is wrong about the escaping.
>
> Thanks,
> Tobias
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group
> athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
