You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Levon Barker <le...@cam.ca> on 2003/10/23 19:00:30 UTC
Is this the right way to do it? - More Data
I have a little more data.
I don't think its the database handler that is giving me trouble. I think
its the statement handler. As some other queries work in the same module
before I get to the statement handler that is breaking.
Should I be making new statement handlers for every new request? Doesn't
seem right.
---------------------------- Original Message ----------------------------
Subject: DBI : Is this the right way to do it?...
From: "Levon Barker" <le...@cam.ca>
Date: Thu, October 23, 2003 12:49
To: modperl@perl.apache.org
--------------------------------------------------------------------------
Hello,
(my last message got cut off)
Can someone verify that this is the right way to use DBI and mod_perl (with
Apache::DBI of course). I get problems where if the user cancels a request
before my module has finished accessing my Oracle db, the module does not
work for any ongoing requests until I restart the Apache server. This is not
real code but an example of how I am putting it all together.
################ START OF CODE SNIPPET ###########
package SomeModuleWhichShowsUpAsaAPage;
my $dbh = DBI->connect ( 'dbi:Oracle:paprodi','test', 'test',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Oracle database connection not made";
my $sql = "select title from industry where id = ?";
my $sth = $dbh->prepare($industrySql);
sub handler {
my $r = shift;
my $id = $r->param('id');
$r->send_http_header('text/html');
print &get_industry_str($id);
return OK;
}
sub get_industry_str {
my $id = shift;
$sth->execute($id);
my $title;
$sth->bind_col( 1, \$title);
$while ($sth->fetch) {
}
return $title;
}
#################### END OF CODE SNIPPET ##################
When a user does cancel the request before the db access has finished, I get
this in my error log the next time a user tries to call the page:
ORA-03113: end-of-file on communication channel
I am worried I havn't got my head arround the shared database handler
concept. Any help would be appreciated.
Thanks,
Levon Barker
Levon Barker
Portfolio Aid
416-413-4053
Re: Is this the right way to do it? - More Data
Posted by Levon Barker <le...@portfolioaid.com>.
Thanks Perrin, your always full of good answers :)
> On Thu, 2003-10-23 at 13:00, Levon Barker wrote:
>> Should I be making new statement handlers for every new request?
>
> You don't have to, but if you don't and the Oracle connection gets lost
> and Apache::DBI reconnects, your stale statement handles will all fail.
> The best thing to do is use prepare_cached. This will use an already
> prepared statement handle if possible.
>
>> package SomeModuleWhichShowsUpAsaAPage;
>>
>> my $dbh = DBI->connect ( 'dbi:Oracle:paprodi','test', 'test',
>> {
>> RaiseError => 1,
>> AutoCommit => 0
>> }
>> ) || die "Oracle database connection not made";
>>
>> my $sql = "select title from industry where id = ?";
>> my $sth = $dbh->prepare($industrySql);
> [...]
>> sub get_industry_str {
>> my $id = shift;
>> $sth->execute($id);
>> my $title;
>> $sth->bind_col( 1, \$title);
>> $while ($sth->fetch) {
>> }
>> return $title;
>> }
>
> Do you load this module from startup.pl or httpd.conf? If you load this
> before apache forks, it will create a database connection and statement
> handle, and then try to use them after the fork. (Your sub that refers
> to $sth is a closure.)
>
> You should not open a database handle before the fork. Instead, open it
> in your handler method, and create a new $sth (with prepare_cached).
> Apache::DBI and prepare_cached will take care of caching these for you.
>
> - Perrin
>
Levon Barker
Portfolio Aid
416-413-4053
Re: Is this the right way to do it? - More Data
Posted by Geoffrey Young <ge...@modperlcookbook.org>.
Perrin Harkins wrote:
> On Thu, 2003-10-23 at 14:42, Geoffrey Young wrote:
>
>>>You don't have to, but if you don't and the Oracle connection gets lost
>>>and Apache::DBI reconnects, your stale statement handles will all fail.
>>>The best thing to do is use prepare_cached. This will use an already
>>>prepared statement handle if possible.
>>
>>I haven't done any of this in a very long time, but last I remember the
>>results of using prepare_cached with Apache::DBI was undefined.
>
>
> The prepare_cached code is very simple and easy to read. It tucks the
> statement handle into a hash. When it gets called again with the same
> SQL it checks the statement to see if it is still active and returns it
> if it is. Otherwise, it prepares a new statement.
>
> I've used prepare_cached with Apache::DBI for years and have never had
> any problems with it. Maybe you're thinking of how it used to die if
> you tried to reuse a statement handle without reading all of its
> results. This is typically caused by a programming mistake, but you can
> call finish() if you want to bail on a statement without reading it all,
> and it was changed from die to warn in DBI a couple of years ago.
geez, it's so easy to get out of touch with these things :)
thanks for the explanation!
--Geoff
Re: Is this the right way to do it? - More Data
Posted by Perrin Harkins <pe...@elem.com>.
On Thu, 2003-10-23 at 14:42, Geoffrey Young wrote:
> > You don't have to, but if you don't and the Oracle connection gets lost
> > and Apache::DBI reconnects, your stale statement handles will all fail.
> > The best thing to do is use prepare_cached. This will use an already
> > prepared statement handle if possible.
>
> I haven't done any of this in a very long time, but last I remember the
> results of using prepare_cached with Apache::DBI was undefined.
The prepare_cached code is very simple and easy to read. It tucks the
statement handle into a hash. When it gets called again with the same
SQL it checks the statement to see if it is still active and returns it
if it is. Otherwise, it prepares a new statement.
I've used prepare_cached with Apache::DBI for years and have never had
any problems with it. Maybe you're thinking of how it used to die if
you tried to reuse a statement handle without reading all of its
results. This is typically caused by a programming mistake, but you can
call finish() if you want to bail on a statement without reading it all,
and it was changed from die to warn in DBI a couple of years ago.
- Perrin
Re: Is this the right way to do it? - More Data
Posted by Geoffrey Young <ge...@modperlcookbook.org>.
> You don't have to, but if you don't and the Oracle connection gets lost
> and Apache::DBI reconnects, your stale statement handles will all fail.
> The best thing to do is use prepare_cached. This will use an already
> prepared statement handle if possible.
I haven't done any of this in a very long time, but last I remember the
results of using prepare_cached with Apache::DBI was undefined.
I guess this has changed? or maybe people now know what the behavior will be :)
--Geoff
Re: Is this the right way to do it? - More Data
Posted by Perrin Harkins <pe...@elem.com>.
On Thu, 2003-10-23 at 13:00, Levon Barker wrote:
> Should I be making new statement handlers for every new request?
You don't have to, but if you don't and the Oracle connection gets lost
and Apache::DBI reconnects, your stale statement handles will all fail.
The best thing to do is use prepare_cached. This will use an already
prepared statement handle if possible.
> package SomeModuleWhichShowsUpAsaAPage;
>
> my $dbh = DBI->connect ( 'dbi:Oracle:paprodi','test', 'test',
> {
> RaiseError => 1,
> AutoCommit => 0
> }
> ) || die "Oracle database connection not made";
>
> my $sql = "select title from industry where id = ?";
> my $sth = $dbh->prepare($industrySql);
[...]
> sub get_industry_str {
> my $id = shift;
> $sth->execute($id);
> my $title;
> $sth->bind_col( 1, \$title);
> $while ($sth->fetch) {
> }
> return $title;
> }
Do you load this module from startup.pl or httpd.conf? If you load this
before apache forks, it will create a database connection and statement
handle, and then try to use them after the fork. (Your sub that refers
to $sth is a closure.)
You should not open a database handle before the fork. Instead, open it
in your handler method, and create a new $sth (with prepare_cached).
Apache::DBI and prepare_cached will take care of caching these for you.
- Perrin