Consultor Eletrônico

Solution ID: P14312

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

CAUSE:

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.

FIX:

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;

NOTE: In order to get the above code to work in the Progress version 9.1C SQL Explorer Tool, you need to add the hotspot subdirectory of the Progress Java Runtime Environment to the PATH system variable before starting up the database. For instance, the following command would be used for that in a DOS prompt:

set Path=%DLC%\jre\bin\hotspot;%Path%

Also, make sure that the above lines of code are not cut short and are not carried to the next line when executing them from SQL Explorer, for instance.