[Zope] Returning incremented id from mysql query?

[Alec Munro]

I was afraid you would say that.
I know about the mysql function, but I have some concerns about it.
Primarily, I want to make sure I always get the right record id, even if
two users are inserting at the same time.
I assume this could be done using an InnoDB table, because transactions
would allow me to bundle up all the instructions and execute them as a
batch (this is what transactions are good for, right?). But outside of
that, what are my options to ensure that I get the right Id.

Or, there may be another way around this. Basically, I want the user to
fill out one form, and then different parts of that form go into
different tables of the database. These tables are related by id. So if
there is some way to do an insert into multiple tables at once, and set
a field in one to be equal to a field being auto incremented in another,
that would also do.

Alec Munro

-----Original Message-----
From: Ed Leafe [mailto:ed@leafe.com] 
Sent: August 14, 2002 8:46 PM
To: Alec Munro
Cc: zope@zope.org
Subject: Re: [Zope] Returning incremented id from mysql query?


On Wednesday, August 14, 2002, at 07:21  PM, Alec Munro wrote:

> I'm looking for a way to get the id generated when I insert data into 
> a table with an auto-incremented key? I've found vague references to 
> this, but assuming I do an insert from a python script, calling an SQL

> method like:
> container.SQL.insertSomething(name="bob")
> how would I get the id.
>
> Thanks very much, hope someone can help me with this.

	I have a python script that I call after inserting a new record;
it 
takes the name of the table as its parameter, named 'tcTable'. The 
script reads:

laRes = context.lastInsertID(tcTable=tcTable)
lastID = laRes[0][0]
return lastID

	'lastInsertID' is a Z SQL method that also takes the name of the

table as a parameter. Its code is:

select last_insert_id() from <dtml-var tcTable>

Note that this syntax is specific to MySQL; other databases have similar

calls to retrieve the last created ID.

      ___/
     /
    __/
   /
  ____/
  Ed Leafe
  http://leafe.com/
  http://foxcentral.net