Consultor Eletrônico

Status: Verified

GOAL:

How to create an SQL-92 database schema trigger without the Progress SQL Explorer Tool?

GOAL:

How to create an SQL-92 database schema trigger using JAVA code?

FACT(s) (Environment):

OpenEdge 10.1A
All Supported Operating Systems
Progress 9.0x
OpenEdge 10.x

FIX:

The following JAVA program demonstrates how to create an SQL-92 database schema trigger without using the Progress SQL Explorer Tool:
import com.progress.sql.sp.*;
import java.sql.*;
import java.util.*;

public class CreateJavaTrigger{
public static void main(String [] args) {
String url = "jdbc:datadirect:openedge://localhost:23456;databaseName=W609214100";
String drv = "com.ddtek.jdbc.openedge.OpenEdgeDriver";
String usr = "yshanshi";
String pwd = "progress";
String dropTrigger =
"DROP TRIGGER PUB.BI_RESOURCE_NOTIF_INFO_UPD_CTR";
String createTrigger =
"CREATE TRIGGER PUB.BI_RESOURCE_NOTIF_INFO_UPD_CTR" + "\n" +
"AFTER UPDATE OF BI_RESOURCE_NOTIF_KEY, BI_RESOURCE_ID, BI_NOTIF_PRTY, BI_NOTIF_NM, BI_QUEUE_NOTIFY_SW, BI_NOTIF_VALUE" + "\n" +
"ON PUB.BI_RESOURCE_NOTIF_INFO" + "\n" +
"REFERENCING NEWROW" + "\n" +
"FOR EACH ROW" + "\n" +
"IMPORT" + "\n" +
"import java.sql.Date;" + "\n" +
"import java.math.BigDecimal;" + "\n" +
"BEGIN" + "\n" +
"BigDecimal VAR_BI_RESOURCE_NOTIF_KEY;" + "\n" +
"if (NEWROW.isNULL(1)) " + "\n" +
"VAR_BI_RESOURCE_NOTIF_KEY = null;" + "\n" +
"else " + "\n" +
"VAR_BI_RESOURCE_NOTIF_KEY = (BigDecimal) NEWROW.getValue(1,NUMERIC);" + "\n" +
"SQLIStatement update_stmt = new SQLIStatement(\"update PUB.BI_RESOURCE_NOTIF_INFO set BI_RESOURCE_NOTIF_INFO_CTR = BI_RESOURCE_NOTIF_INFO_CTR + 1 where BI_RESOURCE_NOTIF_KEY = ? \");" + "\n" +
"update_stmt.setParam(1, VAR_BI_RESOURCE_NOTIF_KEY);" + "\n" +
"update_stmt.execute();" + "\n" +
"END";

Connection con;
Statement stmt;
try {
Class.forName(drv);
}
catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
&nbsp.; con = DriverManager.getConnection(url, usr, pwd);
con.setAutoCommit(false);
stmt = con.createStatement();
stmt.executeUpdate(dropTrigger);
stmt.close();
stmt = con.createStatement();
stmt.executeUpdate(createTrigger);
stmt.close();
con.commit();
con.close();
}
catch (SQLException ex) {
System.out.println ("*** (debugging) in sql exception block...");
while (ex != null) {
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("VendorCode: " + ex.getErrorCode());
ex = ex.getNextException ();
System.out.println ("");
}
}
catch (java.lang.Exception ex) {
System.out.println ("*** (debugging) in general exception block...");
// Got some other type of exception. Dump it.
ex.printStackTrace ();
}
finally {
System.out.println ("finally...");
}
}
}.