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

First, thank you to everyone who responded to my post of last night. You were very helpful.

On an entirely separate project, I'm busy creating a spelling checker that has multiple possible dictionaries to look through, which will be selected by the user. Each of these dictionaries are stored as a table in MySQL.

I'd like to use DBI and a loop to get at them thusly: 

my $sth = $dbh->prepare('SELECT word FROM ? WHERE word = ?');
foreach my $x (@checklists) {
    $sth->execute($x, $word);
    my $ret = $sth->fetchrow_hashref();
    ...etc...
}

[download]
Where @checklists has all the table names to look at and $word has the word I want to spell-check.

When I run this, though, Perl belches out an SQL error: 

DBD::mysql::st execute failed: You have an error in your SQL syntax ne
+ar ''common_dict' WHERE word = 'aa'' at line 1 at ./quiddler.pl line 
+48.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at .
+/quiddler.pl line 49.
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect().

[download]
When I tried the same statement with an explicit table name, it worked great.

Is what I'm trying to do here even possible? Or must I manually talk to each individual table? If I can do this, what's the proper syntax?

Thanks,
Alex Kirk

Replies are listed 'Best First'.
Re: Querying databases in a loop
by VSarkiss (Monsignor) on May 13, 2002 at 19:15 UTC

    Generally speaking, placeholders are not appropriate in the FROM clause. I'm not sure which databases will even support prepare on a statement with a variable FROM. It also won't get you any performance benefit: the prepare'd statement would have to be re-planned everytime you executed it anyway.

    You can have multiple statement handles, one for each table, but whether that's beneficial depends on what you're doing (the number of tables, the number of lookups, how long your program persists, etc.). Here's what I mean (warning, this is untested): 

     my %sth;
foreach my $tab (@checklists)
{
    # BTW, this SQL may be better written:
    #     select 1 from $tab where word = ?
    # if all you're testing for is existence...
    $sth{$tab} = $dbh->prepare("SELECT word from $tab where word = ?")
+;
}
# later...
foreach my $tab (@checklists)
{
    $sth{$tab}->execute($word);
    my $ret = $sth{$tab}->fetchrow_hashref();
}

    [download]
    I don't know whether MySQL will support having multiple prepared statements per handle. You'll have to check.

    HTH

[reply]
[d/l]
[select]
Re: Querying databases in a loop
by perlplexer (Hermit) on May 13, 2002 at 19:23 UTC
    I don't think you can use placeholders for table names.
    The problem is that the resulting string will be quoted, as you see in that nice error message:
    error in your SQL syntax near ''common_dict' WHERE word = 'aa''

    What you can do is use prepare_cached() in the following way 
    
foreach my $x (@checklists) {
    my $sth = $dbh->prepare_cached("SELECT word FROM $x WHERE word = ?
+");
    $sth->execute($word);
    my $ret = $sth->fetchrow_hashref();
    ...etc...
}

    [download]
    This way you won't get a performance hit for re-preparing the SQL statement every time.

    --perlplexer
[reply]
[d/l]
Re: Querying databases in a loop
by perrin (Chancellor) on May 13, 2002 at 19:44 UTC
    Others have pointed out the problem with your DBI code. I just wanted to point out that you might be able to improve things by putting all of these dictionaries in one table and making a column called DICTIONARY_ID which would be a part of the primary key. That would allow you to use a single SQL statement with placeholders.
[reply]
      At first I thought the idea of putting all the words into one giant table with an identifier for which subset they came out of was a good idea. Then I tried it.

      Basically, MySQL suffers a *huge* performance hit with the large table (600K+ words) vs. the smaller table (350+K words); I think I ran up against some sort of limit in the software. For comparison's sake, though, identical queries took 3.31 seconds for the large table, and 0.76 seconds for the smaller one -- over 4 times as long.

      Since it's not possible -- or, as was well pointed out, practical -- to leave the table name as a variable, and since upon further review I only have 4 tables to deal with (I thought it was going to be more like 12, but I ruled a bunch of word subsets out), I'm just going to have 4 separate statements prepped up (and yes, one database handle *can* support multiple queries -- I've done it before).

      Thanks for your help anyway.

      Alex Kirk
[reply]
        What kind of indexing do you have on the table? I think that the one table paradigm is the way to go. You'll have to throw an index or two on it, but no worries there.
[reply]
        As Thor said, you probably just have an indexing problem on your table. I know that the more recent MySQL table types handle very large data sets well, so this should not be a problem.
[reply]
Re: Querying databases in a loop
by kappa (Chaplain) on May 14, 2002 at 06:50 UTC
    There's a good pattern I got from the wonderful Dragonfly book accidentally called "Oracle Design". When you have lots of tables of identical structure (log archives come to mind in addition to what you have), use SQL UNION clause to search them all at once. Of course, MySQL won't let you create a VIEW, so you'll probably have a biiiig SQL statement.
[reply]

Back to Seekers of Perl Wisdom