The microseconds are getting in the way and getting rid of them does work.
Unfortunately, the trigger does not get rid of them and there are several
applications which write to these tables and depend on the microseconds.
It seems to work in cx_Oracle if I call the setinputsizes and use
cx_Oracle.TIMESTAMP as the input size. Do you have some suggestions on how
to add this to a custom type, or a decorated type?
On Friday, July 20, 2012 11:31:26 AM UTC-4, Michael Bayer wrote:
>
>
> On Jul 20, 2012, at 11:19 AM, Tim wrote:
>
> The first thing I did after I started having problems was remove the
> trigger (the above was without the trigger in place).
>
> That being said, it appears that I am having trouble with cx_Oracle and
> not SQLAlchemy. Using cx_Oracle directly, I can select a row by id, getting
> the timestamp. Then I try to select the row again, this time also using the
> timestamp I just retrieved, and get nothing.
>
> Thank you for your help.
>
>
> ah - you might want to truncate those microseconds off of your timestamp,
> they're possibly getting in the way.
>
>
>
> On Thursday, July 19, 2012 11:41:50 PM UTC-4, Michael Bayer wrote:
>>
>> 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 view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/nBweg95AeCwJ.
> 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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/BK9Hf9Lp2xYJ.
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.
