Autogenerated keys
A developer on IDS 9.40 (HP-UX) couldn't retrieve the SERIAL value generated by an INSERT: "SELECT dbinfo('sqlca.sqlerrd1') FROM systables..." always returned 0, though it worked on a v10 box. Respondents explained the call must be issued immediately after the insert (no intervening SQL), using WHERE tabid=1, that it only works for SERIAL/SERIAL8 inserts of 0, that sequences (CURVAL/NEXTVAL) are an alternative, and that PUT/buffered insert cursors break serial retrieval. The poster then found the real cause: his own library code wasn't doing what he assumed, so the problem was in his application, not the server.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 :)
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.
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
>>> Nothing has worked for me yet.
It's normal.
The statement dbinfo('sqlca.sqlerrd1') returns the last serial value
inserted in a table. If you have no table with SERIAL or SERIAL8 column,
sqlerrd1 will always equal to 0.
It's similar with 'serial8' argument.
If you want to have a unique identifiant for a table, you have to add a
SERIAL (or SERIAL8) column :
CREATE TABLE TSAMPLE(COL1 SERIAL, COL2 INT, COL3 CHAR(3));
INSERT INTO TSAMPLE VALUES (0,2,'xxx');
INSERT INTO TSAMPLE VALUES (0,3,'xyx');
INSERT INTO TSAMPLE VALUES (0,4,'yyy');
SELECT * FROM TSAMPLE;
col1 col2 col3
1 2 xxx
2 3 xyx
3 4 yyy
If you want to have a common identifiant, you could use a sequence
(available since 9.40) :
CREATE SEQUENCE SEQSAMPLE INCREMENT BY 1 START WITH 1 NOMAXVALUE;
To increment your sequence, use SEQSAMPLE.NEXTVAL in a SQL statement
:
CREATE TABLE TSAMPLE2(COL1 INT, COL2 INT, COL3 CHAR(3));
INSERT INTO TSAMPLE2 VALUES (SEQSAMPLE.NEXTVAL, 123, 'AAA');
To read the current value, use SEQSAMPLE.CURVAL
SELECT SEQSAMPLE.CURVAL FROM SYSTABLES WHERE TABID=1;
Philippe
________________________________
"CHRIS SALCH"
<chrissalch@letu.
edu> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Autogenerated keys [7979]
13/12/2006 15:09
Please respond to
ids@iiug.org
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.
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.
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