Auto Incrementing

[agriffin02]

Does Progress have auto incrementing type fields? If not how can this be handled as to not duplicate keys in the system?

 

[sdjensen]

It can be handled using triggers and sequences.

Example:
in create trigger
key = next-value(keyseq).

 

[agriffin02]

Hmmm... I can't use a trigger, I would break the current software that is using the database. I am working on some external software that expands the capabilities of Epicor's Vantage which is using Progress 9.1D.

When I insert into a particular table that has an sequential id on it, the id does not get inserted, so I need to make sure that this gets incremented appropriately.

 

[jdpjamesp]

You can still use next-value(keyseq) outside a trigger as far as I know.

 

[agriffin02]

How would that syntax look if I am using table below:

TestTable
Id int
Descr varchar


insert into TestTable (Id, Descr)
values (??, 'My Test')

 

[jdpjamesp]

You'd need to set up a sequence on the database.

then you'd do:

ASSIGN myTable.TableKey = NEXT-VALUE(mySequence).

 

[agriffin02]

I appologize here, I am a newbie with Progress. Can I setup a sequence without affecting any other processes that are going on? Or how do I find out if there already is a sequence set up for this table and column?

 

[tamhas]

Look at the code where the table is created.

 

[agriffin02]

If I do find a sequence for the column that I want, what is the syntax for using a sequence in an insert statement?

 

[jdpjamesp]

I've no idea about inserts I'm afraid.

 

[sdjensen]

How are you connected to the database? SQL or progress client?

If you are using Progress Client you do not use insert statements but something like the following

create customer
assign
  customer.customerid   = next-value(customersequence)
  customer.customername = "Freds sportsware" 
.
...

 

[agriffin02]

I am using the Merant ODBC driver to connect to Progress. Do I use the same assign statement? I have noticed that SQL insert statements do work to at least insert data into the database.

 

[4GLNewbie]

Then if u're working with sql ( on a progress db ) and not Progress as a language to make programs, u should check the db documentation about managing sequences.. i think there is surely something about that.

 

[sdjensen]

Or try asking in the SQL-92 forum as well. They might help you.

 

[Casper]

How would that syntax look if I am using table below:
TestTable
Id int
Descr varchar
insert into TestTable (Id, Descr)
values (??, 'My Test')

If you know the sequence name of the existing sequence then do:

[LEFT]INSERT INTO pub.TestTable (Id, Descr)[/LEFT]
VALUES (pub.<TestTableSequencename>.NEXTVAL, 'Some description');

Casper