Application development with Java - DB2 Express-C: The Developer for XML, PHP, C/C++, Java, and .NET - page 220

subdirectories in the function directory that correspond to the fully qualified
class names and place the related class files in the corresponding
subdirectories. You can create a JAR file too in case you want to copy a set of
stored procedure class files.
4. Register the stored procedure to the database using the CREATE
PROCEDURE command.
5. Call the stored procedure from the client program.
Let us register the stored procedure to create an XML document for the porder
column of the PURCHASEORDER table.
Step 1
Example 5-13 shows the stored procedure. Make sure that the class should be
public and extends the StoredProc class. The method in the class should be
public void. The OUT parameter values are set using the set function.
Example 5-13 Stored procedure
import java.sql.*;
import com.ibm.db2.jcc.*;
import COM.ibm.db2.app.StoredProc;
public class CreateOrder extends StoredProc
{
public void createOrder(int poid, String cart, String porder) throws
Exception
{
PreparedStatement stmt;
Connection con =
DriverManager.getConnection("jdbc:default:connection");
java.util.Date d = new java.util.Date();
String value=null;
String date=d.getYear()+"-"+d.getMonth()+"-"+d.getDate();
String query="Select xmldocument(XMLELEMENT (NAME
\"PurchaseOrder\"," +
"xmlattributes(cast(? as int) as \"PoNum\",cast(? as varchar(10))
as \"OrderDate\", cast(? as varchar(10)) as \"Status\"),"+
"xmlagg(xmlelement(NAME \"item\",xmlconcat(" +
"xmlelement(NAME \"partid\", t.pid)," +
"xmlelement(NAME \"name\", p.name)," +
"xmlelement(NAME \"quantity\",t.quantity)," +
"xmlelement(NAME \"price\", t.quantity*p.price)" +
"))))) from "+ cart +" as t, product as p where p.pid=t.pid";