WorkingWithStoredProcedures

The DAS can work with stored procedures in the same way it works with SQL statements. The following example should look familiar except that a stored prcedure call statement replaces the typical SELECT statement:

DAS das = DAS.FACTORY.createDAS(getConnection());
Command read = das.createCommand("{call GETALLCOMPANIES()}");
DataObject root = read.executeQuery();

The predefined stored procedure "GETALLCOMPANIES" returnes a result just like "select * from company" would. There are, however, procedures that do not return
results and these calls can also be executed with the same programming model. In fact any arbitrary stored procedure can be invoked in this way. The follwing example calls a proc that deletes an identified company:

DAS das = DAS.FACTORY.createDAS(getConnection());
Command delete = das.createCommand("{call DELETECUSTOMER(?)}");
delete.setParameter(1, 1234);
delete.execute();

Stored procedures may also provide IN/OUT and OUT parameters. The following illustrates the use of a stored procedure that returns a result set as well as an OUT parameter:

DAS das = DAS.FACTORY.createDAS(getConfig("storedProcTest.xml"), getConnection());
Command read = das.getCommand("getNamedCustomers");
read.setParameter(1, "Williams");
DataObject root = read.executeQuery();

Integer customersRead = (Integer) read.getParameter(2);

This example makes use of a configuration file to define the command including the OUT parameter:

<Config xsi:noNamespaceSchemaLocation="http:///org.apache.tuscany.das.rdb/config.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   <Command name="getNamedCustomers" SQL="{call GETNAMEDCUSTOMERS(?,?)}" kind="procedure">

     <Parameter direction="IN" columnType="commonj.sdo.String"/>

     <Parameter direction="OUT" columnType="commonj.sdo.IntObject"/>

   </Command>

 </Config>

This stored procedure is defined such that the first argument provides a name to match when looking for customers and the second argument is an OUT parameter and providing the number of customers that match the provided name. The proc also returns the list of matching Customers.