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.

Reply via email to