On Tue, 13 Aug 2002 20:36:10 +0200 (CEST) Michael Spellbrink
<[EMAIL PROTECTED]> wrote:
> In 'Programming the Perl DBI' i have read that its not a good idea to bind
> parameters to the from-clause of a SQL statement.
> The trouble is that i have to do exactly that.
> As an input i have strings $column_name and $owner and a corresponding
> value $value.First,
> I check dba_tables to look up the tables with this column.
It's not that using bind parameters in the FROM clause is a bad idea, it is
just plain not possible. The statement can not be prepared unless the
database engine knows what tables and columns are involved.
>
> my $sql =<<SQL;
> select table_name,
> column_name,
> owner,
> from dba_tables
> where lower(column_name) like ||'\%'|| ? ||'\%'
> SQL
>
> my $sth=$dbh->prepare($sql);
>
> $sth->execute($column_name);
> $sth->bind_column(\$table_name,\$result_column,\$owner);
> while ($sth->fetch)
> {
> push(@table_name,$table_name);
> push(@column_name,$result_column);
> push(@owner,$owner);
> }
>
> Now I want to create select statements for $value in each of the result
> @table_name.
> something like that:
>
> for ($i= 0; $i<@table_name; $i++)
> {
>
> my $sql2="select * from ? where ? = ?";
> my $sth=$dbh->prepare($sql2);
> $sth->execute($table_name[$i],$column_name,$value);
>
> }
You will have to prepare() a separate statement for every table/column
pair. If you save the statement handle in a hash keyed by them, you can
avoid re-preparing the same combination more than once.
For example (not tested):
# Always use strict and check for errors
use strict;
$dbh -> {RaiseError} = 1;
my ( $sth, %sth, @col_name, @col );
my ( $owner, $table, $col );
my $tab_col_h = $dbh -> prepare( <<SQL );
SELECT owner, table_name, column_name
FROM dba_tables
WHERE LOWER( table_name ) LIKE '%' || ? || '%'
SQL
$tab_col_h -> execute( lc( $partial_column_name ) );
$tab_col_h -> bind_columns( \( $owner, $table, $col ) );
while ( $tab_col_h -> fetch ) {
$sth = $sth{$owner}{$table}{$column};
if ( ! defined $sth ) {
$sth = $sth{$owner}{$table}{$column} = $dbh -> prepare(
"SELECT * FROM $owner.$table WHERE $column = ?" );
}
$sth -> execute( $value );
@col_name = @{$sth -> {NAMES}};
$sth -> bind_columns( \( @col[0 .. $#col_names] ) );
while ( $sth -> fetch ) {
# do something with @col and @col_name
}
}
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.