At 04:42 PM 2/10/00 +0000, 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.
In MySQL, just declare your id field to be AUTO_INCREMENT. Nothing could be easier. From the manual: An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. The SQL function LAST_INSERT_ID() returns the last generated AUTO_INCREMENT value. This is maintained on a per-connection basis, so it is safe to use. Andrew