Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

DBI InactiveDestroy problem

by mifflin (Curate)
on Dec 02, 2006 at 01:40 UTC ( #587350=perlquestion: print w/replies, xml ) Need Help??

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 deptid from codes c, dept d where = ? and nvl(d.cdt,sysdate+1) > sysdate and = 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"; }
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=

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.
      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.
        Hmm, try setting InactiveDestroy before you fork.
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?
      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.
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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://587350]
Approved by ikegami
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (3)
As of 2020-10-31 23:21 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (291 votes). Check out past polls.