cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrew Stevens" <at...@hotmail.com>
Subject Re: mysql problem with sql transformer
Date Sat, 27 May 2006 23:21:14 GMT
>From: Yves Vindevogel <yves.vindevogel@implements.be>
>Date: Sat, 27 May 2006 13:14:05 +0200
>
>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>

Not <sql:query isstoredprocedure="true"> ?  Without that, the transformer 
uses a PreparedStatement rather than a CallableStatement.

>call spJobsperday(0, 2, '', '', '', '', '', '', '', '', '') ;

I don't know if it makes any difference, but we always use the SQL escape 
syntax described in the CallableStatement javadocs[1].  Does it make any 
difference if you use
{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.

If you set it up as a "Generic - JDBC" type connection, one of the fields is 
for the location of the driver jar file.  I don't know if it can handle 
multiple versions of drivers it already knows about, though.

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

If you want to avoid that, you can always right-click and "View Source" 
first.  The down side is it may string together everything on one line 
instead.


Andrew.

[1] http://java.sun.com/j2se/1.3/docs/api/java/sql/CallableStatement.html



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org


Mime
View raw message