Re: mod_perl2 & DBD::Oracle problem

[Atsushi Fujita]

Hi Geoffrey,

> you don't need to set $ENV{'ORACLE_SID'} set at startup, just
> $ENV{'ORACLE_HOME'} if you make the instance name part of the DBI
> connect string:

That's great!!
I set only $ENV{'ORACLE_HOME'} and $ENV{'NLS_LANG'} in startup.pl .
And I changed my script as following.(no %ENV setting)
This script worked fine!


my $sid  = 'ynt0';

my $dsn  = 'dbi:Oracle:';
my $user = 'username/password';
my $password = '';

$dbh = DBI->connect("$dsn$sid", $user, $password)
 or die "Cannot connect: ".$DBI::errstr;


Thank you for your advice!

Atsushi



- Original Message -
From: "Geoffrey Young" <[EMAIL PROTECTED]>
To: "Atsushi Fujita" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, August 22, 2002 9:20 PM
Subject: Re: mod_perl2 & DBD::Oracle problem


>
>
> Atsushi Fujita wrote:
>
> > Hi all,
> >
> > Few weeks ago, I posted a problem about DBD::Oracle as following.
>
>
> [snip]
>
>
> >
> >
> > # add by atsushi 2002/08/22
> > $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> > $ENV{'ORACLE_SID'}  = 'ynt0';
> > $ENV{'NLS_LANG'}= 'japanese_japan.ja16euc';
> >
> > 1;
> > 
> >
> >
> > But..., I want to change $ENV{'ORACLE_SID'} dynamically, because I have
many
> > DB to connect.
> > Currently my perl program handles the target DB SID by user http request.
> > I hope we can change %ENV for DBD::Oracle on perl script in the future.
> >
> > Thank you Stas for helping this matter!
> >
>
>
> you don't need to set $ENV{'ORACLE_SID'} set at startup, just
> $ENV{'ORACLE_HOME'} if you make the instance name part of the DBI
> connect string:
>
> my $DBASE='ynto';
> my $DBUSER='foo';
> my $DBPASS='bar';
>
>
> my $dbh = DBI->connect("dbi:Oracle:$DBASE", $DBUSER, $DBPASS,
> {RaiseError => 1, AutoCommit => 0, PrintError => 1}) or die
> $DBI::errstr;
>
> see the DBD::Oracle manpage or the cheetah book for more info about
> Oracle connect strings - you can put just about anything in them, even
>   something like
>
> $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521',
> 'scott/tiger', '')
>
> or
>
> $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
>   (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
>   (CONNECT_DATA=(SID=ORCL)))}, "")
>
> HTH
>
> --Geoff
>
>
>
>
>

Re: Mod_perl Application Development

[Russell Lundberg]

>Jonathan Lonsdale wrote:
>> 2. Distinct content handlers each with their own Location directive.
Could
>> be a pain to maintain the server config.

In my company we wanted an application which would make it easy to give
users shared access to data that changed occasionally. The initial
request was to move a weekly status reporting function from word
documents submitted as email attachments from users around the world.
These were manually compiled into another word doc. It didn't scale
well, people often forgot the submission deadline or garbled the word
template, and execs often wanted a mid-week snapshot, which was fairly
impractical, as was comparing sequentiol status reports for follow up or
action. We wanted a web interface, all data stored in a database, and a
limited number of widgets to maintain.

We created several handlers, each of which is for a particular "phase"
of the application: one hanlder provides a CGI input/edit form. another
handler an input verification screen, another for commiting changes to
the underlying database. There also are several handlers for viewing the
data in typical or interesting wasy: an audit handler shows all changes
made against a given table record, a calendar handler for displaying
date-based data on a month-at-a-time calendar view, a charting app, etc.
The underlying data tables get properly represented onscreen by meta
data stored in other tables in the database. The meta data provides
information such as column labels, default sort order, number of rows to
display on a page, which columns to show in the default view, how to
manipulate the raw data (eg, with a perl subroutine.) For related tables
we create a report derived (with perl subs) from columns of the
underlying data tables.

Now when IT receive a request for another web form, we CREATE the
required data table(s) in the database, run an admin script to populate
the meta data tables with reasonable defaults and the user can begin
entering data. This process takes several minutes once we have agreed
the data to be stored. We then modify the defaults as necessary; the
time required for this varies depending upon the way the user wants data
represented onscreen.

>You would typically have a single handler that covers one "application"

>with many screens, so instead of having an entry there for every
>template you would have a just a small number of entries for
>applications like a shopping cart or a message board.  The applications

>then manage which template to send out on each request using their own
>internal logic.  Things like CGI::Application and Apache::PageKit are a

>formalization of this approach.
>
>People often seem to get bent out of shape about putting a few Location

>directives in httpd.conf, but I find it simple to manage and like doing

>this the standard way.  Having Location directives lets you use all of
>the normal Apache config options for access control, customized
logging,
>etc. without inventing your own config system.  There are exceptional
>situations (a requirement to add new handlers without a restart, for
>example), but most people will be just fine doing it the old-fashioned
way.

We started with  directives in the config file but this became
problematic as the number of tables got larger (>35) and occasionally
tables had to be be deleted (eg, because a short term project ended). To
reduce the administrative burden and the server restarts we created a
ChildInitHandler à la Apache::Dispatch which determines the proper
request handler to assign. Each URI includes the name of the desired
data table plus the proper application phase, and any other query string
data.

We haven't seriously thought through the security implications of this
approach, however the server is behind a firewall and all Internet
access is via VPN.

We recently posted the distribution with a demo database to SourceForge
(). The installation is make-based and
pretty straightforward. The module namespace (Apache::AppWrap) should
soon appear in CPAN.

Best regards,

Russell

