Atif's Blog: September 2007

Friday, September 28, 2007

Refreshing the database connection pool with Apache DBCP

After deploying CAS one problem that cropped up was that after 2 hours Oracle would close the database connection while Apache's DBCP would hold on the connection in its pool unaware what the database has done. Then next time a database query CAS would through the following exception

java.sql.SQLException: ORA-03135: connection lost contact

Our dba suggested we use OCI instead of thin client and he configured ORACLE configuration on the system. However it didn't work. It seems this scenario is not part of the failover, probably because DB responds with a RESET instead of a timeout, so at the connectivity is not lost with the server. I tried looking for a RECONNECT setting for Oracle JDBC but couldn't find it; however, DBCP provides testing of the connection in the pool before using it. So I configured the testing as follows

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:oci:@<DB></value>
</property>
<property name="username">
<value>USERNAME</value>
</property>
<property name="password">
<value>PASSWORD</value>
</property>
<property name="validationQuery" value="select <something> from <table>" />
<property name="testOnBorrow" value="true"/>
</bean>

validationQuery is a quick query that is used to test the connection whenever the connection is borrowed from the pool. If the connection fails then it is removed from the pool and DBCP creates a new connection if it can't find any valid connection. This has been working so far without error. Only overhead is the execution of validationQuery. I would actually prefer to have a RECONNECT option as it would probably be with lower overhead. And for that the search continues.

Sunday, September 16, 2007

Integrating CAS with Perl

At work we are trying to setup CAS (Central Authentication Service from JA-SIG) with Perl and Java. Java worked out fine especially JSPs. Guys are trying to integrate it with acegi framework. So I decided to try out perl. As it turned out JSSE stores the certs in binary format called 'DER' while perl's SSL expects the certs to be in 'PEM' format. So after browsing a bit here is how I managed to get the cert

% openssl s_client -connect myserver:port -showcerts

This produced an output that contained something like

-----BEGIN CERTIFICATE-----
MIICoTCCAgqgAwIBAgIERui89jANBgkqhkiG9w0BAQUFADCBlDELMAkGA1UEBhMC
U0cxEjAQBgNVBAgTCVNpbmdhcG9yZTESMBAGA1UEBxMJU2luZ2Fwb3JlMSYwJAYD
VQQKEx1HZW5vbWUgSW5zdGl0dXRlIG9mIFNpbmdhcG9yZTEVMBMGA1UECxMMQXBw
bGljYXRpb25zMR4wHAYDVQQDExVzc28uZ2lzLmEtc3Rhci5lZHUuc2cwHhcNMDcw
OTEzMDQzMDQ2WhcNMjcwOTA4MDQzMDQ2WjCBlDELMAkGA1UEBhMCU0cxEjAQBgNV
-----END CERTIFICATE-----

I Just copied that into a file called /etc/cacert.pem.

To get things to work, I had to install AuthCAS version 1.3. However, there was an error in the module. It contained a line

unless (defined $xmlRef)

which should have read

unless (defined @xml)

After making the change, the following code ran without any problem


#!/usr/bin/env perl

use AuthCAS;
use CGI;
use CGI::Carp qw( fatalsToBrowser );
use File::Spec::Functions qw(splitpath);

my $q = new CGI();
my ($volume, $directories, $file) = splitpath($0);
my $cas = new AuthCAS(casUrl => "https://server:port",
                    CAFile => "/tmp/cacert.pem",
                      );
my $ticket = $q->param('ticket');
# if no ticket exists then redirect to login
if( $ticket eq "") {
      my $login_url = $cas->getServerLoginURL("http://localhost/cgi-bin/$file");
      print $q->redirect($login_url);
} else {
      my $user = $cas->validateST("http://localhost/cgi-bin/$file", $ticket) or die AuthCAS::get_errors();
print <<END_OF_MESSAGE;
Content-type: text/html

<html>
      <body>
              Hello $user
      </body>
</html>
END_OF_MESSAGE
}

Friday, September 7, 2007

