Consultor Eletrônico

Status: Verified

GOAL:

SQL: How to create SQL Stored Procedure without using the Progress SQL Explorer Tool?

GOAL:

How to create an SQL Stored Procedure using the JAVA programming language?

GOAL:

How to create an SQL Stored Procedure by compiling and executing a JAVA class containing the definition of the SQL Stored Procedure?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x

FIX:

The following JAVA program demonstrates how to create an SQL Stored Procedure without having to use the Progress SQL Explorer Tool. Compiling and executing this JAVA code creates an SQL Stored Procedure named StoredProcedureDeleteCustomer for the Progress 9.1x sports2000 database. To run this code against the OpenEdge 10.x Sports2000 database, change the JAVA statements:
String url = "jdbc:JdbcProgress:T:localhost:9999:sports2000";
String drv = "com.progress.sql.jdbc.JdbcProgressDriver";
to:
String url = "jdbc:datadirect:openedge://localhost:23456;databaseName=Sports2000";
String drv = "com.ddtek.jdbc.openedge.OpenEdgeDriver";
In either case, you will also need to modify the two statements:
String usr = "yshanshi";
String pwd = "progress";
To reflkect your own user name and password:
import com.progress.sql.sp.*;
import java.sql.*;
import java.util.*;
public class StoredProcedureDeleteCustomer {
public static void main(String [] args) {
String url = "jdbc:JdbcProgress:T:localhost:9999:sports2000";
String drv = "com.progress.sql.jdbc.JdbcProgressDriver";
String usr = "yshanshi";
String pwd = "progress";
String createStoredProcedure =
"CREATE PROCEDURE DeleteCustomer()" + "\n" +
"IMPORT" + "\n" +
"import com.progress.sql.sp.*;" + "\n" +
"import java.sql.*;" + "\n" +
"BEGIN" + "\n" +
"SQLIStatement listCust = new SQLIStatement (\"DELETE FROM pub.Customer\");" + "\n" +
"listCust.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 {
con = DriverManager.getConnection(url, usr, pwd);
stmt = con.createStatement();
stmt.executeUpdate(createStoredProcedure);
stmt.close();
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...");
}
}
}
.