Re: mod_perl2 & DBD::Oracle problem

[Geoffrey Young]

Atsushi Fujita wrote:

> Hi all,
> 
> Few weeks ago, I posted a problem about DBD::Oracle as following.


[snip]


> 
> 
> # add by atsushi 2002/08/22
> $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> $ENV{'ORACLE_SID'}  = 'ynt0';
> $ENV{'NLS_LANG'}= 'japanese_japan.ja16euc';
> 
> 1;
> 
> 
> 
> But..., I want to change $ENV{'ORACLE_SID'} dynamically, because I have many
> DB to connect.
> Currently my perl program handles the target DB SID by user http request.
> I hope we can change %ENV for DBD::Oracle on perl script in the future.
> 
> Thank you Stas for helping this matter!
> 


you don't need to set $ENV{'ORACLE_SID'} set at startup, just 
$ENV{'ORACLE_HOME'} if you make the instance name part of the DBI 
connect string:

my $DBASE='ynto';
my $DBUSER='foo';
my $DBPASS='bar';


my $dbh = DBI->connect("dbi:Oracle:$DBASE", $DBUSER, $DBPASS,
{RaiseError => 1, AutoCommit => 0, PrintError => 1}) or die 
$DBI::errstr;

see the DBD::Oracle manpage or the cheetah book for more info about 
Oracle connect strings - you can put just about anything in them, even 
  something like

$dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521', 
'scott/tiger', '')

or

$dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
  (CONNECT_DATA=(SID=ORCL)))}, "")

HTH

--Geoff

Re: mod_perl2 & DBD::Oracle problem

[Atsushi Fujita]

Hi all,

Few weeks ago, I posted a problem about DBD::Oracle as following.

> 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.
> 

This error meaned I didn't set Oracle %ENV or wrong.
I set %ENV correctly, and my debug code showed Oracle %ENV correctly.
I couldn't find the reason at this time...


But today I resolved this problem!
The reason was posted by Doug to the dev list. Here it is:

> ---
> the issue is that environ[] is not thread safe.  so mod_perl2 unties %ENV
> from the underlying environ[] array under the perl-script handler.
> i assume the oracle driver or client library uses getenv() (which fetches
> from the environ[] array).  when %ENV is untied from environ[], perl-land
> will see %ENV changes, but C-land will not see the coresponding changes
> in environ[].
> the 'modperl' handler does not untie %ENV from environ[].
>
> so, one should avoid setting %ENV values whenever possible.
> and if it is required, should be done at startup time.
> --


So, I added Oracle %ENV script in my startup.pl as following and it worked
fine!

[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';


# add by atsushi 2002/08/22
$ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
$ENV{'ORACLE_SID'}  = 'ynt0';
$ENV{'NLS_LANG'}= 'japanese_japan.ja16euc';

1;



But..., I want to change $ENV{'ORACLE_SID'} dynamically, because I have many
DB to connect.
Currently my perl program handles the target DB SID by user http request.
I hope we can change %ENV for DBD::Oracle on perl script in the future.

Thank you Stas for helping this matter!


Atsushi


PS : I attache my environment and my perl script.
  - 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

[test1.cgi]
use DBI;

my $dsn  = 'dbi:Oracle:';
my $user = 'username/password';
my $password = '';

$ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1'; # don't work, no
means...
$ENV{'ORACLE_SID'}  = 'ynt0'; # don't work, no means...
$ENV{'NLS_LANG'}= 'japanese_japan.ja16euc'; # don't work, no means...

print "ORACLE_HOME=$ENV{'ORACLE_HOME'}\n";
print "ORACLE_SID=$ENV{'ORACLE_SID'}\n";
print "NLS_LANG=$ENV{'NLS_LANG'}\n";
print "DSN=$dsn$ENV{'ORACLE_SID'}\n";

$dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
 or die "Cannot connect: ".$DBI::errstr;
...


[httpd.conf]
LoadModule perl_module modules/mod_perl.so
...

PerlRequire "/yopt/httpd-2.0.39_prefork_perl5.6.1normal/conf/startup.pl"
PerlModule ModPerl::Registry

SetHandler perl-script
PerlResponseHandler ModPerl::Registry
PerlOptions +ParseHeaders
Options +ExecCGI







- Original Message -
From: "Stas Bekman" <[EMAIL PROTECTED]>
To: "Atsushi Fujita" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, July 31, 2002 11:18 AM
Subject: Re: mod_perl2 & DBD::Oracle problem


> Atsushi Fujita wrote:
> > 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

Why the content of a main request can't be accessed in a subrequest ?

[imbert]

I try to make an Authentification system.
I have a  PerlAuthenHandler : Authen.pm
And I have a PerlHandler : Login.pm


In Login.pm, after the login and password checking, I have to redirect the
user to his original request.
So if this original request was a POST Request, I have to access
the content.

I'm in a subrequest (because of an internal_redirect) and I try to access
the content of my POST main request like this:

my $prev = $r->prev;
my $content = $prev->content;

I look at the result like this :

print STDERR $content;

but $content is undef !


Then I have try another solution: (also in Login.pm)

my $prev = $r->prev;
my $aprPrev = Apache::Request->instance($prev);
my $content = $aprPrev->param;

but $content is also undef !
It is normal because $aprPrev->param use $prev->content, but when i put this
two following lines in my Authen.pm (PerlAuthenHandler)

my $apr = Apache::Request->instance($r);
$apr->param; 

it work, I can access the content in my Login.pm like this:

my $prev = $r->prev;
my $aprPrev = Apache::Request->instance($prev);
my $content = $aprPrev->param;


So Why the content of a main request can't be accessed in a subrequest ?

And Why i should instance an Apache::Request in the main request,
if I want to acces the content of the main in the subrequest?