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

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 HPDM, and then enter or select these values in the
Create Procedure dialog box of HPDM. For more information, see the HP Database Manager
(HPDM) User Guide.
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:
Procedures in the SALES Schema 73