How to get the sequence value in ESQL/C program?

Hi, when the program execute the following SQL. EXEC SQL select seq1.nextval into :v_id from sysmaster:sysdual; IDS report 217 error. Below are the description of 217 error. Column column-name not found in any table in the query (or SLV is undefined). Do anybody know how to get the current value of sequence in ESQL/C program except store procedure? thanks for your time.

First what version of esql/c do you have? It sounds like esql is treating this as a table and column. I would pass this directly to the engine and have it execute this. EXEC SQL PREPARE p_seq_1 FROM 'select seq1.nextval from sysmaster:sysdual'; EXEC SQL EXECUTE p_seq_1 INTO :v_id John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 08/23/2011 07:45:22 AM: > From: "CHUAN LU" <luchuan@cn.ibm.com> > To: ids@iiug.org > Date: 08/23/2011 07:50 AM > Subject: How to get the sequence value in ESQL/C program? [24669] > Sent by: ids-bounces@iiug.org > > Hi, > > when the program execute the following SQL. > > EXEC SQL select seq1.nextval into :v_id from sysmaster:sysdual; > > IDS report 217 error. > Below are the description of 217 error. > > Column column-name not found in any table in the query (or SLV is undefined). > > Do anybody know how to get the current value of sequence in ESQL/C program > except store procedure? > > thanks for your time. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, How is that sequence defined? I don't believe any sequences exist in sysmaster by default. At least I don't find any in my 11.70FC3 instance, and I get the same result you do. What is the result of select * from syssequences: syssequences is one of the database catalog tables in each database. Cheers, Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "CHUAN LU" <luchuan@cn.ibm.com> To: ids@iiug.org Date: 08/23/11 10:50 AM Subject: How to get the sequence value in ESQL/C program? [24669] Sent by: ids-bounces@iiug.org Hi, when the program execute the following SQL. EXEC SQL select seq1.nextval into :v_id from sysmaster:sysdual; IDS report 217 error. Below are the description of 217 error. Column column-name not found in any table in the query (or SLV is undefined). Do anybody know how to get the current value of sequence in ESQL/C program except store procedure? thanks for your time. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, The IDS version is 11.50FC8W4. Below are the defination. I create it in the user database, not sysmaster. create sequence seq1 start with 1 cache 20; I will try John Miller's suggestion. thanks

If the sequence is defined in the user db, then you have to identify the db in which the sequence exists. Sequences are not instance-wide. So, if you connect to sysmaster, your query should be select userdb:seq1.nextval from sysdual; or, if you connect to userdb, select seq1.nextval from sysmaster:sysdual; Cheers Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "CHUAN LU" <luchuan@cn.ibm.com> To: ids@iiug.org Date: 08/24/11 01:25 AM Subject: Re: How to get the sequence value in ESQL/C progra [24684] Sent by: ids-bounces@iiug.org Hi, The IDS version is 11.50FC8W4. Below are the defination. I create it in the user database, not sysmaster. create sequence seq1 start with 1 cache 20; I will try John Miller's suggestion. thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.