I believe that your premise that ZSQL can only do one query is false. I am able to handle this exact situation (using postgres) with the following in a single ZSQL method: insert into mytable(id,col1,col2) values(nextval('seq_id'),val1, val2); select curval('seq_id'); This will cause the ZSQL method to return the newly inserted id to you and this all happens within a single transaction. -mike
Hi All, Twice now I've come into what must be a fairly common problem when using SQL databases in Zope: I simply want MySQL to give me a unique identifier and I need to retrieve what its value is to continue using the correct record.
The MySQL side of this is very straightforward -- I just make an AUTO_INCREMENT field and use LAST_INSERT_ID() to get what its value was.
I'm less sure how safe this is in Zope -- the whole point being, of course, that when running multi-threaded, another thread *could* stick another record into the table, so that LAST_INSERT_ID() comes up with the wrong value. As a perhaps naive attempt to handle this in a previous application (for which the consequences of an error were not too severe), I simply used the threading module and acquired a lock -- I'm hoping that this will keep at least any threads within my current Zope process from interfering. (Please forgive my ignorance of threads if this betrays a deep misunderstanding! Corrections will be much appreciated. ;-)).
I can however, picture installations with more than one Zope *process* running, but accessing the same database, which would presumeably allow collisions under high loads. :-(
I tried using MySQL's table-locking mechanism to do this, but the lock would cause *all* my queries to fail -- including the ones that I was supposed to be protecting. Since each ZSQL method can only do one query, I couldn't figure out how to run a batch of them.
MySQL can do transactional tables nowadays, but I'm not sure if they would actually make sense for this application -- this "get a unique new record id" function is the only thing I really need to do this way. And I'm not even sure that transactions would be proof against this (would transactions stop the LAST_INSERT_ID() being incremented in the middle of the transaction? It doesn't seem like it would actually violate the transaction model to do that).
There doesn't seem to be much on the Zope website or in the ZDG about how to deal with this situation (or perhaps I don't know what to search for). Has anyone else solved it? Is the thread-locking solution adequate, or is there a better way?
Any suggestions?
Thanks, Terry
-- Terry Hancock ( hancock at anansispaceworks.com ) Anansi Spaceworks http://www.anansispaceworks.com
"Some things are too important to be taken seriously"
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )