schweini has asked for the wisdom of the Perl Monks concerning the following question:

oh thee enlightened,

i'm trying to piece this HTML-app together, which generates quite a lot of tables. these tables must eventually be printed on decommissioned, bleached and generally mutilated trees. i kinda have to do this via a nice, inviting "print this table"-link somewhere in the table.
my lack of wisdom manifests itself in the following ways:
my tables are always generated via my sub printTable(), which gets an (executed) $sth and some formating hints as its arguments. my idea was to make this nice sub re-construct the SQL needed to generate the current table, and pass this information (somehow) to a generic printTableToPaper.pl script, which would re-format the table, display it as HTML and document.print() it or pass it to 'html2ps | ps2pdf' or something like that. simply doing this via CSS soesn't work, since i have multiple tables per HTML-page, and sometimes, only parts of the tables are actually shown (whereas the whole table should be printed when that link is clicked).
to cut a long SoPW short: i simply wanted to store $sth->{'Statement'} and $sth->{'ParamValues'} somewhere, and pass them to my printing script. but DBD::mysql doesn't seem to support the latter (why? i thought a massivly used module like this one would be very up-to-date.)
so, does anybody have an idea how i could re-construct the SQL that got executed using DBD::mysql?
please?

edited: Fri Dec 20 05:32:54 2002 by jeffa - title truncation (was: mysql and $sth->{'ParamValues'} (or howto ...)

Replies are listed 'Best First'.
(jeffa) Re: How to reconstruct a SQL query from an executed $sth
by jeffa (Bishop) on Dec 20, 2002 at 05:26 UTC
    I ran a test script and confirmed (for myself) that $sth->{ParamValues} is indeed not set with DBD::mysql. Bummer, but you can work around it: if you can pass $sth to your printing script, then surely you can pass some scalar that holds the SQL and an array ref that holds the param values to that script.

    Sounds to me like what you are building is brute force (lots of wheel re-invention) and might benefit from a different approach. I can't help but cringe at the thought of subroutines that print output instead of returning output - maybe if you seperated your content from your format you wouldn't have this problem.

    Just as a thought, i might approach this by writing a sub or two that accepts some parameters, grabs the data from the database and returns a data structure containing that data. Then, that data structure could be passed to a variety of subs that handle how it should be displayed. XML is very good for this kind of problem - you can transform it to HTML for the website (check out XML::LibXML and XML::LibXSLT) and to a PDF file for the printer (check out XML::Handler::AxPoint). You might even be able to use the powerful mod_perl 'plugin' AxKit to drive your app.

    Update:
    Consider this site. This particular node is #221372. If i want to access this node, i can pass the number off to index.pl like so:

    http://www.perlmonks.org/index.pl?node_id=221372

    Behind the scenes at Perlmonks, a SQL query is executed and the contents of this node are munged into themed HTML. If i want an XML version of this node, i simply pass another parameter to index.pl like so:

    http://www.perlmonks.org/index.pl?node_id=221372&displaytype=xml

    Again, a SQL query is executed, but this time the contents are rendered into XML. My point of all this is that for both requests, only the node id and display type were needed. No SQL was reconstructed. I don't see why your app has to reconstruct SQL to serve different displays of the same data.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
Re: How to reconstruct a SQL query from an executed $sth
by chromatic (Archbishop) on Dec 20, 2002 at 03:09 UTC

    Do you mean "tables" in the "relational database entity" sense or "tables" in the "columnar data" sense? If it's the former, you should already have a copy of your schema. describe [table] will help you out in MySQL-land. If it's the latter, why not just issue a SELECT statement? That's why you have a database!

[reply]
[d/l]
[select]
      errr...tables in the HTML-sense. i really gotta express myself better. i'll try it in staccato-style:
      i use perl to generate html-pages. these contain tables that are the result of (more or less) complex sql-queries which use lots of placeholders (simplest example: the result of a search-form, where the user's query is, of course, bound to a placeholder). i $sth->execute() the $sth, and pass it to a generic printTable() sub, which does all the (rather advanced, if i may say so) html-formatting. i'd like these tables to generate and offer a "print this table!" link. thus, i have to pass the sql-query that actually generates this table to the script in charge of printing. this wouldn't be taht much of a problem if mysql would support $sth->{'ParamValues'} (which returns the values bound to placeholders). but dbd::mysql (to my knowledge) doesn't support this (yet?). thus, i fear i'm quite screwed (to put it lightly). thus i'm looking for a way to re-construct the SQL that an executed statement handle...well...executed.
      geez. i really shouldn't have bought this weak coffee. :-)
[reply]
[d/l]
[select]
(i offer strategy) Re: How to reconstruct a SQL query from an executed $sth
by PodMaster (Abbot) on Dec 20, 2002 at 10:22 UTC
    Make use of session technology (cookies, Apache::Session), and cache everything (DB_File or Cache::Cache). That way all you have to remember is some kind of unique key (session id + something), and you already have the data cached for a printable view (the original query, the data, and whatever else you need).

    MJD says you can't just make shit up and expect the computer to know what you mean, retardo!
    ** The Third rule of perl club is a statement of fact: pod is sexy.

[reply]

Back to Seekers of Perl Wisdom