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)
[EMAIL PROTECTED]:~# 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 |
+------------+------+-------+----------+----------+
[EMAIL PROTECTED]:~#
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
