[Zope] ZSQL retreive auto_increment id inserted [Q]

[Jim Sanford]

In PostgreSQL there is a SERIAL data type that is an autoincrementing 4 byte
integer.

If you have a table called data1 with a field called recid that is created
as a SERIAL type and a char field called name, you would add a record and
get the recid like this:

insert into data1 (name) values ('George Smith');
<dtml-var sql_delimiter>
select last_value from data1_recid_seq;

data1_recid_seq is a 1 row sequence table created when you create the data1
table with the recid field as a SERIAL type

 __________________________________________________________________

              Jim Sanford
      .       Database Engineer
     / \  /   Accelerated Technology, Inc.
    /   /     720 Oak Circle Drive East
   /  /  \    Mobile, AL 36609
  / /     \   Voice: 334-661-5770  fax: 334-661-5788
 /         \  E-Mail: jsanford@atinucleus.com
              Web: http://www.atinucleus.com

 Source Code, No Royalties, Any CPU...It just make sense !
 __________________________________________________________________


----- Original Message -----
From: chas <panda@skinnyhippo.com>
To: Anthony Baxter <anthony@interlink.com.au>; Sam Gendler
<sgendler@teknolojix.com>
Cc: Bruno Mattarollo <brunomadv@ciudad.com.ar>; <zope@zope.org>
Sent: Thursday, November 18, 1999 12:00 PM
Subject: Re: [Zope] ZSQL retreive auto_increment id inserted [Q]


>
> >>>> Sam Gendler wrote
> >> I didn't take a long look, but it looks as though this solution
requires
> >> you to call a separate ZSQL query in order to retrieve the ID.
Needless to
> >> say, there is a wide open race condition if someone else inserts
another
> >> entry before you get the results from the second query. I was looking
for
> >> something that returns the last_insert_id as the result set to an
INSERT
> >> query, much the way the apis (for mysql anyway) do in other
environments.
> >> Please correct me if I am wrong.
>
> I guess this all depends how Zope shares database connections between
> multiple ZSQL methods that are called within the same DTML method.
>
> eg. With straight CGI and MySQL, this was never a problem; even if 2
people
> executed the same CGI script, each script had its own database connection
> and insert_id()'s were specific to the database connection in which the
> last insert was done.
>
> Now, does Zope dedicate the database connection to all the ZSQL scripts
> within a DTML method before handing it over to another DTML method ?
> If not, is there any way of forcing this ?
>
> >>Anthony Baxter wrote :
> >What I do with Oracle and Sequences is have multiple ZSQL methods: the
> >first looks up the next value:
> >
> >select sequence_name.nextval from dual
> >
> >I take this number back, and use it in subsequence DTML and SQL code.
>
> Sorry, you've lost me here - is this specific to Oracle ?
>
> chas
>
>
> _______________________________________________
> 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 )
>
>