Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

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

by atemon (Chaplain)
on Oct 16, 2007 at 08:06 UTC ( [id://645130]=perlquestion: print w/replies, xml ) Need Help??

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();
I get the error
"Can't use an undefined value as an ARRAY reference at /myprogram.pl l +ine 23."
The line causing error is
$sth2->bind_columns( \( @row2 { @{ $sth2->{ NAME_lc } } } ));
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++; }
    This also failed :(
  • I tried
    while ( $sth->fetch() ){ %record=(%row); push @names, $record{ 'NAME' }; delete $record{ 'NAME' }; $data->{"data_$i"} = \%record; $i++; }
    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.

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;
    --
    Andreas
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.

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__
    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
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 };
    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 };
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://645130]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2024-06-13 07:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.