Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

// Return a result set of rows from the PARTLOC table listing
// locations that have this part in stock and the quantity they
// have on hand.
PreparedStatement getLocations = conn.prepareStatement(
" SELECT * " +
" FROM invent.partloc " +
" WHERE partnum = ? ");
getLocations.setInt(1, partNum);
locations[0] = getLocations.executeQuery();
// Return a result set of rows from the PARTSUPP table listing
// suppliers who supply this part.
PreparedStatement getSuppliers = conn.prepareStatement(
" SELECT * " +
" FROM invent.partsupp " +
" WHERE partnum = ? ");
getSuppliers.setInt(1, partNum);
suppliers[0] = getSuppliers.executeQuery();
// Return a result set of rows from the EMPLOYEE table listing
// sales reps that have sold this part.
PreparedStatement getReps = conn.prepareStatement(
" SELECT * " +
" FROM persnl.employee " +
" WHERE empnum in ( SELECT O.salesrep " +
" FROM sales.orders O, " +
" sales.odetail D " +
" WHERE D.partnum = ? " +
" AND O.ordernum = D.ordernum ) " +
" ORDER BY empnum ");
getReps.setInt(1, partNum);
reps[0] = getReps.executeQuery();
}
Creating the Procedure: PARTDATA
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).
partdataName:
Sales.jar > Sales > partDataCode:
Procedures in the SALES Schema 87