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.
Any other ideas would be more than welcome.
Firstly, thanks for the replies but I forgot to mention something important! Also, I'd like to apologise now for the length of this post - it's not exactly succinct! This post is more of a, "has anyone got any better ideas", sort of thing. Feel free to criticise, correct or suggest anything. 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 I haven't had the chance to try something like Chris McDonough suggested yet but it could be useful. So a couple of work-around ideas: 1. Use ZODB to keep track of the latest docnum for each doctype. My understanding is that ZODB does support transactions - is that correct? 2. Lock and unlock the table around the "real" queries. 3. Change to a database manager that supports transactions i.e. PostgreSQL. 4. Some sort of file system lock from an external python method. And a couple of questions: 1. Does anyone know the state of the ZPyGreSQLDA product? 2. Would it be better to use ODBC? Is this even possible on Linux? 3. What happens to Zope if the user cancels the request after the lock but before the unlock? I know it's unlikely but someone's bound to manage it! You know what users are like :-). I suspect all of the solutions I can think of are susceptible to this. Well, thanks for reading this and thanks for any ideas. 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