Consultor Eletrônico

SUMMARY:

This Solution applies to Progress version 9.1C. It provides a tested example for creating a Java trigger for the SQL-92 engine programmatically.

EXPLANATION:

In trying to create a Java trigger, the SQL-92 engine parses the String contents and returns a syntax error if the required syntax is not following the expected format.

Especially important are the new lines and the spaces between the

BEGIN
and the
END

lines, as the example code below demonstrates.

SOLUTION:

//File StoreTrigger.java
import java.sql.*;

public class StoreTrigger {

public static void main(String[] args) {

// REGISTER DRIVER
try {
Driver d = (Driver)Class.forName("com.progress.sql.jdbc.JdbcProgressDriver").newInstance();
} catch (Exception e) {
System.out.println(e);
}

// GET CONNECTION
Connection con = null;
try{
con = DriverManager.getConnection("jdbc:jdbcprogress:T:pcrwe2:7777:sports","pub","pub");
System.out.println("Connection" + con + " established");
}catch(Exception e){
System.out.println(e);
}

Statement stmt = null;
// CREATE STATEMENT
try {
stmt = con.createStatement();
} catch (Exception e){
System.out.println(e);
}

// EXECUTE UPDATE
int results = 0;
try {
results = stmt.executeUpdate("drop trigger PUB.sampleTrigger");
System.out.println("Dropped trigger " + results);
} catch (Exception e){
System.out.println(e);
}

// SEND COMMIT
try {
con.commit();
} catch (Exception e) {
System.out.println(e);
}

// CLOSE STATEMENT
try {
stmt.close();
} catch (Exception e) {
System.out.println(e);
}

// CREATE STATEMENT
try {
stmt = con.createStatement();
} catch (Exception e){
System.out.println(e);
}

// EXECUTE UPDATE
String trigger = null;
try {
trigger = "CREATE TRIGGER PUB.sampleTrigger\n AFTER INSERT ON PUB.MYTABLE1\n REFERENCING NEWROW\n FOR EACH ROW\nIMPORT\n import java.io.*;\nBEGIN\n int myvar = 7;\n int mynum = ((Integer)NEWROW.getValue(1,myvar)).intValue();\n try {FileWriter mystream= new FileWriter(\"trigger.log\",true);\n mystream.write(\"\\nAfter NEWROW.getValue\");\n mystream.write(\"\\nGot value: \" + mynum);\n mystream.close();\n } catch (Exception e) {;}\n myvar = myvar + mynum;\n SQLIStatement insert_to_myTable2 = new SQLIStatement (\"INSERT INTO myTable2 values (\" + myvar +\")\");\n insert_to_myTable2.execute();\nEND";
System.out.println("Trigger to create:\n" + trigger + "\n");
results = stmt.executeUpdate(trigger); } catch (Exception e){ System.out.println(e); }
System.out.println("Created trigger " + trigger + "\nResult: " + results);
// CLOSE STATEMENT
try {
stmt.close();
} catch (Exception e) {
System.out.println(e);
}
// SEND COMMIT
try {
con.commit();
System.out.println("Connection " + con + " committed");
} catch (Exception e) {
System.out.println(e);
}

// CLOSE CONNECTION
try {
con.close();
System.out.println("Connection " + con + " closed");
} catch (Exception e) {
System.out.println(e);
}
} // end main
} // end StoreTrigger.java


Running the code returns the following output to the screen:

Connectioncom.progress.sql.jdbc.JdbcProgressConnection@7590db established
Dropped trigger 0
Trigger to create:
CREATE TRIGGER PUB.sampleTrigger
AFTER INSERT ON PUB.MYTABLE1
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.io.*;
BEGIN
int myvar = 7;
int mynum = ((Integer)NEWROW.getValue(1,myvar)).intValue();
try {FileWriter mystream= new FileWriter("trigger.log",true);
mystream.write("\nAfter NEWROW.getValue");
mystream.write("\nGot value: " + mynum);
mystream.close();
} catch (Exception e) {;}
myvar = myvar + mynum;
SQLIStatement insert_to_myTable2 = new SQLIStatement ("INSERT INTO myTable2 values (" + myvar +")");
insert_to_myTable2.execute();
END

Created trigger CREATE TRIGGER PUB.sampleTrigger
AFTER INSERT ON PUB.MYTABLE1
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.io.*;
BEGIN
int myvar = 7;
int mynum = ((Integer)NEWROW.getValue(1,myvar)).intValue();
try {FileWriter mystream= new FileWriter("trigger.log",true);
mystream.write("\nAfter NEWROW.getValue");
mystream.write("\nGot value: " + mynum);
mystream.close();
} catch (Exception e) {;}
myvar = myvar + mynum;
SQLIStatement insert_to_myTable2 = new SQLIStatement ("INSERT INTO myTable2 values (" + myvar +")");
insert_to_myTable2.execute();
END
Result: 0
Connection com.progress.sql.jdbc.JdbcProgressConnection@7590db committed
Connection com.progress.sql.jdbc.JdbcProgressConnection@7590db closed


References to Written Documentation:

Progress SQL-92 Guide and Reference