RE: Perl DBI->Connect: how to detect a a lost connection



Ravi Malghan wrote:
Hi: I have a script which connects to a database when it starts up

$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;",
"$username", "$password", {AutoCommit => 1});

followed by a while loop which runs a query for this connection at 60
second
intervals. If the database goes down for some reason, I want the
script to
try reconnecting to the database. How do I figure out within the while
look
if the database connection is still valid. If tried using the $dbh
variable
(if ($dbh)then connection is fin. else connection is bad). That
doesn't seem
to work. How do I figure out if the $dbh connection has been lost
within the
while loop?

I suggest you connect to the database every time around the loop instead
of just
once before it. If you use the connect_cached method instead of connect
then the
connection will be verified and used again if it is still valid.

HTH,

Rob


I have seen in other implementations. using a simple "select
sysdate from dual" (which is very fast since it does not query any
table) type of check and trapping the response to see if it is
connected. But, that will take more instructions than simply
re-connecting, don't know how much of a performance thing it will
be versus the "select/check", Though if Rob suggests a re-connect,
he might already have been down that alley.


Stu




--
To unsubscribe, e-mail: beginners-unsubscribe@xxxxxxxx
For additional commands, e-mail: beginners-help@xxxxxxxx
http://learn.perl.org/



Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
.

Relevant Pages

  • ADO Object in VB Script Only Calls SQLGetData for column 1
    ... I try I can only get the script to retrieve the first column of my query. ... Dim adoConnection As ADODB.Connection ... '—Build our connection string to use when we open the connection -- ...
    (microsoft.public.data.ado)
  • Re: DB-Library error 10038
    ... that result set is still associated with the connection. ... inner part of the while loop. ... create the inner query in an array. ...
    (microsoft.public.sqlserver.programming)
  • Re: TCP listener
    ... This loop is required because of the main purpose of the program I am trying ... I am using the push server product's .Net sample and trying to modify it ... that it only works untill I close connection to the c# listener. ... private volatile bool monitorPort = true; ...
    (microsoft.public.dotnet.languages.csharp)
  • SSIS - Error Messages when looping through Excel Files
    ... What's the trick to use a Foreach File in Folder loop when looping through ... Now if I have my XL Connection manager set to a specific file, ...
    (microsoft.public.sqlserver.dts)
  • Re: Changing connection information on OLEDB connection
    ... Sybase OLEDB datasources, it seems to cache one connection so, even ... Loop" event. ... but the second loop around it doesn't seem to be working. ...
    (microsoft.public.sqlserver.dts)