I used TypeDecorator and get_dbapi_type to return the type object I wanted.
The parameters get the type cx_Oracle.TIMESTAMP for any filtering I do
myself, but the ones used in the version feature seem to ignore this and
thus still fail to locate the row.
On Friday, July 20, 2012 4:12:45 PM UTC-4, Tim wrote:
>
> 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 [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.
>>>
>>>
>>>
>> --
>> 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 [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.
>>
>>
>>
--
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/-/tiMo6LLnpQEJ.
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.
