You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Fredo Sartori <sa...@spdfraktion.de> on 2002/08/15 09:44:34 UTC
apache2, DBD/Oracle problem
Hi,
I have problems connecting to an Oracle-DB through apache2. A test
script, that works fine when run directly from the shell, fails to
connect to the Orcacle DB, when run through apache. As far as I can see,
the SID is not passed to the DB server.
.....................................
The foolowing message is found in the error log:
[Thu Aug 15 09:25:46 2002] [error] 27824: ModPerl::Registry:
`DBI->connect(sun1) failed: Error while trying to retrieve text for
error ORA-12505 (DBD ERROR: OCIServerAttach) at
/server/apache-dev/perl/test-oracle line 23
'
.......................................
the log file of the Oracle listener on the DB box ahows:
15-AUG-02 09:25:46 *
(CONNECT_DATA=(SID=*)(SERVICE_NAME=sun1.)(CID=(PROGRAM=)(HOST=ollenhauer)(USER=www-adm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.22.250.27)(PORT=53048)) sh * * * 0
......................................
The following versions are used:
apache 2.0.39
mod_perl: 1.99_04
DBI: 1.30
DBD-Oracle: 1.12
.................
#sample script:
#!/usr/local/bin/perl -w
BEGIN {
$ENV{ORACLE_HOME} = '/opt/oracle/app/product/8.1.6';
$ENV{ORACLE_SID} = 'SPD';
}
use CGI;
use DBI;
my $db_name = 'dbi:Oracle:sun1';
my $db_user = 'webuser';
my $db_pass = 'topsecret';
my $q=CGI->new;
my $dbh = DBI->connect($db_name, $db_user, $db_pass,
{LongReadLen => 65534,
RaiseError => 1,
AutoCommit => 0});
my $query = q{
SELECT count (*)
FROM asdb_mgr.stammdaten14
WHERE bis is null };
my $sth = $dbh->prepare($query);
my $rc = $sth->execute();
my $count = $sth->fetchrow_array;
print $q->header;
print "Anzahl der SPD-MdBs: $count\n";
$sth->finish;
$dbh->disconnect;
...............................
httpd.conf:
Alias /perl /server/apache-dev/perl
<IfModule mod_perl.c>
PerlRequire "/server/apache-dev/conf/perl-startup.pl"
<Location /perl>
SetHandler perl-script
PerlResponseHandler ModPerl::Registry
PerlOptions +ParseHeaders
Options +ExecCGI
</Location>
</IfModule>
......................................
perl-startup.sh:
# file:perl-startup.pl
# ---------------
use Apache2 ();
use Apache::compat ();
use lib qw(/server/apache-dev/perl/);
use ModPerl::Util ();
use Apache::RequestRec ();
use Apache::RequestIO ();
use Apache::RequestUtil ();
use Apache::Server ();
use Apache::ServerUtil ();
use Apache::Connection ();
use Apache::Log ();
use APR::Table ();
use ModPerl::Registry ();
use Apache::Const -compile => ':common';
use APR::Const -compile => ':common';
1;
..........................................
Thanks,
Fredo
--
Dr. Fredo Sartori Tel. 030-227-55061
SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
Platz der Republik e-mail: sartori@spdfraktion.de
11011 Berlin
Re: apache2, DBD/Oracle problem
Posted by Fredo Sartori <sa...@spdfraktion.de>.
Hi,
the problem connecting to an oracle DB through mod_perl is solved: the
solution was, to convert the script into a mod_perl handler as it was
suggested by Stas. I adopted the code posted by Atsushi Fujita and it
worked just perfect for me.
So, thank you all for your help.
Fredo
--
Dr. Fredo Sartori Tel. 030-227-55061
SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
Platz der Republik e-mail: sartori@spdfraktion.de
11011 Berlin
Re: apache2, DBD/Oracle problem
Posted by Stas Bekman <st...@stason.org>.
Atsushi Fujita wrote:
> Hi Stas,
>
> I checked using by 'SetHandler modperl', but apache responded no data without
> error.
> Apache log said HTTP response code was 200, but size was 0.
>
> 192.168.1.xxx - - [16/Aug/2002:22:01:51 +0900] "GET /cgi-bin/test1.cgi
> HTTP/1.1" 200 0
>
> What is wrong? I still counldn't understand clearly.
because under 'SetHandler modperl' the script must be written
differently. I guess the easiest is to try the other way around. Take
the mod_perl handler that worked, and change it to be 'SetHandler
perl-script', does it still work?
> Thank you.
>
> Atsushi
>
> ----[/conf/httpd.conf]----
> <IfModule mod_perl.c>
> PerlRequire "/yopt/httpd-2.0.39_prefork_perl5.6.1normal/conf/startup.pl"
>
> PerlModule ModPerl::Registry
> <Location /cgi-bin>
> SetHandler modperl
> PerlResponseHandler ModPerl::Registry
> PerlOptions +ParseHeaders
> Options +ExecCGI
> </Location>
> </IfModule>
> --------
>
>
> ----[/conf/startup.pl]----
> use Apache2 ();
>
> use lib qw(/yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin);
>
> # enable if the mod_perl 1.0 compatibility is needed
> # use Apache::compat ();
>
> use ModPerl::Util (); #for CORE::GLOBAL::exit
>
> use Apache::RequestRec ();
> use Apache::RequestIO ();
> use Apache::RequestUtil ();
>
> use Apache::Server ();
> use Apache::ServerUtil ();
> use Apache::Connection ();
> use Apache::Log ();
>
> use APR::Table ();
>
> use ModPerl::Registry ();
>
> use Apache::Const -compile => ':common';
> use APR::Const -compile => ':common';
>
> 1;
> --------
>
>
> ----[/cgi-bin/test1.cgi]----
> #!/yopt/perl5.6.1normal/bin/perl
> use strict;
> use DBI;
>
> my $dsn = 'dbi:Oracle:';
> my $user = 'username/password';
> my $password = '';
>
> my $dbh;
> my $sth;
>
> my $sql = "select SEQUENCE_OWNER, SEQUENCE_NAME, LAST_NUMBER from
> ALL_SEQUENCES";
>
> my $rv;
> my @row;
>
> #################
>
> print "Content-type: text/html\n\n";
>
> $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> $ENV{'ORACLE_SID'} = 'ynt0';
> $ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
>
> print "ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n";
> print "ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n";
> print "NLS_LANG=$ENV{'NLS_LANG'}<br>\n";
> print "DSN=$dsn$ENV{'ORACLE_SID'}<br>\n";
>
> $dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
> or die "Cannot connect: ".$DBI::errstr;
>
> $sth = $dbh->prepare($sql)
> or die "Cannot prepare: ".$dbh->errstr();
>
> $rv = $sth->execute
> or die "Cannot execute: ".$sth->errstr();
>
> print "sth=$sth,rv=$rv\n";
>
> while(@row = $sth->fetchrow_array){
> print "@row\n";
> }
>
> $sth->finish();
> $dbh->disconnect();
>
> exit(0);
> --------
>
>
> ----- Original Message -----
> From: "Stas Bekman" <st...@stason.org>
> To: "Atsushi Fujita" <at...@ynot.co.jp>
> Cc: <mo...@apache.org>
> Sent: Friday, August 16, 2002 6:12 PM
> Subject: Re: apache2, DBD/Oracle problem
>
>
>
>>Atsushi Fujita wrote:
>>
>>>Hi Stas,
>>>
>>>
>>>
>>>>Can you please try to convert the script into a mod_perl handler and
>>>>test again?
>>>
>>>
>>>OK, I just checked it.
>>>The result was everything fine using by mod_perl handler!
>>>There was no problem in my new code.
>>
>> >
>>
>>>...But I want to use ModPerl::Registry, because this is easy to migrate
>>
> from
>
>>>normal CGI script.
>>>Please investigate the reason.
>>
>>I still doubt it's a registry problem. Because you've used different
>>SetHandler's for the registry and the mod_perl handler.
>>
>>I bet that if you set:
>>
>> <Location /perl>
>> SetHandler modperl
>> ^^^^^^^
>> PerlResponseHandler ModPerl::Registry
>> PerlOptions +ParseHeaders
>> Options +ExecCGI
>> </Location>
>>
>>it'll all work.
>>
>>Read:
>>
>
> http://perl.apache.org/docs/2.0/user/config/config.html#mod_perl_2_0_Handlers
>
>>and that will explain the problem.
>>
>>Please let us know if this was indeed the cause.
>>
>>__________________________________________________________________
>>Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
>>http://stason.org/ mod_perl Guide ---> http://perl.apache.org
>>mailto:stas@stason.org http://use.perl.org http://apacheweek.com
>>http://modperlbook.org http://apache.org http://ticketmaster.com
>>
>>
>
>
--
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: apache2, DBD/Oracle problem
Posted by Atsushi Fujita <at...@ynot.co.jp>.
Hi Stas,
I checked using by 'SetHandler modperl', but apache responded no data without
error.
Apache log said HTTP response code was 200, but size was 0.
192.168.1.xxx - - [16/Aug/2002:22:01:51 +0900] "GET /cgi-bin/test1.cgi
HTTP/1.1" 200 0
What is wrong? I still counldn't understand clearly.
Thank you.
Atsushi
----[/conf/httpd.conf]----
<IfModule mod_perl.c>
PerlRequire "/yopt/httpd-2.0.39_prefork_perl5.6.1normal/conf/startup.pl"
PerlModule ModPerl::Registry
<Location /cgi-bin>
SetHandler modperl
PerlResponseHandler ModPerl::Registry
PerlOptions +ParseHeaders
Options +ExecCGI
</Location>
</IfModule>
--------
----[/conf/startup.pl]----
use Apache2 ();
use lib qw(/yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin);
# enable if the mod_perl 1.0 compatibility is needed
# use Apache::compat ();
use ModPerl::Util (); #for CORE::GLOBAL::exit
use Apache::RequestRec ();
use Apache::RequestIO ();
use Apache::RequestUtil ();
use Apache::Server ();
use Apache::ServerUtil ();
use Apache::Connection ();
use Apache::Log ();
use APR::Table ();
use ModPerl::Registry ();
use Apache::Const -compile => ':common';
use APR::Const -compile => ':common';
1;
--------
----[/cgi-bin/test1.cgi]----
#!/yopt/perl5.6.1normal/bin/perl
use strict;
use DBI;
my $dsn = 'dbi:Oracle:';
my $user = 'username/password';
my $password = '';
my $dbh;
my $sth;
my $sql = "select SEQUENCE_OWNER, SEQUENCE_NAME, LAST_NUMBER from
ALL_SEQUENCES";
my $rv;
my @row;
#################
print "Content-type: text/html\n\n";
$ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
$ENV{'ORACLE_SID'} = 'ynt0';
$ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
print "ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n";
print "ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n";
print "NLS_LANG=$ENV{'NLS_LANG'}<br>\n";
print "DSN=$dsn$ENV{'ORACLE_SID'}<br>\n";
$dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
or die "Cannot connect: ".$DBI::errstr;
$sth = $dbh->prepare($sql)
or die "Cannot prepare: ".$dbh->errstr();
$rv = $sth->execute
or die "Cannot execute: ".$sth->errstr();
print "sth=$sth,rv=$rv\n";
while(@row = $sth->fetchrow_array){
print "@row\n";
}
$sth->finish();
$dbh->disconnect();
exit(0);
--------
----- Original Message -----
From: "Stas Bekman" <st...@stason.org>
To: "Atsushi Fujita" <at...@ynot.co.jp>
Cc: <mo...@apache.org>
Sent: Friday, August 16, 2002 6:12 PM
Subject: Re: apache2, DBD/Oracle problem
> Atsushi Fujita wrote:
> > Hi Stas,
> >
> >
> >>Can you please try to convert the script into a mod_perl handler and
> >>test again?
> >
> >
> > OK, I just checked it.
> > The result was everything fine using by mod_perl handler!
> > There was no problem in my new code.
> >
> > ...But I want to use ModPerl::Registry, because this is easy to migrate
from
> > normal CGI script.
> > Please investigate the reason.
>
> I still doubt it's a registry problem. Because you've used different
> SetHandler's for the registry and the mod_perl handler.
>
> I bet that if you set:
>
> <Location /perl>
> SetHandler modperl
> ^^^^^^^
> PerlResponseHandler ModPerl::Registry
> PerlOptions +ParseHeaders
> Options +ExecCGI
> </Location>
>
> it'll all work.
>
> Read:
>
http://perl.apache.org/docs/2.0/user/config/config.html#mod_perl_2_0_Handlers
> and that will explain the problem.
>
> Please let us know if this was indeed the cause.
>
> __________________________________________________________________
> Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
> http://stason.org/ mod_perl Guide ---> http://perl.apache.org
> mailto:stas@stason.org http://use.perl.org http://apacheweek.com
> http://modperlbook.org http://apache.org http://ticketmaster.com
>
>
Re: apache2, DBD/Oracle problem
Posted by Stas Bekman <st...@stason.org>.
Atsushi Fujita wrote:
> Hi Stas,
>
>
>>Can you please try to convert the script into a mod_perl handler and
>>test again?
>
>
> OK, I just checked it.
> The result was everything fine using by mod_perl handler!
> There was no problem in my new code.
>
> ...But I want to use ModPerl::Registry, because this is easy to migrate from
> normal CGI script.
> Please investigate the reason.
I still doubt it's a registry problem. Because you've used different
SetHandler's for the registry and the mod_perl handler.
I bet that if you set:
<Location /perl>
SetHandler modperl
^^^^^^^
PerlResponseHandler ModPerl::Registry
PerlOptions +ParseHeaders
Options +ExecCGI
</Location>
it'll all work.
Read:
http://perl.apache.org/docs/2.0/user/config/config.html#mod_perl_2_0_Handlers
and that will explain the problem.
Please let us know if this was indeed the cause.
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: apache2, DBD/Oracle problem
Posted by Atsushi Fujita <at...@ynot.co.jp>.
Hi Stas,
> Can you please try to convert the script into a mod_perl handler and
> test again?
OK, I just checked it.
The result was everything fine using by mod_perl handler!
There was no problem in my new code.
...But I want to use ModPerl::Registry, because this is easy to migrate from
normal CGI script.
Please investigate the reason.
Thank you very much.
Atsushi
PS: I attach my conf and code using by mod_perl handler.
----[/conf/httpd.conf]----
<IfModule mod_perl.c>
PerlRequire "/yopt/httpd-2.0.39_prefork_perl5.6.1normal/conf/startup.pl"
# Perl module Auto-Reloading
PerlModule Apache::Reload
PerlInitHandler Apache::Reload
PerlSetVar ReloadAll Off
PerlSetVar ReloadModules "MyApache::* ModPerl::* Apache::*"
PerlModule MyApache::test3
<Location /MyApache/test3>
SetHandler modperl
PerlResponseHandler MyApache::test3
</Location>
</IfModule>
--------
----[/conf/startup.pl]----
use Apache2 ();
use lib qw(/yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin);
# enable if the mod_perl 1.0 compatibility is needed
# use Apache::compat ();
use ModPerl::Util (); #for CORE::GLOBAL::exit
use Apache::RequestRec ();
use Apache::RequestIO ();
use Apache::RequestUtil ();
use Apache::Server ();
use Apache::ServerUtil ();
use Apache::Connection ();
use Apache::Log ();
use APR::Table ();
use ModPerl::Registry ();
use Apache::Const -compile => ':common';
use APR::Const -compile => ':common';
1;
--------
----[/cgi-bin/MyApache/test3.pm]----
package MyApache::test3;
use strict;
use Apache::Const -compile => qw(OK);
use DBI;
sub handler {
my $r = shift;
my $dsn = 'dbi:Oracle:';
my $user = 'username/password';
my $password = '';
my $dbh;
my $sth;
my $sql = "select SEQUENCE_OWNER, SEQUENCE_NAME, LAST_NUMBER from
ALL_SEQUENCES";
my $rv;
my @row;
$r->content_type('text/html');
$ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
$ENV{'ORACLE_SID'} = 'ynt0';
$ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
$r->print("Now is: " . scalar(localtime) . "<br>\n");
$r->print("ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n");
$r->print("ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n");
$r->print("NLS_LANG=$ENV{'NLS_LANG'}<br>\n");
$r->print("DSN=$dsn$ENV{'ORACLE_SID'}<br>\n");
$dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
or die "Cannot connect: ".$DBI::errstr;
$sth = $dbh->prepare($sql)
or die "Cannot prepare: ".$dbh->errstr();
$rv = $sth->execute
or die "Cannot execute: ".$sth->errstr();
$r->print("sth=[$sth],rv=[$rv]<br>\n");
while(@row = $sth->fetchrow_array){
$r->print("@row<br>\n");
}
$sth->finish();
$dbh->disconnect();
$r->print("<br>Finished!!<br>\n");
return Apache::OK;
}
1;
--------
----- Original Message -----
From: "Stas Bekman" <st...@stason.org>
To: "Atsushi Fujita" <at...@ynot.co.jp>
Cc: "Lyle Brooks" <br...@deseret.com>; "Fredo Sartori"
<sa...@heinemann.spdfraktion.de>; <mo...@apache.org>
Sent: Friday, August 16, 2002 11:42 AM
Subject: Re: apache2, DBD/Oracle problem
> Atsushi Fujita wrote:
> > Hi Lyle,
> >
> >
> >>I does seem to be an ORACLE environment issue.
>
> Can you please try to convert the script into a mod_perl handler and
> test again? I doubt this is registry problem, but probably something
> that about setting up the env for mod_perl in general.
>
> What would help most is to write a test that exposes this problem. I
> don't have Oracle, so if you could help me reproduce the problem, I'd
> know what to fix.
>
> Thanks!
>
> __________________________________________________________________
> Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
> http://stason.org/ mod_perl Guide ---> http://perl.apache.org
> mailto:stas@stason.org http://use.perl.org http://apacheweek.com
> http://modperlbook.org http://apache.org http://ticketmaster.com
>
>
Re: apache2, DBD/Oracle problem
Posted by Stas Bekman <st...@stason.org>.
Atsushi Fujita wrote:
> Hi Lyle,
>
>
>>I does seem to be an ORACLE environment issue.
Can you please try to convert the script into a mod_perl handler and
test again? I doubt this is registry problem, but probably something
that about setting up the env for mod_perl in general.
What would help most is to write a test that exposes this problem. I
don't have Oracle, so if you could help me reproduce the problem, I'd
know what to fix.
Thanks!
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: apache2, DBD/Oracle problem
Posted by Atsushi Fujita <at...@ynot.co.jp>.
Hi Lyle,
> I does seem to be an ORACLE environment issue.
Yes, I added printing out ORACLE ENV script in Oracle.pm as following.
----[perl5.6.1normal/lib/site_perl/5.6.1/i686-linux/DBD/Oracle.pm] line 259
print "DEBUG DBD[3] : dbh=[$dbh], dbname=[$dbname], user=[$user],
auth=[$auth], attr=[$attr]<br>\n";
for(keys %$attr){
print "attr{$_}=[${$attr}{$_}]<br>\n";
}
for(keys %ENV){
print "ENV{$_}=[$ENV{$_}]<br>\n";
}
# Call Oracle OCI logon func in Oracle.xs file
# and populate internal handle data.
DBD::Oracle::db::_login($dbh, $dbname, $user, $auth, $attr)
or return undef;
--------
It showed correct ORACLE ENV and $dbh and so on, but it couldn't connect at
next DBD::Oracle::db::_login(). It showed errror of ORACLE_HOME and NLS
settings...
But it worked fine on normal CGI-script.
The printed out %ENV from there were as following on mod_perl2 and normal
CGI-script.
----[%ENV on mod_perl2]----
ENV{GATEWAY_INTERFACE}=[CGI-Perl/1.1]
ENV{SERVER_SOFTWARE}=[Apache/2.0.39 (Unix) mod_perl/1.99_04-dev Perl/v5.6.1]
ENV{MOD_PERL}=[mod_perl/1.99_04-dev]
ENV{ORACLE_HOME}=[/u01/app/oracle/product/9.0.1]
ENV{ORACLE_SID}=[ynt0]
ENV{NLS_LANG}=[japanese_japan.ja16euc]
--------
----[%ENV on normal CGI-script]----
ENV{GATEWAY_INTERFACE}=[CGI/1.1]
ENV{SERVER_SOFTWARE}=[Apache/2.0.39 (Unix)]
ENV{ORACLE_HOME}=[/u01/app/oracle/product/9.0.1]
ENV{ORACLE_SID}=[ynt0]
ENV{NLS_LANG}=[japanese_japan.ja16euc]
--------
Maybe I should check more deep area...
By the way, is there anyone who is working DBD Oracle under mod_perl2?
Thank you.
Atsushi
----- Original Message -----
From: "Lyle Brooks" <br...@deseret.com>
To: "Atsushi Fujita" <at...@ynot.co.jp>
Cc: "Fredo Sartori" <sa...@heinemann.spdfraktion.de>; <mo...@apache.org>
Sent: Thursday, August 15, 2002 11:07 PM
Subject: Re: apache2, DBD/Oracle problem
> I does seem to be an ORACLE environment issue.
>
> IIRC, the original post had the Oracle environment variables set in a
> BEGIN block.
>
> I ran into a similar issue, where I changed ORACLE_HOME and TNS_ADMIN to
> point to a new location. I set these in a BEGIN block in my startup.pl
> file. But the mod_perl code kept going back to the old location.
>
> I finally tracked it down that another section of my code base was setting
> the Oracle environment variables back to their original locations. Once
> I tracked that down and corrected it, I had no more problems. But for
> awhile I thought either mod_perl was loosing it.
>
> So...."it could be" that another script (ie. something loaded in the
> startup.pl or equivalent) is affecting the Oracle environment variables
> in some way. That would be consistent with your observations that the
> script works fine on the command-line, but not in the mod_perl environment.
>
> You might want to print out the Oracle environment variables right before
> you do your connect, just to test this.
>
> Hopefully it's that simple.
>
>
>
> Quoting Atsushi Fujita (atsushi@ynot.co.jp):
> > Hi Fredo,
> >
> > I had a similar problem in similar environment.
> > My simple DBD program worked fine on normal shell and normal apache, but
it
> > didn't work under the mod_perl2.
> > It showed error as following in apache error_log.
> >
> > ----[error_log]----
> > DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
> > ORACLE_HOME and NLS settings etc. at
> > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> > [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot
connect:
> > (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> > NLS settings etc.at
> > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> > --------
> >
> > This looks like a problem of %ENV.
> > So, I inserted debug code in DBD.pm to output %ENV, and it was correct in
> > DBD.pm under mod_perl2.
> > But I didn't check deep C code, I gave up to check it...
> >
> > My machine is as following.
> > - perl5.6.1(non thread)
> > - DBI-1.30
> > - DBD-Oracle-1.12
> > - mod_perl1.99_04(DSO build)
> > - apache2.0.39(prefork)
> > - Oracle9.0.1.3 for Linux in same machine
> >
> > And I attached my last mail.
> > Sorry for being long mail...
> >
> > Thank you.
> >
> > Atsushi
> >
> >
> > ----- Original Message -----
> > From: "Atsushi Fujita" <at...@ynot.co.jp>
> > To: <mo...@perl.apache.org>
> > Sent: Monday, July 29, 2002 8:39 PM
> > Subject: mod_perl2 & DBD::Oracle problem
> >
> >
> > > Hi all,
> > >
> > > I am trying to use DBD::Oracle1.12 on mod_perl2.
> > > But it doesn't work fine.
> > > It shows error as following in error_log at $dbh = DBI->connect.
> > >
> > > ----[error_log]----
> > > DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize.
Check
> > > ORACLE_HOME and NLS settings etc. at
> > > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> > > [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot
> > connect:
> > > (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> > > NLS settings etc.at
> > > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> > > --------
> > >
> > >
> > > ----[test1.cgi]----
> > > use DBI;
> > >
> > > my $dsn = 'dbi:Oracle:';
> > > my $user = 'username/password';
> > > my $password = '';
> > >
> > > $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> > > $ENV{'ORACLE_SID'} = 'ynt0';
> > > $ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
> > >
> > > print "ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n";
> > > print "ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n";
> > > print "NLS_LANG=$ENV{'NLS_LANG'}<br>\n";
> > > print "DSN=$dsn$ENV{'ORACLE_SID'}<br>\n";
> > >
> > > $dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
> > > or die "Cannot connect: ".$DBI::errstr;
> > > ...
> > > --------
> > >
> > > At first, I suspect that the reason is %ENV in this script.
> > > But I set surely, and this output as following.
> > >
> > > ----[Broser output HTML]----
> > > ORACLE_HOME=/u01/app/oracle/product/9.0.1
> > > ORACLE_SID=ynt0
> > > NLS_LANG=japanese_japan.ja16euc
> > > DSN=dbi:Oracle:ynt0
> > >
> > > Internal Server Error
> > > The server encountered an internal error or misconfiguration and was
unable
> > to
> > > complete your request.
> > > ....
> > > --------
> > >
> > > And if I turned off the mod_perl, it works fine on normal CGI-script.
> > > This error occurred only mod_perl.
> > > (I tested mod_perl1.26 & apache 1.3.26, it worked fine...)
> > >
> > > My machine is as following.
> > > - perl5.6.1(non thread)
> > > - DBI-1.30
> > > - DBD-Oracle-1.12
> > > - mod_perl1.99_04(DSO build)
> > > - apache2.0.39(prefork)
> > >
> > >
> > > ----[httpd.conf]----(is this wrong??)
> > > LoadModule perl_module modules/mod_perl.so
> > > .......
> > > <IfModule mod_perl.c>
> > > PerlModule ModPerl::Registry
> > > <Location /cgi-bin>
> > > SetHandler perl-script
> > > PerlResponseHandler ModPerl::Registry
> > > PerlOptions +ParseHeaders
> > > Options +ExecCGI
> > > </Location>
> > > </IfModule>
> > > --------
> > >
> > >
> > > Thanks,
> > >
> > > Atsushi.
> >
> >
> >
> > ----- Original Message -----
> > From: "Fredo Sartori" <sa...@spdfraktion.de>
> > To: <mo...@apache.org>
> > Sent: Thursday, August 15, 2002 4:44 PM
> > Subject: apache2, DBD/Oracle problem
> >
> >
> > > Hi,
> > >
> > > I have problems connecting to an Oracle-DB through apache2. A test
> > > script, that works fine when run directly from the shell, fails to
> > > connect to the Orcacle DB, when run through apache. As far as I can see,
> > > the SID is not passed to the DB server.
> > >
> > > .....................................
> > > The foolowing message is found in the error log:
> > >
> > > [Thu Aug 15 09:25:46 2002] [error] 27824: ModPerl::Registry:
> > > `DBI->connect(sun1) failed: Error while trying to retrieve text for
> > > error ORA-12505 (DBD ERROR: OCIServerAttach) at
> > > /server/apache-dev/perl/test-oracle line 23
> > > '
> > >
> > > .......................................
> > > the log file of the Oracle listener on the DB box ahows:
> > >
> > >
> > > 15-AUG-02 09:25:46 *
> > >
> >
(CONNECT_DATA=(SID=*)(SERVICE_NAME=sun1.)(CID=(PROGRAM=)(HOST=ollenhauer)(USER
> > =www-adm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.22.250.27)(PORT=53048)) sh
* *
> > * 0
> > >
> > > ......................................
> > > The following versions are used:
> > > apache 2.0.39
> > > mod_perl: 1.99_04
> > > DBI: 1.30
> > > DBD-Oracle: 1.12
> > >
> > >
> > > .................
> > > #sample script:
> > > #!/usr/local/bin/perl -w
> > >
> > > BEGIN {
> > > $ENV{ORACLE_HOME} = '/opt/oracle/app/product/8.1.6';
> > > $ENV{ORACLE_SID} = 'SPD';
> > > }
> > >
> > > use CGI;
> > >
> > > use DBI;
> > >
> > >
> > > my $db_name = 'dbi:Oracle:sun1';
> > > my $db_user = 'webuser';
> > > my $db_pass = 'topsecret';
> > >
> > > my $q=CGI->new;
> > >
> > > my $dbh = DBI->connect($db_name, $db_user, $db_pass,
> > > {LongReadLen => 65534,
> > > RaiseError => 1,
> > > AutoCommit => 0});
> > >
> > > my $query = q{
> > > SELECT count (*)
> > > FROM asdb_mgr.stammdaten14
> > > WHERE bis is null };
> > >
> > > my $sth = $dbh->prepare($query);
> > >
> > > my $rc = $sth->execute();
> > >
> > > my $count = $sth->fetchrow_array;
> > >
> > > print $q->header;
> > > print "Anzahl der SPD-MdBs: $count\n";
> > >
> > > $sth->finish;
> > >
> > > $dbh->disconnect;
> > >
> > > ...............................
> > > httpd.conf:
> > >
> > > Alias /perl /server/apache-dev/perl
> > > <IfModule mod_perl.c>
> > >
> > > PerlRequire "/server/apache-dev/conf/perl-startup.pl"
> > >
> > > <Location /perl>
> > > SetHandler perl-script
> > > PerlResponseHandler ModPerl::Registry
> > > PerlOptions +ParseHeaders
> > > Options +ExecCGI
> > > </Location>
> > >
> > > </IfModule>
> > >
> > > ......................................
> > > perl-startup.sh:
> > >
> > > # file:perl-startup.pl
> > > # ---------------
> > > use Apache2 ();
> > > use Apache::compat ();
> > >
> > > use lib qw(/server/apache-dev/perl/);
> > >
> > >
> > > use ModPerl::Util ();
> > > use Apache::RequestRec ();
> > > use Apache::RequestIO ();
> > > use Apache::RequestUtil ();
> > >
> > > use Apache::Server ();
> > > use Apache::ServerUtil ();
> > > use Apache::Connection ();
> > > use Apache::Log ();
> > >
> > > use APR::Table ();
> > >
> > > use ModPerl::Registry ();
> > >
> > > use Apache::Const -compile => ':common';
> > > use APR::Const -compile => ':common';
> > >
> > > 1;
> > >
> > > ..........................................
> > >
> > >
> > > Thanks,
> > >
> > > Fredo
> > >
> > > --
> > > Dr. Fredo Sartori Tel. 030-227-55061
> > > SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
> > > Platz der Republik e-mail: sartori@spdfraktion.de
> > > 11011 Berlin
> > >
> > >
>
Re: apache2, DBD/Oracle problem
Posted by Lyle Brooks <br...@deseret.com>.
I does seem to be an ORACLE environment issue.
IIRC, the original post had the Oracle environment variables set in a
BEGIN block.
I ran into a similar issue, where I changed ORACLE_HOME and TNS_ADMIN to
point to a new location. I set these in a BEGIN block in my startup.pl
file. But the mod_perl code kept going back to the old location.
I finally tracked it down that another section of my code base was setting
the Oracle environment variables back to their original locations. Once
I tracked that down and corrected it, I had no more problems. But for
awhile I thought either mod_perl was loosing it.
So...."it could be" that another script (ie. something loaded in the
startup.pl or equivalent) is affecting the Oracle environment variables
in some way. That would be consistent with your observations that the
script works fine on the command-line, but not in the mod_perl environment.
You might want to print out the Oracle environment variables right before
you do your connect, just to test this.
Hopefully it's that simple.
Quoting Atsushi Fujita (atsushi@ynot.co.jp):
> Hi Fredo,
>
> I had a similar problem in similar environment.
> My simple DBD program worked fine on normal shell and normal apache, but it
> didn't work under the mod_perl2.
> It showed error as following in apache error_log.
>
> ----[error_log]----
> DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
> ORACLE_HOME and NLS settings etc. at
> /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot connect:
> (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> NLS settings etc.at
> /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> --------
>
> This looks like a problem of %ENV.
> So, I inserted debug code in DBD.pm to output %ENV, and it was correct in
> DBD.pm under mod_perl2.
> But I didn't check deep C code, I gave up to check it...
>
> My machine is as following.
> - perl5.6.1(non thread)
> - DBI-1.30
> - DBD-Oracle-1.12
> - mod_perl1.99_04(DSO build)
> - apache2.0.39(prefork)
> - Oracle9.0.1.3 for Linux in same machine
>
> And I attached my last mail.
> Sorry for being long mail...
>
> Thank you.
>
> Atsushi
>
>
> ----- Original Message -----
> From: "Atsushi Fujita" <at...@ynot.co.jp>
> To: <mo...@perl.apache.org>
> Sent: Monday, July 29, 2002 8:39 PM
> Subject: mod_perl2 & DBD::Oracle problem
>
>
> > Hi all,
> >
> > I am trying to use DBD::Oracle1.12 on mod_perl2.
> > But it doesn't work fine.
> > It shows error as following in error_log at $dbh = DBI->connect.
> >
> > ----[error_log]----
> > DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
> > ORACLE_HOME and NLS settings etc. at
> > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> > [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot
> connect:
> > (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> > NLS settings etc.at
> > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> > --------
> >
> >
> > ----[test1.cgi]----
> > use DBI;
> >
> > my $dsn = 'dbi:Oracle:';
> > my $user = 'username/password';
> > my $password = '';
> >
> > $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> > $ENV{'ORACLE_SID'} = 'ynt0';
> > $ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
> >
> > print "ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n";
> > print "ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n";
> > print "NLS_LANG=$ENV{'NLS_LANG'}<br>\n";
> > print "DSN=$dsn$ENV{'ORACLE_SID'}<br>\n";
> >
> > $dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
> > or die "Cannot connect: ".$DBI::errstr;
> > ...
> > --------
> >
> > At first, I suspect that the reason is %ENV in this script.
> > But I set surely, and this output as following.
> >
> > ----[Broser output HTML]----
> > ORACLE_HOME=/u01/app/oracle/product/9.0.1
> > ORACLE_SID=ynt0
> > NLS_LANG=japanese_japan.ja16euc
> > DSN=dbi:Oracle:ynt0
> >
> > Internal Server Error
> > The server encountered an internal error or misconfiguration and was unable
> to
> > complete your request.
> > ....
> > --------
> >
> > And if I turned off the mod_perl, it works fine on normal CGI-script.
> > This error occurred only mod_perl.
> > (I tested mod_perl1.26 & apache 1.3.26, it worked fine...)
> >
> > My machine is as following.
> > - perl5.6.1(non thread)
> > - DBI-1.30
> > - DBD-Oracle-1.12
> > - mod_perl1.99_04(DSO build)
> > - apache2.0.39(prefork)
> >
> >
> > ----[httpd.conf]----(is this wrong??)
> > LoadModule perl_module modules/mod_perl.so
> > .......
> > <IfModule mod_perl.c>
> > PerlModule ModPerl::Registry
> > <Location /cgi-bin>
> > SetHandler perl-script
> > PerlResponseHandler ModPerl::Registry
> > PerlOptions +ParseHeaders
> > Options +ExecCGI
> > </Location>
> > </IfModule>
> > --------
> >
> >
> > Thanks,
> >
> > Atsushi.
>
>
>
> ----- Original Message -----
> From: "Fredo Sartori" <sa...@spdfraktion.de>
> To: <mo...@apache.org>
> Sent: Thursday, August 15, 2002 4:44 PM
> Subject: apache2, DBD/Oracle problem
>
>
> > Hi,
> >
> > I have problems connecting to an Oracle-DB through apache2. A test
> > script, that works fine when run directly from the shell, fails to
> > connect to the Orcacle DB, when run through apache. As far as I can see,
> > the SID is not passed to the DB server.
> >
> > .....................................
> > The foolowing message is found in the error log:
> >
> > [Thu Aug 15 09:25:46 2002] [error] 27824: ModPerl::Registry:
> > `DBI->connect(sun1) failed: Error while trying to retrieve text for
> > error ORA-12505 (DBD ERROR: OCIServerAttach) at
> > /server/apache-dev/perl/test-oracle line 23
> > '
> >
> > .......................................
> > the log file of the Oracle listener on the DB box ahows:
> >
> >
> > 15-AUG-02 09:25:46 *
> >
> (CONNECT_DATA=(SID=*)(SERVICE_NAME=sun1.)(CID=(PROGRAM=)(HOST=ollenhauer)(USER
> =www-adm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.22.250.27)(PORT=53048)) sh * *
> * 0
> >
> > ......................................
> > The following versions are used:
> > apache 2.0.39
> > mod_perl: 1.99_04
> > DBI: 1.30
> > DBD-Oracle: 1.12
> >
> >
> > .................
> > #sample script:
> > #!/usr/local/bin/perl -w
> >
> > BEGIN {
> > $ENV{ORACLE_HOME} = '/opt/oracle/app/product/8.1.6';
> > $ENV{ORACLE_SID} = 'SPD';
> > }
> >
> > use CGI;
> >
> > use DBI;
> >
> >
> > my $db_name = 'dbi:Oracle:sun1';
> > my $db_user = 'webuser';
> > my $db_pass = 'topsecret';
> >
> > my $q=CGI->new;
> >
> > my $dbh = DBI->connect($db_name, $db_user, $db_pass,
> > {LongReadLen => 65534,
> > RaiseError => 1,
> > AutoCommit => 0});
> >
> > my $query = q{
> > SELECT count (*)
> > FROM asdb_mgr.stammdaten14
> > WHERE bis is null };
> >
> > my $sth = $dbh->prepare($query);
> >
> > my $rc = $sth->execute();
> >
> > my $count = $sth->fetchrow_array;
> >
> > print $q->header;
> > print "Anzahl der SPD-MdBs: $count\n";
> >
> > $sth->finish;
> >
> > $dbh->disconnect;
> >
> > ...............................
> > httpd.conf:
> >
> > Alias /perl /server/apache-dev/perl
> > <IfModule mod_perl.c>
> >
> > PerlRequire "/server/apache-dev/conf/perl-startup.pl"
> >
> > <Location /perl>
> > SetHandler perl-script
> > PerlResponseHandler ModPerl::Registry
> > PerlOptions +ParseHeaders
> > Options +ExecCGI
> > </Location>
> >
> > </IfModule>
> >
> > ......................................
> > perl-startup.sh:
> >
> > # file:perl-startup.pl
> > # ---------------
> > use Apache2 ();
> > use Apache::compat ();
> >
> > use lib qw(/server/apache-dev/perl/);
> >
> >
> > use ModPerl::Util ();
> > use Apache::RequestRec ();
> > use Apache::RequestIO ();
> > use Apache::RequestUtil ();
> >
> > use Apache::Server ();
> > use Apache::ServerUtil ();
> > use Apache::Connection ();
> > use Apache::Log ();
> >
> > use APR::Table ();
> >
> > use ModPerl::Registry ();
> >
> > use Apache::Const -compile => ':common';
> > use APR::Const -compile => ':common';
> >
> > 1;
> >
> > ..........................................
> >
> >
> > Thanks,
> >
> > Fredo
> >
> > --
> > Dr. Fredo Sartori Tel. 030-227-55061
> > SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
> > Platz der Republik e-mail: sartori@spdfraktion.de
> > 11011 Berlin
> >
> >
Re: apache2, DBD/Oracle problem
Posted by Atsushi Fujita <at...@ynot.co.jp>.
Hi Fredo,
I had a similar problem in similar environment.
My simple DBD program worked fine on normal shell and normal apache, but it
didn't work under the mod_perl2.
It showed error as following in apache error_log.
----[error_log]----
DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at
/yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
[Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot connect:
(UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
NLS settings etc.at
/yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
--------
This looks like a problem of %ENV.
So, I inserted debug code in DBD.pm to output %ENV, and it was correct in
DBD.pm under mod_perl2.
But I didn't check deep C code, I gave up to check it...
My machine is as following.
- perl5.6.1(non thread)
- DBI-1.30
- DBD-Oracle-1.12
- mod_perl1.99_04(DSO build)
- apache2.0.39(prefork)
- Oracle9.0.1.3 for Linux in same machine
And I attached my last mail.
Sorry for being long mail...
Thank you.
Atsushi
----- Original Message -----
From: "Atsushi Fujita" <at...@ynot.co.jp>
To: <mo...@perl.apache.org>
Sent: Monday, July 29, 2002 8:39 PM
Subject: mod_perl2 & DBD::Oracle problem
> Hi all,
>
> I am trying to use DBD::Oracle1.12 on mod_perl2.
> But it doesn't work fine.
> It shows error as following in error_log at $dbh = DBI->connect.
>
> ----[error_log]----
> DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
> ORACLE_HOME and NLS settings etc. at
> /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot
connect:
> (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> NLS settings etc.at
> /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> --------
>
>
> ----[test1.cgi]----
> use DBI;
>
> my $dsn = 'dbi:Oracle:';
> my $user = 'username/password';
> my $password = '';
>
> $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> $ENV{'ORACLE_SID'} = 'ynt0';
> $ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
>
> print "ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n";
> print "ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n";
> print "NLS_LANG=$ENV{'NLS_LANG'}<br>\n";
> print "DSN=$dsn$ENV{'ORACLE_SID'}<br>\n";
>
> $dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
> or die "Cannot connect: ".$DBI::errstr;
> ...
> --------
>
> At first, I suspect that the reason is %ENV in this script.
> But I set surely, and this output as following.
>
> ----[Broser output HTML]----
> ORACLE_HOME=/u01/app/oracle/product/9.0.1
> ORACLE_SID=ynt0
> NLS_LANG=japanese_japan.ja16euc
> DSN=dbi:Oracle:ynt0
>
> Internal Server Error
> The server encountered an internal error or misconfiguration and was unable
to
> complete your request.
> ....
> --------
>
> And if I turned off the mod_perl, it works fine on normal CGI-script.
> This error occurred only mod_perl.
> (I tested mod_perl1.26 & apache 1.3.26, it worked fine...)
>
> My machine is as following.
> - perl5.6.1(non thread)
> - DBI-1.30
> - DBD-Oracle-1.12
> - mod_perl1.99_04(DSO build)
> - apache2.0.39(prefork)
>
>
> ----[httpd.conf]----(is this wrong??)
> LoadModule perl_module modules/mod_perl.so
> .......
> <IfModule mod_perl.c>
> PerlModule ModPerl::Registry
> <Location /cgi-bin>
> SetHandler perl-script
> PerlResponseHandler ModPerl::Registry
> PerlOptions +ParseHeaders
> Options +ExecCGI
> </Location>
> </IfModule>
> --------
>
>
> Thanks,
>
> Atsushi.
----- Original Message -----
From: "Fredo Sartori" <sa...@spdfraktion.de>
To: <mo...@apache.org>
Sent: Thursday, August 15, 2002 4:44 PM
Subject: apache2, DBD/Oracle problem
> Hi,
>
> I have problems connecting to an Oracle-DB through apache2. A test
> script, that works fine when run directly from the shell, fails to
> connect to the Orcacle DB, when run through apache. As far as I can see,
> the SID is not passed to the DB server.
>
> .....................................
> The foolowing message is found in the error log:
>
> [Thu Aug 15 09:25:46 2002] [error] 27824: ModPerl::Registry:
> `DBI->connect(sun1) failed: Error while trying to retrieve text for
> error ORA-12505 (DBD ERROR: OCIServerAttach) at
> /server/apache-dev/perl/test-oracle line 23
> '
>
> .......................................
> the log file of the Oracle listener on the DB box ahows:
>
>
> 15-AUG-02 09:25:46 *
>
(CONNECT_DATA=(SID=*)(SERVICE_NAME=sun1.)(CID=(PROGRAM=)(HOST=ollenhauer)(USER
=www-adm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.22.250.27)(PORT=53048)) sh * *
* 0
>
> ......................................
> The following versions are used:
> apache 2.0.39
> mod_perl: 1.99_04
> DBI: 1.30
> DBD-Oracle: 1.12
>
>
> .................
> #sample script:
> #!/usr/local/bin/perl -w
>
> BEGIN {
> $ENV{ORACLE_HOME} = '/opt/oracle/app/product/8.1.6';
> $ENV{ORACLE_SID} = 'SPD';
> }
>
> use CGI;
>
> use DBI;
>
>
> my $db_name = 'dbi:Oracle:sun1';
> my $db_user = 'webuser';
> my $db_pass = 'topsecret';
>
> my $q=CGI->new;
>
> my $dbh = DBI->connect($db_name, $db_user, $db_pass,
> {LongReadLen => 65534,
> RaiseError => 1,
> AutoCommit => 0});
>
> my $query = q{
> SELECT count (*)
> FROM asdb_mgr.stammdaten14
> WHERE bis is null };
>
> my $sth = $dbh->prepare($query);
>
> my $rc = $sth->execute();
>
> my $count = $sth->fetchrow_array;
>
> print $q->header;
> print "Anzahl der SPD-MdBs: $count\n";
>
> $sth->finish;
>
> $dbh->disconnect;
>
> ...............................
> httpd.conf:
>
> Alias /perl /server/apache-dev/perl
> <IfModule mod_perl.c>
>
> PerlRequire "/server/apache-dev/conf/perl-startup.pl"
>
> <Location /perl>
> SetHandler perl-script
> PerlResponseHandler ModPerl::Registry
> PerlOptions +ParseHeaders
> Options +ExecCGI
> </Location>
>
> </IfModule>
>
> ......................................
> perl-startup.sh:
>
> # file:perl-startup.pl
> # ---------------
> use Apache2 ();
> use Apache::compat ();
>
> use lib qw(/server/apache-dev/perl/);
>
>
> use ModPerl::Util ();
> use Apache::RequestRec ();
> use Apache::RequestIO ();
> use Apache::RequestUtil ();
>
> use Apache::Server ();
> use Apache::ServerUtil ();
> use Apache::Connection ();
> use Apache::Log ();
>
> use APR::Table ();
>
> use ModPerl::Registry ();
>
> use Apache::Const -compile => ':common';
> use APR::Const -compile => ':common';
>
> 1;
>
> ..........................................
>
>
> Thanks,
>
> Fredo
>
> --
> Dr. Fredo Sartori Tel. 030-227-55061
> SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
> Platz der Republik e-mail: sartori@spdfraktion.de
> 11011 Berlin
>
>
Re: [OT] Re[2]: apache2, DBD/Oracle problem
Posted by Fredo Sartori <sa...@spdfraktion.de>.
Hello Mike,
of course: the error message of the listener is correct: theres is no
SID sent to the DB server. But, if the same script is run from the
command line, the SID is transmitted.
Fredo
Am Don, 2002-08-15 um 11.34 schrieb Mike P. Mikhailov:
> Hello Fredo Sartori,
>
> Hmm... It looks like a TNS error.
>
> TNS-12505 TNS:listener could not resolve SID given in connect descriptor
>
> I think it has nothing to do with mod_perl :(
>
> Thursday, August 15, 2002, 3:10:59 PM, you wrote:
>
> FS> Hello Mike,
>
> FS> the code works from the shell as a regular perl script. The service is
> FS> correctly resolved with tnsping.
>
> FS> Fredo
>
> FS> Am Don, 2002-08-15 um 10.05 schrieb Mike P. Mikhailov:
> >> Hello Fredo Sartori,
> >>
> >> You are connecting to Oracle with service specified in connect
> >> string. Does this service resolves correctly with tnsping ?
> >>
> >> Does this code work correctly as regular perl script ?
> >>
> >> Thursday, August 15, 2002, 1:44:34 PM, you wrote:
> >>
> >> --
> >> WBR, Mike P. Mikhailov
> >> mailto:mike@sibtel.ru
> >>
> >> You can have my Unix system when you pry it from my cold, dead fingers
> >>
> >>
>
>
>
> --
> WBR, Mike P. Mikhailov
> mailto:mike@sibtel.ru
>
> I believe in God, only I spell it Nature.
>
>
--
Dr. Fredo Sartori Tel. 030-227-55061
SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
Platz der Republik e-mail: sartori@spdfraktion.de
11011 Berlin
[OT] Re[2]: apache2, DBD/Oracle problem
Posted by "Mike P. Mikhailov" <mi...@sibtel.ru>.
Hello Fredo Sartori,
Hmm... It looks like a TNS error.
TNS-12505 TNS:listener could not resolve SID given in connect descriptor
I think it has nothing to do with mod_perl :(
Thursday, August 15, 2002, 3:10:59 PM, you wrote:
FS> Hello Mike,
FS> the code works from the shell as a regular perl script. The service is
FS> correctly resolved with tnsping.
FS> Fredo
FS> Am Don, 2002-08-15 um 10.05 schrieb Mike P. Mikhailov:
>> Hello Fredo Sartori,
>>
>> You are connecting to Oracle with service specified in connect
>> string. Does this service resolves correctly with tnsping ?
>>
>> Does this code work correctly as regular perl script ?
>>
>> Thursday, August 15, 2002, 1:44:34 PM, you wrote:
>>
>> --
>> WBR, Mike P. Mikhailov
>> mailto:mike@sibtel.ru
>>
>> You can have my Unix system when you pry it from my cold, dead fingers
>>
>>
--
WBR, Mike P. Mikhailov
mailto:mike@sibtel.ru
I believe in God, only I spell it Nature.
Re: apache2, DBD/Oracle problem
Posted by Fredo Sartori <sa...@spdfraktion.de>.
Hello Mike,
the code works from the shell as a regular perl script. The service is
correctly resolved with tnsping.
Fredo
Am Don, 2002-08-15 um 10.05 schrieb Mike P. Mikhailov:
> Hello Fredo Sartori,
>
> You are connecting to Oracle with service specified in connect
> string. Does this service resolves correctly with tnsping ?
>
> Does this code work correctly as regular perl script ?
>
> Thursday, August 15, 2002, 1:44:34 PM, you wrote:
>
> --
> WBR, Mike P. Mikhailov
> mailto:mike@sibtel.ru
>
> You can have my Unix system when you pry it from my cold, dead fingers
>
>
--
Dr. Fredo Sartori Tel. 030-227-55061
SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
Platz der Republik e-mail: sartori@spdfraktion.de
11011 Berlin
Re: apache2, DBD/Oracle problem
Posted by "Mike P. Mikhailov" <mi...@sibtel.ru>.
Hello Fredo Sartori,
You are connecting to Oracle with service specified in connect
string. Does this service resolves correctly with tnsping ?
Does this code work correctly as regular perl script ?
Thursday, August 15, 2002, 1:44:34 PM, you wrote:
FS> Hi,
FS> I have problems connecting to an Oracle-DB through apache2. A test
FS> script, that works fine when run directly from the shell, fails to
FS> connect to the Orcacle DB, when run through apache. As far as I can see,
FS> the SID is not passed to the DB server.
FS> .....................................
FS> The foolowing message is found in the error log:
FS> [Thu Aug 15 09:25:46 2002] [error] 27824: ModPerl::Registry:
`DBI->>connect(sun1) failed: Error while trying to retrieve text for
FS> error ORA-12505 (DBD ERROR: OCIServerAttach) at
FS> /server/apache-dev/perl/test-oracle line 23
FS> '
FS> .......................................
FS> the log file of the Oracle listener on the DB box ahows:
FS> 15-AUG-02 09:25:46 *
FS> (CONNECT_DATA=(SID=*)(SERVICE_NAME=sun1.)(CID=(PROGRAM=)(HOST=ollenhauer)(USER=www-adm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.22.250.27)(PORT=53048)) sh * * * 0
FS> ......................................
FS> The following versions are used:
FS> apache 2.0.39
FS> mod_perl: 1.99_04
FS> DBI: 1.30
FS> DBD-Oracle: 1.12
FS> .................
FS> #sample script:
FS> #!/usr/local/bin/perl -w
FS> BEGIN {
FS> $ENV{ORACLE_HOME} = '/opt/oracle/app/product/8.1.6';
FS> $ENV{ORACLE_SID} = 'SPD';
FS> }
FS> use CGI;
FS> use DBI;
FS> my $db_name = 'dbi:Oracle:sun1';
FS> my $db_user = 'webuser';
FS> my $db_pass = 'topsecret';
my $q=CGI->>new;
my $dbh = DBI->>connect($db_name, $db_user, $db_pass,
FS> {LongReadLen => 65534,
FS> RaiseError => 1,
FS> AutoCommit => 0});
FS> my $query = q{
FS> SELECT count (*)
FS> FROM asdb_mgr.stammdaten14
FS> WHERE bis is null };
my $sth = $dbh->>prepare($query);
my $rc = $sth->>execute();
my $count = $sth->>fetchrow_array;
print $q->>header;
FS> print "Anzahl der SPD-MdBs: $count\n";
$sth->>finish;
$dbh->>disconnect;
FS> ...............................
FS> httpd.conf:
FS> Alias /perl /server/apache-dev/perl
FS> <IfModule mod_perl.c>
FS> PerlRequire "/server/apache-dev/conf/perl-startup.pl"
FS> <Location /perl>
FS> SetHandler perl-script
FS> PerlResponseHandler ModPerl::Registry
FS> PerlOptions +ParseHeaders
FS> Options +ExecCGI
FS> </Location>
FS> </IfModule>
FS> ......................................
FS> perl-startup.sh:
FS> # file:perl-startup.pl
FS> # ---------------
FS> use Apache2 ();
FS> use Apache::compat ();
FS> use lib qw(/server/apache-dev/perl/);
FS> use ModPerl::Util ();
FS> use Apache::RequestRec ();
FS> use Apache::RequestIO ();
FS> use Apache::RequestUtil ();
FS> use Apache::Server ();
FS> use Apache::ServerUtil ();
FS> use Apache::Connection ();
FS> use Apache::Log ();
FS> use APR::Table ();
FS> use ModPerl::Registry ();
FS> use Apache::Const -compile => ':common';
FS> use APR::Const -compile => ':common';
FS> 1;
FS> ..........................................
FS> Thanks,
FS> Fredo
--
WBR, Mike P. Mikhailov
mailto:mike@sibtel.ru
You can have my Unix system when you pry it from my cold, dead fingers