Application object and DB handle

["John D. Leonard II" <jo...@ce.gatech.edu>]

All:

I use DBI and PostgresSQL on Apache 1.3.20, mod_perl 1.25, and Apache::ASP
2.17.

What is the "best" way to create and dispose of DB handles?  Are there
implementation or performance gotcha's between the following alternatives:

A) Should I create and destroy a single global variable (e.g., $dbh) in the
Application_OnStart and _OnEnd.

B) Should each $Session create and destroy it's own DB handle stored in the
$Session object?

C) Should each script create and destroy it's own handle in the
Script_OnStart and _OnEnd?

D) Should I use the approach offered by Apache::DBI?

Thanks!

JL
------
John D. Leonard II, Associate Professor            Phone: 404/894-2360
School of Civil and Environmental Engineering       FAX:  404/894-2278
Georgia Institute of Technology           http://traffic.ce.gatech.edu
Atlanta, GA  30332-0355              mailto:john.leonard@ce.gatech.edu


---------------------------------------------------------------------
To unsubscribe, e-mail: asp-unsubscribe@perl.apache.org
For additional commands, e-mail: asp-help@perl.apache.org

Re: Application object and DB handle

[Joshua Chamas <jo...@chamas.com>]

"John D. Leonard II" wrote:
> 
> All:
> 
> I use DBI and PostgresSQL on Apache 1.3.20, mod_perl 1.25, and Apache::ASP
> 2.17.
> 
> What is the "best" way to create and dispose of DB handles?  Are there
> implementation or performance gotcha's between the following alternatives:
> 
> A) Should I create and destroy a single global variable (e.g., $dbh) in the
> Application_OnStart and _OnEnd.
> 

No, the Application events do not reflect the state of the current
web httpd process executing.  Each httpd needs its own connection
to the database, which consist of local RAM data structures and 
file handles, network sockets, etc. that cannot be capture by 
serialization into $Application or $Session.

For this reason the best place to do your db init is in Script_OnStart
like so:

# httpd.conf, always set UseStrict while developing... 
# like "use strict" in perl
PerlSetVar UseStrict 1

# global.asa
use vars qw($Db); # declare global for use in scripts/includes
use Apache::DBI;  # use before DBI
sub Script_OnStart {
  $Db = DBI->connect(..., { RaiseError => 1 }); # always have RaiseError turned on
}

> B) Should each $Session create and destroy it's own DB handle stored in the
> $Session object?
> 

No see above.

> C) Should each script create and destroy it's own handle in the
> Script_OnStart and _OnEnd?
> 

Yes, see above.

> D) Should I use the approach offered by Apache::DBI?
> 

Yes, see above.

The apache httpd web process model doesn't let you do some of the 
neat things you can do under IIS because under IIS everything 
runs under the web process, and under apache, each client
connection is handled by a separate forked child web process.

Even though this can cause some developer inconvenience,
this model greatly increases the stability of the web server,
because you can mess up your current web process in apache,
with some bad code without taking down the entire web server.

Things will change in the Apache 2.0 / mod_perl 2.0 however,
as one will be able to mix multiprocess models with threaded
model, so hopefully soon we will have the best of both worlds.

--Josh
_________________________________________________________________
Joshua Chamas                           Chamas Enterprises Inc.
NodeWorks <- Web Link Checking          Huntington Beach, CA  USA 
http://www.nodeworks.com                1-714-625-4051

---------------------------------------------------------------------
To unsubscribe, e-mail: asp-unsubscribe@perl.apache.org
For additional commands, e-mail: asp-help@perl.apache.org

Re: Application object and DB handle

[Dariusz Pietrzak <da...@ajax.umcs.lublin.pl>]

> What is the "best" way to create and dispose of DB handles?  Are there

> Application_OnStart and _OnEnd.
I believe that would be the most efficient way.

> D) Should I use the approach offered by Apache::DBI?
and that would be the "best".

--
Dariusz Pietrzak
Certified Nobody


---------------------------------------------------------------------
To unsubscribe, e-mail: asp-unsubscribe@perl.apache.org
For additional commands, e-mail: asp-help@perl.apache.org