SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

DAILYORDERS Stored Procedure
Java Method
public static void numDailyOrders(Date date,
int[] numOrders)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
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
-----------
2
--- SQL operation complete.
On March 19, 2003, there were two orders.
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:default:connection");
Examples of the Sample SPJs 115