[Zope] Database transaction

[Andrew Wilcox]

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