IDS Forum

Re: Autogenerated keys

Posted By: Chris Salch
Date: Wednesday, 13 December 2006, at 10:55 a.m.

In Response To: Re: Autogenerated keys (ART KAGEL, BLOOMBERG/ 731 LEXIN)

Tracked it down, some library code was not doing what I thought it was.
Very annoying.

Thanks.

On Wed, 2006-12-13 at 10:01 -0500, ART KAGEL, BLOOMBERG/ 731 LEXIN
wrote:
> So with that definition, if you insert a row with:
>
> INSERT INTO li_stu_geggrps_rec (group_id) values (0);
>
> Then immediately (prior to ANY other SQL statement) issue:
>
> SELECT dbinfo( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 1;
>
> The latter will return the serial value assigned to group_id in the row just
> inserted.
>
> If you are using a PUT cursor for the insert then you have a problem, because
> the serial value is not actually assigned until the batch or rows held in the
> cursor are physically flushed to the server either with a manual FLUSH
> <cursorname>; statement or due to the cursor's internal buffer filling. When
> more than one row are flushed together only the serial value assigned to the
> last row flushed will be returned and there is no guarantee that if N rows
> were
> flushed that the N assigned serials are sequential. Intermediate values
> between
> the lowest one assigned to the batch and the one returned may have been
> assigned to other sessions' insertions. SERIAL columns and PUT cursors do not
> work well together.
>
> Art S. Kagel
>
> ----- Original Message -----
> From: Chris Salch <ids@iiug.org>
> At: 12/13 9:52:06
>
> This is an extracted version of the ddl, (I'm stuck with a tool that
> generates this stuff where I can't get at it.)
>
> CREATE TABLE informix.lu_stu_deggrps_rec (
>
> group_id SERIAL NOT NULL,
>
> id INTEGER DEFAULT 0 NOT NULL,
>
> idx INTEGER DEFAULT 0 NOT NULL,
>
> deggrp CHAR(8) DEFAULT '' NOT NULL,
>
> PRIMARY KEY(group_id)
> )
> ;
> CREATE INDEX informix.stu_deggrp_idx2
>
> ON informix.lu_stu_deggrps_rec(id, idx)
> ;
> CREATE UNIQUE INDEX informix.9629_36107
>
> ON informix.lu_stu_deggrps_rec(group_id)
> ;
> CREATE UNIQUE INDEX informix.stu_deggrp_idx1
>
> ON informix.lu_stu_deggrps_rec(id, idx, deggrp)
> ;
>
> On Wed, 2006-12-13 at 09:22 -0500, ART KAGEL, BLOOMBERG/ 731 LEXIN
> wrote:
> > What type of 'auto generated key'? Is this a serial column, serial 8 column,
> > or
> > an integer inserted using a sequence? Please provide specifics. If a serial
> > or serial8 are you inserting specific values or a '0' permitting a server
> > assigned value?
> >
> > Art S. Kagel
> >
> > ----- Original Message -----
> > From: Chris Salch <ids@iiug.org>
> > At: 12/13 9:19:28
> >
> > I need a way to find an auto generated key after an insert statement. I'm
> > working with informix 9.40.HC5 on an HPUX box and have tried the following
> > query, which works quite well on an informix 10 test machine:
> >
> > select dbinfo('sqlca.sqlerrd1') from systables where tabname='systables'
> >
> > Unfortunately, it always returns 0. I've tried this with dbinfo('serial8'),
> > tabid=1 and several combinations of these. Nothing has worked for me yet.
> >
> > Any suggestions?
> >
> > (If there is a nice way to get the ibm jdbc driver to return it's keys
> > according to the jdbc standard and not some obfuscated method I would gladly
> > settle for that :)
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
--
-----------------------
Chris Salch
Programmer Analyst
LeTourneau University
903-233-3537

Messages In This Thread

• Autogenerated keys
  CHRIS SALCH -- Wednesday, 13 December 2006, at 9:09 a.m.
  • Re: Autogenerated keys
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Wednesday, 13 December 2006, at 9:22 a.m.
    • Re: Autogenerated keys
      Chris Salch -- Wednesday, 13 December 2006, at 9:42 a.m.
      • Re: Autogenerated keys
        ART KAGEL, BLOOMBERG/ 731 LEXIN -- Wednesday, 13 December 2006, at 10:01 a.m.
        • Re: Autogenerated keys
          Chris Salch -- Wednesday, 13 December 2006, at 10:55 a.m.
  • Re: Autogenerated keys
    Philippe Pignon1 -- Wednesday, 13 December 2006, at 9:44 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.