DBI InactiveDestroy problem

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

I'm trying to implement a forking process with DBI and cannot get the database handle attribute InactiveDestroy to work. As you can see below, after I have reaped the child process with waitpid my database handle is not longer valid. Im currently using DBI 1.39 and the docs on it say that this was fixed. See DBI 1.39. I've even tried installing versions all the way up to the current 1.53 and I get the same problems. What am I doing wrong? Is it possible to do this without having to reconnect?

use strict;
use warnings;
use DBI;
use DBU;

print "$$ started\n";

my $dbh = DBI->connect('DBI:Oracle:dev', 'xxx', 'yyy', {PrintError => 
+1});

my $depts_sql = <<EOT;
select d.id deptid
from codes c, dept d
where c.id = ? and nvl(d.cdt,sysdate+1) > sysdate and c.cd = d.id
EOT
my $depts_csr = $dbh->prepare($depts_sql);

deptid($depts_csr);

my $pid = fork;
print "$$ pid = $pid\n";
if (!defined $pid) {
   print "$$ fork error\n";
}
elsif ($pid == 0) {
   print "$$ child\n";
   $dbh->{InactiveDestroy} = 1;
   # do something here in the child process
   exit;
}

deptid($depts_csr); # this call will succeed because the child has not
+ been reaped yet

waitpid $pid, 0;

deptid($depts_csr); # this call will fail

$dbh->disconnect;

sub deptid {
   my ($csr) = @_;
   $csr->execute('WVPLPRT');
   print "depts=";
   while (my ($deptid) = $csr->fetchrow_array) {
      print "$deptid,";
   }
   print "\n";
}
[download]

Here is the output

# perl problem
21754 started
depts=1,10,11,13,14,15,17,18,19,2,20,21,23,27,3,30,5,9,
21754 pid = 21757
21757 pid = 0
21757 child
depts=1,10,11,13,14,15,17,18,19,2,20,21,23,27,3,30,5,9,
DBD::Oracle::st execute failed: ORA-01001: invalid cursor (DBD: oexfet
+ error) at problem line 42.
DBD::Oracle::st fetchrow_array failed: no statement executing (perhaps
+ you need to call execute first) at problem line 44.
depts=
[download]

Comment on DBI InactiveDestroy problem Select or Download Code

Replies are listed 'Best First'.
Re: DBI InactiveDestroy problem by perrin (Chancellor) on Dec 02, 2006 at 17:38 UTC
See if there are more database handles than you realize open. You can check this with the ChildHandles method described in the DBI docs.	[reply]
Re^2: DBI InactiveDestroy problem by mifflin (Curate) on Dec 02, 2006 at 23:45 UTC
I could try that when I get back into work on monday but I've distilled the problem down to the code you see above. What you see is the entire program and I only do one prepare. If I reconnect and reprepare after the waitpid the errors go away. I was hoping to not have to do that.	[reply]
Re^3: DBI InactiveDestroy problem by perrin (Chancellor) on Dec 03, 2006 at 17:38 UTC
Hmm, try setting InactiveDestroy before you fork.	[reply]
Re: DBI InactiveDestroy problem by lbjay (Novice) on Dec 11, 2006 at 21:19 UTC
The DBI perldoc states "Note that some databases, including Oracle, don’t support passing a database connection across a fork." Could that be the problem?	[reply]
Re^2: DBI InactiveDestroy problem by mifflin (Curate) on Dec 14, 2006 at 01:59 UTC
I'm not trying to use the parent's $dbh in the child process. My child process will (in the final program) connect to the database itself, if need be. What I don't want to happen is have the parent's $dbh connection be closed when the child process finishes. The InactiveDestroy attribute is supposed to help here but it does not seem to.	[reply]
Re: DBI InactiveDestroy problem by etcshadow (Priest) on Jun 07, 2007 at 03:04 UTC
Hmmm, actually, that code looks like it should work. I tested it (substituting "select * from dual" for the SQL and my own database), and it works just fine for me. The error you are reporting indicates that something in the child process destroyed the statement handle ($csr), not the database handle. You get exactly this sort of error if you try to re-use the database handle in the child process at the same time as you are using it in the parent. If this were a problem with inactive destroy of the database handle, then the error you would get would not be "invalid cursor" but rather "end-of-file on communication channel". If you doubt this, then comment out the setting of InactiveDestroy. Anyway, I'd look more closely at what you are actually doing in that block of code you allude to as "do something here in child process", as that is almost certainly the actual source of your error (not the database handle DESTROY). `------------ :Wq Not an editor command: Wq` [download]	[reply] [d/l]