storing DBI database handle in CGI::Session object ?

spx2 has asked for the wisdom of the Perl Monks concerning the following question:

I've recently wrote a CGI Perl script that is querying a mysql database.
The only problem is that every time it is loaded by the same user a new
database connection needs to be made which takes time.
I thought it would be nice if I could store that database handle in a
CGI::Session object so I can reuse it every time and not need to re-connect
at least until the cookie used by the CGI::Session object expires.

The code I've used is the following:
It's also based on what I've read from CGI::Session official documentation :

  3 use CGI qw/:standard/;
  4 use CGI::Session;
  5 use Data::Dumper;
  6 use DBI;
  7 my $session = new CGI::Session();
  8 
  9 my $dbh;
 10 if($session->param('dbh')) {
 11     $dbh = $session->param('dbh');
 12 } else {
 13     $dbh = DBI->connect("dbi:mysql...connection_string_continues_h
+ere");
 14     $session->param('dbh',$dbh);
 15     $session->save_param();
 16     $session->flush;
 17 };
[download]

So I'm expecting that after these lines are run I get a database handle
fast.I'm not sure if this code is working properly so I've come to ask the
wisdom of the monks to see how to do this?

Also,not really intentionally this arises the question What is really a database handle or
in short a dbh ?
What kind of information does it store ?
What is it really ?

Thank you

Comment on storing DBI database handle in CGI::Session object ? Download Code

Replies are listed 'Best First'.
Re: storing DBI database handle in CGI::Session object ? by perrin (Chancellor) on Feb 13, 2008 at 00:44 UTC
You can't serialize database handles. They are wrappers around C libraries and contain C structures and open sockets. To get persistent database connections, you need to use mod_perl or FastCGI. Plain vanilla CGI can't do it because the perl process is created completely new for every request.	[reply]
Re^2: storing DBI database handle in CGI::Session object ? by spx2 (Deacon) on Feb 13, 2008 at 01:01 UTC
I understand now. My only experience with FastCGI was at installing an application written using Catalyst to run with fastcgi. How do I do this for a single script ? Is there any documentation somewhere describing how to run a single script with FastCGI ?	[reply]
Re^3: storing DBI database handle in CGI::Session object ? by perrin (Chancellor) on Feb 13, 2008 at 01:41 UTC
I'm sure there is, but I don't use FastCGI, so I'm not the best person to ask. I use mod_perl. If you want to learn about mod_perl, there's a lot of documentation at http://perl.apache.org/ and a mailing list that is friendly to newbies. If you want to learn about FastCGI, try looking it up on CPAN or posting a new question here about it.	[reply]
Re^3: storing DBI database handle in CGI::Session object ? by kirillm (Friar) on Feb 15, 2008 at 11:31 UTC
To run CGI scripts persistently, you can also use CGI::SpeedyCGI. With this module, a script can be made persistent by changing `#!/usr/bin/perl` to `#!/usr/local/bin/speedy` (or what the path to the speedy binary is on your system). Then you can keep database handles in that persistent process. -- Kirill	[reply] [d/l] [select]
Re: storing DBI database handle in CGI::Session object ? by runrig (Abbot) on Feb 13, 2008 at 00:50 UTC
No, you can not keep a persistent database handle using CGI. The code you are looking at is for storing session information in a database, not for storing a database handle. You could keep a persistent connection if you use mod_perl and get your program to work under Apache::Registry and use Apache::DBI...but you really ought to figure out if it's really worth the trouble or if you're just prematurely optimizing.	[reply]