Peter, Kosh,
I've got a new iteration of the stored procedure support for
esql. (Kosh, I will mail you the necessary files separately because
you indicated that you have problems with CVS).
Bob, I've CC'ed you since you might be interested to try the
<esql:results from-column=""/> feature.
Since the next release is due in a couple of days I don't want to
commit these changes right now unless I hear success stories from
you. The DBMS I use on a regulary basis does not support most of
what's in this patch so I rely on you to send bug reports (and
fixes!). Diff is against today's CVS HEAD as usual.
The attributes to <esql:call/> have been renamed to be a bit more
XMLish (needsQuery -> needs-query, resultsetFromObject ->
resultset-from-object)
* use <esql:call/> instead of <esql:query/>, for stored procedures,
use either DBMS specific syntax or JDBC escape syntax "{? = foo(?)}"
* if driver requires to use the "executeQuery()" method instead of
the "execute()" method (like e.g. INFORMIX does), set
'needs-query="true"' attribute to <esql:call/>
* if a result set is returned through the (only) return parameter of
a stored procedure, one can use e.g. 'resultset-from-object="1"' as
attribute to <esql:call/> to automatically use this result set. For
a more general alternative see further below.
* <esql:call-results/> (child of <esql:execute-query/>) may contain
code that will always be executed whether the call returned a
resultset or not.
* all <esql:get-xxx/> tags accept new attribute 'from-call="yes"' to
indicate that the value is retrieved from the CallableStatement
rather than the current ResultSet.
* Retrieve a ResultSet from any column and use it like the result of a
nested query by e.g. <esql:results from-column="1"/>. Of course the
column needs to return a result that is castable to ResultSet.
Example:
<esql:call>{? =
foo(<esql:parameter><xsp:expr>1</xsp:expr></esql:parameter>)}</esql:call>
<esql:call-results>
<esql:results from-column="1" from-call="true">
<esql:row-results>
<esql:get-string column="1"/>
</esql:row-results>
</esql:results>
</esql:call-results>
Disclaimer:
I have only tested a fraction of these modifications as the DBMS I use
does not support the other variants. I have, however, tried to ensure
that the resulting code compiles well. Please try this code and report
failures (preferably with fixes :-) and successes to me and cocoon-dev
Chris.
--
C h r i s t i a n H a u l
[EMAIL PROTECTED]
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
? esql.patch
Index: EsqlQuery.java
===================================================================
RCS file:
/home/cvs/xml-cocoon2/src/java/org/apache/cocoon/components/language/markup/xsp/EsqlQuery.java,v
retrieving revision 1.2
diff -r1.2 EsqlQuery.java
12a13
> import java.sql.CallableStatement;
32a34
> private boolean resultSetValid = false;
47a50,57
> public EsqlQuery( ResultSet aResultSet ) {
> this.connection = null;
> this.statement = null;
> this.query = null;
> this.resultSetValid = true;
> this.hasResultSet = (this.resultSet != null);
> }
>
120a131,143
>
> public CallableStatement prepareCall() throws SQLException {
> switch(limitMethod) {
> case EsqlConnection.LIMIT_METHOD_JDBC:
> preparedStatement = connection.prepareCall( getQueryString(),
>ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
> break;
> default:
> preparedStatement = connection.prepareCall( getQueryString() );
> };
> statement = preparedStatement;
> return((CallableStatement)preparedStatement);
> }
>
124a148,151
> public CallableStatement getCallableStatement() {
> return((CallableStatement) preparedStatement);
> }
>
174,180c201,239
< if (preparedStatement != null) {
< hasResultSet = preparedStatement.execute();
< }
< else {
< hasResultSet = statement.execute( getQueryString() );
< }
< return(hasResultSet);
---
> return this.execute(false);
> }
>
> /**
> * some brain dead DBMSs (Oracle) return their result sets for
> * callable statements through the some (i.e. the first) returned
> * object.
> */
> public boolean execute( int resultSetFromObject ) throws SQLException {
> hasResultSet = this.execute(false);
> resultSet = (ResultSet) ((CallableStatement) preparedStatement).getObject(1);
> resultSetValid = true;
> return hasResultSet;
> }
>
> /**
> * some other brain dead DBMSs (Informix) don't like their callable
> * statements to be 'execute'd but require 'executeQuery' instead.
> */
> public boolean execute( boolean needsQuery ) throws SQLException {
> if ( needsQuery ) {
> if (preparedStatement != null) {
> resultSet = preparedStatement.executeQuery();
> }
> else {
> resultSet = statement.executeQuery( getQueryString() );
> }
> hasResultSet = (resultSet != null);
> resultSetValid = true;
> } else {
> if (preparedStatement != null) {
> hasResultSet = preparedStatement.execute();
> }
> else {
> hasResultSet = statement.execute( getQueryString() );
> }
> resultSetValid = false;
> }
> return(hasResultSet);
212,213c271,275
< resultSet = statement.getResultSet();
< resultSetMetaData = resultSet.getMetaData();
---
> if ( resultSetValid ) {
> resultSetMetaData = resultSet.getMetaData();
> } else {
> resultSet = statement.getResultSet();
> }
Index: java/esql.xsl
===================================================================
RCS file:
/home/cvs/xml-cocoon2/src/java/org/apache/cocoon/components/language/markup/xsp/java/esql.xsl,v
retrieving revision 1.4
diff -r1.4 esql.xsl
207a208
> <xsp:include>java.sql.CallableStatement</xsp:include>
440c441,443
< <xsl:variable name="query"><xsl:call-template
name="get-nested-string"><xsl:with-param name="content"
select="esql:query"/></xsl:call-template></xsl:variable>
---
>
> <xsl:variable name="query"><xsl:choose><xsl:when
>test="esql:query"><xsl:call-template name="get-nested-string"><xsl:with-param
>name="content" select="esql:query"/></xsl:call-template></xsl:when><xsl:when
>test="esql:call"><xsl:call-template name="get-nested-string"><xsl:with-param
>name="content"
>select="esql:call"/></xsl:call-template></xsl:when></xsl:choose></xsl:variable>
>
462a466,505
> <xsl:when test="esql:call">
> try {
> _esql_query.prepareCall();
> } catch (SQLException _esql_exception_<xsl:value-of
>select="generate-id(.)"/>) {
> throw new RuntimeException("Error preparing statement: " +
>_esql_query.getQueryString() + ": "+_esql_exception_<xsl:value-of
>select="generate-id(.)"/>.getMessage());
> }
> <xsl:for-each select="esql:call//esql:parameter">
> try {
> <xsl:if test="@direction='out' or @direction='inout'">
> <xsl:text>_esql_query.getCallableStatement().</xsl:text>
> registerOutParameter(<xsl:value-of select="position()"/>,
>Types.<xsl:call-template name="get-Sql-Type"><xsl:with-param
>name="type"><xsl:value-of
>select="@type"/></xsl:with-param></xsl:call-template><xsl:if test="@typename">,
><xsl:value-of select="@typename"/> </xsl:if>);
> </xsl:if>
> <xsl:if test="not(@direction) or @direction='inout'">
> <xsl:text>_esql_query.getCallableStatement().</xsl:text>
> <xsl:choose>
> <xsl:when test="@type">
> <xsl:variable name="type"><xsl:value-of
>select="concat(translate(substring(@type,1,1),'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),substring(@type,2))"/></xsl:variable>
> <xsl:text>set</xsl:text><xsl:value-of
>select="$type"/>(<xsl:value-of select="position()"/>,<xsl:call-template
>name="get-nested-content"><xsl:with-param name="content"
>select="."/></xsl:call-template>);<xsl:text>
> </xsl:text>
> </xsl:when>
> <xsl:otherwise>
> <xsl:text>setString(</xsl:text><xsl:value-of
>select="position()"/>,String.valueOf(<xsl:call-template
>name="get-nested-string"><xsl:with-param name="content"
>select="."/></xsl:call-template>));<xsl:text>
> </xsl:text>
> </xsl:otherwise>
> </xsl:choose>
> </xsl:if>
> } catch (SQLException _esql_exception_<xsl:value-of
>select="generate-id(.)"/>) {
> throw new RuntimeException("Error setting parameter on statement: " +
>_esql_query.getQueryString() + ": "+_esql_exception_<xsl:value-of
>select="generate-id(.)"/>);
> }
> </xsl:for-each>
> try {
> <xsl:choose>
> <xsl:when test="esql:call[@needs-query='true' or
>@needs-query='yes']">_esql_query.execute(true);</xsl:when>
> <xsl:when
>test="esql:call[@resultset-from-object]">_esql_query.execute(<xsl:copy-of
>select="esql:call[@resultset-from-object]"/>);</xsl:when>
> <xsl:otherwise>_esql_query.execute();</xsl:otherwise>
> </xsl:choose>
> } catch (SQLException _esql_exception_<xsl:value-of
>select="generate-id(.)"/>) {
> throw new RuntimeException("Error executing prepared statement: " +
>_esql_query.getQueryString() + ": "+_esql_exception_<xsl:value-of
>select="generate-id(.)"/>);
> }
> </xsl:when>
509a553,558
> <xsl:if test="esql:call">
> // call results
> <xsp:content>
> <xsl:apply-templates select="esql:call-results"/>
> </xsp:content>
> </xsl:if>
561a611
> <xsl:template match="esql:call//esql:parameter">"?"</xsl:template>
572a623,631
> <xsl:template match="esql:execute-query//esql:call-results">
> <xsp:logic>
> // call results2
> </xsp:logic>
> <xsp:content>
> <xsl:apply-templates/>
> </xsp:content>
> </xsl:template>
>
784c843
< <xsl:template match="esql:row-results//esql:get-string" name="get-string">
---
> <xsl:template
>match="esql:row-results//esql:get-string|esql:call-results//esql:get-string"
>name="get-string">
794c853
< <xsl:template match="esql:row-results//esql:get-date">
---
> <xsl:template
>match="esql:row-results//esql:get-date|esql:call-results//esql:get-date">
806c865
< <xsl:template match="esql:row-results//esql:get-time">
---
> <xsl:template
>match="esql:row-results//esql:get-time|esql:call-results//esql:get-time">
818c877
< <xsl:template match="esql:row-results//esql:get-timestamp">
---
> <xsl:template
>match="esql:row-results//esql:get-timestamp|esql:call-results//esql:get-timestamp">
830c889
< <xsl:template match="esql:row-results//esql:get-boolean">
---
> <xsl:template
>match="esql:row-results//esql:get-boolean|esql:call-results//esql:get-boolean">
835c894
< <xsl:template match="esql:row-results//esql:get-double">
---
> <xsl:template
>match="esql:row-results//esql:get-double|esql:call-results//esql:get-double">
847c906
< <xsl:template match="esql:row-results//esql:get-float">
---
> <xsl:template
>match="esql:row-results//esql:get-float|esql:call-results//esql:get-float">
864c923
< <xsl:template match="esql:row-results//esql:get-object">
---
> <xsl:template
>match="esql:row-results//esql:get-object|esql:call-results//esql:get-object">
869c928
< <xsl:template match="esql:row-results//esql:get-array">
---
> <xsl:template
>match="esql:row-results//esql:get-array|esql:call-results//esql:get-array">
874c933
< <xsl:template match="esql:row-results//esql:get-struct">
---
> <xsl:template
>match="esql:row-results//esql:get-struct|esql:call-results//esql:get-struct">
879c938
< <xsl:template match="esql:row-results//esql:get-int">
---
> <xsl:template match="esql:row-results//esql:get-int|esql:call-results//esql:get-int">
884c943
< <xsl:template match="esql:row-results//esql:get-long">
---
> <xsl:template
>match="esql:row-results//esql:get-long|esql:call-results//esql:get-long">
889c948
< <xsl:template match="esql:row-results//esql:get-short">
---
> <xsl:template
>match="esql:row-results//esql:get-short|esql:call-results//esql:get-short">
894c953
< <xsl:template match="esql:row-results//esql:get-ascii">
---
> <xsl:template
>match="esql:row-results//esql:get-ascii|esql:call-results//esql:get-ascii">
902c961
< <xsl:template match="esql:row-results//esql:get-xml">
---
> <xsl:template match="esql:row-results//esql:get-xml|esql:call-results//esql:get-xml">
975c1034
< <xsl:template
match="esql:row-results//esql:get-row-position|esql:results//esql:get-row-position">
---
> <xsl:template
>match="esql:row-results//esql:get-row-position|esql:results//esql:get-row-position|esql:call-results//esql:get-row-position">
980c1039
< <xsl:template match="esql:row-results//esql:get-column-name">
---
> <xsl:template
>match="esql:row-results//esql:get-column-name|esql:call-results//esql:get-column-name">
985c1044
< <xsl:template match="esql:row-results//esql:get-column-label">
---
> <xsl:template
>match="esql:row-results//esql:get-column-label|esql:call-results//esql:get-column-label">
990c1049
< <xsl:template match="esql:row-results//esql:get-column-type-name">
---
> <xsl:template
>match="esql:row-results//esql:get-column-type-name|esql:call-results//esql:get-column-type-name">
995c1054
< <xsl:template match="esql:row-results//esql:get-column-type">
---
> <xsl:template
>match="esql:row-results//esql:get-column-type|esql:call-results//esql:get-column-type">
1003a1063,1096
> <xspdoc:desc></xspdoc:desc>
> <xsl:template
>match="esql:row-results//esql:results[@from-column]|esql:call-results//esql:results[@from-column]"
> priority="2">
> <xsp:logic>
> // nested result set
> if (_esql_query != null) {
> _esql_queries.push(_esql_query);
> }
> _esql_query = new EsqlQuery((ResultSet) <xsl:call-template
>name="get-resultset"/>.getObject(<xsl:value-of select="@from-column"/>));
> {
> if (_esql_query.hasResultSet()) {
> do {
> _esql_query.getResultRows();
>
> if (_esql_query.nextRow()) {
> <xsl:apply-templates select="esql:row-results"/>
> }
> else {
> <xsl:apply-templates select="esql:no-results"/>
> }
> _esql_query.getResultSet().close();
>
> } while(_esql_query.getMoreResults());
> } else {
> <xsl:apply-templates select="esql:no-results"/>
> }
> }
> if (_esql_queries.empty()) {
> _esql_query = null;
> } else {
> _esql_query = (EsqlQuery)_esql_queries.pop();
> }
> </xsp:logic>
> </xsl:template>
>
1020c1113
< <xsl:call-template name="get-query"/><xsl:text>.getResultSet()</xsl:text>
---
> <xsl:call-template name="get-query"/><xsl:choose><xsl:when test="@from-call='yes'
>or
>@from-call='true'"><xsl:text>.getCallableStatement()</xsl:text></xsl:when><xsl:otherwise><xsl:text>.getResultSet()</xsl:text></xsl:otherwise></xsl:choose>
1089a1183,1203
>
> <xsl:template name="get-Sql-Type">
> <xsl:param name="type"/>
> <xsl:choose>
> <!-- just do the 'unusual' mappings -->
> <xsl:when test="$type='Byte'">TINYINT</xsl:when>
> <xsl:when test="$type='Short'">SMALLINT</xsl:when>
> <xsl:when test="$type='Int'">INTEGER</xsl:when>
> <xsl:when test="$type='Long'">BIGINT</xsl:when>
> <xsl:when test="$type='Float'">REAL</xsl:when>
> <xsl:when test="$type='BigDecimal'">DECIMAL</xsl:when>
> <xsl:when test="$type='Boolean'">BIT</xsl:when>
> <xsl:when test="$type='String'">VARCHAR</xsl:when>
> <xsl:when test="$type='Bytes'">BINARY</xsl:when>
> <xsl:when test="$type='AsciiStream'">LONGVARCHAR</xsl:when>
> <xsl:when test="$type='UnicodeStream'">LONGVARCHAR</xsl:when>
> <xsl:when test="$type='BinaryStream'">VARBINARY</xsl:when>
> <xsl:otherwise><xsl:value-of
>select="translate(@type,'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')"/></xsl:otherwise>
> </xsl:choose>
> </xsl:template>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
