DBI::bind_colums throws error when element of binding hash is deleteed

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

Dear monks, you may think this program a crazy one, but I think it worth to discuss.

I was trying to fetch data from two different tables into separate hashes. I always use 'strict'. I use DBI's function bind_columns with the fancy syntax. The code is

    my $data  = {};
    my $SQL   = "SELECT * FROM my_table ORDER BY order";
    my $sth   = $dbh->prepare( $SQL );
    $sth->execute() or die DBI->errstr;

    my %row;

    $sth->bind_columns( \( @row{ @{ $sth->{ NAME_uc } } } ));
    $i = 1;
    
    while ( $sth->fetch() ){
        push @names, $row{ 'NAME' };
        delete $row{ 'NAME' };             #this line causes problem a
+t 'bind_columns' down there
        $data->{"data_$i"} = { %row };
        $i++;
    }
    $sth->finish();
    
    my $second_data  = {};
    $SQL2    = "SELECT * FROM my_second_table ORDER BY order";
    my $sth2 = $dbh->prepare( $SQL2 );
    $sth2->execute();

    my %row2    = ();

    $sth2->bind_columns( \( @row2 { @{ $sth2->{ NAME_lc } } } )); # Er
+ror due to delete $row{ 'NAME' }; at top
    $i=1;       

    while ( $sth2->fetch() ){
        
        $second_data->{"data_$i"} = { %row2 };
        $i++;

    }
    $sth2->finish();
[download]

I get the error

"Can't use an undefined value as an ARRAY reference at /myprogram.pl l
+ine 23."
[download]

The line causing error is

$sth2->bind_columns( \( @row2 { @{ $sth2->{ NAME_lc } } } ));
[download]

When I comment line delete $row{ 'NAME' }; everything works fine.

I wonder why it throws this error since first set of variables has NO connection with the second set, except that they use same DB handle.

Other Observations I have made are

Deleting data from any other hash does NOT make any problem.

I tried

while ( $sth->fetch() ){
    %record=%row;
    push @names, $record{ 'NAME' };
    delete $record{ 'NAME' };
    $data->{"data_$i"} = { %record };
    $i++;
}
[download]

This also failed :(

I tried

while ( $sth->fetch() ){
    %record=(%row);
    push @names, $record{ 'NAME' };
    delete $record{ 'NAME' };
    $data->{"data_$i"} = \%record;
    $i++;
}
[download]

This also made NO difference

Thanks in advance for the help.

--VC

There are three sides to any argument.....
your side, my side and the right side.

Comment on DBI::bind_colums throws error when element of binding hash is deleteed Select or Download Code

Replies are listed 'Best First'.
Re: DBI::bind_colums throws error when element of binding hash is deleteed by andreas1234567 (Vicar) on Oct 16, 2007 at 08:27 UTC
You might want to add to error handling to your code: `my $sth2 = $dbh->prepare( $SQL2 ) or die $dbh->errstr; my $rv = $sth2->execute() or die $sth->errstr;` [download] -- Andreas	[reply] [d/l]
Re: DBI::bind_colums throws error when element of binding hash is deleteed by perrin (Chancellor) on Oct 16, 2007 at 12:25 UTC
DBI's bind_columns is not just copying data into your hash. It does things at a low-level to efficiently alias the data from the query into the variables you bind to. Modifying variables that you give to bind_columns is essentially not allowed -- you can read them, but that's all. If you want to modify the data, don't use bind_columns. Use fetchrow_hashref or something instead.	[reply]
Re: DBI::bind_colums throws error when element of binding hash is deleteed by tcf03 (Deacon) on Oct 16, 2007 at 12:26 UTC
If I understand you correctly, take a look at tblSync. I wrote this to do table synchronization between heterogeneous databases. I have since added functionality to handle tables w/ no unique keys. I believe that the following code is what you're after. # Set up you db connections - my statment handler is $rsth. # prepare and execute your query etc... __SNIP___ my $fetch = \&fetch; $counter = 0; my @rmaporder = qw\|col1 col2 col3\|; my %rdb; for ( @rmaporder ) { $counter++; $rdb{$_} = undef; $rsth->bind_col( $counter, \$rdb{$_} ); } my $rkey = 'col1'; # Fetch the data and hold in these hashes %RemoteDB = $fetch->($rsth, \%rdb, $rkey); sub fetch { my $sth = shift; my $dbhash = shift; my $key = shift; my %DB; while ( $sth->fetchrow_hashref ) { # Ensure that ALL data is properly quoted # using the dbi->quote method $DB{$dbhash->{$key}}{$_} = $rdbh->quote($dbhash->{$_}) for keys %$dbhash } return %DB or errpt ( 'FATAL', 'Unable to return data from fetch', + 'NA', 'NA' ); } __SNIP__ [download] Ted -- "That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved." --Ralph Waldo Emerson	[reply] [d/l]
Re: DBI::bind_colums throws error when element of binding hash is deleteed by runrig (Abbot) on Oct 16, 2007 at 16:53 UTC
perrin is right above. You can't bind a column, delete the bound variable, then expect it to still be bound. You probably want to do something more like: `my @columns = @{$sth->{NAME_uc}}; $sth->bind_columns(\@row{@columns}); my @data_cols = grep { $_ ne 'NAME' } @columns; ...then later $data->{"data_$i"} = { map { $_ => $row{$_} } @data_cols };` [download] Updated...fixed hashref assignment. Or: `# This only works if NAME is the first column...or you # would have to change the "select *" to select # specific columns in a specific order my @columns = grep { $_ ne 'NAME' } @{$sth->{NAME_uc}}; $sth->bind_columns(\my $name, \@row{@columns}); ...then later push @names, $name; ...and $data->{"data_$i"} = { %row };` [download]	[reply] [d/l] [select]
Re: DBI::bind_colums throws error when element of binding hash is deleteed by atemon (Chaplain) on Oct 17, 2007 at 04:38 UTC
Hi,everybody Thanks for all your help and concern. I have tried the possibilities discussed here and got it working. I copied the hash to another one and operated the new hash leaving the binded hash as it is. Thanks everybody :) Cheers ! --VC There are three sides to any argument..... your side, my side and the right side.	[reply]