#! /usr/bin/perl -w
#file_name: ADB.pm
use strict ;
package ADB;
sub new
{
my $invocant = shift ;
my $class = ref ( $invocant ) || $invocant ;
my $self = { airgenDBName=>"airgen_cm_db",
dbaccess=>"/usr/informix/bin/dbaccess",
debugFlag=>0,
m_rowCount=>-1,
m_arrayRefFieldNames=>[],
m_selectStatementExecuted=>0,
@_ } ;
if ( ! -f $self->{dbaccess} )
{
print "$self->{dbaccess} does not exist, exiting....\n" ;
exit ( 1 ) ;
}
if ( "@_" !~ /airgenDBName / )
{
if ( defined ( $ENV{AIRGEN_DB_NAME} ) )
{
$self->{airgenDBName} = $ENV{AIRGEN_DB_NAME} ;
}
}
if ( defined ( $ENV{ADB_DEBUG} ) )
{
$self->{debugFlag} = $ENV{ADB_DEBUG} ;
}
print "Your DB is $self->{airgenDBName}.\n" ;
print "You can do export AIRGEN_DB_NAME= to use your own DB\n" ;
print "You can do export ADB_DEBUG=1 to turn on debug flag\n" ;
bless ($self, $class ) ;
return $self ;
}
sub getSelectResults
{
my $self = shift ( @_ ) ;
my $SQLStatement = shift ( @_ ) ;
if ( $SQLStatement !~ /^\s*SELECT/i &&
$SQLStatement !~ /^\s*EXECUTE/i )
{
print "\nOnly SELECT or EXECUTE statement can be used in this function!\n";
print "ADB is now exiting....\n" ;
exit ( 1 ) ;
}
my @SQLStatementWordsArray = split ( /\s+/, $SQLStatement ) ;
my $SELECTwordsCount = 0 ;
my $EXECUTEwordsCount = 0 ;
for ( my $i = 0 ; $i < @SQLStatementWordsArray ; $i++ )
{
if ( $SQLStatementWordsArray[$i] =~ /^SELECT$/i )
{
$SELECTwordsCount++ ;
}
if ( $SQLStatementWordsArray[$i] =~ /^EXECUTE$/i )
{
$EXECUTEwordsCount++ ;
}
}
if ( $SELECTwordsCount + $EXECUTEwordsCount > 1 )
{
print "\n" ;
print "WARNING: Please submit only one SELECT or EXECUTE PROCEDURE\n" ;
print "at a time when calling this function, otherwise, the query \n" ;
print "results may not be correct!\n" ;
}
my $callingLineNum = shift ( @_ ) || "Unknown calling line number" ;
my @selectResults = `echo '$SQLStatement'|
$self->{dbaccess} $self->{airgenDBName}` ;
#print @selectResults, "\n" ;
if ( ( $? != 0 ) || ( "@selectResults" =~ /^Error\s+/i ) )
{
print "Error when retrieving with $SQLStatement\n" ;
print "Calling line number is at $callingLineNum in script\n" ;
print "Exiting...\n" ;
exit ( 1 ) ;
}
$self->{m_arrayRefFieldNames} = [] ;
chomp ( @selectResults ) ;
if ( $self->{debugFlag} == 1 )
{
for ( my $i = 0 ; $i < @selectResults ; $i++ )
{
print "$i:", $selectResults[$i], ":\n" ;
}
}
my @fieldNames = ( ) ;
my @allValueRecords = ( ) ;
if ( ! defined ( $selectResults[2] ) ) #execute procedure without return
{
return ;
}
elsif ( $selectResults[2] =~ /^$/ ) #values displayed vertically
{ # second line contains nothing
if ( !defined ( $selectResults[3] ) )
{
$self->{m_rowCount} = 0 ;
if ( $self->{debugFlag} == 1 )
{
print "Not any results found for:\n$SQLStatement\n" ;
}
return @allValueRecords ; #undefined this case!!!!
}
#we have records
my $recordNumber = 0 ;
my $fieldNameDone = 0 ;
$allValueRecords[$recordNumber] = [] ; # [] is a reference to
# an array, which is empty now
for ( my $i = 3 ; $i < @selectResults - 1 ; $i++ )
{ #last row is empty and useless
if ( ( $selectResults[$i] =~ /(\w+)\s+(.*)/ ) ||
( $selectResults[$i] =~ /(\w+\+)\s+(.*)/ ) ||
( $selectResults[$i] =~ /(\(.*\))\s+(.*)/ ) #like (constant)
)
{
if ( $fieldNameDone == 0 )
{
my $aFieldName = $1 ;
push ( @fieldNames, $aFieldName ) ;
}
my $aFieldValue = $2 ;
if ( $aFieldValue =~ /^\s+(.*?)\s+$/ )
{
$aFieldValue = $1 ; # to remove leading/trailing spaces
}
push ( @{$allValueRecords[$recordNumber]}, $aFieldValue ) ;
# Note: here we just filled in the empty array pointed to by the
# first element of allValueRecords array
}
elsif ( $selectResults[$i] =~ /^$/ )
{
$recordNumber++ ;
$allValueRecords[$recordNumber] = [] ;
$fieldNameDone = 1 ;
}
}
$self->{m_rowCount} = $recordNumber + 1 ;
@{$self->{m_arrayRefFieldNames}}=@fieldNames ;
return @allValueRecords ;
}
else #values displayed horizontally
{ #second line must have something
if ( $selectResults[4] =~ /^$/ )
{
$self->{m_rowCount} = 0 ;
if ( $self->{debugFlag} == 1 )
{
print "Not any results found for:\n$SQLStatement\n" ;
}
return @allValueRecords ; #undefined this case!!!!
}
#we have records
@fieldNames = split ( /\s+/, $selectResults[2] ) ;
@{$self->{m_arrayRefFieldNames}}=@fieldNames ;
my @lastCharacterIndices = getLastCharacterIndexForEachFieldName (
$selectResults[2] ) ;
my $recordNumber = 0 ;
for ( my $i = 4 ; $i < @selectResults - 1 ; $i++ )
{ #last row is empty and useless
$allValueRecords[$recordNumber] = [] ;
for ( my $j = 0 ; $j < @lastCharacterIndices - 1 ; $j++ )
{ #lastCharacterIndices:-1 10 36 41 57 73
my $aFieldValue = substr ( $selectResults[$i],
$lastCharacterIndices[$j]+1,
$lastCharacterIndices[$j+1] -
$lastCharacterIndices[$j] ) ;
if ( $aFieldValue =~ /^\s+(.*?)\s+$/ )
{
$aFieldValue = $1 ; # to remove leading/trailing spaces
}
push ( @{$allValueRecords[$recordNumber]}, $aFieldValue ) ;
}
$recordNumber++ ;
}
$self->{m_rowCount} = $recordNumber ;
return @allValueRecords ; #can be undefined!!! when rowCount = 0
}
#both if and else return something
}
#private function
sub getLastCharacterIndexForEachFieldName
{
my $string = shift ( @_ ) ;
my $leadingSpaceCount = 0 ;
#before strip leading space
for ( my $i = 0 ; $i < length($string) ; $i++ )
{
if ( substr($string, $i, 1) eq ' ' )
{
next ;
}
else
{
if ( $i == 0 )
{
$leadingSpaceCount = 0 ;
last ;
}
$leadingSpaceCount = $i ;
last ;
}
}
#before strip trailing space
my $trailingSpaceCount = 0 ;
for ( my $i = length($string) -1 ; $i >= 0 ; $i-- )
{
if ( substr ( $string, $i, 1 ) eq ' ' )
{
$trailingSpaceCount++ ;
next ;
}
else
{
if ( $i == ( length ( $string ) - 1 ) )
{
$trailingSpaceCount = 0 ;
}
last ;
}
}
$string = substr ( $string, $leadingSpaceCount,
length($string) - $leadingSpaceCount -
$trailingSpaceCount ) ; #to remove leading
#and trailing spaces
my @lastCharacterIndices = ( -1 ) ;
my @stringArray = split ( /\s+/, $string ) ;
my $stringArrayElementNumber = @stringArray ;
my $previousIndex = $leadingSpaceCount - 1 ;
while ( $stringArrayElementNumber >= 2 )
{
my $index = getLastCharacterIndexForAFieldName ( $string ) ;
# actually lastCharacter ( if it is space ) is not beloing to the
# field name
push ( @lastCharacterIndices, $index + $previousIndex ) ;
$previousIndex = $index + $previousIndex + 1 ;
$string = substr ( $string, $index + 1, length( $string) - $index ) ;
@stringArray = split ( /\s+/, $string ) ;
$stringArrayElementNumber = @stringArray ;
}
push ( @lastCharacterIndices, $previousIndex + length ( $string ) +
$trailingSpaceCount - 1 ) ; #value is last index (0 based) to use.
return @lastCharacterIndices ;
}
# private function
sub getLastCharacterIndexForAFieldName
{ # we must have at least 2 field names to invoke this function
my $string = shift ( @_ ) ;
my $middleSpaceFound = 0 ;
my $characterFound = 0 ;
my $lastCharacterIndex = 0 ;
for ( my $i = 0 ; $i < length ( $string ) ; $i++ )
{
if ( substr ( $string, $i, 1 ) eq ' ' )
{
if ( $characterFound == 1 )
{
$middleSpaceFound = 1 ;
}
$characterFound = 0 ;
next ;
}
else
{
$characterFound = 1 ;
if ( $middleSpaceFound == 0 ) # we have not found a middle space yet
{ # do nothing, continue only
#ignore
}
else #we found a middle space
{
if ( $middleSpaceFound == 1 && $characterFound == 1 )
{
$lastCharacterIndex = $i - 1 ;
last ;
}
}
next ;
}
}
return $lastCharacterIndex ;
}
sub getRowCount
{
my $self = shift ( @_ ) ;
return $self->{m_rowCount} ; # can be -1 if query error,
# or 0 if now row found.
}
sub getFieldNames
{
my $self = shift ( @_ ) ;
my @fieldNames = ( ) ;
for ( my $i = 0 ; $i < @{$self->{m_arrayRefFieldNames}} ; $i++ )
{
if ( ${$self->{m_arrayRefFieldNames}}[$i] =~ /^$/ )
{ }
else
{
push ( @fieldNames, ${$self->{m_arrayRefFieldNames}}[$i] ) ;
}
}
return @fieldNames ; #an array
}
sub changeMyDB
{
my $self = shift ( @_ ) ;
my $consequence = 0 ; # 0 means failure, 1 means success
my $SQLStatement = shift ( @_ ) ;
my $callingLineNum = shift ( @_ ) || "Unknown calling line number" ;
my @executeResults = `echo '$SQLStatement'|
$self->{dbaccess} $self->{airgenDBName}` ;
print @executeResults, "\n" ;
if ( ( $? != 0 ) || ( "@executeResults" =~ /^Error\s+/i ) )
{
print "Error when changing DB with $SQLStatement\n" ;
print "Calling line number is at $callingLineNum in script\n" ;
#print "Error message from dbaccess is:\n" ;
#print @executeResults, "\n" ;
print "Exiting...\n" ;
$consequence = 0 ;
exit ( 1 ) ;
}
$consequence = 1 ;
return $consequence ;
}
1 ;