From: Yves Vindevogel <[EMAIL PROTECTED]>
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)
[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>
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
