Apache::DBI help

[zanzib63]

Hi everyone,
I want to have persistent database connection
I use:
perl v5.8.7
Apache/2.0.55
mod_perl-2.0.2
Apache::DBI 1.01
I change the DB, USER, PSW value for obvious reasons.

Here is my Httpd.conf:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PerlModule Apache2::Status
PerlModule Apache::DBI

PerlRequire startup.pl
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here is my startup.pl:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/usr/local/bin/perl -w

# make sure we are in a sane environment.
$ENV{MOD_PERL} or die "GATEWAY_INTERFACE not Perl!";

use Apache::DBI ();

$Apache::DBI::DEBUG = 2;
Apache::DBI->connect_on_init(DB, USER, PSW,
{AutoCommit => 0, RaiseError => 1, PrintError => 0});

1;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the apache2 error.log:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4627 Apache::DBI PerlChildInitHandler
4628 Apache::DBI PerlChildInitHandler
[Thu Jul 06 11:03:38 2006] [notice] Apache/2.0.55 (Ubuntu)
mod_perl/2.0.2 Perl/v5.8.7 configured -- resuming normal operations
4627 Apache::DBI need ping: yes
4628 Apache::DBI need ping: yes
4627 Apache::DBI new connect to 'DB USER PSW AutoCommit=0
PrintError=0 RaiseError=1 Username=USER'
4628 Apache::DBI new connect to 'DB USER PSW AutoCommit=0
PrintError=0 RaiseError=1 Username=USER'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If i look with Perl-status ([url]http://localhost/perl-status?inc[/url]) I got:

Apache::DBI 1.01
mod_perl2 2.000002
So the 2 modules are loaded.

With mysqladmin processlist, I see the 2 persistent connections:
+----+------+-----------+-----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+-----+---------+------+-------+------------------+
| 5 | root | localhost | ion | Sleep | 398 | |
|
| 6 | root | localhost | ion | Sleep | 398 | |
|
| 7 | root | localhost | | Query | 0 | | show processlist
|
+----+------+-----------+-----+---------+------+-------+------------------+

So everything seems to be alright.
But when i try to make transaction or temporary table with CGI script,
the connection aren't persistent. There is no reconnect: a connection
is created every time even if the connection's parameters are
identical.
Can you help me?

[Sherm Pendley]

"zanzib63" <provencb@iro.umontreal.ca> writes:

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Here is the apache2 error.log:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 4627 Apache::DBI PerlChildInitHandler
> 4628 Apache::DBI PerlChildInitHandler
> [Thu Jul 06 11:03:38 2006] [notice] Apache/2.0.55 (Ubuntu)
> mod_perl/2.0.2 Perl/v5.8.7 configured -- resuming normal operations
> 4627 Apache::DBI need ping: yes
> 4628 Apache::DBI need ping: yes
> 4627 Apache::DBI new connect to 'DB USER PSW AutoCommit=0
> PrintError=0 RaiseError=1 Username=USER'
> 4628 Apache::DBI new connect to 'DB USER PSW AutoCommit=0
> PrintError=0 RaiseError=1 Username=USER'
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Note the PIDs in the first column - that's not a single Apache child process
re-connecting, it's each child establishing its own connection.

> But when i try to make transaction or temporary table with CGI script,
> the connection aren't persistent.

Of course not. CGI is not a persistent environment. The script runs, prints
output, then exits.

sherm--

--
Cocoa programming in Perl: [url]http://camelbones.sourceforge.net[/url]
Hire me! My resume: [url]http://www.dot-app.org[/url]

[zanzib63]

> Of course not. CGI is not a persistent environment. The script runs, prints

> output, then exits.

Sorry if this is a stupid question but how can I use persistent
connection ? Our intranet application run on apache/perl/cgi. There
must be a way to keep persistent connection beetween 2 script call?

[Sherm Pendley]

"zanzib63" <provencb@iro.umontreal.ca> writes:

>> Of course not. CGI is not a persistent environment. The script runs, prints
>> output, then exits.

>
> Sorry if this is a stupid question but how can I use persistent
> connection?

One way is to use Apache::DBI under mod_perl. That keeps your compiled script
in memory instead of launching it as a separate child process for each request,
which is what happens when it's run as a CGI.

sherm--

--
Cocoa programming in Perl: [url]http://camelbones.sourceforge.net[/url]
Hire me! My resume: [url]http://www.dot-app.org[/url]

[Sherm Pendley]

Sherm Pendley <sherm@Sherm-Pendleys-Computer.local> writes:

> "zanzib63" <provencb@iro.umontreal.ca> writes:
>

>>> Of course not. CGI is not a persistent environment. The script runs, prints
>>> output, then exits.

>>
>> Sorry if this is a stupid question but how can I use persistent
>> connection?

>
> One way is to use Apache::DBI under mod_perl. That keeps your compiled script
> in memory instead of launching it as a separate child process for each request,
> which is what happens when it's run as a CGI.

To elaborate a bit - you want to run your application as a mod_perl handler,
or using Apache::Registry. Even though you've installed mod_perl and loaded
Apache::DBI, scripts in /cgi-bin are still run as separate child processes,
even those written in Perl. (That's part of the definition of a CGI, by the
way - if it's not a separate child process, it's not a CGI.)

Apache::Registry is a good way to migrate CGI scripts to mod_perl.

sherm--

--
Cocoa programming in Perl: [url]http://camelbones.sourceforge.net[/url]
Hire me! My resume: [url]http://www.dot-app.org[/url]

[zanzib63]

Thanks for all the info. I really appreciate your help