[Zope] Returning incremented id from mysql query?

[Chris Kratz]

Hello Alec,

My experience has been that all sql statements that are executed within one 
request are all serialized through a single connection.  Now, we use postgres 
instead of mysql, so there is a possiblity that your da works differently.  
But, we depend on this behavior for this very reason (to be able to get 
inserted sequence numbers).  Also, at least in postgres everything works 
within a transaction, so we have to be working in the same connection for the 
duration of a request.

Hope that helps,

-Chris

On Thursday 15 August 2002 10:22 am, Alec Munro wrote:
> Hmm, just did some reading on last_insert_id, and it says that it works
> on a per-connection basis.
> How are Zope's connections handled? Is it simply one connection that
> various SQL scripts can use when they need it? How about if a single SQL
> method contained multiple statements? Will these statements all be fed
> into the connection at once, or is there a possibility of another
> statement from another file sneaking in between two of them.
>
> -----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
>
>
>
>
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )

-- 
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com