SEO

#1 (**permalink**) 04-20-2008, 07:40 AM

Is it possible to set up sequences to mimic the actions of a serial
field ? (IDS 9.4 FC2)
I want to be able to insert a new record with out changing the
application code but with more control of the limits of the serial
field.

I have a table -
create table tab1 ( tab1_id int8, descrip char(20) ) ;
alter table tab1 add constraint primary key (tab1_id) constraint
pk_tab1;

And a sequence -
create sequence tab1_seq start with 10000 maxvalue 19999 cycle ;

I would like to run sql -
insert into tab1 ( descript ) values ("first record" )

And to see -

tab1_id descrip
10000 first record

How can I link one to the other ? The Syntax manual is unclear although
it mentions synonyms, and the SQL tutorial does not mention them.

Colin Bull

__________________________________________________ _____________________
This email has been scanned for all known viruses by the MessageLabs Email
Security System.
__________________________________________________ _____________________

sending to informix-list

#2 (**permalink**) 04-20-2008, 07:40 AM

colin,

according to the SQL syntax Guide "Authorized users of a sequence can
request a new value by including the sequence.NEXTBAL expression in SQL
statement"

http://publibfp.boulder.ibm.com/epubs/pdf/ct1sqna.under "create
sequence" (2-207)

so, I tried :

create table tab1 ( tab1_id int8, descript char(20) ) ;
alter table tab1 add constraint primary key (tab1_id) constraint
pk_tab1;

create sequence tab1_seq start with 10000 maxvalue 19999 cycle
;

insert into tab1 ( tab1_id, descript )
values (tab1_seq.nextval,"first" );

insert into tab1 ( tab1_id, descript )
values (tab1_seq.nextval,"second" );

select * from tab1;

seems to do what you want

Colin Bull wrote:
> Is it possible to set up sequences to mimic the actions of a serial
> field ? (IDS 9.4 FC2)
> I want to be able to insert a new record with out changing the
> application code but with more control of the limits of the serial
> field.
>
> I have a table -
> create table tab1 ( tab1_id int8, descrip char(20) ) ;
> alter table tab1 add constraint primary key (tab1_id)
constraint
> pk_tab1;
>
> And a sequence -
> create sequence tab1_seq start with 10000 maxvalue 19999 cycle ;
>
> I would like to run sql -
> insert into tab1 ( descript ) values ("first record" )
>
> And to see -
>
> tab1_id descrip
> 10000 first record
>
>
> How can I link one to the other ? The Syntax manual is unclear
although
> it mentions synonyms, and the SQL tutorial does not mention them.
>
>
> Colin Bull
>
>
__________________________________________________ _____________________
> This email has been scanned for all known viruses by the MessageLabs
Email
> Security System.
>
__________________________________________________ _____________________
>
> sending to informix-list

#3 (**permalink**) 04-20-2008, 07:40 AM

not only can I not spell, I can't copy!!!!

sequence.NEXTVAL

#4 (**permalink**) 04-20-2008, 07:40 AM

Here's an example which does what you want. You have to use both a
trigger and a function:

create table mytab (
tid integer,
txt varchar(10)
);

create sequence myseq start with 10000 maxvalue 19999 cycle;

create function myval()
returning integer;
return (select myseq.nextval from systables where tabid=1);
end function;

create trigger mytrig insert on mytab
referencing new as post
for each row when (post.tid is null)
(execute function myval() into tid);

insert into mytab (txt) values ('first');
insert into mytab (txt) values ('second');
insert into mytab (txt) values ('third');

regards
Claus

Colin Bull wrote:
> Is it possible to set up sequences to mimic the actions of a serial
> field ? (IDS 9.4 FC2)
> I want to be able to insert a new record with out changing the
> application code but with more control of the limits of the serial
> field.
>
> I have a table -
> create table tab1 ( tab1_id int8, descrip char(20) ) ;
> alter table tab1 add constraint primary key (tab1_id) constraint
> pk_tab1;
>
> And a sequence -
> create sequence tab1_seq start with 10000 maxvalue 19999 cycle ;
>
> I would like to run sql -
> insert into tab1 ( descript ) values ("first record" )
>
> And to see -
>
> tab1_id descrip
> 10000 first record
>
>
> How can I link one to the other ? The Syntax manual is unclear although
> it mentions synonyms, and the SQL tutorial does not mention them.
>
>
> Colin Bull
>
> __________________________________________________ _____________________
> This email has been scanned for all known viruses by the MessageLabs Email
> Security System.
> __________________________________________________ _____________________
>
> sending to informix-list

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Sequences