Databases Reference
In-Depth Information
stmt = con.prepareStatement(query);
stmt.setInt(1,poid);
stmt.setString(2, date);
stmt.setString(3,"unshipped");
ResultSet rs=stmt.executeQuery();
rs.next();
value=rs.getString(1);
rs.close();
set(3,value);
}
}
Step 2
Compile the above class using the javac compiler.
Step 3
Copy the
CreateOrder.class
to the
sqllib/function
directory.
Step 4
Register the stored procedure using the following command:
CREATE PROCEDURE createOrder( IN poid int,IN cart varchar(10), OUT
porder varchar(5000)) DYNAMIC RESULT SETS 0 NOT DETERMINISTIC LANGUAGE
JAVA PARAMETER STYLE DB2GENERAL NO DBINFO FENCED THREADSAFE READS SQL
DATA PROGRAM TYPE SUB EXTERNAL NAME 'CreateOrder.createOrder'
Step 5
Call the stored procedure from the client program.
The
createPorder
method of the application calls this stored procedure to create
the
PORDER
column value for the
PURCHASEORDER
table. You can call this stored
procedure from the command line using the following command:
call createOrder(poid, cart, ?)
where
poid
is the purchase order ID and
cart
is the table name. Before calling the
stored procedure, make sure that the
poid
value does not exist in the
PURCHASEORDER
table and
CART
table has the
PID
and
QUANTITY
columns of
varchar(10) and int data type respectively. Also, populate the
CART
table with
product ID and quantity values.
PID
inserted in this table should exist in the
PRODUCT
table.
The stored procedure returns the XML value in its output parameter denoted by
the question mark (?) while calling the stored procedure.
Search WWH ::
Custom Search