Consultor Eletrônico

Status: Verified

GOAL:

How To Auto-increment Fields in SQL-92 After Adding a Record

FACT(s) (Environment):

Progress 9.1A
Progress 9.1B
Progress 9.1C

FIX:

In order to create an auto-increment field, you must use a combination of sequences and database triggers. When working with the SQL-92 engine, the 4GL-based Progress database triggers cannot be invoked. Therefore, equivalent database triggers need to be written using Java, with the intention of accomplishing the same functionality on the SQL-92 side.

The sample code below demonstrates how the customer number field from the sports2000 sample Progress database (sports2000.customer.custNum) can be auto-increased after inserting a record into that database table through a SQL-92 command -- that is,
INSERT INTO pub.customer (name,contact) VALUES
('Progress Software','Paulo Lima');

-- Sample code

CREATE TRIGGER increaseCustNum
AFTER INSERT ON pub.customer
IMPORT
import java.sql.*;
BEGIN

Integer max = new Integer(0);

SQLCursor sqlc = new SQLCursor("SELECT MAX(custNum) FROM pub.customer");

sqlc.open();
sqlc.fetch();

if ( sqlc.found () ) {
max = (Integer) sqlc.getValue(1, INTEGER);
}

sqlc.close();

int maxv = 0;
maxv = max.intValue();
maxv ++;

String stmt = "UPDATE pub.customer SET custNum = " + maxv + " WHERE
custNum = 0 ";
SQLIStatement update_stmt = new SQLIStatement( stmt );
update_stmt.execute();

END;