Consultor Eletrônico

Title: Sample Java Stored Procedure Program for SQL92 Engine

KnowledgeBase number: 19424
Creation Date: 18-January-2000
Modified Date:

This document applies to : Progress SQL92 Engine products
Version and Release Number: Progress V9

Details:

This knowledgebase entry provides a sample Java stored procedure wto show the syntax and sample constructions using the Progress SQL92 Engine in V9. This stored procedure creates a result set with the total number of orders per customer.

A similar result can be obtained using the following select statement:

select "order".custnum, customer.name, count("order".custnum) from
pub."order", pub.customer
where "order".custnum >= <parameter>
and customer.custnum = "order".custnum
group by "order".custnum, customer.name


Stored Procedure Example
========================

DROP PROCEDURE view_orders;
CREATE PROCEDURE view_orders ( 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;


References to Progress documentation
- Progress SQL-92 Guide and Reference


EAG/18-Jan-2000