In release 4.0, DODS supports stored
procedures. DODS generates a Query class to execute each stored procedure, and
generates a DO class to contain the result set returned (if any).
To describe a stored procedure, create a tag containing the attribute
is-stored-procedure="true"
Stored procedures can have IN parameters, OUT parameters, IN OUT parameters, return
values, and result set columns. Each of these is defined by a tag, with the attribute
sp-param-type="xxx"
where xxx is one of the following: in, out, inout, return, resultset.
Here's an Oracle stored procedure that takes an IN parameter, an OUT parameter, an IN
OUT parameter, and returns a value:
CREATE FUNCTION demo(
inParam IN INTEGER,
outParam OUT INTEGER,
inOutParam IN OUT INTEGER )
RETURN INTEGER
AS
BEGIN
outParam := inParam + 5;
inOutParam := inParam + inOutParam;
RETURN ( inOutParam + 5 );
END;
And here is how it is described in a .doml file:
<class id="myApp.data.demo" is-stored-proc="true">
<field id="retVal" sp-param-index="1" sp-param-type="inout">
<field id="inParam" sp-param-index="2" sp-param-type="in">
<field id="outParam" sp-param-index="3" sp-param-type="out">
<field id="inOutParam" sp-param-index="4" sp-param-type="inout">
</class>
DODS will generate a DemoQuery class with the following methods (presented in
reasonable invocation order):
void setInParam(int)
void setInOutParam(int)
void callStoredProcedure()
int getRetVal()
int getOutParam()
int getInOutParam()
Stored procedures that return ResultSets
Here's a Sybase stored procedure that returns a ResultSet:
CREATE PROCEDURE getNamesAndAges AS
BEGIN
SELECT name, age FROM employee;
END;
And here is how it is described in a .doml file:
<class id="myApp.data.getNamesAndAges" is-stored-proc="true">
<field id="name" sp-param-index="1" sp-param-type="resultset">
<field id="age" sp-param-index="2" sp-param-type="resultset">
</class>
To run the stored procedure and retrieve the results:
GetNamesAndAgesQuery query = new GetNamesAndAgesQuery();
GetNamesAndAgesDO[] namesAndAges = query.getDOArray();
for ( int i = 0; i < namesAndAges.length; i++ )
print( "Name: " + namesAndAges[i].getName() +
", Age: " + namesAndAges[i].getAge() );
|