Matt Goodall wrote:
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.
[snip]
The stuff I forgot to mention is that I'm running Zope on Linux and that my database table looks something like this:
doctype char(1) not null, docnum int not null, ... other stuff ... primary key (doctype,docnum)
When I'm adding a record the docnum needs to be the next number for the doctype which means I can't use an auto incrementing field. I need to do something like:
begin transaction select max(docnum) from mytable where doctype='x' nextdocnum = docnum+1 insert into mytable values( 'x', nextdocnum, ... ) commit
What you could do is create another table to keep track of your index keys: CREATE TABLE sequences( sequence varchar(16) PRIMARY KEY, n int) where sequence is an identifier (doctype) and n is an incremented number for that key which you use to get your new key. So, to use this in your sql method: SELECT count(*) FROM sequences WHERE sequence=<doctype> if(count = 0) # Create a new sequence record INSERT INTO sequences SET sequence = <doctype>, n = 0 # Increment the index UPDATE sequences SET n = LAST_INSERT_ID(n+1) WHERE sequence = <doctype> # Get the index x = SELECT LAST_INSERT_ID() # Now insert the record in your table INSERT INTO mytable VALUES( 'x', nextdocnum, ... ) This method does not need transactions because even if multiple instances are running at the same time, they will each get their own index from the sequences table, so when they insert into mytable, the records will be unique. Cheers, Stephen -- Stephen Harrison - stephen@nipltd.com New Information Paradigms - www.nipltd.com