- Added menus to GPDB web
- Tested and documented access of Oracle databases, such as GPDB, using standard Perl and standard Oracle libraries from various architectures
Joe Ramey had worked on getting the Oracle Linux client API library to work with Perl. I am trying to validate that this works on all supported architectures where all supported architectures is considered the subset of:
- Solaris (e.g. Stashu)
- Linux 32-bit (e.g. Fad01)
- Linux 64-bit AMD Opteron processor (e.g. DrTeeth)
- Linux 64-bit Intel processor (e.g. Fad11)
How to properly code Perl to access an Oracle DB
Normal access to SQL databases in Perl usually consists of use'ing two different Perl modules. First a DBI.pm module which is the DataBase Independent interface and the DBD or DataBase Driver module for the particular SQL database involved. We are interested in Oracle here but others include mSQL, MySQL, etc.
Couple this with the fact that we wish to use the standard Perl (e.g. /apps/perl/5.8.3/bin/perl) and standard Perl modules (/apps/perl/modules-<datecode> where datecode is some combination of a year/month date code whose apparent LCD seems to be modules-0412).
So first our Perl script should start with:
#!/apps/perl/5.8.3/bin/perl use strict; use warnings; # Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm use lib "/apps/perl/modules-0412/lib";
So as the comment indicates we are picking up the DBI.pm module from here. Next, normally, we'd simply include the lib path for the Oracle DBD.pm (or in the special case of Oracle, the Oracle.pm file) as well as the supporting shared library Oracle.so (which is architecturally dependent) by coding the following:
# Perl/Oracle libraries use lib "/apps/oracle/perl/10.2.0.1.0/lib";
However, since Linux client support is not included in /apps/oracle/perl/10.2.0.1.0/lib (yet) we need to pick up Joe's stuff:
# Linux API is in Ramey's home dir... use lib "/home/ramey/oracle/perl/10.2.0.1.0/lib";
The next excerpt from my code, shown in it's entirety later, shows a lib path I used to use that allows Solaris clients to work correctly, however we wish to move away from this special cased library thus it's commented out here:
# This is a non standard Oracle #use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";
As the full code listing that follows shows I then use my current development libraries and attempt to log into the development version of GPDB, and Oracle database, and retrieve and display some rudimentary data:
Entire code listing of ~x0062320/testgpdb.pl
#!/apps/perl/5.8.3/bin/perl
use strict;
use warnings;
# Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm
use lib "/apps/perl/modules-0412/lib";
# Perl/Oracle libraries
use lib "/apps/oracle/perl/10.2.0.1.0/lib";
# Linux API is in Ramey's home dir...
use lib "/home/ramey/oracle/perl/10.2.0.1.0/lib";
# This is a non standard Oracle
#use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";
# Current development GPDB modules
use lib "/web/gpdb/cgi-bin";
use GPDB::gpdb;
# Log into GPDB - read only
gpdb_login (undef, undef, "OracleDevelopment");
# Get a project
my @projects = gpdb_getProject ("uma");
# Play with it a little bit...
my %project = %{$projects [0][0]};
print "Project: ${project {PROJECT}{NAME}}\n";
print "Site: ${project {PROJECT}{SITE_NAME}}\n";
print "Vobs:\n";
foreach (@{$project {CLEARCASE}}) {
my %cc = %{$_};
print "\t${cc {VOB_TAG}}\n";
} # foreach
print "done\n";
Results
Here's the results running on the target architectures:
$ ssh stashu testgpdb.pl
DBI connect('host=dflorad01.itg.ti.com;sid=flddsync;port=1521','cm_gpdb_readonly',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc. at /web/gpdb/cgi-bin/GPDB/DBHelper.pm line 136
Could not connect to database. Check $ORACLE_HOME. at /web/gpdb/cgi-bin/GPDB/primitive.pm line 81
$ ssh fad01 testgpdb.pl
Project: uma
Site: Dallas
Vobs:
/cdb/dspumatdl
/cdb/uma
/cdb/uma_2.2
/cdb/uma_2.6a
done
$ ssh drteeth testgpdb.pl
Project: uma
Site: Dallas
Vobs:
/cdb/dspumatdl
/cdb/uma
/cdb/uma_2.2
/cdb/uma_2.6a
done
$ ssh fad11 testgpdb.pl
Can't load
'/home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so' for module DBI: /lib/libc.so.6: version `GLIBC_2.3' not found (required by
/home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so) at /apps/perl/5.8.3/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229.
at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259
BEGIN failed--compilation aborted at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259.
Compilation failed in require at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
Compilation failed in require at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
Compilation failed in require at ./testgpdb.pl line 20.
BEGIN failed--compilation aborted at ./testgpdb.pl line 20.
So as we can see this is not working for Solaris or Linux 64-bit Intel.