Can I get table names from a SQL query using Perl DBI?

Question

I write small snippets in Perl and DBI (SQLite yay!)

I would like to write some specific queries to text files with the same file name as the names of the tables in which the query is being executed.

Here is the code that I use to output the 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's how I can call it:

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

What I really want, instead of going to "results.txt" (this is hardcoded above), now I need to go to "TADA.txt".

If this were the connection between the tables "HAI" and "LOL", then the result set should be written in "HAI.LOL.txt"

Is what I'm saying even possible using magic in DBI?

SQL- , SQL-, .

, . .

Answer 1

, . SQL . . - , . , SQL, . logger , ; .

, . :

sub make_query {
    my ($table, $columns, $conditions) = @_;
    return "SELECT $columns FROM $table WHERE $conditions";
}

sub run_query {
    my ($query) = @_;
    $dbh->prepare($query);
    ...
}

run_query( make_query( 'foo', '*', '1=1' ) );

, . , - :

sub make_query {
    my ($table, $columns, $conditions) = @_;
    return +{
        query => "SELECT $columns FROM $table WHERE $conditions",
        table => $table,
    } # an object might not be a bad idea
}

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

    $dbh->prepare($query->{query});
    log_to_file( $query->{table}.'.log', ... );

    ...
}

run_query( make_query( 'foo', '*', '1=1' ) );

API , , .

SQL::Abstract SQL. .

: , , SQLite. EXPLAIN :

sqlite> explain select * from test;
0|Trace|0|0|0|explain select * from test;|00|
1|Goto|0|11|0||00|
2|SetNumColumns|0|2|0||00|
3|OpenRead|0|2|0||00|
4|Rewind|0|9|0||00|
5|Column|0|0|1||00|
6|Column|0|1|2||00|
7|ResultRow|1|2|0||00|
8|Next|0|5|0||00|
9|Close|0|0|0||00|
10|Halt|0|0|0||00|
11|Transaction|0|0|0||00|
12|VerifyCookie|0|1|0||00|
13|TableLock|0|2|0|test|00|
14|Goto|0|2|0||00|

, TableLock - , . YMMV, .

Answer 2

, SQL , ( ), ( - - ).

, - ( ) .

Answer 3

SQL::Statement, .

use SQL::Statement;
use strict;
use warnings;

my $sql = <<"    END_SQL";
    SELECT TADA.fileName, TADA.labelName
    FROM   TADA
    END_SQL
my $parser = SQL::Parser->new();
$parser->{RaiseError} = 1;
$parser->{PrintError} = 0;
my $stmt = eval { SQL::Statement->new($sql, $parser) }
    or die "parse error: $@";
print join',',map{$_->name}$stmt->tables;