Grabbing source table names from a SQL query

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

I am writing small snippets in PERL and DBI ( SQLite yay! )

Let me deviate a little bit and say how cool SQLite is. It makes handling a database just like string manipulation in PERL!

Anyways, what I wanted to know is that, I would like to log some specific queries to text files having the same filename as that of the table name(s) on which the query is run.

Here is the code I use to dump results to a text file :

sub dumpResultsToFile {
    my ( $query ) = @_;

    # Prepare and execute the query
    my $sth = $dbh->prepare( $query );
    $sth->execute();

    # Open the output file
    open FILE, ">results.txt" or die "Can't open results output file: 
+$!";

    # Dump the formatted results to the file
    $sth->dump_results( 80, "\n", ", ", \*FILE );

    # Close the output file
    close FILE or die "Error closing result file: $!\n";
}
[download]

Here is how I can call this :

dumpResultsToFile ( <<"    END_SQL" );
    SELECT TADA.fileName, TADA.labelName
    FROM   TADA
    END_SQL
[download]

What I effectiively want is, instead of stuff going to "results.txt" ( that is hardcoded above ), it should now go to "TADA.txt".

Had this been a join between tables "HAI" and "LOL", then the resultset should be written to "HAI.LOL.txt"

Is what I am saying even possible using some magic in DBI?

I would rather do without parsing the SQL query for tables, but if there is a widely used and debugged function to grab source table names in a SQL query, that would work for me too!

What I want is just to have a filename that gives some hint as to what query output it holds. Segregating based on table name seems a nice way for now.

Comment on Grabbing source table names from a SQL query Select or Download Code

Replies are listed 'Best First'.
Re: Grabbing source table names from a SQL query by shmem (Chancellor) on Oct 18, 2009 at 14:18 UTC
You could construct the filename at the moment you build the query, and pass it as a second argument to `dumpResultsToFile()`: `my @tables = qwTADA; dumpResultsToFile ( <<" END_SQL", join('.', @tables) ); SELECT $tables[0].fileName, $tables[0].labelName FROM $tables[0] END_SQL sub dumpResultsToFile { my ( $query, $filename ) = @_; ... open my $fh, '>', "$filename.txt" or die` [download] If you want a filename depending on tables, get the table names from their origin, i.e. where that information enters your program. Asking that information from some other module deep in the processing chain looks wrong to me, since you have it handy in the first place.	[reply] [d/l] [select]
Re^2: Grabbing source table names from a SQL query by PoorLuzer (Beadle) on Oct 22, 2009 at 02:20 UTC
This is a really good answer. Focused on "Getting things done" than being pedantic. Would still love to know if grabbing query elemnst from a statement handle can work though.	[reply]
Re: Grabbing source table names from a SQL query by wfsp (Abbot) on Oct 18, 2009 at 14:16 UTC
SQL::Statement may be worth a look.	[reply]
Re: Grabbing source table names from a SQL query by dirving (Friar) on Oct 18, 2009 at 16:39 UTC
If you only need to handle fairly straightforward queries you can probably get away with using a regular expression to pull out the table names. Something like this should probably work: `my @tables = $query =~ /(?:FROM\|JOIN)\s+(\w+)/gi;` [download] If you want to handle more cases, there are a number of SQL parsing modules available on CPAN, such as the one recommended by wfsp above. -- David Irving	[reply] [d/l]
Re: Grabbing source table names from a SQL query by CountZero (Bishop) on Oct 18, 2009 at 19:10 UTC
Perl (the language) or perl (the interpreter), but never PERL. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]