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

PARTLOCS Procedure
The PARTLOCS procedure accepts a part number and quantity and returns a set of location
codes that have the exact quantity and a set of location codes that have more than that quantity.
Java Method: partLocations()
Example A-16 partLocations() Method
public static void partLocations(int partNum,
int quantity,
ResultSet exactly[],
ResultSet moreThan[])
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getLocationsExact = conn.prepareStatement(
" SELECT L.loc_code, L.partnum, L.qty_on_hand " +
" FROM invent.partloc L " +
" WHERE L.partnum = ? " +
" AND L.qty_on_hand = ? " +
" ORDER BY L.partnum ");
getLocationsExact.setInt(1, partNum);
getLocationsExact.setInt(2, quantity);
PreparedStatement getLocationsMoreThan = conn.prepareStatement(
" SELECT L.loc_code, L.partnum, L.qty_on_hand " +
" FROM invent.partloc L " +
" WHERE L.partnum = ? " +
" AND L.qty_on_hand > ? " +
" ORDER BY L.partnum ");
getLocationsMoreThan.setInt(1, partNum);
getLocationsMoreThan.setInt(2, quantity);
exactly[0] = getLocationsExact.executeQuery();
moreThan[0] = getLocationsMoreThan.executeQuery();
}
Creating the Procedure: PARTLOCS
To create this procedure in the INVENT schema, upload the Inventory.jar file to the Neoview
platform, navigate to the INVENT 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.
partlocsName:
Inventory.jar > Inventory > partLocationsCode:
• First SQL parameter:
— Name: partnum
— Direction: IN
— SQL Data Type: INTEGER
— Java Data Type: int
• Second SQL parameter:
— Name: qty
— Direction: IN
— SQL Data Type: INTEGER
— Java Data Type: int
Parameters:
• Dynamic result sets: 2
• Accesses Database: selected
Attributes:
Procedures in the INVENT Schema 99