wow, nice job. I have to write a test for that but that's a definite high priority for the next SQLA release: http://www.sqlalchemy.org/trac/ticket/2539
On Jul 25, 2012, at 5:35 PM, Tim wrote: > 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 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/-/tiMo6LLnpQEJ. >> 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/-/HJ3XcaBdb9MJ. > 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.
