Re: [sqlalchemy] SA-Lockmode-oracle
On May 26, 2010, at 9:16 AM, dhanil anupurath wrote: DatabaseError: (DatabaseError) ORA-00907: missing right parenthesis OK, the syntax error here is fixed in ra84fef18507e . But the bad news is Oracle really can't handle FOR UPDATE with an ORDER BY or with double-nested subqueries in any case, you now get http://ora-02014.ora-code.com/ so its essentially a moot issue. -- 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.
[sqlalchemy] SA-Lockmode-oracle
Hi
I am using oracle/mysql and sqlalchemy for a project I am working on.
I am using lockmode in SA query,while generating an ORACLE query it
causes trouble.
The class definition is like this:
class AvailState(Base):
MONITORING = 1
NOT_MONITORING = 0
__tablename__ = 'avail_current'
entity_id = Column(Unicode(50),Sequence('entity_id_seq'),
primary_key = True)
entity_type = Column(Integer)
avail_state = Column(Integer)
monit_state = Column(Integer)
transient_state = Column(Unicode(255))
transient_state_time = Column(DateTime)
owner = Column(Unicode(255))
timestamp = Column(DateTime)
description = Column(Unicode(256)
I have the table generated:
Table ColumnData
Type
AVAIL_CURRENT ENTITY_ID Varchar2
ENTITY_TYPE Number
AVAIL_STATE Number
MONIT_STATE Number
TRANSIENT_STATEVarchar2
TRANSIENT_STATE_TIME Date
OWNER Varchar2
TIMESTAMP Date
DESCRIPTION
Varchar2
The query I have written in SA is:
avail_states=DBSession.query(AvailState).with_lockmode(cls.lockmode).\
filter(AvailState.entity_id.in_(input_entityids)).\
order_by(AvailState.entity_type.asc()).\
order_by(AvailState.entity_id.asc()).\
order_by(AvailState.owner.asc()).all()
The SA-generated query is :
SELECT avail_current_entity_id, avail_current_entity_type,
avail_current_avail_state, avail_current_monit_state,
avail_current_transient_state, avail_current_transient__1,
avail_current_owner, avail_current_timestamp,
avail_current_description FROM (SELECT avail_current_entity_id,
avail_current_entity_type, avail_current_avail_state,
avail_current_monit_state, avail_current_transient_state,
avail_current_transient__1, avail_current_owner,
avail_current_timestamp, avail_current_description, ROWNUM AS ora_rn
FROM (SELECT avail_current.entity_id AS avail_current_entity_id,
avail_current.entity_type AS avail_current_entity_type,
avail_current.avail_state AS avail_current_avail_state,
avail_current.monit_state AS avail_current_monit_state,
avail_current.transient_state AS avail_current_transient_state,
avail_current.transient_state_time AS avail_current_transient__1,
avail_current.owner AS avail_current_owner, avail_current.timestamp AS
avail_current_timestamp, avail_current.description AS
avail_current_description FROM avail_current WHERE
avail_current.entity_id = bc3e8724-d282-08a9-272a-458a22e93f74 FOR
UPDATE) WHERE ROWNUM =1) WHERE ora_rn 0
The query works fine with MYSQL
I am geting an error like this
Traceback (most recent call last):
File model/availability.py, line 278, in set_none_state
avail=DBSession.query(AvailState).with_lockmode(cls.lockmode).\
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1300, in first
ret = list(self[0:1])
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1221, in __getitem__
return list(res)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1361, in __iter__
return self._execute_and_instances(context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1364, in
_execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/session.py, line 754, in execute
return self.__connection(engine, close_with_result=True).execute(
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
Re: [sqlalchemy] SA-Lockmode-oracle
On May 26, 2010, at 9:16 AM, dhanil anupurath wrote:
Hi
I am using oracle/mysql and sqlalchemy for a project I am working on.
I am using lockmode in SA query,while generating an ORACLE query it
causes trouble.
The class definition is like this:
class AvailState(Base):
MONITORING = 1
NOT_MONITORING = 0
__tablename__ = 'avail_current'
entity_id = Column(Unicode(50),Sequence('entity_id_seq'),
primary_key = True)
entity_type = Column(Integer)
avail_state = Column(Integer)
monit_state = Column(Integer)
transient_state = Column(Unicode(255))
transient_state_time = Column(DateTime)
owner = Column(Unicode(255))
timestamp = Column(DateTime)
description = Column(Unicode(256)
I have the table generated:
TableColumnData
Type
AVAIL_CURRENT ENTITY_ID Varchar2
ENTITY_TYPE Number
AVAIL_STATE Number
MONIT_STATE Number
TRANSIENT_STATEVarchar2
TRANSIENT_STATE_TIME Date
OWNER Varchar2
TIMESTAMP Date
DESCRIPTION
Varchar2
The query I have written in SA is:
avail_states=DBSession.query(AvailState).with_lockmode(cls.lockmode).\
filter(AvailState.entity_id.in_(input_entityids)).\
order_by(AvailState.entity_type.asc()).\
order_by(AvailState.entity_id.asc()).\
order_by(AvailState.owner.asc()).all()
The SA-generated query is :
SELECT avail_current_entity_id, avail_current_entity_type,
avail_current_avail_state, avail_current_monit_state,
avail_current_transient_state, avail_current_transient__1,
avail_current_owner, avail_current_timestamp,
avail_current_description FROM (SELECT avail_current_entity_id,
avail_current_entity_type, avail_current_avail_state,
avail_current_monit_state, avail_current_transient_state,
avail_current_transient__1, avail_current_owner,
avail_current_timestamp, avail_current_description, ROWNUM AS ora_rn
FROM (SELECT avail_current.entity_id AS avail_current_entity_id,
avail_current.entity_type AS avail_current_entity_type,
avail_current.avail_state AS avail_current_avail_state,
avail_current.monit_state AS avail_current_monit_state,
avail_current.transient_state AS avail_current_transient_state,
avail_current.transient_state_time AS avail_current_transient__1,
avail_current.owner AS avail_current_owner, avail_current.timestamp AS
avail_current_timestamp, avail_current.description AS
avail_current_description FROM avail_current WHERE
avail_current.entity_id = bc3e8724-d282-08a9-272a-458a22e93f74 FOR
UPDATE) WHERE ROWNUM =1) WHERE ora_rn 0
Oracle apparently cannot nest FOR UPDATE inside a nested query (ticket 1815 is
added for this issue). You will have to forego the usage of
limit()/offset()/first() in your query, and only use all(). (Your example
python code is not the actual code since it doesn't match your stacktrace).
--
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.
Re: [sqlalchemy] SA-Lockmode-oracle
Hi,
Thanks for the quick reply.
In my query i am not using any limit()/offset()/first()
but i am using order by ascending. can order by cause an issue??
avail_states=DBSession.query(
AvailState).with_lockmode('update').\
filter(AvailState.entity_id.in_(input_entityids)).\
order_by(AvailState.entity_type.asc()).\
order_by(AvailState.entity_id.asc()).\
order_by(AvailState.owner.asc()).all()
the given python code is the actual code ( stack trace got modified while i
removed some package names. sorry about that.)
In mysql same code generates a single select for update query.
in oracle it translates into 3 nested queries.
thnx again
On Wed, May 26, 2010 at 9:53 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On May 26, 2010, at 9:16 AM, dhanil anupurath wrote:
Hi
I am using oracle/mysql and sqlalchemy for a project I am working on.
I am using lockmode in SA query,while generating an ORACLE query it
causes trouble.
The class definition is like this:
class AvailState(Base):
MONITORING = 1
NOT_MONITORING = 0
__tablename__ = 'avail_current'
entity_id = Column(Unicode(50),Sequence('entity_id_seq'),
primary_key = True)
entity_type = Column(Integer)
avail_state = Column(Integer)
monit_state = Column(Integer)
transient_state = Column(Unicode(255))
transient_state_time = Column(DateTime)
owner = Column(Unicode(255))
timestamp = Column(DateTime)
description = Column(Unicode(256)
I have the table generated:
TableColumnData
Type
AVAIL_CURRENT ENTITY_ID Varchar2
ENTITY_TYPE
Number
AVAIL_STATE
Number
MONIT_STATE
Number
TRANSIENT_STATEVarchar2
TRANSIENT_STATE_TIME Date
OWNER
Varchar2
TIMESTAMP
Date
DESCRIPTION
Varchar2
The query I have written in SA is:
avail_states=DBSession.query(AvailState).with_lockmode(cls.lockmode).\
filter(AvailState.entity_id.in_(input_entityids)).\
order_by(AvailState.entity_type.asc()).\
order_by(AvailState.entity_id.asc()).\
order_by(AvailState.owner.asc()).all()
The SA-generated query is :
SELECT avail_current_entity_id, avail_current_entity_type,
avail_current_avail_state, avail_current_monit_state,
avail_current_transient_state, avail_current_transient__1,
avail_current_owner, avail_current_timestamp,
avail_current_description FROM (SELECT avail_current_entity_id,
avail_current_entity_type, avail_current_avail_state,
avail_current_monit_state, avail_current_transient_state,
avail_current_transient__1, avail_current_owner,
avail_current_timestamp, avail_current_description, ROWNUM AS ora_rn
FROM (SELECT avail_current.entity_id AS avail_current_entity_id,
avail_current.entity_type AS avail_current_entity_type,
avail_current.avail_state AS avail_current_avail_state,
avail_current.monit_state AS avail_current_monit_state,
avail_current.transient_state AS avail_current_transient_state,
avail_current.transient_state_time AS avail_current_transient__1,
avail_current.owner AS avail_current_owner, avail_current.timestamp AS
avail_current_timestamp, avail_current.description AS
avail_current_description FROM avail_current WHERE
avail_current.entity_id = bc3e8724-d282-08a9-272a-458a22e93f74 FOR
UPDATE) WHERE ROWNUM =1) WHERE ora_rn 0
Oracle apparently cannot nest FOR UPDATE inside a nested query (ticket 1815
is added for this issue). You will have to forego the usage of
limit()/offset()/first() in your query, and only use all(). (Your example
python code is not the actual code since it doesn't match your stacktrace).
--
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.comsqlalchemy%2bunsubscr...@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.
Re: [sqlalchemy] SA-Lockmode-oracle
On May 26, 2010, at 10:10 AM, dhanil anupurath wrote: Hi, Thanks for the quick reply. In my query i am not using any limit()/offset()/first() but i am using order by ascending. can order by cause an issue?? your stacktrace says you are using first(): Traceback (most recent call last): File model/availability.py, line 278, in set_none_state avail=DBSession.query(AvailState).with_lockmode(cls.lockmode).\ File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/orm/query.py, line 1300, in first ret = list(self[0:1]) also SQLA 0.5 has inferior support for Oracle so I would suggest upgrading to 0.6 (the bug fix will only be in 0.6 in any case). the nested queries are because Oracle does not support LIMIT/OFFSET directly. A scheme using ROW NUMBER must be used. That scheme needs to be adjusted to move FOR UPDATE to the outside of the query. -- 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.
