cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yves Vindevogel <yves.vindevo...@implements.be>
Subject Re: esql stored proc call (WAS mysql problem with sql transformer (and esql too))
Date Sat, 27 May 2006 16:28:22 GMT
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>&#0;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-25&#1;2&#1;3&#8;08:48:59&#8;
>>
>> 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


Mime
View raw message