RE: Cocoon ESQL stored procedures

["Durrant, Peter" <Pe...@COGNOS.com>]

Hi Chris

> From: Christian Haul
> [mailto:haul@dvs1.informatik.tu-darmstadt.de]
> Sent: Saturday, December 22, 2001 9:21
>
> I had a rather long car journey yesterday - good for thinking
> about the problem :-) I suggest to add @direction and @typename
> to <esql:parameter/> to mark out / inout parameters (and
> optionally @typename for structured types) and make @type
> mandatory for those parameters. The rest should be transparent
> i.e. no other syntax addition would be needed. Obviously,
> access to out parameters would only be possible via column
> number. I'm currently working on it (almost done).  

I've been looking at the problem of handling stored procedures in esql today
and have developed some code and examples to illustrate my research so far.

So far I've looked at calling stored procedures on MS SQL Server and Oracle.
Both databases require a different approach to getting the ResultSet. In SQL
Server I can call executeQuery to return a ResultSet (in the same way as for
a standard SQL query). In Oracle, however, I have to specify an output type
for the database to bind to -- in my case I'm using
oracle.jdbc.OracleTypes.CURSOR (which is descended from ResultSet). From
what I understand most databases work the MS way, only Oracle seems to be
different!

So to illustrate my solution to handle both types of databases I've attached
my code.

On my XSP page I've called a stored procedure (called get_titles, input
parameter is an int and the output is a resultSet) using a syntax similiar
to what you proposed. For SQL server this looks like:

<cogesql:stored-proc bound="true">
	<cogesql:statement>{call get_titles(?)}</cogesql:statement>
	<cogesql:parameter>
		<cogesql:index>1</cogesql:index>
		<cogesql:direction>in</cogesql:direction>
		<cogesql:type>int</cogesql:type>
		<cogesql:value>104</cogesql:value>
	</cogesql:parameter>
</cogesql:stored-proc>

and for Oracle::

<cogesql:stored-proc bound="false">
	<cogesql:statement>begin ? := get_titles(?);
end;</cogesql:statement>
	<cogesql:parameter>
		<cogesql:index>1</cogesql:index>
		<cogesql:direction>out</cogesql:direction>
		<cogesql:type>oracle.jdbc.OracleTypes.CURSOR</cogesql:type>
	</cogesql:parameter>
	<cogesql:parameter>
		<cogesql:index>2</cogesql:index>
		<cogesql:direction>in</cogesql:direction>
		<cogesql:type>int</cogesql:type>
		<cogesql:value>104</cogesql:value>
	</cogesql:parameter>
</cogesql:stored-proc>

The only significant difference between the two statements is that in Oracle
I've had to specify the output data type. The get_titles function is
identical on both databases acting on the same data and the same tables.

The <cogesql:stored-proc> tag I've used describes whether the output data
needs to be bound (@bound). For Oracle this is true, for MS SQL Server it is
false.

The parameter structure defines:
	(1) the index - this is the index corresponding to the ? parameter
in the stored procedure (see <cogesql:statement> tag).
	(2) the direction - describes whether the parameter is an input or
an output
	(3) the type - data type of parameter
	(4) the value - * for inputs only *

In my logicsheet I have the following xsp:logic where r is a ResultSet and
connection is my database connection:

-- start of code --

java.sql.CallableStatement s = connection.prepareCall("<xsl:value-of
select="cogesql:statement"/>");

// register the parameters
<xsl:for-each select="cogesql:parameter">
	<xsl:if test="cogesql:direction='out'">
		s.registerOutParameter(<xsl:value-of
select="cogesql:index"/>, <xsl:value-of select="cogesql:type"/>);
	</xsl:if>
	<xsl:if test="cogesql:direction='in'">
		s.setInt(<xsl:value-of select="cogesql:index"/>,
<xsl:value-of select="cogesql:value"/>);
	</xsl:if>
</xsl:for-each>

				
<xsl:if test="@bound='true'"> <!-- MS SQL Server and ... -->
	r = s.executeQuery();
</xsl:if>

<xsl:if test="@bound='false'"> <!-- Oracle and ... -->
	s.execute();
	r = (java.sql.ResultSet)s.getObject(1);
</xsl:if>

-- end of code --

The difference here between esql and my code is:
	(1) I've used a CallableStatement instead of Statement
	(2) Well, I've only coded for int parameters! The parameter type
needs to be accounted for
	(3) If @bound is true then the query can be executed in the same way
as in esql using executeQuery
	(4) If @bound is false then the resultSet must be generated using
execute rather than executeQuery and the query results bound to the result
set.
	(5) I'm assuming the 1st parameter is the output -- i.e. the 1 in
s.getObject(1).

I don't know how far you got with looking at stored procedures before
Christmas when we first spoke, but perhaps this is useful for you. Can you
let me know if we can help to make the appropriate changes to esql?

Thanks
Pete
--
Cognos, London, UK



For information, if I was doing this explicitly in Java for the function
get_titles where I want a ResultSet returned I would do the following (for
Oracle):

-- start of code --

String query = "begin ? := get_titles(?); end;";
CallableStatement s = connection.prepareCall(query);

// register the type of the out param - an Oracle specific type
s.registerOutParameter(1, OracleTypes.CURSOR);

// register the in parameter
s.setInt(2, 104);

// execute and retrieve the result set
s.execute();
ResultSet r = (ResultSet)s.getObject(1);

-- end of code --

This message may contain privileged and/or confidential information.  If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate, or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender by
e-mail promptly that you have done so.  Thank You.

---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org