DODS FAQs - Does DODS support Stored Procedures?
dods.gif (11357 bytes)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() );

For all the latest information on DODS, please refer to http://dods.enhydra.org
Questions, comments, feedback? Let us know...