14 messages in com.mysql.lists.perlMaintaining State/Cached Connections
FromSent OnAttachments
Jason Allocco06 Jun 2001 08:13 
ed phillips06 Jun 2001 09:34 
Jeremy Zawodny06 Jun 2001 10:05 
Dodger06 Jun 2001 12:30 
Todd Finney06 Jun 2001 16:14 
Dave Rolsky08 Jun 2001 00:11 
Jochen Wiedmann08 Jun 2001 01:21 
Todd Finney08 Jun 2001 07:49 
Jochen Wiedmann08 Jun 2001 08:54 
Todd Finney08 Jun 2001 09:58 
Jochen Wiedmann08 Jun 2001 15:18 
Stas Bekman08 Jun 2001 21:36 
Jochen Wiedmann10 Jun 2001 07:04 
Stas Bekman10 Jun 2001 07:32 
Subject:Maintaining State/Cached Connections
From:Dodger (se@aquest.com)
Date:06/06/2001 12:30:34 PM
List:com.mysql.lists.perl

Hello:

I am working on a script to run under mod_perl.

I have researched the Apache::DBI module, and it seems to be able to do what I have in mind. The program I am (re)building takes a large number of hits and I am rebuilding it.

Before I go too far with any specific implementation, I would like to ask the list a question:

The documentation for Apache::DBI indicates that it is useful in that it overrides the connect() and disconnect() methods to force a connect_cached type connection instead, determining if there is a database handle already active to the database in question and, if so, giving back the currently active handle. It verifies that the handle is live by pinging it. If it is dead, it reestablishes it and hands it back off.

My question is this: If I have an active statement handle prepared in a startup file outside the script and available as a package global, will it go dead under mod_perl and Apache::DBI?

Effectively, I have many statements in this script, and would like to keep them as live, usable statement handles ready to be execute()ed at any time, prepared before the script even runs, so that the script can simply run the pre-prepared query. Apache::DBI's documentation indicates that this can be done with database handles, and that it will keep them alive, but it doens't say anything about statement handles.

For better illustration, let's say I had this:

FILE: startup.pl

-- use CGI; use Apache::DBI; $dbh = DBI->connect('DBI:mysql:database','user','password');

$reusable_insert = $dbh->prepare(<<"eosql"); INSERT INTO table1 (col1, col2, col3) VALUES (?, ?, ?) eosql

$reusable_select = $dbh->prepare(<<"eosql"); SELECT col1, col2, col3 FROM table1 LIMIT 5 eosql

--

FILE: program.mp (mod_perl script, as I have too many things that would break if I just ran everything mod_perl)

-- my $cgi = new CGI; print $cgi->header,<<"eohtml"; <html><head><title>Test thing</title></head><body> <p>Last five users said</p> <table border="0"> <tr><th>col1</th><th>col2</th><th>col3</th></tr> eohtml

$reusable_select->execute; my $step = 1; while (my ($col1, $col2, $col3) = $reusable_select->fetchrow_array) { my $colour = $step?'FFFFFF':'DDDDDD'; $step = $step?0:1; print " <tr bgcolor=\"$colour\"><td>$col1</td><td>$col2</td><td>$col3</td></tr>\n";} print "</table>\n"; if (my $col1 = $cgi->param('col1')) { my $col2 = $cgi->param('col2'); my $col3 = $cgi->param('col3'); $reusable_insert->execute($col1,$col2,$col3); print "<p>Stored your entry. Thanks.</p>\n";} print <<"eohtml" and exit; <form method="POST"> <ul><li>col1: <input type="text" name="col1"></li> <li>col2: <input type="text" name="col2"></li> <li>col3: <input type="text" name="col3"></li></ul> <input type="submit"></form></body></html> eohtml

--

I know that on startup everything should be fine and dandy with this... but what if $dbh gets disconnected and reconnected behind the scenes? are the statement handles $reusable_insert and $reusable_select dead?

I can't easily test this, as I would prefer not to bring down the MySQL server to do so.

-- Sean Cannon