| import java.sql.* ; |
| import java.math.* ; |
| |
| public class Inventory |
| { |
| // The SUPPLIERINFO procedure accepts a supplier number and returns the |
| // supplier's name, street, city, state, and post code to separate output |
| // parameters. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#supplierinfo-procedure |
| // for more documentation. |
| public static void supplierInfo( BigDecimal suppNum |
| , String[] suppName |
| , String[] streetAddr |
| , String[] cityName |
| , String[] stateName |
| , String[] postCode |
| ) throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getSupplier = |
| conn.prepareStatement( "SELECT suppname, street, city, " |
| + " state, postcode " |
| + "FROM trafodion.invent.supplier " |
| + "WHERE suppnum = ?" |
| ) ; |
| |
| getSupplier.setBigDecimal( 1, suppNum ) ; |
| ResultSet rs = getSupplier.executeQuery() ; |
| rs.next() ; |
| |
| suppName[0] = rs.getString( 1 ) ; |
| streetAddr[0] = rs.getString( 2 ) ; |
| cityName[0] = rs.getString( 3 ) ; |
| stateName[0] = rs.getString( 4 ) ; |
| postCode[0] = rs.getString( 5 ) ; |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |
| // The SUPPLYQUANTITIES procedure returns the average, minimum, and maximum |
| // quantities of available parts in inventory to separate output |
| // parameters. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#supplyquantities-procedure |
| // for more documentation. |
| public static void supplyQuantities( int[] avgQty |
| , int[] minQty |
| , int[] maxQty |
| ) throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getQty = |
| conn.prepareStatement( "SELECT AVG(qty_on_hand), " |
| + " MIN(qty_on_hand), " |
| + " MAX(qty_on_hand) " |
| + "FROM trafodion.invent.partloc" |
| ) ; |
| |
| ResultSet rs = getQty.executeQuery() ; |
| rs.next() ; |
| |
| avgQty[0] = rs.getInt( 1 ) ; |
| minQty[0] = rs.getInt( 2 ) ; |
| maxQty[0] = rs.getInt( 3 ) ; |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |
| // The PARTLOCATIONS 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. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#partlocations-procedure |
| // for more documentation. |
| 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 trafodion.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 trafodion.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() ; |
| |
| } |
| } |