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: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
