[Zope] Database transaction

Chris McDonough chrism@digicool.com
Thu, 10 Feb 2000 12:51:55 -0500


Matt, MySQL doesn't support transactions currently (but you probably
know this). 

There is a function in mySQL that allows you to retrieve the "next" UID
from a key column.  I can't remember for the life of me what it is, but
it's in the MySQL manual.  You'd need to incorporate this into a
subselect in the insert or update statement in the SQL query eg:

INSERT INTO my_table (my_col1, my_col2, my_col3) values (SELECT
next_available_id, <dtml-var myargument>, <dtml-var myargument2>)

I dont even know if MySQL allows this, but its the most atomic way to do
this.

Matt Goodall wrote:
> 
> Hi,
> 
> When adding to a database table I need to calculate the next number from
> the existing records, increment that number and add the new record using
> the calculated number.
> 
> The critical thing is that the transaction must be uninterrupted or two
> or more users could be assigned the same number.
> 
> Can this be done with the Zope database connections? I'm using MySQL at
> the moment but I could (presumably) change to PostgreSQL quite easily.
> 
> Any other ideas would be more than welcome.
> 
> Cheers, Matt.
> 
> --
> Matt Goodall             |  Isotek Electronics Ltd
> email: mgg@isotek.co.uk  |  Claro House, Servia Road
> Tel: +44 113 2343202     |  Leeds, LS7 1NL
> Fax: +44 113 2342918     |  England

-- 
Chris McDonough - Digital Creations, Inc.
Publishers of Zope - http://www.zope.org