Re: Disconnect database connection after idle timeout

On Thu, 13 Nov 2014 17:40:05 -0500
Perrin Harkins <[email protected]> wrote:

> 
> No, that's perfect. Since pnotes gets cleaned up at the end of every
> request, there's no danger of the handle sticking around. I assume you're
> calling DBI->connect() to get the handle in the HeaderParser phase, and
> using Apache::DBI?
> 
> The dangerous thing is to put a DBI handle in a global and just keep using
> it on every request. If you put a DBI handle in a global (or a closure
> variable) make sure you clean it up at the end of every request.
> 

I have a package that returns a DBI handle when called, using connect_cached, without Apache::DBI (see package Marica::db_handle below)

For each request I do :

In the PerlHeaderParserHandler

    my $dbh = Marica::db_handle::get_dbh_data( $database, $r->pnotes('session')->{preferred_datestyle} ) ;

    $r->pnotes( 'dbh' => $dbh ) ;

Then in the PerlResponseHandler

    my $dbh = $r->pnotes('dbh') ;

    my $data_set = $dbh->selectall_arrayref( $sql ) ;

It's very convenient, all my modules use this, and I only have one place to edit the database connection if needed.
If I understand correctly, I could also use Apache::DBI and a persistent connection for a similar result, modulo what you wrote in an earlier message regarding the connection being more explicit (which I don't quite grasp, I'll have to study on that)?

package Marica::db_handle ;

use strict ;

use warnings ;

sub get_dbh_data {

    my $db_name = shift ;

    #paramètre d'affichage des dates; les caches de connection en tiennent compte
    my $preferred_datestyle = shift || 'iso' ;

    my $dbh = DBI->connect_cached( "DBI:Pg:dbname=$db_name", 'www-data', undef, {
	PrintError => 1,
    	RaiseError => 1,
    	AutoCommit => 1,
    	pg_bool_tf => 1,
	private_preferred_datestyle => $preferred_datestyle } )
    
    	or die "Cannot connect to db: $DBI::errstr" ;

    return $dbh ;

 }




-- 
					Salutations, Vincent Veyron

https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats pour le service juridique