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-16
MONTHLYORDERS Stored Procedure
MONTHLYORDERS Stored Procedure
Java Method
public static void numMonthlyOrders(int month,
int[] numOrders)
throws SQLException
{
if ( month < 1 || month > 12 )
throw new
SQLException ("Invalid value for month. " +
"Retry the CALL statement " +
"using a number from 1 to 12 " +
"to represent the month.", "38001" );
}
Connection conn = DriverManager.getConnection("jdbc:sqlmx:");
PreparedStatement getNumOrders =
conn.prepareStatement("SELECT COUNT(month(order_date)) " +
"FROM samdbcat.sales.orders " +
"WHERE month(order_date) = ?");
getNumOrders.setInt(1, month);
ResultSet rs = getNumOrders.executeQuery();
rs.next();
numOrders[0] = rs.getInt(1);
rs.close();
conn.close();
}
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT, OUT number INT)
EXTERNAL NAME 'Sales.numMonthlyOrders'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
CALL Statement to Invoke the SPJ
To invoke the MONTHLYORDERS procedure in MXCI:
CALL samdbcat.sales.monthlyorders(3,?);
The MONTHLYORDERS procedure determines the total number of orders during a
specified month and returns this output in MXCI:
NUMBER
-----------
4
--- SQL operation complete.
In March, there were four orders.