package DBhandler;

$VERSION = 1.00;
use strict;
use DBI;
use Carp;
use Data::Dumper;

{
  #Encapsulated class data

    my %_log_attr =  #      DEFAULT  ACCESSIBILITY REQUIRED
        ( 
          _cache        => [undef,   'read',        undef], 
          _handle       => [undef,   'read',        undef]
        );

    #is a specirfied object attribute accessible in a given mode
    sub _accessible {
      my ($self, $attr, $mode) = @_;
      $_log_attr{$attr}[1] =~ /$mode/
    }

    # Classwide default value for a specified object attributes
    sub _default_for {
      my ($self, $attr) = @_;
      $_log_attr{$attr}[0];
    }

    sub _required {
      my ($self, $attr) = @_;
      $_log_attr{$attr}[2];
    }

    # list of names of all specified object attributes
    sub _standard_keys
    {
      keys %_log_attr;
    }

    sub _get_db_handle {

        my ($self, %args) = @_;
        croak "Must supply username" unless ($args{user});
        croak "Must supply password" unless ($args{pwd});
        croak "Must supply SID"      unless ($args{sid});

        my $dsn  = "DBI:Oracle:".$args{sid};           #set the datase
+t name
        my %attr = ( RaiseError => 1, PrintError => 0 ); #set error ra
+ising and printing 

        # lets go get the handle
        my $handle = DBI->connect($dsn,$args{user},$args{pwd}, \%attr)
+; 
    
        croak "Unable to connect to ".$args{sid} unless ($handle);
        return $handle;
    }

    sub _execute {
        my ($self, %args) = @_;
        my ($retval, %rethash, @retary);

        croak "Invalid statement name (".$args{statement}.") " 
              if (!defined $self->{_cache}{$args{statement}} );

        croak "Must supply a handle to execute the statement on! " 
              if (!defined $args{handle} );

        #prepare the sql for execution
        my $sth = $self->{_db_handle}{$args{handle}}->prepare($self->{
+_cache}{$args{statement}});

        $sth->execute(@{$args{bindvar}});                      #execut
+e the statement
        if ($self->{_cache}{$args{statement}} =~ /^SELECT/i) {
            if (defined $args{hash}) {
                while (my @row = $sth->fetchrow_array ){  #fetch the r
+ows into an array reference
                    push @{$rethash{shift(@row)}}, @row;   #push the a
+rray onto an list to return
                }
                $retval = \%rethash;
            } else {
                while (my $row = $sth->fetchrow_arrayref ){  #fetch th
+e rows into an array reference
                    push @retary, [$row];        #push the array onto 
+an list to return
                }
                $retval = \@retary;               #return a reference 
+to the container  
            } 
        }

        croak "DB error when executing ".$args{statement}." (".$self->
+{_cache}{$args{statement}}.")"
            if (defined $self->{_db_handle}{$args{handle}}->errstr);
        return $retval;

    }
    
}
    
sub new {
    my ($caller, %arg) = @_;
    my $caller_is_obj = ref($caller);
    my $class = $caller_is_obj  || $caller;
    my $self  = bless {}, $class;
    foreach my $attrname ($self->_standard_keys() )
    {
      my ($argname) = ($attrname =~ /^_(.*)/);
      if (exists $arg{$argname})     
          { $self->{$attrname} = $arg{$argname} ;
          }
      elsif ($caller_is_obj)
          { $self->{$attrname} = $caller->{$argname}; 
          }
      else
          { $self->{$attrname} = $self->_default_for($attrname) }
      croak "Must supply value for ".$argname 
         if ($self->_required($attrname) && !$self->{$attrname} ) ;
    }
    croak "Must supply DB handle name" unless ($arg{handle} );
    $self->{_db_handle}{$arg{handle}} = $self->_get_db_handle(%arg); 

    return $self;
}

# This routine will accept a hash containing a statement
# handle (hash key) and statement (hash value)
sub add_sql {

    my ($self, %cache) = @_;
    croak "Must supply a statement to cache " if (!%cache);
    foreach my $ele (keys %cache){
        $self->{_cache}{$ele} = $cache{$ele}; 
    }
    
}

sub execute {
    my ($self, %args) = @_;    
    my $statement;

    croak "SQL statement not found for ".$args{statement} 
          unless ($self->{_cache}{$args{statement}} );

    croak "DB handle not found for ".$args{handle} 
          unless ($self->{_db_handle}{$args{handle}} );

    return $self->_execute(%args);
}

sub fetch_handle {
    my ($self, %args) = @_;
    croak "Must supply SID"  unless ($args{sid});
    croak "Must supply name" unless ($args{handle});
    croak "Must supply user" unless ($args{user});
    croak "Must supply pwd"  unless ($args{pwd});

    $self->{_db_handle}{$args{handle}} = $self->_get_db_handle(%args);
+ 


}

sub DESTROY {
    my $self = shift;
    foreach (keys %{$self->{_db_handle}}) {
        $self->{_db_handle}{$_}->disconnect;
    }
}
1
[download]