Neoview Guide to Stored Procedures in Java (R2.2)

MONTHLYORDERS Procedure
The MONTHLYORDERS procedure accepts an integer representing the month and returns the
number of orders during that month to an output parameter.
Java Method: numMonthlyOrders()
Example A-4 numMonthlyOrders() 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");
PreparedStatement getNumOrders =
conn.prepareStatement("SELECT COUNT(month(order_date)) " +
"FROM 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();
}
Creating the Procedure: MONTHLYORDERS
To create this procedure in the SALES schema, upload the Sales.jar file to the Neoview
platform, navigate to the SALES schema in DB Admin, and then enter or select these values in
the Create Procedure Wizard of DB Admin. For instructions, see “Uploading SPJ JAR Files to
the Neoview Platform” (page 35) and “Creating SPJs” (page 43).
monthlyordersName:
Sales.jar > Sales > numMonthlyOrdersCode:
• First SQL parameter:
— Name: monthnum
— Direction: IN
— SQL Data Type: INTEGER
— Java Data Type: int
• Second SQL parameter:
— Name: ordernum
— Direction: OUT
— SQL Data Type: INTEGER
— Java Data Type: int[]
Parameters:
• Dynamic result sets: 0
• Accesses Database: selected
Attributes:
80 Sample SPJs