DBI - Table names & placeholders

[McDarren]

Howdy :)

(I thought this might have been a FAQ, but it doesn't appear to be)

I'm aware that table names cannot be used with place holders. From the DBI docs - "With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it."

Fair enough.
But what is the usual/recommended approach when you strike a situation where you think you would _like_ to use placeholders for table names?

In my particular situation, I have something like (contrived example):

INSERT INTO Widgets201003 (moo, baa, cluck) VALUES (?, ?, ?)
INSERT INTO Widgets201004 (moo, baa, cluck) VALUES (?, ?, ?)
INSERT INTO Widgets201005 (moo, baa, cluck) VALUES (?, ?, ?)

Obviously, it would be nice if I could do:

INSERT INTO ? (moo, baa, cluck) VALUES (?, ?, ?)

But of course, that doesn't work.
One approach might be to come up with a better data base design, but unfortunately I'm dealing with legacy stuff that cannot be changed without causing copious amounts of grief and heartache ;)

I've thought about perhaps building a hash of SQL statements, using the table names as keys - but that feels really horrible.

So, how do others generally approach this?
Is there a module about that addresses this particular problem?

If it matters, I'm dealing with both MySQL & PostgreSQL

many thanks,
Darren :)

[ikegami]

If you're worried about unusual table names, you need $dbh->quote_identifier.

for my $table (qw( Widgets201003 Widgets201004 Widgets201005 )) {
    my $q_table = $dbh->quote_identifier($table);
    my $sth = $dbh->prepare("
        INSERT INTO $q_table (
                   moo, baa, cluck
               ) VALUES (
                   ?, ?, ?
               )
    ");
    ...
}

[McDarren]

If you're worried about unusual table names

Well, no - that's not my problem.
The problem is that I will not know the names of the tables until run-time.
Let me give another (once again, contrived) example, that might explain it a bit better.

Imagine that I have two sets of tables, one a set of transactional records (eg, sales records), and the other a set of consolidated tables for reporting. So I have a Sales table with attributes for Date/Time, ItemID & Price.

Then I have a set of tables that look like Sales201003, Sales201004, Sales201005, etc - a separate table for each year/month combination. (This is not the way I would design it - but this is how it is and so I need to deal with it).

The SalesYYYYMM tables have one row per day, per item, along with attributes for the total number sold, and total revenue.

So I have code that selects records from the Sales table, and then inserts summarised data into the appropriate SalesYYYYMM tables. The code I would _like_ to be able to write would look something like this:

# Some code here which selects data from Sales table
# ...
# ...
# then...

my @sales_inserts;
my $insert_sql = 'INSERT INTO ? (ItemID, Day, Revenue, Count) VALUES (
+?, ?, ?, ?);
my $sales_insert_dbq = $sales_dbh->prepare($insert_sql);    
while (my @data = $dbq->fetchrow_array) { 
    my ($year, $month, $day, $itemid, $revenue, $count) = @data;
    my $sales_table = "Sales$year$month";
    my @insert_values = ($sales_table, $day, $itemid, $revenue, $count
+);
    push @sales_inserts, [@insert_values];
}       
eval {
    for my $i (0 .. $#sales_inserts) {
        $sales_insert_dbq->execute(@{$sales_inserts[$i]});
    }                                                   
        $sales_dbh->commit;
};                                          
if ($@) {
    # Error handling, rollback, etc
}

But the above won't work because I can't use a placeholder for a table name.
I can work around it easily enough by creating a separate statement handle for each YYYYMM combination.
I'm just not sure what the best approach to that would be.

Oh, and please ignore the fact that it's horrible data base design, and that calculated data is being written back into the data base. As I said, it's a contrived example that helps to describe my problem.

[ikegami]

The bottom loop should be

for (@$sales_inserts) {
    my $table = shift(@$_);
    my $sth = $dbh->prepare('
        INSERT INTO '.$dbh->quote_identifier($table).' (
                   ItemID, Day, Revenue, Count
               ) VALUES (
                   ?, ?, ?, ?
               )
    ');
    $sth->execute(@$_);
}

or

my %sth_cache;
for (@$sales_inserts) {
    my $table = shift(@$_);
    my $sth = $sth_cache{$table} ||= $dbh->prepare('
        INSERT INTO '.$dbh->quote_identifier($table).' (
                   ItemID, Day, Revenue, Count
               ) VALUES (
                   ?, ?, ?, ?
               )
    ');
    $sth->execute(@$_);
}

[ig]

You could produce and prepare the specific queries as you need them. It would be easy and inefficient to use each statement once. Alternatively you could cache them and reuse them. A hash keyed on the table name to hold the prepared statements or a simple sub to prepare them combined with memoize.

[Anonymous Monk]

I solved this problem when I had to deal a few dozen tables all with the same format and function each named by a client number determined from input data. The trick is to use a synonym. Write code for one table using a false table name. Then create a synonym named with your false name. Then alter the synonym to point to the table you need to use when you need to and reuse the bulk of your code. If the term 'synonym' is not familiar, consider it an alias or a symbolyc link with its own name that represents another object (table?) that may have another name. I don't know the performance impact of this method but I suspect altering a synonym is pretty cheap next to recompiling your insert statements. Hope this helps.

[GrandFather]

Given you can only insert into one table at a time, if you want to insert into several tables you are going to have to wrap the insert code in a loop and generate the SQL and a statement handle for each table so string interpolation for the table name is just fine. That assumes of course that your table names come from a trusted source (not, for example, from Mrs Drop-Tables).

[Ollie Jones]

Ask yourself what you're trying to accomplish. Are you trying to build an elegant abstraction layer for maintaining several tables? Are you trying to crank up the throughput of your PERL program? Are you trying to crank up the shared performance of your database server? Are you trying to get your work done before sunrise?

In a perfect world, these goals would all be aligned. In the real world, they aren't, exactly.

You're not dealing with Oracle, so that helps. Prepared statements can make an enormous difference to Oracle shared database performance. It makes sense to commit ugliness in your PERL program to make Oracle faster, just because it's so doggone expensive.

You can make your PERL program elegant by creating a simple little statement-template scheme, substituting in the table name you need.

You can make your PERL program and your data base a little faster by preparing each variant just once, and reusing the appropriate prepared statement each time you need it. But, unless you're going to use these statements tens of thousands of times, it isn't worth the trouble; you'll sacrifice a lot of time getting it right in return for a little time saved.

[runrig]

See if the insert_hash example in the prepare_cached docs gives you any ideas.