I am writing small snippets in Perl and DBI (SQLite yay!)
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";
}
Here is how I can call this :
dumpResultsToFile ( <<" END_SQL" );
SELECT TADA.fileName, TADA.labelName
FROM TADA
END_SQL
What I effectively 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. Seggregating based on
table name seems a nice way for now.
Best Answer
Probably not. Your SQL generation code takes the wrong approach. You are hiding too much information from your program. At some point, your program knows which table to select from. Instead of throwing that information away and embedding it inside an opaque SQL command, you should keep it around. Then your logger function doesn't have to guess where the log data should go; it knows.
Maybe this is clearer with some code. Your code looks like:
This doesn't let you do what you want to do. So you should structure your program to do something like:
The API is the same, but now you have the information you need to log the way you want.
Also, consider SQL::Abstract for dynamic SQL generation. My code above is just an example.
Edit: OK, so you say you're using SQLite. It has an EXPLAIN command which you could parse the output of:
Looks like TableLock is what you would want to look for. YMMV, this is a bad idea.