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 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.
