Listing 1: the java stored procedure

import java.sql.*; // JDBC classes

public class UPDATEINVENTORY {
public static void UPDATEINVENTORY(int quantity.intitemId,
ResultSet throws SQLException
{
// Get connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection"

String sql = "SELECT CLAU.INVENTORY.QUANTITY"
+ " FROM CLAU.INVENTORY"
+ " WHERE CLAU.INVENTORY.ITEMID = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setInt(1, itemId);
stmt.execute();

ResultSet rs = stmt.getResultSet();
rs.next();
int currentQuantity = rs.getInt(1);

if (currentQuantity > quantity)
{
int newQuantity = currentQuantity - quantity;
sql = "UPDATE CLAU.INVENTORY SET QUANTITY = ? "
+ " WHERE CLAU.INVENTORY.ITEMID = ?";
stmt = con.prepareStatement(sql);
stmt.setInt(1, newQuantity);
stmt.setInt(2, itemId);
stmt.executeUpdate();

sql = "SELECT CLAU.INVENTORY.DESCRIPTION,CLAU.INVENTORY.QUANTIY, C
+ " FROM CLAU.INVENTORY"
+ " WHERE CLAU.INVENTORY.ITEMID = ?";
stmt = con.prepareStatement(sql);
stmt.setInt(1, itemId);
stmt.execute();

resultSet[0] = stmt.getResultSet();
}
}
}



Listing 2: inventory.dadx file

<?xml version="1.0" encoding="UTF-8"?>
<dadx:DADX xmlns:dadx="http://schemas.ibm.com/db2/dxx/dadx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:schemaLocation=
"http://schemas.ibm.com/db2/dxx/dadxdadx.xsd">
<dadx:result_set_metadata rowName="result" name="detail">
<dadx:column name="DESCRIPTION" type="VARCHAR" nullable="true" />
<dadx:column name="QUANTITY" type="INTEGER" nullable="true"/>
<dadx:column name="ETA" type="VARCHAR" nullable="true"/>
</dadx:result_set_metadata>

<dadx:result_set_metadata rowName="result" name="ignore">
<dadx:column name="QUANTITY" type="INTEGER" nullable="true"/>
</dadx:result_set_metadata>

<dadx:operation name="UpdateInventory">
<dadx:call>
<dadx:SQL_call>CALL UPDATEINVENTORY(:quantity, :itemId)</dadx:SQL_call>
<dadx:parameter name="quantity" type="xsd:int" kind="in" />
<dadx:parameter name="itemId" type="xsd:int" kind="in" />
<dadx:result_set name="results1" metadata="ignore" />
<dadx:result_set name="results2" metadata="detail" /> 
</dadx:call>
</dadx:operation>
</dadx:DADX>