On May 6, 2010, at 7:47 AM, Tobias wrote:
> 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.
it is, if you say func.MDSYS.SDO_GEOMETRY('POINT(0 0)'). The tokens before
the final one are sent into "packagenames". see the third example at
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.func
.
>
>
>>>> 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 <tobias.sauerw...@camptocamp.com> 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 sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> 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 sqlalch...@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.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
