SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Sample SPJs

HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004

A-15

DAILYORDERS Stored Procedure

Java Method

public static void numDailyOrders(Date date,

int[] numOrders)

throws SQLException

{

Connection conn = DriverManager.getConnection("jdbc:sqlmx:");

PreparedStatement getNumOrders =

conn.prepareStatement("SELECT COUNT(order_date) " +

"FROM samdbcat.sales.orders " +

"WHERE order_date = ?");

getNumOrders.setDate(1, date);

ResultSet rs = getNumOrders.executeQuery();

rs.next();

numOrders[0] = rs.getInt(1);

rs.close();

conn.close();

}

CREATE PROCEDURE Statement

CREATE PROCEDURE samdbcat.sales.dailyorders(IN DATE, OUT number INT)

EXTERNAL NAME 'Sales.numDailyOrders'

EXTERNAL PATH '/usr/mydir/myclasses'

LANGUAGE JAVA

PARAMETER STYLE JAVA

READS SQL DATA;

CALL Statement to Invoke the SPJ

To invoke the DAILYORDERS procedure in MXCI:

CALL samdbcat.sales.dailyorders(DATE '2003-03-19', ?);

The DAILYORDERS procedure determines the total number of orders on a specified

date and returns this output in MXCI:

NUMBER

-----------

--- SQL operation complete.

On March 19, 2003, there were two orders.