I think I've found what is causing my problems. See
http://paste.ofcode.org/38cMYRa7u268EsuUnWQXjfg
Also, I want to thank you for you help. It is very much appreciated.
Tim
On Monday, July 23, 2012 10:15:12 PM UTC-4, Michael Bayer wrote:
>
> the sqlalchemy.types.TIMESTAMP type has dbapi.TIMESTAMP established as the
> DBAPI type object to use. When the cx_oracle dialect is in use, the
> cursor.setinputsizes() method is called, passing in a value for every type
> that has a "dbapi" type defined with it, with the exception of a handful of
> string types which I've observed cause cx_oracle to make poorer decisions.
>
> So when you use sqlalchemy.types.TIMESTAMP, setinputsizes() should be
> called for any statement where the type is known. This should be the case
> for the version_id criteria used by the ORM.
>
> If you use a TypeDecorator with get_dbapi_type(), that may or may not work
> in this particular case - it's very possible that your custom TypeDecorator
> is reduced to its underlying type object before get_dbapi_type() is called.
>
>
>
>
> On Jul 23, 2012, at 1:45 PM, Tim wrote:
>
> 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.
>
>
>
--
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/-/HJ3XcaBdb9MJ.
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.
