Mailing-List: contact cocoon-dev-help@xml.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cocoon-dev@xml.apache.org
Date: Wed, 30 Jan 2002 11:10:15 +0100
From: Christian Haul <haul@dvs1.informatik.tu-darmstadt.de>
To: "Durrant, Peter" <Peter.Durrant@COGNOS.com>, Kosh.Podder@ubsw.com,
        "NUGENT ROBERT (APP3RJN)" <APP3RJN@ups.com>
Cc: cocoon-dev@xml.apache.org
Subject: esql stored procedure support
Message-ID: <20020130111014.A1849@bremen.dvs1.informatik.tu-darmstadt.de>
Reply-To: haul@dvs1.informatik.tu-darmstadt.de
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="XMCwj5IQnwKtuyBG"
Content-Disposition: inline
User-Agent: Mutt/1.3.22.1i
Organization: Databases and Distributed Systems Group,
 Darmstadt University of Technology

--XMCwj5IQnwKtuyBG
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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
haul@informatik.tu-darmstadt.de
    fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

--XMCwj5IQnwKtuyBG
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="esql.patch"

? 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>
> 


--XMCwj5IQnwKtuyBG
Content-Type: text/plain; charset=us-ascii

---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
--XMCwj5IQnwKtuyBG--