Consultor Eletrônico

Status: Verified

GOAL:

SQL: How to create and invoke a java stored procedure to return a customer name?

GOAL:

Sample code of an SQL java stored procedure.

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x

FIX:

The following steps demonstrate how to create and invoke an SQL Java stored procedure. This sample stored procedure takes a customer number as input and returns the customer name:
1. Save the following SQL script in your Progress Working Directory as 'CreateProcedureGetName.sql':
DROP PROCEDURE getName;
CREATE PROCEDURE getName( IN from_cust INTEGER)
RESULT (
name CHARACTER(30)
)
BEGIN
String sname = "";
SQLCursor custcursor = new SQLCursor ("select name from pub.customer where custnum = ?");

custcursor.setParam (1, from_cust);
custcursor.open ();
custcursor.fetch ();
while (custcursor.found())
{
sname = (String) custcursor.getValue(1, CHARACTER);
SQLResultSet.set (1, sname);
SQLResultSet.insert ();
custcursor.fetch();
}
custcursor.close ();
END
COMMIT WORK;
2. Save the following SQL script in your Progress Working Directory as 'InvokeTheGetNameStoredProcedure.sql':
CALL getName(1);
3. Create and serve a copy of the Sports2000 demo database in your working directory:
3.A. Start -> Programs -> DLC -> Proenv
3.B. prodb Sports2000 Sports2000
3.C. proserve Sports2000 -H <hostname> -N TCP -S 9999
4. Connect the SQL Explorer Tool to the database ( The SQL Explorer Tool graphical display is not supported in OpenEdge 10.x. Another tool must be used to execute the SQL code examples):
4.A. Start -> Programs -> DLC -> SQL Explorer Tool
4.B. File -> Connect -> Type in the <hostname>, <Port or Service Name>, <databasename> and <yourusername>
5. Create the stored procedure using the SQL Explorer Tool
5.A. File -> Run File -> CreateProcedureGetName.sql
6. Invoke the GetName() stored procedure:
6.A. File -> Run File -> InvokeTheGetNameStoredProcedure.sql