Consultor Eletrônico

Status: Unverified

GOAL:

How to create a Java stored procedure with multiple input and output parameters

GOAL:

Example SQL stored procedure

FACT(s) (Environment):

Progress 9.x
OpenEdge 10.x

FIX:

This solution assumes that you have installed the necessary JDK/SDK (if required for your platform) to allow the usage of Java stored procedures.

The stored procedure example below will run against the Progress sports2000 database and will return one record (due to the conditions in the code). The record that it returns has a custnum = 1 and state = MA.
When calling the procedure, if you change the custnum that is input from 5 to 6, it will then also return the record for customer number 5.
To create the stored procedure:

drop procedure view_part;
create procedure view_part( IN incustnum integer , IN instate varchar(18) )
result ( mycustnum integer , myname varchar(18) , mystate varchar(18) )
begin
Integer scustnum = new Integer(0);
String sname = "" , sstate = "";
SQLCursor partcursor = new SQLCursor("select custnum , name, state from pub.customer where custnum < 10");
partcursor.open();
partcursor.fetch();
while (partcursor.found())
{
// Assign the three columns from the row to some local variables
scustnum = (Integer) partcursor.getValue(1,INTEGER);
sname = (String) partcursor.getValue(2,CHAR);
sstate = (String) partcursor.getValue(3,CHAR);
// If the customer's custnum is less than the input custnum AND
// the customer's state is equal to the input state then return
// the record.
if ( scustnum.compareTo(incustnum) < 0 & sstate.equals(instate) )
{
SQLResultSet.set(1,scustnum);
SQLResultSet.set(2,sname);
SQLResultSet.set(3,sstate);
SQLResultSet.insert();
}
partcursor.fetch();
}
partcursor.close();
end
commit;

To run the stored procedure from the client, run:
call view_part( 5 , 'MA' );