You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by neil <nb...@aisoftware.com.au> on 2002/08/07 03:41:17 UTC
esql patch for paging and ResultSets from Oracle stored procs
Here are some minor mods to esql for cocoon-2.0.3
EsqlQuery.java
--------------
Fix paging:
With EsqlQuery.position initialised to -1:
I asked for 3 rows starting at 0 and got 4 rows starting at 0
I asked for 3 rows starting at i > 0 and got 3 rows starting at i + 1
with position initialised to 0 it works as expected.
I've also added calls to preparedStatement.setMaxRows() (assuming that since
its in the JDBC API it might do
some good - I haven't attempted to test for a performance difference). If
all JDBC drivers do this properly
the esql code to quit after maxRows could be removed.
esql.xsl
--------
Added empty template for esql:allow-multiple-results to avoid this element
being copied to the result XML.
Changed get-Sql-Type so that an unrecognized type is copied verbatim into
the java code without having "Types."
prepended and without being uppercased. This allows:
<esql:call>{ call foo( <esql:parameter direction="out"
type="oracle.jdbc.driver.OracleTypes.CURSOR"></esql:parameter> ) }</esql:cal
l>
<esql:call-results>
<esql:use-results>
<esql:result><esql:get-object column="1"
from-call="true"/></esql:result>
<esql:results>
<esql:row-results>
to process a ResultSet returned as an OUT parameter by an Oracle stored
procedure (I think cocoon-2.0.2 behaved like this).
Changed esql:is-null to allow a column number or name to be specified in the
column attribute (consistent with
the esql:get-* elements).
Note:
I got "java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Error setting up static cursor cache."
from MS SQL Server using LIMIT_METHOD_JDBC (thrown by ResultSet.absolute()).
Maybe MS SQL Server needs ";selectMethod=cursor" appended to the dburl to
support ResultSet.absolute().
But this has a bad effect on performance so I didn't try it for this (I do
use it to support multiple calls
to stored procedures in a transaction). Instead I removed
<esql:use-limit-clause>jdbc</esql:use-limit-clause>
from my xsp so that limitMethod keeps its initial value of
EsqlConnection.LIMIT_METHOD_NOLIMIT and multiple
calls to ResultSet.next() are used to skip rows. This seems to work quickly
even with 1000's of rows.
Index: EsqlQuery.java
===================================================================
RCS file:
/home/cvspublic/xml-cocoon2/src/java/org/apache/cocoon/components/language/m
arkup/xsp/EsqlQuery.java,v
retrieving revision 1.11.2.6
diff -u -b -r1.11.2.6 EsqlQuery.java
--- EsqlQuery.java 28 Jun 2002 08:16:42 -0000 1.11.2.6
+++ EsqlQuery.java 7 Aug 2002 00:38:40 -0000
@@ -66,7 +66,7 @@
*
* based on the orginal esql.xsl
* @author <a href="mailto:tcurdt@dff.st">Torsten Curdt</a>
- * @version CVS $Id: EsqlQuery.java,v 1.11.2.6 2002/06/28 08:16:42 haul Exp
$
+ * @version CVS $Id: EsqlQuery.java,v 1.4 2002/07/29 05:15:02 neil Exp $
*/
public class EsqlQuery {
@@ -82,7 +82,7 @@
private int resultCount = 0;
private boolean hasResultSet = false;
private boolean resultSetValid = false;
- private int position = -1;
+ private int position = 0;
private int maxRows = -1;
private int skipRows = 0;
private boolean keepgoing = true;
@@ -183,12 +183,32 @@
public PreparedStatement prepareStatement() throws SQLException {
switch(limitMethod) {
+ case EsqlConnection.LIMIT_METHOD_POSTGRESQL:
+ case EsqlConnection.LIMIT_METHOD_MYSQL:
+ preparedStatement = connection.prepareStatement(
getQueryString() );
+ break;
case EsqlConnection.LIMIT_METHOD_JDBC:
+ // Produce scrollable ResultSet and skip rows with
ResultSet.absolute(skipRows).
+ // With SQL Server, statement.getResultSet() throws
+ // java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
setting up static cursor cache.
+ // Same error with TYPE_SCROLL_SENSITIVE.
preparedStatement = connection.prepareStatement( getQueryString(),
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ if (maxRows > -1) {
+ // if all JDBC driver's honoured this the code to quit after maxRows
could be removed
+ preparedStatement.setMaxRows(skipRows + maxRows);
+ }
break;
+ case EsqlConnection.LIMIT_METHOD_NOLIMIT:
default:
+ // maxRows can be set without the limit method being set - it defaults to
LIMIT_METHOD_NOLIMIT
+ // which is not such a good name as its really another way of limiting
using JDBC.
+ // Produce non-scrollable ResultSet and skip rows with multiple
ResultSet.next().
preparedStatement = connection.prepareStatement(
getQueryString() );
- };
+ if (maxRows > -1) {
+ preparedStatement.setMaxRows(skipRows + maxRows);
+ }
+ break;
+ }
statement = preparedStatement;
return(preparedStatement);
}
@@ -196,11 +216,22 @@
public CallableStatement prepareCall() throws SQLException {
switch(limitMethod) {
+ case EsqlConnection.LIMIT_METHOD_POSTGRESQL:
+ case EsqlConnection.LIMIT_METHOD_MYSQL:
+ preparedStatement = connection.prepareCall( getQueryString() );
+ break;
case EsqlConnection.LIMIT_METHOD_JDBC:
preparedStatement = connection.prepareCall( getQueryString(),
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ if (maxRows > -1) {
+ preparedStatement.setMaxRows(skipRows + maxRows);
+ }
break;
+ case EsqlConnection.LIMIT_METHOD_NOLIMIT:
default:
preparedStatement = connection.prepareCall( getQueryString() );
+ if (maxRows > -1) {
+ preparedStatement.setMaxRows(skipRows + maxRows);
+ }
};
statement = preparedStatement;
return((CallableStatement)preparedStatement);
Index: java/esql.xsl
===================================================================
RCS file:
/home/cvspublic/xml-cocoon2/src/java/org/apache/cocoon/components/language/m
arkup/xsp/java/esql.xsl,v
retrieving revision 1.13.2.7
diff -u -b -r1.13.2.7 esql.xsl
--- java/esql.xsl 2 Jul 2002 13:39:22 -0000 1.13.2.7
+++ java/esql.xsl 7 Aug 2002 00:38:41 -0000
@@ -1,6 +1,5 @@
<?xml version="1.0"?>
-
-<!-- $Id: esql.xsl,v 1.13.2.7 2002/07/02 13:39:22 haul Exp $-->
+<!-- $Id: esql.xsl,v 1.12 2002/07/19 03:39:41 neil Exp $-->
<!--
============================================================================
@@ -56,7 +55,7 @@
* ESQL Logicsheet
*
* @author ?
- * @version CVS $Revision: 1.13.2.7 $ $Date: 2002/07/02 13:39:22 $
+ * @version CVS $Revision: 1.12 $ $Date: 2002/07/19 03:39:41 $
-->
<xsl:stylesheet version="1.0"
@@ -362,6 +361,7 @@
<xsl:template match="esql:connection/esql:username"/>
<xsl:template match="esql:connection/esql:password"/>
<xsl:template match="esql:connection/esql:pool"/>
+<xsl:template match="esql:connection/esql:allow-multiple-results"/>
<xsl:template match="esql:connection/esql:autocommit"/>
<xsl:template match="esql:connection/esql:use-limit-clause"/>
<xsl:template match="esql:connection/esql:property"/>
@@ -386,7 +386,7 @@
<xsl:template name="set-call-parameter">
<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>);
+ registerOutParameter(<xsl:value-of select="position()"/>,
<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' or @direction='in'">
<xsl:text>_esql_query.getCallableStatement().</xsl:text>
@@ -960,7 +960,7 @@
<xspdoc:desc>allows null-column testing. Evaluates to a Java expression,
which is true when the referred column contains a null-value for the current
resultset row</xspdoc:desc>
<xsl:template
match="esql:row-results//esql:is-null|esql:call-results//esql:is-null">
- <xsp:expr>((<xsl:call-template
name="get-resultset"/>.getObject("<xsl:value-of select="@column"/>") ==
null) || <xsl:call-template name="get-resultset"/>.wasNull())</xsp:expr>
+ <xsp:expr>((<xsl:call-template
name="get-resultset"/>.getObject(<xsl:call-template name="get-column"/>) ==
null) || <xsl:call-template name="get-resultset"/>.wasNull())</xsp:expr>
</xsl:template>
<xsl:template match="esql:result"/>
@@ -1080,19 +1080,19 @@
<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','ABCDEFGHIJKLMNOPQRSTUV
WXYZ')"/></xsl:otherwise>
+ <xsl:when test="$type='Byte'">Types.TINYINT</xsl:when>
+ <xsl:when test="$type='Short'">Types.SMALLINT</xsl:when>
+ <xsl:when test="$type='Int'">Types.INTEGER</xsl:when>
+ <xsl:when test="$type='Long'">Types.BIGINT</xsl:when>
+ <xsl:when test="$type='Float'">Types.REAL</xsl:when>
+ <xsl:when test="$type='BigDecimal'">Types.DECIMAL</xsl:when>
+ <xsl:when test="$type='Boolean'">Types.BIT</xsl:when>
+ <xsl:when test="$type='String'">Types.VARCHAR</xsl:when>
+ <xsl:when test="$type='Bytes'">Types.BINARY</xsl:when>
+ <xsl:when test="$type='AsciiStream'">Types.LONGVARCHAR</xsl:when>
+ <xsl:when test="$type='UnicodeStream'">Types.LONGVARCHAR</xsl:when>
+ <xsl:when test="$type='BinaryStream'">Types.VARBINARY</xsl:when>
+ <xsl:otherwise><xsl:value-of select="$type"/></xsl:otherwise>
</xsl:choose>
</xsl:template>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: esql patch for paging and ResultSets from Oracle stored procs
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 09.Aug.2002 -- 09:31 AM, neil wrote:
> Thanks for applying this stuff (or alternatives) so quickly.
>
> OK so just decrease my row numbers by one then the same description applies:
> With EsqlQuery.position initialised to -1:
> I asked for 3 rows starting at 1 and got 4 rows starting at 1
> I asked for 3 rows starting at i > 1 and got 3 rows starting at i + 1
> with position initialised to 0 it works as expected.
> This was without any <esql:use-limit-clause>.
>
> The "else" that you mention - does that have something to do with this problem?
Yes, the row index is initialized as -1 to be able to tell if we are before the
first row. When the first row is accessed, this should be initialized. This was
missing.
Chris.
PS: Haven't managed to update CVS yesterday, will do it today for 2.1 and afterwards
for 2.0.4.
--
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
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
RE: esql patch for paging and ResultSets from Oracle stored procs
Posted by neil <nb...@aisoftware.com.au>.
Thanks for applying this stuff (or alternatives) so quickly.
>> EsqlQuery.java
>>
>> Fix paging:
>
>Thanks, an "else" was missing.
>
>> With EsqlQuery.position initialised to -1:
>> I asked for 3 rows starting at 0 and got 4 rows starting at 0
>> I asked for 3 rows starting at i > 0 and got 3 rows starting at i + 1
>> with position initialised to 0 it works as expected.
>
>I'm not with you on this one -- according to JDBC rows are numbered
>from 1.
OK so just decrease my row numbers by one then the same description applies:
With EsqlQuery.position initialised to -1:
I asked for 3 rows starting at 1 and got 4 rows starting at 1
I asked for 3 rows starting at i > 1 and got 3 rows starting at i + 1
with position initialised to 0 it works as expected.
This was without any <esql:use-limit-clause>.
The "else" that you mention - does that have something to do with this problem?
Cheers,
Neil.
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
RE: esql patch for paging and ResultSets from Oracle stored procs
Posted by neil <nb...@aisoftware.com.au>.
I just tried the latest from the cocoon_2_0_3_branch and got...
org.apache.cocoon.components.language.LanguageException: Error compiling control_xsp:
Line 448, column 45: unreported exception: java.lang.IllegalAccessException; must be caught or declared to be thrown
Line 448 in control_xsp.java is:
registerOutParameter(1, EsqlQuery.getType("oracle.jdbc.driver.OracleTypes.CURSOR"));
EsqlQuery.getType(String typeName) throws ClassNotFoundException, NoSuchFieldException, IllegalAccessException
These need to be declared as thrown in the generated code (or caught, logged, and something else thrown).
Also, I don't think paging works within esql:call-results/esql:use-results e.g.:
<esql:call-results>
<esql:use-results>
<esql:result><esql:get-object column="8" from-call="true"/></esql:result>
<esql:skip-rows><xsp:expr>sessionData.getRow() - 1</xsp:expr></esql:skip-rows>
<esql:max-rows><xsp:expr>sessionData.getRowsPerPage()</xsp:expr></esql:max-rows>
I'll look into this one next week.
Regards,
Neil.
-----Original Message-----
From: Christian Haul [mailto:haul@dvs1.informatik.tu-darmstadt.de]
Sent: Thursday, 8 August 2002 6:51 PM
To: cocoon-users@xml.apache.org; nbacon@aisoftware.com.au
Subject: Re: esql patch for paging and ResultSets from Oracle stored
procs
[snip]
> Changed get-Sql-Type so that an unrecognized type is copied verbatim into
> the java code without having "Types."
Not applied -- I believe it's better to follow the SQLTransformer here
that uses Class.forName(). Reason: reduce XSP dependency from
DBMS. Drawback: Since it's runtime it's slightly slower, OTOH an
intelligent compiler could optimize it away. Hence, different patch
applied.
[snip]
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: esql patch for paging and ResultSets from Oracle stored procs
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 07.Aug.2002 -- 11:41 AM, neil wrote:
> Here are some minor mods to esql for cocoon-2.0.3
Thanks a lot for your patches! This great!
> EsqlQuery.java
>
> Fix paging:
Thanks, an "else" was missing.
> With EsqlQuery.position initialised to -1:
> I asked for 3 rows starting at 0 and got 4 rows starting at 0
> I asked for 3 rows starting at i > 0 and got 3 rows starting at i + 1
> with position initialised to 0 it works as expected.
I'm not with you on this one -- according to JDBC rows are numbered
from 1.
> I've also added calls to preparedStatement.setMaxRows() (assuming that since
> its in the JDBC API it might do
> some good - I haven't attempted to test for a performance difference). If
> all JDBC drivers do this properly
> the esql code to quit after maxRows could be removed.
Applied.
> Added empty template for esql:allow-multiple-results to avoid this element
> being copied to the result XML.
Applied.
> Changed get-Sql-Type so that an unrecognized type is copied verbatim into
> the java code without having "Types."
Not applied -- I believe it's better to follow the SQLTransformer here
that uses Class.forName(). Reason: reduce XSP dependency from
DBMS. Drawback: Since it's runtime it's slightly slower, OTOH an
intelligent compiler could optimize it away. Hence, different patch
applied.
> Changed esql:is-null to allow a column number or name to be specified in the
> column attribute (consistent with
> the esql:get-* elements).
Applied.
Thanks again for your patches.
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
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>