cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "neil" <nba...@aisoftware.com.au>
Subject esql v1.22 multiple returned update counts and ResultSets
Date Fri, 17 May 2002 01:48:42 GMT
Hi,
it seems that a few cocoon users like me, are hacking about with esql to get
it to do what we need. I'm working on the change shown below and am happy to
share it if anyone wants it.

JDBC can handle a sequence of values returned from a stored procedure, where
the values can be update counts and ResultSets in any order. It looks to me
like esql v1.22 will only handle a sequence with all the ResultSets before
the first update count and at most one update count. e.g.
ResultSet*updateCount? (where * and ? are as in a RE).

MS SqlServer stored procs return an update count for every
insert/update/delete performed and a ResultSet for every query (whose
results aren't consumed in the procedure) in the order that they are
performed. So its important to handle update counts and ResultSets in any
order.

Instead of esql.xsl code like:

      if (_esql_query.hasResultSet()) {
        do {
          // handle ResultSet
        } while(_esql_query.getMoreResults());
      } else {
        if (_esql_query.getStatement().getUpdateCount() &gt;= 0) {
          <xsl:apply-templates select="esql:update-results/*"/>
        }
        else{
          <xsl:apply-templates select="esql:no-results"/>
        }
      }

we need something that produces java code that looks like this:

String query = "{ ? = foo() }";
java.sql.CallableStatement cs = conn.prepareCall(
    query,
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY );

// The JDK javadocs say "If used, the result parameter must be
// registered as an OUT parameter".
// It doesn't matter what the Type is.
// esql will do this if instead of giving it "{ ? ="
// we give it "<esql:parameter direction="out" type="Int"/> ="
cs.registerOutParameter(1, java.sql.Types.INTEGER);

int resultCount = 0;
for ( boolean nextResultIsResultSet = cs.execute(); true;
nextResultIsResultSet = cs.getMoreResults() ) {
    if (nextResultIsResultSet) {
	java.sql.ResultSet rs = cs.getResultSet();
	++resultCount;
	// handle ResultSet
	rs.close();
    } else {
	// either the next result is an update count or there are no more results
	int updateCount = cs.getUpdateCount();
	if ( updateCount == -1 ) {
	    break; // no more results
	    // only finished when cs.getMoreResults() == false &&
cs.getUpdateCount() == -1
	    // awful bit of API but it works
	}
	++resultCount;
	// handle updateCount
    }
    // there may still be more ResultSets and/or update counts
}
if (resultCount == 0) {
    // no returned results
}

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: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>


Mime
View raw message