Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Perl DBI connect cached still active

by newperldeveloper (Sexton)
on Oct 29, 2020 at 00:43 UTC ( [id://11123285]=perlquestion: print w/replies, xml ) Need Help??

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

I am doing a perl query using
my $prod_dbh = DBI->connect_cached('DBI:Sybase:database','web_','web') + || die "Couldn't connect to DB!\n" . DBI->errstr; my $parent_sth = $prod_dbh->prepare_cached('select * from table where +id = ?'); $parent_sth->bind_param(1,$id); $parent_sth->execute(); my @parent_data = $parent_sth->fetchrow_array();
If I try to do another query it fails and give an error
my $child_sth = $prod_dbh->prepare_cached('select * from table2 where +id = ?'); $child_sth->bind_param(1,$parent_data[0]); $child_sth->execute(); my @child_data = $child_sth->fetchrow_array(); print Dumper(@child_data);
Fails with statement handle DBI::st=HASH(0x1ed1280) still Active at When I add the line $parent_sth->finish(); to the first block of code. I get no data from the @child_data If I just grab the query and run it data is returned so I know the result should contain data but nothing is being returned when I dump child_data. I know it is executing the code when I print after the dumper I get my printed content. I was reading I need to

Replies are listed 'Best First'.
Re: Perl DBI connect cached still active
by tybalt89 (Monsignor) on Oct 29, 2020 at 01:53 UTC

    See $if_active in perldoc DBI

    check line marked HERE in this sample of code ( since you didn't provide a Short, Self-Contained, Correct Example )

    #!/usr/bin/perl use strict; # https://perlmonks.org/?node_id=11123285 use warnings; use DBI; my $dbfile = 'db.11123285'; unlink $dbfile; my $db = DBI->connect_cached( "DBI:SQLite(RaiseError=>1,PrintError=>0):$dbfile"); eval { $db->do('create table note (id integer primary key, ts int, mess tex +t)'); $db->do('insert into note (ts, mess) values (?, ?)', {}, 1, 'one'); $db->do('insert into note (ts, mess) values (?, ?)', {}, 2, 'two'); }; #system "sqlite3 $dbfile .dump"; my $sth = $db->prepare_cached('select * from note where id = ?'); $sth->bind_param(1, 2); $sth->execute; my @data = $sth->fetchrow_array; use Data::Dump 'dd'; dd \@data; $sth = $db->prepare_cached('select * from note where id = ?', {}, 1); +# HERE $sth->bind_param(1, 1); $sth->execute; @data = $sth->fetchrow_array; use Data::Dump 'dd'; dd \@data; unlink $dbfile; # cleanup

    Outputs:

    [2, 2, "two"] [1, 1, "one"]

    Note that the "still Active" message does not prevent correct execution, so something else must be causing your lack of data.

      When I send in the same parameter to the second query I am getting 0 rows. Which is impossible because the first returns data. I did get the message statement handle DBI::st=HASH(0x20e3278) still Active, that is the reason I used $parent_sth->finish() thinking that would clear anything up. It gets rid of the message but the second query does not execute.

        Maybe the (Sybase) database driver doesn't like only half-fetching the resultset? Have you tried fetching the complete resultset and then issuing your second query? Something like:

        my $prod_dbh = DBI->connect_cached('DBI:Sybase:database','web_','web') + || die "Couldn't connect to DB!\n" . DBI->errstr; my $parent_sth = $prod_dbh->prepare_cached('select * from table where +id = ?'); $parent_sth->bind_param(1,$id); $parent_sth->execute(); my $parent_data = $parent_sth->fetchall_arrayref(); ... my $child_sth = $prod_dbh->prepare_cached('select * from table2 where +id = ?'); $child_sth->bind_param(1,$parent_data[0]); $child_sth->execute(); my $child_data = $child_sth->fetchall_arrayref(); print Dumper($child_data->[0]);

        Of course, with your statement handles named $parent_data and $child_data, maybe you are trying to fetch data in a parent/child relationship where you instead could be doing an SQL JOIN between the two tables to issue only a single query?

        select parent.* , child.* from table parent join table2 child on (parent.id=child.id) where parent.id = ?

        Maybe $parent_data[0] is not what you think it is...

        Try adding RaiseError=>1 like my example showed. Perhaps there is some other error you are not testing for.

        Also try using prepare instead of prepare_cached

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2024-03-29 11:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found