you'd have to check cx_oracle's behavior here in conjunction with your trigger
(I'm assuming you're using a trigger here based on your previous message).
SQLAlchemy, as you can see below, runs the UPDATE statement, adding the current
known version to the WHERE criterion. It then checks the matched row count,
which is on the DBAPI as cursor.rowcount, that the row actually matched,
indicating that the row intended to be UPDATED was located and in fact had the
correct version. If your trigger is interfering with cx_oracle's ability to
return the correct rowcount, then you'd get this issue. So you'd need to
distill your test case into a cx_oracle script that emits the intended UPDATE
statement, including the version criterion, and then confirm that
cursor.rowcount is in fact returning the correct number. The trigger you're
doing might be getting in the way.
If you aren't using a trigger at all, and this is just default "version"
behavior, that should be working as we do have tests here which we run against
cx_oracle with success.
On Jul 19, 2012, at 11:01 PM, Tim wrote:
> I can not get versioning to work in Oracle (it does work for me in sqlite and
> Postgresql just changing the connect string).
>
> I am using timestamp with time zones for the version_id_col. Can anyone
> verify that this does work.
>
> SQLAlchemy==0.7.8
> cx-Oracle==5.1.2
>
> Python 2.6.6 (r266:84292, Dec 27 2010, 00:02:40)
> [GCC 4.4.5] on linux2
>
> >>> import ver.models as m; import transaction
> >>> s = m.DBSession()
> >>> i = s.query(m.MyModel).first()
> 2012-07-19 22:32:54,807 INFO [sqlalchemy.engine.base.Engine][MainThread]
> SELECT USER FROM DUAL
> 2012-07-19 22:32:54,807 INFO [sqlalchemy.engine.base.Engine][MainThread] {}
> 2012-07-19 22:32:54,818 INFO [sqlalchemy.engine.base.Engine][MainThread]
> BEGIN (implicit)
> 2012-07-19 22:32:54,819 INFO [sqlalchemy.engine.base.Engine][MainThread]
> SELECT models_id, models_name, models_value, models_ins_upd_timestamp
> FROM (SELECT models.id AS models_id, models.name AS models_name, models.value
> AS models_value, models.ins_upd_timestamp AS models_ins_upd_timestamp
> FROM models)
> WHERE ROWNUM <= :ROWNUM_1
> 2012-07-19 22:32:54,819 INFO [sqlalchemy.engine.base.Engine][MainThread]
> {'ROWNUM_1': 1}
> >>> i.value += 1
> >>> transaction.commit()
> 2012-07-19 22:33:18,656 INFO [sqlalchemy.engine.base.Engine][MainThread]
> UPDATE models SET value=:value, ins_upd_timestamp=:ins_upd_timestamp WHERE
> models.id = :models_id AND models.ins_upd_timestamp =
> :models_ins_upd_timestamp
> 2012-07-19 22:33:18,656 INFO [sqlalchemy.engine.base.Engine][MainThread]
> {'ins_upd_timestamp': datetime.datetime(2012, 7, 19, 22, 33, 18, 655856),
> 'models_ins_upd_timestamp': datetime.datetime(2012, 7, 19, 22, 31, 46,
> 814740), 'value': 2, 'models_id': 1}
> 2012-07-19 22:33:18,659 INFO [sqlalchemy.engine.base.Engine][MainThread]
> ROLLBACK
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_manager.py",
> line 107, in commit
> return self.get().commit()
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py",
> line 354, in commit
> reraise(t, v, tb)
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py",
> line 345, in commit
> self._commitResources()
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py",
> line 493, in _commitResources
> reraise(t, v, tb)
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/transaction-1.3.0-py2.6.egg/transaction/_transaction.py",
> line 465, in _commitResources
> rm.tpc_begin(self)
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/zope.sqlalchemy-0.7.1-py2.6.egg/zope/sqlalchemy/datamanager.py",
> line 86, in tpc_begin
> self.session.flush()
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/session.py",
> line 1583, in flush
> self._flush(objects)
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/session.py",
> line 1654, in _flush
> flush_context.execute()
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/unitofwork.py",
> line 331, in execute
> rec.execute(self)
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/unitofwork.py",
> line 475, in execute
> uow
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/persistence.py",
> line 59, in save_obj
> mapper, table, update)
> File
> "/home/timw/.virtualenvs/sat/lib/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-i686.egg/sqlalchemy/orm/persistence.py",
> line 504, in _emit_update_statements
> (table.description, len(update), rows))
> StaleDataError: UPDATE statement on table 'models' expected to update 1
> row(s); 0 were matched.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/-_uLzovXdkgJ.
> 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.
--
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.