Perl Module Browser in Perl/tk

I was interested in using Tk to develop some GUI application. As I was playing with Perl/tk I realised that there was no GUI based application for me to browse perl module installations on the system, so I decided to build one and try out Perl/tk widgets as well. I found the initial code on the web and modified it to suit my purpose.


#!/usr/bin/env perl
use strict;
use Tk;
use Tk::HList;
use Tk::BrowseEntry;
use Tk::Dialog;
use ExtUtils::Packlist;
use ExtUtils::Installed;

my $mw = MainWindow->new;
my $inst = ExtUtils::Installed->new();
my $dropdown;
my @modules;
my $dropdown_default_msg = "-- Choose a Module --";

# Mainwindow: sizex/y, positionx/y
$mw->geometry("600x300+100+120");
my $top = $mw->Frame()->pack(-anchor=>'n', -expand=>'yes', -fill=>'both');
my $bottom = $mw->Frame()->pack(-anchor=>'n');
# Default value
&create_datagrid($top, $bottom, $inst);
MainLoop;

sub create_datagrid {
    my $mother = $_[0];
    my $bottom = $_[1];
    my $inst = $_[2];

    # Create dropdown to choose module
    my $dropdown_value;
    $dropdown = $mother->BrowseEntry(
        -label    => "Choose Module",
        -variable => \$dropdown_value,
 # populate the dropdown list
 -listcmd => sub {
  if($#modules != 0 ) {
   $dropdown->delete(0, $#modules);
  }
  @modules = sort($inst->modules());
  foreach (@modules) {
   $dropdown->insert('end', $_);
  }
            }
     )->pack(-anchor=>'n');

    my $grid = $mother->Scrolled(
        'HList',
        -columns    => 1,
        -scrollbars => 'osoe',
        -width      => 40,
        -height     => 10,
        -background => 'white',
     )->pack(-anchor=>'n', -expand=>'yes', -fill=>'both');
    # Set the initial value for the dropdown
    $dropdown_value = $dropdown_default_msg;
    
    # Configure dropdown what to do when sth is selected
    $dropdown->configure(
        # What to do when an entry is selected
        -browsecmd => sub {
  my @values = sort($inst->files($dropdown_value));
  $grid->delete('all'); # delete currently displayed values
  my $i = 1;
  foreach(@values) {
   $grid->add($i);
   $grid->itemCreate($i++, 0, -text => $_);
  }
  $grid->add($i);
  $grid->itemCreate($i, 0,
    -text => $inst->packlist($dropdown_value)->packlist_file()
  );
        },
    );

    my $delete_button = $bottom->Button(-text=>'Delete',
   -command => sub {
    my @button_text = ("Cancel", "Continue >>");
    if($dropdown_value ne $dropdown_default_msg) {
     my $confirm = $mw->Dialog(-title => "Delete $dropdown_value?",
     -text => "This action shall remove $dropdown_value from the filesystem",
     -default_button => $button_text[0],
     -buttons => \@button_text);
     my $value = $confirm->Show();
     if($value eq $button_text[1]) {
      foreach(sort($inst->files($dropdown_value))) {
       unlink $_;
      }
      unlink $inst->packlist($dropdown_value)->packlist_file();
      $dropdown_value = $dropdown_default_msg;
      $grid->delete('all');
      undef $inst;
      $inst = ExtUtils::Installed->new();     }
    }
       }
   )->grid(-column => 0, -row => 0);
    my $exit_button = $bottom->Button(-text=>'Exit',
   -command=> sub {exit;},
   )->grid(-column => 1, -row => 0);
}

Tuesday, September 4, 2007

Installing Perl DBD::Oracle against 10g

I just installed oracle instantclient and perl DBD against 10g. Following was my configuration

OS: Fedora 7

Make sure that you have libstdc++.so.5 installed. It can be installed as follows
$ yum install compat-libstdc++-33

Oracle Instant client: 10.2.0.3

this constitutes of the following 3 rpms that you can get from oracle's website

oracle-instantclient-basic-10.2.0.3-1.i386.rpm

oracle-instantclient-devel-10.2.0.3-1.i386.rpm

oracle-instantclient-sqlplus-10.2.0.3-1.i386.rpm

(I believe you can skip the sqlplus installation, but I installed it).

DBD::Oracle: 1.19

Verify that the oracle installation is working by typing the following on your command prompt

$ sqlplus

You should get the following output

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 30 00:14:22 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name:

Set the following environment variables

$ export LD_LIBRARY_PATH = /usr/lib/oracle/10.2.0.3/client/lib/
$ export ORACLE_SID = <SID>
$ export ORACLE_USERID = <username>/<password>@<servername>/<SID>

I had problems with ORACLE_HOME environment variable. When it was set to /usr/lib/oracle/10.2.0.3/client/lib/ I would get the following error message from sqlplus

Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

removing the variable ORACLE_HOME did the trick.

I downloaded DBD::Oracle from CPAN

$ perl -MCPAN -e shell

cpan> get Oracle::DBD
cpan> quit

change directory to ~/.cpan/build/DBD-Oracle-x.xx and run the following command

$ perl Makefile.PL -m /usr/share/oracle/10.2.0.3/client/demo.mk
$ make
$ make test

Following is the output of the test runs

t/01base................ok
t/10general.............ok
t/15nls.................ok
t/20select..............ok
t/21nchar............... Database and client versions and character sets:
Database 10.2.0.1.0 CHAR set is AL32UTF8 (Unicode), NCHAR set is AL16UTF16 (Unicode)
Client 10.2.0.3 NLS_LANG is '', NLS_NCHAR is ''
t/21nchar...............ok
t/22nchar_al32utf8......ok
t/22nchar_utf8..........ok
t/23wide_db.............ok
t/23wide_db_8bit........ok
t/23wide_db_al32utf8....ok
t/24implicit_utf8.......ok
t/25plsql...............ok
t/26exe_array...........ok
t/30long................ok
122/470 skipped: various reasons
t/31lob.................ok 7/9DBD::Oracle::db selectrow_array failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 103 in '
SELECT cache_lobs + nocache_lobs AS temp_lob_count
FROM v$temporary_lobs templob, <*>v$session sess
WHERE sess.sid = templob.sid
AND sess.audsid = userenv('sessionid') ')
[for Statement "SELECT cache_lobs + nocache_lobs AS temp_lob_count
FROM v$temporary_lobs templob, v$session sess
WHERE sess.sid = templob.sid
AND sess.audsid = userenv('sessionid') "] at t/31lob.t line 58.
t/31lob.................NOK 9/9
# Failed test 'no temp lobs left'
# at t/31lob.t line 166.
# got: undef
# expected: '0'
# Looks like you failed 1 test of 9.
t/31lob.................dubious
Test returned status 1 (wstat 256, 0x100)
DIED. FAILED test 9
Failed 1/9 tests, 88.89% okay
t/40ph_type.............ok 3/19 Placeholder behaviour for ora_type=1 (the default) varies with
Oracle version. Oracle 7 didn't strip trailing spaces, Oracle 8 did, until 9.2.x Your system doesn't. If that seems odd, let us know.
t/40ph_type.............ok
t/50cursor..............ok
t/55nested..............ok
t/60reauth..............ORACLE_USERID_2 not defined. Tests skipped.
skipped all skipped: no reason given
t/70meta................ok
Failed Test Stat Wstat Total Fail List of Failed
-------------------------------------------------------------------------------
t/31lob.t 1 256 9 1 9
1 test and 122 subtests skipped.
Failed 1/20 test scripts. 1/1062 subtests failed.
Files=20, Tests=1062, 455 wallclock secs ( 3.60 cusr + 11.08 csys = 14.68 CPU)
Failed 1/20 test programs. 1/1062 subtests failed.
make: *** [test_dynamic] Error 255

To install the module run

$ make install

Perl module was good to go.

Atif's Blog