Return-Path: 
 <users-return-86879-apmail-cocoon-users-archive=cocoon.apache.org@cocoon.apache.org>
Delivered-To: apmail-cocoon-users-archive@www.apache.org
Received: (qmail 90533 invoked from network); 27 May 2006 11:14:38 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 27 May 2006 11:14:38 -0000
Received: (qmail 44895 invoked by uid 500); 27 May 2006 11:14:31 -0000
Delivered-To: apmail-cocoon-users-archive@cocoon.apache.org
Received: (qmail 44847 invoked by uid 500); 27 May 2006 11:14:30 -0000
Mailing-List: contact users-help@cocoon.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:users-help@cocoon.apache.org>
list-unsubscribe: <mailto:users-unsubscribe@cocoon.apache.org>
List-Post: <mailto:users@cocoon.apache.org>
Reply-To: users@cocoon.apache.org
List-Id: <users.cocoon.apache.org>
Delivered-To: mailing list users@cocoon.apache.org
Received: (qmail 44836 invoked by uid 99); 27 May 2006 11:14:30 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 27 May 2006 04:14:30 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: neutral (asf.osuosl.org: local policy)
Received: from [195.130.137.75] (HELO europa.telenet-ops.be) (195.130.137.75)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 27 May 2006 04:14:29 -0700
Received: from localhost (localhost.localdomain [127.0.0.1])
	by europa.telenet-ops.be (Postfix) with SMTP id DFA1923411C
	for <users@cocoon.apache.org>; Sat, 27 May 2006 13:14:06 +0200 (CEST)
Received: from [10.32.3.100] (d54C23A04.access.telenet.be [84.194.58.4])
	by europa.telenet-ops.be (Postfix) with ESMTP id 9FDF823409D
	for <users@cocoon.apache.org>; Sat, 27 May 2006 13:14:06 +0200 (CEST)
Message-ID: <4478347D.1040304@implements.be>
Date: Sat, 27 May 2006 13:14:05 +0200
From: Yves Vindevogel <yves.vindevogel@implements.be>
User-Agent: Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
To: users@cocoon.apache.org
Subject: Re: mysql problem with sql transformer
References: <BAY104-F33FA5349D68E8455A8C887AD9F0@phx.gbl>
In-Reply-To: <BAY104-F33FA5349D68E8455A8C887AD9F0@phx.gbl>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

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