You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by "Durrant, Peter" <Pe...@COGNOS.com> on 2002/01/09 16:18:10 UTC
ESQL Extension
Hi
I've made some changes to ESQL to handle calls to stored procedures. At the
moment it is limited to calling stored procedures that generate
java.sql.resultSet output - i.e. SELECT. More work needs to be done to
handle stored procedures that return other types of data. However, might
this be beyond the scope of what was originally envisaged for ESQL?
The reason I've made this change is because Oracle wasn't returning a
resultSet as part of a normal query call to a stored procedure.
If this code is useful to extend ESQL then I'll happily submit it. So
(1) are other people interested in the functionality?
(2) who do I need to talk to about syntax etc. of the ESQL stored
procedure functionality?
(3) how do I go about submitting my changes? I guess someone should
look at the changes I've made...
This is my first contribution to open source and I'm not sure if there's an
ethical code submission process. :-)
The (brief) details of the changes are:
(1) addition of CallableStatement to the EsqlQuery class and
corresponding import
(2) an <esql:stored-proc> added to the spec -- this was added to
distinguish between prepared statements and stored procedure calls. This
could perhaps be improved - comments would be welcome on this. Maybe add
this as an attribute of <esql:query>?
(3) a direction attribute was added for stored procedure parameters
-- it describes whether data should be input or output.
(4) addition of code to handle stored procedure statement
construction
(5) if the query was of an "Oracle"-type, then the getting of the
resultSet was changed to a statement.getObject call. This assumes that the
output type is derived from resultSet.
I've now implemented a call to stored procedures to look like:
- (the function is called get_titles and returns an Oracle cursor and takes
an integer parameter)
- (you can see the "hack" of the <esql:stored-proc/> tag. Could this be
made an attribute of <esql:query>? At the moment it is used to distinguish
between a prepared statement and stored procedure call)
<xsl:template match="myns:get_titles">
<esql:connection>
<esql:pool>dbpoolOracle</esql:pool>
<esql:execute-query>
<esql:query>
begin <esql:parameter direction="out"
type="oracle.jdbc.OracleTypes.CURSOR"/>
:= get_titles(<esql:parameter direction="in"
type="int">100</esql:parameter>); end;
</esql:query>
<esql:stored-proc/>
<esql:results>
<esql:row-results>
<row><esql:get-columns/></row>
</esql:row-results>
</esql:results>
</esql:execute-query>
</esql:connection>
</xsl:template>
Well that's it so far. :-)
I look forward to hearing from you!
Cheers
Pete
---
Cognos, London, UK
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
Re: ESQL Extension
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 09.Jan.2002 -- 10:18 AM, Durrant, Peter wrote:
> Hi
>
> I've made some changes to ESQL to handle calls to stored procedures. At the
A more and more common request :-)
> moment it is limited to calling stored procedures that generate
> java.sql.resultSet output - i.e. SELECT. More work needs to be done to
> handle stored procedures that return other types of data. However, might
> this be beyond the scope of what was originally envisaged for ESQL?
Currently, we don't even do CallableStatements. (I have some patches
to that respect but haven tested them, yet. First, I'll need to create
some SPs to test against...)
> The reason I've made this change is because Oracle wasn't returning a
> resultSet as part of a normal query call to a stored procedure.
>
> If this code is useful to extend ESQL then I'll happily submit it. So
> (1) are other people interested in the functionality?
definitely
> (2) who do I need to talk to about syntax etc. of the ESQL stored
> procedure functionality?
this group
> (3) how do I go about submitting my changes? I guess someone should
> look at the changes I've made...
> This is my first contribution to open source and I'm not sure if there's an
> ethical code submission process. :-)
post a diff -u against HEAD (cvs version) to this group
> The (brief) details of the changes are:
> (1) addition of CallableStatement to the EsqlQuery class and
> corresponding import
The problem I see here is that you'd need to cater for SPs that don't
have "out" parameters. So perhaps <call/> instead of <query/> could be
used to distinguish.
> (2) an <esql:stored-proc> added to the spec -- this was added to
> distinguish between prepared statements and stored procedure calls. This
> could perhaps be improved - comments would be welcome on this. Maybe add
> this as an attribute of <esql:query>?
see above. I'd rather not use a tag after spec'ing the query / call to
do the right thing(tm).
> (3) a direction attribute was added for stored procedure parameters
yes.
> (4) addition of code to handle stored procedure statement
> construction
yes.
> (5) if the query was of an "Oracle"-type, then the getting of the
> resultSet was changed to a statement.getObject call. This assumes that the
> output type is derived from resultSet.
need to see this to understand it.
> I've now implemented a call to stored procedures to look like:
> - (the function is called get_titles and returns an Oracle cursor and takes
> an integer parameter)
> - (you can see the "hack" of the <esql:stored-proc/> tag. Could this be
> made an attribute of <esql:query>? At the moment it is used to distinguish
> between a prepared statement and stored procedure call)
see above.
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org