IDS Forum

Re: Autogenerated keys

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Wednesday, 13 December 2006, at 10:01 a.m.

In Response To: Re: Autogenerated keys (Chris Salch)

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

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

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.