Re: ODBC and Serial
A serial column is not a sequence object. The value does not exist outside the context of the row. Sequence objects have been added to IDS with version 9.4. With earlier versions of IDS, developers get the next sequential value for a column in one of two ways: write a dummy row in the table containing the serial field, read it back and then update it when the rest of the data is gathered or create a table containing single row with the next available number, read and update that table and use the value as if it were a sequence. Christine Normile I/T Specialist, Informix Solutions Data Management Worldwide Sales Support Telephone: 877-252-5399 T/L 273-0981 Mobile: 210-365-5073 |---------+----------------------------> | | "THOMAS CARISCH" | | | <tcarisch@paninfo| | | .com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 04/25/2003 08:15 | | | AM | |---------+----------------------------> >------------------------------------------------------------------------------- ---------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: ODBC and Serial [994] | >------------------------------------------------------------------------------- ---------------------------------------| Hello to you all, does anybody know the way to retrieve the current SERIAL value thru ODBC. The sqlca.sqerrd[x] isn't supported by ODBC!? We use a VB application and need that value for preparing the next select in our program. Unfortunately there doesn't exist a SQL-function like nextval() and so on how e.g IBM UDB does for their identity columns. Any ideas? Thank's for help Thomas
There is a slow, undocumented and unrecommended way to get the serial value for the table not using 'dbinfo' 1. select partnum from systables where tabname = 'MYTAB' (into PRTNUM) 2. select serialv from sysmaster:sysptnhdr where partnum = PRTNUM Note, that this method is not recommended if this table is heavily loaded by inserts Formally, Informix keeps serial value for the table in the 'tablespace tablespace' - special disk structure that describes each partition (index or table fragment) in the dbspace. ------------------------------------------ Alexey Sonkin Senior Database Administrator -----Original Message----- From: Christine N.... [mailto:cnormile@us.ibm.com] Sent: Friday, April 25, 2003 2:16 PM To: ids@iiug.org Subject: Re: ODBC and Serial [1000] A serial column is not a sequence object. The value does not exist outside the context of the row. Sequence objects have been added to IDS with version 9.4. With earlier versions of IDS, developers get the next sequential value for a column in one of two ways: write a dummy row in the table containing the serial field, read it back and then update it when the rest of the data is gathered or create a table containing single row with the next available number, read and update that table and use the value as if it were a sequence. Christine Normile I/T Specialist, Informix Solutions Data Management Worldwide Sales Support Telephone: 877-252-5399 T/L 273-0981 Mobile: 210-365-5073 |---------+----------------------------> | | "THOMAS CARISCH" | | | <tcarisch@paninfo| | | .com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 04/25/2003 08:15 | | | AM | |---------+----------------------------> >--------------------------------------------------------------------------- -------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: ODBC and Serial [994] | >--------------------------------------------------------------------------- -------------------------------------------| Hello to you all, does anybody know the way to retrieve the current SERIAL value thru ODBC. The sqlca.sqerrd[x] isn't supported by ODBC!? We use a VB application and need that value for preparing the next select in our program. Unfortunately there doesn't exist a SQL-function like nextval() and so on how e.g IBM UDB does for their identity columns. Any ideas? Thank's for help Thomas