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
