Consultor Eletrônico

Status: Verified

GOAL:


SQL: How do we code a Progress stored Java procedure to have return values?

GOAL:

How do we write a JAVA SQL stored procedure with INPUT and OUTPUT parameters?

GOAL:

Sample JAVA SQL stored procedure with INPUT and OUTPUT parameters.

FACT(s) (Environment):

Progress 9.1x
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x

FIX:

The solution was developed against the Progress Sports2000 demo database and consists of the following two sample code files:

1. CreateProcedureViewOrders.sql: This is the SQL code needed to create a stored procedure named ViewOrders that returns a Resultset from the Progress Sports2000 demo database.

2. ViewOrders.java: This is the java code for the program that invokes the above stored procedure and processes the returned ResultSet.

Make sure to modify the user name and password and the url string to match your configuration:

/*************** Start of CreateProcedureViewOrders.sql *******************/

DROP PROCEDURE ViewOrders;
CREATE PROCEDURE ViewOrders ( IN from_cust INTEGER)
RESULT (
custnum NUMERIC,
name CHARACTER(30),
totalorders NUMERIC
)
BEGIN
Integer icustnum = new Integer(0);
Integer itotorders = new Integer(0);
String sname = "";

SQLCursor ordercursor = new SQLCursor (
"select custnum, count(1) from pub.\"order\" where custnum >=? group by custnum"
);
SQLCursor custcursor = new SQLCursor (
"select name from pub.customer where custnum = ?"
);

ordercursor.setParam (1, from_cust);
ordercursor.open ();
ordercursor.fetch ();
while (ordercursor.found())
{
icustnum = (Integer) ordercursor.getValue(1, INTEGER);
itotorders = (Integer) ordercursor.getValue(2, INTEGER);

custcursor.setParam (1, icustnum);
custcursor.open ();
custcursor.fetch ();
if (custcursor.found()) sname = (String) custcursor.getValue(1, CHARACTER);
custcursor.close ();

SQLResultSet.set (1, icustnum);
SQLResultSet.set (2, sname);
SQLResultSet.set (3, itotorders);
SQLResultSet.insert ();

ordercursor.fetch();
}
ordercursor.close ();
END
COMMIT WORK;
/*************** End of CreateProcedureViewOrders.sql ***********************/

/*************** Start of the ViewOrders.java program *******************/
import java.sql.*;
public class ViewOrders {
public static void main(String[] args) {
try {
// initialize variables
String url = "jdbc:JdbcProgress:T:localhost:9999:sports2000";
String userid = "username";
String passwd = "password";
String callString = "{CALL ViewOrders (?)}";
CallableStatement viewOrders;
int CustNum = 2092;
ResultSet rs;

// loads the driver object
Class.forName("com.progress.sql.jdbc.JdbcProgressDriver");
// creates a connection object
Connection con = DriverManager.getConnection(url, userid, passwd);
// Prepare the statement
viewOrders = con.prepareCall(callString);
// Bind the parameter
viewOrders.setInt(1, CustNum);
// Execute the statement
viewOrders.execute();
rs = viewOrders.getResultSet();
while (rs.next()) {
int custnum = rs.getInt(1);
String name = rs.getString(2);
int totalorders = rs.getInt(3);
System.out.println(custnum + " " + name + " " + totalorders);
}

viewOrders.close();
con.commit();
con.close();
}
catch (SQLException ex) {
// If an SQL Exception was generated. Catch it and display the error information.
// Note that there could be multiple error objects chained together.
// System.out.println ("** Error In SQLException: \n");
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 ("** Error In java.lang.exception: \n");
// Got some other type of exception - dump it.
ex.printStackTrace ();
}
}
}


/*************** End of the ViewOrders.java program *******************/