Re: Design Pattern to keep a connection opened?

On 11/5/2011 11:46 PM, Bruce Johnson wrote:
> On Nov 5, 2011, at 3:57 PM, Brandon Phelps wrote:
>
>> Can anyone point me in the right direction for keeping a connection to my database opened?
>>
>> The script I need to write will run 24/7 executing a postgresql stored procedure which inserts records into the database.  The problem I think I might have is using a standard procedural syntax of:
>>
>> Open connection
>> Loop
>>     Call stored procedure/function
>>
>> ...if my connection gets interrupted somewhere during the loop any subsequent iterations will obviously fail, so I need a way of reconnecting to the database should the need arise.  Basically something like this (I'm just not sure how to actually code this):
> Why not just open a database connection, run the procedure and close it again? :
>
> Loop
> 	open db
> 	run proc
> 	close db
> End loop
>
> ?
> If the connection is staying open long enough to get stale and be disconnected, then you shouldn't be running into timing issues.
>
>
>

The application's loop iterates fairly fast sometimes.  I didn't think 
it would be very efficient to keep opening and closing the connections.  
Here is what I ended up doing:

my $DB_host = 'myservername';
my $DB_name = 'mydbname';
my $DB_user = 'myusername';
my $DB_pass = 'mypassword';
my $DB_dsn = "dbi:Pg:database=$DB_name;host=$DB_host";

my $dbh;

sub get_database {
     if (defined($dbh) && $dbh->ping) {
         return $dbh;
     } else {
         $dbh = DBI->connect($DB_dsn, $DB_user, $DB_pass);
         return $dbh;
     }
}

sub insert_message {
     my ($field1, $field2, $field3, $field4) = @_;

     my $local_db = get_database();

     $local_db->do("SELECT insert_message(" . $local_db->quote($field1) 
. ", " . $local_db->quote($field2) . ", " . $local_db->quote($field3) . 
", " . $local_db->quote($field4) . ")");
}

# Pseudo-code:
Loop
     insert_message("data", "data", "data", "data");
End Loop


This way I don't have to re-open connections constantly.  Chances are 
the connection will never close anyway, but now at least if I do a 
restart on the postgresql daemon or something I don't have to worry 
about things breaking on the application side.

-- 
Brandon