On Sun, Jan 9, 2011 at 12:04 AM, Puneet Kishor <punk.kish@gmail.com> wrote:

On Saturday, January 8, 2011 at 12:31 PM, Gurunandan Bhat wrote:

On Sat, Jan 8, 2011 at 8:26 PM, Puneet Kishor <punk.kish@gmail.com> wrote:

Gurunandan Bhat wrote:
If I rewrite connect_db as:

my $dbh;
sub connect_db {

if (! defined $dbh) {
$dbh = DBI->connect("dbi:SQLite:dbname=".setting('database')) or
die $DBI::errstr;

}
return $dbh;
}

will the application use a single connection for all connections under
all perl webservers (in-built, mod_perl, Starman, Twiggy etc.).

I am interested in a definitive and clear answer as well. I tried the above method a while back, and kept on getting errors on transactions (I am using SQLite).

Admittedly, this (using a common database handle for all connections) might not neccesarily be a Good Thing precisely because of transaction issues (and consequently slowdowns because of locks) that it leads to.

However one way to handle transaction issues could be to have a per-session database handle. Requestor passes a session id to connect_db, which sees if the session has been allocated a handle and generates a new handle if not. If the session is an old one, it merely picks up the correct previously allocated handle and returns it. If per-session persistence is possible, then you should not have transaction issues.

Is that possible?

heh heh! I have no idea, although, the logic that you describe seems very, well, logical. Should work. I just don't know how to do it.

If someone who is first-hand experienced in this, and especially with different web servers, would do a little write-up, that would be tremendous.

You could try something like this (NOT TESTED):

my $session_dbh;
sub connect_db {

my $session_id = shift;

if (! exists $session_dbh->{$session_id}) {
my $dbh = DBI->connect("dbi:SQLite:dbname=".setting('database')) or
die $DBI::errstr;
$session_dbh->{$session_id} = $dbh;
}
return $session_dbh->{$session_id};
}

--
Puneet Kishor

Sent with Sparrow

Since I am using Apache, I added `PerlModule Apache::DBI` to my httpd.conf, and went back to a simple $dbh creation instead of checking for `defined $dbh`

Knowing the correct way to implement this, perhaps even the way*s* for different servers (I am assuming different web servers might have different ways of implementing persistent connections) would be tremendously helpful.

If that is true, a line to that effect in an appropriate place in the
tutorial would emphasize the persistent features of the solution.

Also, are any hooks run when the application instance is destroyed? If
there are, implementing a graceful closing of open DB connection(s)
might provide a valuable lesson in the Tutorial.

Thank you.