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

Hi Monks,
Below code snippet causing perl program to be hanged, 

my $dbh;
$dbh = dbi->connect_cached(args);
#args has connection string and the options
For Oracle db
#control is not coming here since script seems to be hanging at this p
+oint

[download]
What could be the possible reason?
Thank you.

Replies are listed 'Best First'.
Re: Issue with dbi connect_cached method
by choroba (Cardinal) on Apr 22, 2022 at 13:59 UTC
    If you replace connect_cached with connect, does it start working?

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
[select]

      Hi, Choroba
      Thank you for the revert.
      Yes, "connect" seems to be working instead of connect_cached (i tried this before but didn't work, not sure why).
      Instances of "connect" have been replaced with the "connect_cached", long back in the code to improve the performance I guess.
      Please, note that "connect_cached" is causing an issue only for oracle DB whereas it seems to be working for the SQL server.
      Can you please shed some light on this?

      Thank you

[reply]

        Most likely, the database server has disconnected the database connection and now ->connect_cached tries to reuse the connection but times out. Consider speaking with your database administrator and network administrator after what time the database and the network will drop an existing connection.

        Another problem can be if you use the fork system call. Ideally, DBD::Oracle (and the underlying database library) would detect that and reestablish the connection, but maybe they don't and try to reuse the connection of the parent.

[reply]
[d/l]
Re: Issue with dbi connect_cached method
by Fletch (Bishop) on Apr 22, 2022 at 13:58 UTC

    Sunspots. Or Deep Sevens. Slightly longer shot: Black Lectroids from Planet 10 (check with Dr. Lizardo first, though, on that one). Try jiggling the handle first.

    (You've given such a minimal context with nearly zero details no one is going to be able to guess (unless they're reaaaaaly lucky) what the real problem is. Perhaps you should try calling DBI->trace( 2 ) and enable more diagnostics as noted in TFM?)

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

[reply]
[d/l]
Re: Issue with dbi connect_cached method
by Anonymous Monk on Apr 27, 2022 at 07:31 UTC

    Thank you all,

    I was searching more on this issue, and found that we can use dbh->ping method and then re-connect on the basis of response.

    Do you think using dbh->ping is good way to check if db connnection is active or not.

    please, suggest!

    Regards

[reply]

      Yes, $dbh->ping is a good way to check if a connection is still live.

[reply]
[d/l]

        Thank you for the revert.

        Regards,

[reply]

Back to Seekers of Perl Wisdom