Re: http://marc.theaimsgroup.com/?l=xml-cocoon-users&m=102165197728849&w=2
> note that you are always calling getUpdateCount on the same statement.
will
> it's state be changed?!? That would be indeed a ugly behaviour...
The code I included in the original post is abridged working code that
correctly handles result sets and update counts in any order, as described
in the JDK javadocs. In the case that several update counts are returned
before the first result set then you simply go through the "else" part of
the loop several times before the "if" part.
Yes - cs.getMoreResults() changes the state of cs.
> I believe that we actually should expect
>
> (resultset, update count)
No. The JDK javadoc is quite clear that each result is either:
- a result set (true returned from execute() or getMoreResults()) or
- an update count (false returned)
and not both of these and the code provided shows that it works as
documented.
The loop test cannot be:
> } while(_esql_query.getMoreResults());
because this would stop the loop whenever the next result is an update count
(without processing this update count and any following results). Please
read the javadoc carefully any try experimenting with a simple standalone
test programme.
Please also note from the JDK javadocs: "For maximum portability, a call's
ResultSet objects and update counts should be processed prior to getting the
values of output parameters."
This was the wrong way around in esql 1.22.
Its a nice idea to add an index attribute or some such to allow a different
<esql:row-results> or <esql:no-results> for each returned result set. But
there should still be a way to use the same <esql:row-results> or
<esql:no-results> (maybe a default to use if the index isn't matched).
Here's my patch to esql.xsl v1.22:
- handles any number of result sets and update counts returned in any order
- processes returned result sets and update counts prior to getting the
values of output parameters
but it doesn't add an index attribute to allow different <esql:row-results>
or <esql:no-results> for each returned result set.
Its only been tested with MS SQLServer jdbc driver and stored procs
returning one result set (sometimes as the first result and other times
after multiple update counts) and no OUT parameters, but it is hopefully
more general that this.
--- org/apache/cocoon/components/language/markup/xsp/java/esql.xsl.1.22 Fri
May 17 00:41:14 2002
+++ org/apache/cocoon/components/language/markup/xsp/java/esql.xsl Fri May
17 07:12:56 2002
@@ -1,6 +1,16 @@
<?xml version="1.0"?>
-<!-- $Id: esql.xsl,v 1.22 2002/05/16 16:10:07 haul Exp $-->
+<!-- Modified by Pinnacle Software to handle multiple values returned
+ from stored procedures. The values returned can be any sequence of
+ ResultSets and update counts.
+ Pinnacle Software version:
+ $Id: esql.xsl,v 1.5 2002/05/17 07:12:56 neil Exp $
+ Modified copy of Apache's version:
+ Id: esql.xsl,v 1.22 2002/05/16 16:10:07 haul Exp
+ Works with Apache's:
+ EsqlQuery.java,v 1.15
+ EsqlHelper.java,v 1.9
+-->
<!--
============================================================================
@@ -56,7 +66,7 @@
* ESQL Logicsheet
*
* @author ?
- * @version CVS $Revision: 1.22 $ $Date: 2002/05/16 16:10:07 $
+ * @version CVS $Revision: 1.5 $ $Date: 2002/05/17 07:12:56 $
-->
<xsl:stylesheet version="1.0"
@@ -428,7 +438,7 @@
</xsl:for-each>
<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:when
test="esql:call[@resultset-from-object]">_esql_query.execute(<xsl:value-of
select="esql:call/@resultset-from-object"/>);</xsl:when>
<xsl:otherwise>_esql_query.execute();</xsl:otherwise>
</xsl:choose>
</xsl:when>
@@ -445,16 +455,12 @@
</xsl:otherwise>
</xsl:choose>
getLogger().debug("esql query: " + _esql_query.getQueryString());
- <xsl:if test="esql:call">
- // call results
- <xsp:content>
- <xsl:apply-templates select="esql:call-results"/>
- </xsp:content>
- </xsl:if>
- if (_esql_query.hasResultSet()) {
- do {
+ // start handling multiple returned update counts and ResultSets ...
+ int countReturnedResults = 0;
+ for ( boolean nextResultIsResultSet = _esql_query.hasResultSet();
true; nextResultIsResultSet = _esql_query.getMoreResults() ) {
+ if (nextResultIsResultSet) {
+ ++countReturnedResults;
_esql_query.getResultRows();
-
if (_esql_query.nextRow()) {
<xsl:apply-templates select="esql:results"/>
}
@@ -462,16 +468,29 @@
<xsl:apply-templates select="esql:no-results"/>
}
_esql_query.getResultSet().close();
-
- } while(_esql_query.getMoreResults());
} else {
- if (_esql_query.getStatement().getUpdateCount() >= 0) {
- <xsl:apply-templates select="esql:update-results/*"/>
+ // either the next result is an update count or there are no more
results
+ int updateCount = _esql_query.getStatement().getUpdateCount();
+ if ( updateCount == -1 ) {
+ break; // no more results
}
- else{
- <xsl:apply-templates select="esql:no-results"/>
+ ++countReturnedResults;
+ <xsl:apply-templates select="esql:update-results/*"/>
}
}
+ <xsl:if test="esql:call">
+ // call results
+ <!--
+ You can fetch OUT parameters in xsp like this:
+ <esql:call-results>
+ <esql:get-int column="3" from-call="true"/>
+ The JDBC apidocs say you should do this after
+ processing any returned values.
+ -->
+ <xsp:content>
+ <xsl:apply-templates select="esql:call-results"/>
+ </xsp:content>
+ </xsl:if>
_esql_query.getStatement().close();
} catch (SQLException _esql_exception_<xsl:value-of
select="generate-id(.)"/>) {
<xsl:choose>
PRIVILEGED - PRIVATE AND CONFIDENTIAL
This email and any files transmitted with it are intended solely for the use
of the addressee(s) and may contain information which is confidential or
privileged. If you receive this email and you are not the addressee (or
responsible for delivery of the email to the addressee), please disregard
the contents of the email, delete the email and notify the author
immediately.
Before opening or using any attachments, please scan them for viruses and
defects. We do not accept any liability for loss or damage, which may arise
from your receipt of this e-mail. Our liability is limited to re-supplying
any affected attachments.
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
