Dynamically passing table names to a sql query(DBI)

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

Hello Monks,
I was writing a code which needs a table name to be passed as a parameter to the SQL query. In the code $table is supposed to be a variable that is passed to the query in run time. How do we pass the table name as a variable in run time.

 
use DBI;
my $dbh = DBI->connect("dbi:ODBC:PRDB") || die "Can't connect to $data
+_source: $DBI::errstr";
my $sth = $dbh->prepare( q{SELECT * FROM $table}) 
          || die "Can't prepare statement: $DBI::errstr";
my $rc = $sth->execute();
@test = $sth->fetchrow_array;
.....Some display section here.
$dbh->disconnect;
[download]

Thanks.

Comment on Dynamically passing table names to a sql query(DBI) Download Code

Replies are listed 'Best First'.
Re: Dynamically passing table names to a sql query(DBI) by hiseldl (Priest) on Oct 09, 2002 at 14:47 UTC
my $sth = $dbh->prepare( q{SELECT FROM $table}) \|\| die "Can't prepare statement: $DBI::errstr";* ...you have the query single quoted which does not interpolate your $table variable, try this: `my $sth = $dbh->prepare( qq{SELECT * FROM $table}) \|\| die "Can't prepare statement: $DBI::errstr";` [download] ...notice the "qq{" which is double quotes and will interpolate $table. -- hiseldl What time is it? It's Camel Time!	[reply] [d/l]
Re^2: Dynamically passing table names to a sql query(DBI) by adrianh (Chancellor) on Oct 09, 2002 at 19:54 UTC
If you're paranoid, and not certain that the table name can be interpolated directly, you also might want to consider using quote_identifier to quote the table name for the select. Untested code... `my $quoted_table = $dbh->quote_identifier($table); my $sth = $dbh->prepare(qq{SELECT * FROM $quoted_table}) or die "Can't prepare statement: $DBI::errstr";` [download]	[reply] [d/l]
Re: Re: Dynamically passing table names to a sql query(DBI) by Anonymous Monk on Oct 09, 2002 at 15:03 UTC
Thank you so much for the input, it works fine.	[reply]
Re: Dynamically passing table names to a sql query(DBI) by dws (Chancellor) on Oct 09, 2002 at 20:04 UTC
How do we pass the table name as a variable in run time. If you want to specify a table name dynamically, you have to construct it into the query before you prepare the query. Interpolation works just fine for this, though you'd be advised to untaint the table name beforehand, lest someone sneak in something really nasty. If you look around a bit, you'll ocassionally see people doing something like `$sth = $dbh->prepare("select * from ?"); $sth->execute($name);` [download] On the surface, this appears to work (for some databases), but the effect is a coincidence of implementation. DBI/DBD does not make sure that bind parameters are in syntactically valid places in the query. That test is deffered to the database. Some databases don't support query preparation. For these, the DBD arranges to hang on to whatever query you pass to `prepare()`. When you `execute()`, the save query is massaged to expand quoted bind parameters, and is passed to the database for execution. Non valid parameter substitution appears to work, until you target a database that does support query preparation. Then, KaBOOM.	[reply] [d/l] [select]
Re: Dynamically passing table names to a sql query(DBI) by princepawn (Parson) on Oct 11, 2002 at 14:12 UTC
DBIx::Recordset does this quite well and in a database-independant manner.	[reply]