You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Yves Vindevogel <yv...@implements.be> on 2006/05/27 17:47:08 UTC
Re: mysql problem with sql transformer (and esql too)
XSP with ESQL seems to give the same problem
<xsp:page language="java"
xmlns:xsp="http://apache.org/xsp"
xmlns:xsp-request="http://apache.org/xsp/request/2.0"
xmlns:esql="http://apache.org/cocoon/SQL/v2"
xmlns:xsp-session="http://apache.org/xsp/session/2.0"
create-session="true">
<html><body>
<esql:connection>
<esql:pool>pdt10010</esql:pool>
<esql:execute-query>
<esql:query>call spJobsperday(0, 2, ' ', ' ', ' ', ' ',
' ', ' ', ' ', ' ', ' ')</esql:query>
<esql:results>
<table>
<esql:row-results>
<tr>
<td><esql:get-string
column="eventdate"/></td>
<td><esql:get-string column="jobs"/></td>
<td><esql:get-string column="pages"/></td>
<td><esql:get-string
column="firstjob"/></td>
<td><esql:get-string column="lastjob"/></td>
</tr>
</esql:row-results>
</table>
</esql:results>
<esql:no-results>
<p>Sorry, no results!</p>
</esql:no-results>
</esql:execute-query>
</esql:connection>
</body></html>
</xsp:page>
XML Parsing Error: reference to invalid character number
Location: http://127.0.0.1:8888/2.0.0/reports/user/jobsperday.html
Line Number 6, Column 13:
<td>�619-10-18</td>
And without the eventdate (which seems to give the problem)
<html>
−
<body>
−
<table>
−
<tr>
<td>0</td>
<td/>
<td/>
<td>00:00:00</td>
</tr>
−
<tr>
<td>0</td>
<td/>
<td/>
<td>00:00:00</td>
</tr>
</table>
</body>
</html>
Yves Vindevogel wrote:
> Ok, here's some more copy and pasting
>
> This is the command line executed in putty or ssh with the results
> (which are correct)
> (0 means offset 0, 2 means limit 2)
>
> root@ns20822:~# mysql pdt20000 --execute "call spjobsperday(0, 2, '',
> '', '', '', '', '', '', '', '')"
> +------------+------+-------+----------+----------+
> | EventDate | Jobs | Pages | FirstJob | LastJob |
> +------------+------+-------+----------+----------+
> | 2005-03-25 | 2 | 3 | 08:48:59 | 11:33:11 |
> | 2005-04-11 | 1 | 1 | 14:37:06 | 14:37:06 |
> +------------+------+-------+----------+----------+
> root@ns20822:~#
>
>
> Below the XSL page
>
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
> xmlns:sql="http://apache.org/cocoon/SQL/2.0"
> >
>
> <xsl:param name="dir"/>
> <xsl:param name="file"/>
> <xsl:param name="page"/>
>
> <xsl:param name="ipaddress"/>
> <xsl:param name="hostname"/>
> <xsl:param name="eventdate1"/>
> <xsl:param name="eventdate2"/>
> <xsl:param name="documentname"/>
> <xsl:param name="username"/>
> <xsl:param name="printqueue"/>
> <xsl:param name="printport"/>
> <xsl:param name="applicationtype"/>
>
> <xsl:template match="/">
> <report>
> <xsl:copy-of select="session"/>
>
> <xsl:variable name="doc">../reports/<xsl:value-of
> select="$dir"/>/<xsl:value-of select="$file"/>.xml</xsl:variable>
>
> <xsl:variable name="reportrows"><xsl:value-of
> select="/session/reportrows"/></xsl:variable>
>
> <xsl:variable name="thispage">
> <xsl:choose>
> <xsl:when test="$page"><xsl:value-of select="$page"/></xsl:when>
> <xsl:otherwise>1</xsl:otherwise>
> </xsl:choose>
> </xsl:variable>
>
> <xsl:variable name="skiprows"><xsl:value-of select="($thispage - 1) *
> $reportrows"/></xsl:variable>
>
> <layout>
> <xsl:copy-of select="document($doc)/report/title"/>
> </layout>
>
> <data>
> <sql:execute-query>
> <sql:query>
> call spJobsperday(0, 2, '', '', '', '', '', '', '', '', '') ;
> </sql:query>
> </sql:execute-query>
> </data>
> </report>
> </xsl:template>
> </xsl:stylesheet>
>
> This is the result of it, stopped after the transform
>
> <report>
> −
> <session>
> <dbase>pdt20000</dbase>
> <username>Van Heede</username>
> <reportrows>2</reportrows>
> </session>
> −
> <layout>
> <title>Jobs per Day</title>
> </layout>
> −
> <data>
> −
> <sql:rowset>
> −
> <sql:row>
> <sql:eventdate>619-10-18</sql:eventdate>
> <sql:jobs>0</sql:jobs>
> <sql:pages/>
> <sql:firstjob/>
> <sql:lastjob>00:00:00</sql:lastjob>
> </sql:row>
> −
> <sql:row>
> <sql:eventdate>619-10-18</sql:eventdate>
> <sql:jobs>0</sql:jobs>
> <sql:pages/>
> <sql:firstjob/>
> <sql:lastjob>00:00:00</sql:lastjob>
> </sql:row>
> </sql:rowset>
> </data>
> </report>
>
>
> When I execute this using a normal "select * from tbldnjobs order by
> eventdate desc " (which is executed in the stored procedure ...), the
> data is returned correctly. tbldnjobs is a download table in which we
> stored results for speed reasons. If we give no parameters (the empty
> strings) we can use those. If a parameter is passed, we need to do
> this: "select eventdate, count(id), min(eventdate), max(eventdate)
> from tblprintjobs where (parameters here) group by eventdate order by
> eventdate desc" On 2 - 3 million records spread over 2 months, this
> makes a query on 60 records or 3 million records. This is why the
> stored procedure is used.
> My page pastes all the values in the screen to the stored procedures,
> and there we decide how we execute the query. The resultset returns
> always the same data structure, so it's transparent to the resulting
> webpage.
>
>
> Ok, after this ....
>
> I downloaded Aqua Data Studio, and ran the query with the jars
> provided by them. This works. (see below)
>
> eventdate jobs pages firstjob lastjob
> ------------ ------- -------- ----------- ----------
> 25/03/2005 2 3 8:48:59 11:33:11
> 11/04/2005 1 1 14:37:06 14:37:06
>
> 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
>
> I then opened the folders and searched for the jars. Found them and
> replaced them temporarily.
> That didn't work, Aqua Data complains it can't find the driver, so I
> suppose it's in their files somewhere what jars to load.
>
> I then worked the other way: I copied their mysql.jar into jetty and
> restarted.
> This results in the same error, the data is still garbled (identical
> as with the original jars)
>
>
> When I change my stored proc slightly to cast the fields as chars, I
> get other data, but garbled again
> <sql:row><sql:eventdate>05-03-252308:48:59
>
> As you can see, the data is correct now, but the characters to split
> them are still incorrect.
> This makes my think that this is indeed a bug in the transformer.
>
> I will test one more thing: using the xsp data generator instead of
> the transformer.
> Need to write some code for that, so I will get back .....
>
>>> <sql:row>
>>> <sql:eventdate>619-10-18</sql:eventdate>
>>> <sql:jobs>305420361</sql:jobs>
>>> <sql:pages/>
>>> <sql:firstjob/>
>>> <sql:lastjob>01:20:22</sql:lastjob>
>>> </sql:row>
>>> −
>>> <sql:row>
>>> <sql:eventdate>619-10-18</sql:eventdate>
>>> <sql:jobs>305420361</sql:jobs>
>>> <sql:pages/>
>>> <sql:firstjob/>
>>> <sql:lastjob>01:20:22</sql:lastjob>
>>> </sql:row>
>>>
>>>
>>
>> It would have helped if the command line output you quoted was for
>> the same call as the Cocoon transformer output - perhaps there's
>> something about the specific values that makes them be skipped. For
>> example, are they displayed as zeroes in the shell? Are they really
>> zeroes or nulls (in which case, it's not unreasonable that the
>> elements come back empty)? How exactly are the results "messed up" -
>> just missing pages/firstjob entries, or are other values wrong too?
>> Are all of the pages/firstjob elements empty, or only some of them?
>> What's with those characters between the rows - are they present in
>> the transformer's input, a copy/paste artifact from your mail client,
>> or are they being inserted by the transformer?
>>
> As you can see, the exact same calls give totally different results.
> The dates are completely wrong (should be in 2005 or 2006) and the
> other data is empty.
> The extra characters are "minus" and "plus", as they are a copy from
> Firefox, where those characters let you expand / collapse the tree
> below it.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: esql stored proc call (WAS mysql problem with sql transformer
(and esql too))
Posted by Yves Vindevogel <yv...@implements.be>.
Retry with CALL
<esql:execute-query>
<esql:call>call spJobsperday(0, 2, '', '', '', '', '',
'', '', '', '')</esql:call>
<esql:call-results>
<esql:use-results>
<esql:result><xsp:expr>(ResultSet)<esql:get-object
column="1"/></xsp:expr></esql:result>
<esql:results>
<esql:row-results>
<esql:get-string column="1"/>
</esql:row-results>
</esql:results>
</esql:use-results>
</esql:call-results>
</esql:execute-query>
Here I get the error
java.lang.RuntimeException: Error executing statement: call
spJobsperday(0, 2, '', '', '', '', '', '', '', '', ''):
java.sql.SQLException: Operation not allowed after ResultSet closed
Could somebody look at my syntax ?
What should it be to get this working ?
Yves Vindevogel wrote:
> XSP with ESQL seems to give the same problem
>
> <xsp:page language="java"
> xmlns:xsp="http://apache.org/xsp"
> xmlns:xsp-request="http://apache.org/xsp/request/2.0"
> xmlns:esql="http://apache.org/cocoon/SQL/v2"
> xmlns:xsp-session="http://apache.org/xsp/session/2.0"
> create-session="true">
>
> <html><body>
> <esql:connection>
> <esql:pool>pdt10010</esql:pool>
> <esql:execute-query>
> <esql:query>call spJobsperday(0, 2, ' ', ' ', ' ', ' ',
> ' ', ' ', ' ', ' ', ' ')</esql:query>
> <esql:results>
> <table>
> <esql:row-results>
> <tr>
> <td><esql:get-string
> column="eventdate"/></td>
> <td><esql:get-string column="jobs"/></td>
> <td><esql:get-string column="pages"/></td>
> <td><esql:get-string
> column="firstjob"/></td>
> <td><esql:get-string
> column="lastjob"/></td>
> </tr>
> </esql:row-results>
> </table>
> </esql:results>
> <esql:no-results>
> <p>Sorry, no results!</p>
> </esql:no-results>
> </esql:execute-query>
> </esql:connection>
> </body></html>
>
>
>
> </xsp:page>
>
>
> XML Parsing Error: reference to invalid character number
> Location: http://127.0.0.1:8888/2.0.0/reports/user/jobsperday.html
> Line Number 6, Column 13:
> <td>�619-10-18</td>
>
>
> And without the eventdate (which seems to give the problem)
>
> <html>
> −
> <body>
> −
> <table>
> −
> <tr>
> <td>0</td>
> <td/>
> <td/>
> <td>00:00:00</td>
> </tr>
> −
> <tr>
> <td>0</td>
> <td/>
> <td/>
> <td>00:00:00</td>
> </tr>
> </table>
> </body>
> </html>
>
>
>
>
> Yves Vindevogel wrote:
>> Ok, here's some more copy and pasting
>>
>> This is the command line executed in putty or ssh with the results
>> (which are correct)
>> (0 means offset 0, 2 means limit 2)
>>
>> root@ns20822:~# mysql pdt20000 --execute "call spjobsperday(0, 2, '',
>> '', '', '', '', '', '', '', '')"
>> +------------+------+-------+----------+----------+
>> | EventDate | Jobs | Pages | FirstJob | LastJob |
>> +------------+------+-------+----------+----------+
>> | 2005-03-25 | 2 | 3 | 08:48:59 | 11:33:11 |
>> | 2005-04-11 | 1 | 1 | 14:37:06 | 14:37:06 |
>> +------------+------+-------+----------+----------+
>> root@ns20822:~#
>>
>>
>> Below the XSL page
>>
>> <?xml version="1.0" encoding="ISO-8859-1"?>
>> <xsl:stylesheet version="1.0"
>> xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>> xmlns:sql="http://apache.org/cocoon/SQL/2.0"
>> >
>>
>> <xsl:param name="dir"/>
>> <xsl:param name="file"/>
>> <xsl:param name="page"/>
>>
>> <xsl:param name="ipaddress"/>
>> <xsl:param name="hostname"/>
>> <xsl:param name="eventdate1"/>
>> <xsl:param name="eventdate2"/>
>> <xsl:param name="documentname"/>
>> <xsl:param name="username"/>
>> <xsl:param name="printqueue"/>
>> <xsl:param name="printport"/>
>> <xsl:param name="applicationtype"/>
>>
>> <xsl:template match="/">
>> <report>
>> <xsl:copy-of select="session"/>
>>
>> <xsl:variable name="doc">../reports/<xsl:value-of
>> select="$dir"/>/<xsl:value-of select="$file"/>.xml</xsl:variable>
>>
>> <xsl:variable name="reportrows"><xsl:value-of
>> select="/session/reportrows"/></xsl:variable>
>>
>> <xsl:variable name="thispage">
>> <xsl:choose>
>> <xsl:when test="$page"><xsl:value-of select="$page"/></xsl:when>
>> <xsl:otherwise>1</xsl:otherwise>
>> </xsl:choose>
>> </xsl:variable>
>>
>> <xsl:variable name="skiprows"><xsl:value-of select="($thispage - 1) *
>> $reportrows"/></xsl:variable>
>>
>> <layout>
>> <xsl:copy-of select="document($doc)/report/title"/>
>> </layout>
>>
>> <data>
>> <sql:execute-query>
>> <sql:query>
>> call spJobsperday(0, 2, '', '', '', '', '', '', '', '', '') ;
>> </sql:query>
>> </sql:execute-query>
>> </data>
>> </report>
>> </xsl:template>
>> </xsl:stylesheet>
>>
>> This is the result of it, stopped after the transform
>>
>> <report>
>> −
>> <session>
>> <dbase>pdt20000</dbase>
>> <username>Van Heede</username>
>> <reportrows>2</reportrows>
>> </session>
>> −
>> <layout>
>> <title>Jobs per Day</title>
>> </layout>
>> −
>> <data>
>> −
>> <sql:rowset>
>> −
>> <sql:row>
>> <sql:eventdate>619-10-18</sql:eventdate>
>> <sql:jobs>0</sql:jobs>
>> <sql:pages/>
>> <sql:firstjob/>
>> <sql:lastjob>00:00:00</sql:lastjob>
>> </sql:row>
>> −
>> <sql:row>
>> <sql:eventdate>619-10-18</sql:eventdate>
>> <sql:jobs>0</sql:jobs>
>> <sql:pages/>
>> <sql:firstjob/>
>> <sql:lastjob>00:00:00</sql:lastjob>
>> </sql:row>
>> </sql:rowset>
>> </data>
>> </report>
>>
>>
>> When I execute this using a normal "select * from tbldnjobs order by
>> eventdate desc " (which is executed in the stored procedure ...), the
>> data is returned correctly. tbldnjobs is a download table in which we
>> stored results for speed reasons. If we give no parameters (the empty
>> strings) we can use those. If a parameter is passed, we need to do
>> this: "select eventdate, count(id), min(eventdate), max(eventdate)
>> from tblprintjobs where (parameters here) group by eventdate order by
>> eventdate desc" On 2 - 3 million records spread over 2 months, this
>> makes a query on 60 records or 3 million records. This is why the
>> stored procedure is used.
>> My page pastes all the values in the screen to the stored procedures,
>> and there we decide how we execute the query. The resultset returns
>> always the same data structure, so it's transparent to the resulting
>> webpage.
>>
>>
>> Ok, after this ....
>>
>> I downloaded Aqua Data Studio, and ran the query with the jars
>> provided by them. This works. (see below)
>>
>> eventdate jobs pages firstjob lastjob
>> ------------ ------- -------- ----------- ----------
>> 25/03/2005 2 3 8:48:59 11:33:11
>> 11/04/2005 1 1 14:37:06 14:37:06
>>
>> 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
>>
>> I then opened the folders and searched for the jars. Found them and
>> replaced them temporarily.
>> That didn't work, Aqua Data complains it can't find the driver, so I
>> suppose it's in their files somewhere what jars to load.
>>
>> I then worked the other way: I copied their mysql.jar into jetty and
>> restarted.
>> This results in the same error, the data is still garbled (identical
>> as with the original jars)
>>
>>
>> When I change my stored proc slightly to cast the fields as chars, I
>> get other data, but garbled again
>> <sql:row><sql:eventdate>05-03-252308:48:59
>>
>> As you can see, the data is correct now, but the characters to split
>> them are still incorrect.
>> This makes my think that this is indeed a bug in the transformer.
>>
>> I will test one more thing: using the xsp data generator instead of
>> the transformer.
>> Need to write some code for that, so I will get back .....
>>
>>>> <sql:row>
>>>> <sql:eventdate>619-10-18</sql:eventdate>
>>>> <sql:jobs>305420361</sql:jobs>
>>>> <sql:pages/>
>>>> <sql:firstjob/>
>>>> <sql:lastjob>01:20:22</sql:lastjob>
>>>> </sql:row>
>>>> −
>>>> <sql:row>
>>>> <sql:eventdate>619-10-18</sql:eventdate>
>>>> <sql:jobs>305420361</sql:jobs>
>>>> <sql:pages/>
>>>> <sql:firstjob/>
>>>> <sql:lastjob>01:20:22</sql:lastjob>
>>>> </sql:row>
>>>>
>>>>
>>>
>>> It would have helped if the command line output you quoted was for
>>> the same call as the Cocoon transformer output - perhaps there's
>>> something about the specific values that makes them be skipped. For
>>> example, are they displayed as zeroes in the shell? Are they really
>>> zeroes or nulls (in which case, it's not unreasonable that the
>>> elements come back empty)? How exactly are the results "messed up" -
>>> just missing pages/firstjob entries, or are other values wrong too?
>>> Are all of the pages/firstjob elements empty, or only some of them?
>>> What's with those characters between the rows - are they present in
>>> the transformer's input, a copy/paste artifact from your mail
>>> client, or are they being inserted by the transformer?
>>>
>> As you can see, the exact same calls give totally different results.
>> The dates are completely wrong (should be in 2005 or 2006) and the
>> other data is empty.
>> The extra characters are "minus" and "plus", as they are a copy from
>> Firefox, where those characters let you expand / collapse the tree
>> below it.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
>> For additional commands, e-mail: users-help@cocoon.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org