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