http://qs321.pair.com?node_id=11123286


in reply to Perl DBI connect cached still active

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

[download]

Outputs: 

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

[download]

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

Replies are listed 'Best First'.
Re^2: Perl DBI connect cached still active
by newperldeveloper (Sexton) on Oct 29, 2020 at 11:03 UTC
    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.
[reply]
[d/l]
[select]

      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]);

      [download]

      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 = ?

      [download]
[reply]
[d/l]
[select]

        Corion++ Sybase will fail if you finish an sth when there are unread results. You can override that by setting the syb_flush_finish attribute 

          my $prod_dbh = DBI->connect_cached('DBI:Sybase:database','web_','web
+' {syb_flush_finish => 1})

        [download]
        but that wouldn't be my recommendation. I find most cases of extracting data are better suited by using selectall_arrayref. Sure you lose the re-usability of prepared statements (I only used those if I'm in a loop) and you may have memory issues if the data being returned is huge but I find the vast majority of my use cases are better served by the simplicity of selectall_arrayref: 
          my $parent_data = $prod_dbh->selectall_arrayref('select * from table
+ where id = ?', {Slice => {}}, $id );

        [download]
        -derby
[reply]
[d/l]
[select]

      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

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom