I can do even more with the generator, I found out ...
I extended my stored procedure to return 2 resultsets: 1 with the data,
and one with the "dashboard" data.
For instance, when I create my report on "Jobs per day", I have a little
box below my report stating how many days we have, how many users,...
This is what I return in my second resultset, called dashboard.
I can now return that from one call:
[EMAIL PROTECTED]:~# mysql pdt20000 --user=root --password='[EMAIL PROTECTED];'
--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 |
+------------+------+-------+----------+----------+
+----------+
| reccount |
+----------+
| 197 |
+----------+
[EMAIL PROTECTED]:~#
<report>
<esql:connection>
<esql:allow-multiple-results>yes</esql:allow-multiple-results>
<esql:pool>pdt20000</esql:pool>
<esql:execute-query>
<esql:call>call spJobsperday(0, 2, '', '', '', '', '',
'', '', '', '')</esql:call>
<esql:results>
<data>
<esql:row-results>
<row><esql:get-columns/></row>
</esql:row-results>
</data>
</esql:results>
<esql:results>
<dashboard>
<esql:row-results>
<esql:get-columns/>
</esql:row-results>
</dashboard>
</esql:results>
</esql:execute-query>
</esql:connection>
</report>
<report>
−
<data>
−
<row>
<EventDate>2005-03-25</EventDate>
<Jobs>2</Jobs>
<Pages>3</Pages>
<FirstJob>08:48:59</FirstJob>
<LastJob>11:33:11</LastJob>
</row>
−
<row>
<EventDate>2005-04-11</EventDate>
<Jobs>1</Jobs>
<Pages>1</Pages>
<FirstJob>14:37:06</FirstJob>
<LastJob>14:37:06</LastJob>
</row>
</data>
−
<dashboard>
<reccount>197</reccount>
</dashboard>
</report>
Yves Vindevogel wrote:
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, '', '', '', '', '', '', '', '', ''}
?
Your suggestion gives this error
<sql:error>
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '0, 2, '', '', '', '', '', '', '', '', ''' at line 1
</sql:error>
I suppose you meant this:
<sql:execute-query>
<sql:query>
{call spJobsperday(0, 2, '', '', '', '', '', '', '', '', '')}
</sql:query>
</sql:execute-query>
Does not work. Wrong results again.
Anyway, this seems to work, but I have to use a generator ...
<esql:connection>
<esql:pool>pdt20000</esql:pool>
<esql:execute-query>
<esql:call>call spJobsperday(0, 2, '', '', '', '', '', '', '', '',
'')</esql:call>
<esql:results>
<data>
<esql:row-results>
<row><esql:get-columns/></row>
</esql:row-results>
</data>
</esql:results>
</esql:execute-query>
</esql:connection>
<data>
−
<row>
<EventDate>2005-03-25</EventDate>
<Jobs>2</Jobs>
<Pages>3</Pages>
<FirstJob>08:48:59</FirstJob>
<LastJob>11:33:11</LastJob>
</row>
−
<row>
<EventDate>2005-04-11</EventDate>
<Jobs>1</Jobs>
<Pages>1</Pages>
<FirstJob>14:37:06</FirstJob>
<LastJob>14:37:06</LastJob>
</row>
</data>
</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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
