Problems with my DB lookup script

Mych has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

Well not MY DB lookup, I inhereted it... I'm new to perl and have been asked to make some changes to an existing script we use.

Originally the script just looked up one field in a single table. I was asked to change this so that it pulled back several fields from several databases.

The lookup sub looks like it can already take several fields in the parameters. Unfortunately the results back are not what I expected. I'm not sure where to go from here...

Below are snippets of relevant code and results from the debug file.

Sub lookup_NTSD_val, Sub lookup and Sub trim

# used to lookup_values from the CSSD database
sub lookup_NTSD_val
{
    LogIt( $fhDEBUG, "=x" x 40 ); # so we can see the start of a new "
+run"
   LogIt( $fhDEBUG,"Entering sub:lookup_NTSD_val args: @_") if $CONFIG
+{DEBUG} > 3;
   my $cols     = shift;  # comma sep
   my $table    = shift;
   my $criteria = shift;

   LogIt( $fhDEBUG,"calling: lookup( $cols, $table, $criteria);") if $
+CONFIG{DEBUG}>1;


   my $NTSDval = lookup($CONFIG{"NTSD_DB_DSN"},  $cols, $table, $crite
+ria);

   LogIt( $fhDEBUG, "lookup_NTSD_val got result \"" . $NTSDval->{$cols
+} . "\"") if $CONFIG{DEBUG}>1;

        if ($NTSDval->{$cols} == ""){ 
            ++$EVENT->{'slots'}->{'NTSD_DATA'};
        }
        LogIt( $fhDEBUG, "NTSD_DATA now equals \"" . $EVENT->{'slots'}
+->{'NTSD_DATA'} . "\"") if $CONFIG{DEBUG}>1;
   
   return( $NTSDval->{ $cols });

}

sub lookup
{
   LogIt( $fhDEBUG, "Entering sub:lookup args: @_") if $CONFIG{DEBUG} 
+> 3;
   my $DSN      = shift;    # comma sep
   my $cols     = shift;  
   my $table    = shift;
   my $criteria = shift;

   my $LookupResult;

   # Let's create the Connection object used to establish the connecti
+on
   #


   my $conn = Win32::OLE->CreateObject('ADODB.Connection');

   # Open a connection using the SQL Server OLE DB Provider

   LogIt( $fhDEBUG, "Opening ADODB connection") if $CONFIG{DEBUG} > 3;

   $conn->Open(<<EOF);
$DSN
EOF

   LogIt( $fhDEBUG, "COMPLETE: Opening ADODB connection") if $CONFIG{D
+EBUG} > 3;
   # changed above line to log status of connection

   # LogIt( $fhDEBUG, $connStatus) if $CONFIG{DEBUG} > 3;

   # End of 07/04/09 changes

   my $sql =<<SQL;
   SELECT $cols
   FROM   $table
   WHERE  $criteria
SQL


   map { LogIt( $fhDEBUG, $_ )} split /\n/, $sql  if $CONFIG{DEBUG} > 
+3;
   my $rs = $conn->Execute($sql);
   LogIt( $fhDEBUG, "COMPLETE: executing SQL") if $CONFIG{DEBUG} > 3;

   if( !defined( $rs ) ){
      LogIt( $fhDEBUG,"something went wrong: ", Win32::OLE->LastError(
+));
      LogIt( $fhERROR, "something went wrong: ", Win32::OLE->LastError
+());
      die "<lookup> died something went wrong: ", Win32::OLE->LastErro
+r(),"\n";
   }
  

   while( ! $rs->EOF) 
   {
      my $i= 0;
      COL:
      foreach my $col (split /,/, $cols)
      {

          next COL unless $col =~ /\w+/;
          LogIt( $fhDEBUG,"getting val for \$col $col") if $CONFIG{DEB
+UG} > 3;
          LogIt( $fhDEBUG,"Value: ". $rs->Fields($i)->value) if $CONFI
+G{DEBUG} > 3;


          $LookupResult->{$col} = $rs->Fields($i)->value;
          trim( $LookupResult->{$col});
          $i++;
      }
   $rs->MoveNext;
   }

   LogIt( $fhDEBUG, "Closing ADODB connection") if $CONFIG{DEBUG} > 3;
   
   $conn->Close();
   
   ( map { LogIt( $fhDEBUG,"". $_ ) } split /\n/, Dumper $LookupResult
+ ) if $CONFIG{DEBUG}  > 3;
   return( $LookupResult );
}

sub trim {
   LogIt( $fhDEBUG,"Entering sub:trim args: @_") if $CONFIG{DEBUG} > 3
+;
   for(@_)
   {
       s/^\s+//;
       s/\s+$//;
   }
}
[download]

If I use the following call...

$EVENT->{'slots'}->{'NTSD_SLA_CATEGORY'} = lookup_NTSD_val( '[Highest Site Category]', '[Servers]', "left([Server], $L) = '$hostN'");

I get a result in the debug logs...

main::Entering sub:lookup_NTSD_val args: [Highest Site Category] [Serv
+ers] left([Server], 9) = 'ServerXXX'
main::lookup                Opening ADODB connection
main::lookup                COMPLETE: Opening ADODB connection
main::lookup                   SELECT [Highest Site Category]
main::lookup                   FROM   [Servers]
main::lookup                   WHERE  left([Server], 9) = 'ServerXXX'
main::lookup                COMPLETE: executing SQL
main::lookup                getting val for $col [Highest Site Categor
+y]
main::lookup                Value: D
main::trim                  Entering sub:trim args: D
main::lookup                Closing ADODB connection
main::lookup                $VAR1 = {
          '[Highest Site Category]' => 'D'
        };
main::lookup_NTSD_val       lookup_NTSD_val got result "D"
[download]

But if I make a call for several fields like so...

$EVENT->{'slots'}->{'NTSD_VALUES'} = lookup_NTSD_val(" '[Cover]','[UseSubType]','[Building]','[Network]','[Environment]','[AssetNo]','[Primary_IP_Address]','[ServiceID]','[OS]','[Team]'", '[ServerComp]', "left([ServerName], $L) = '$hostN'");

The debug log gives me...

main::lookup_NTSD_val       Entering sub:lookup_NTSD_val args: '[Serve
+rDescription]','[ServerLocation]','[ServerDomain]','[ServerServiceNo]
+','[AsstNo]','[ApproxUsers]','[IPAddress1]','[SvrStatus]','[OSVersion
+]' [ServerComp] left([ServerName], 9) = 'ServerXXX'
main::lookup                Opening ADODB connection
main::lookup                COMPLETE: Opening ADODB connection
main::lookup                   SELECT '[ServerDescription]','[ServerLo
+cation]','[ServerDomain]','[ServerServiceNo]','[AsstNo]','[ApproxUser
+s]','[IPAddress1]','[SvrStatus]','[OSVersion]'
main::lookup                   FROM   [ServerComp]
main::lookup                   WHERE  left([ServerName], 9) = 'ServerX
+XX'
main::lookup                COMPLETE: executing SQL
main::lookup                getting val for $col '[ServerDescription]'
main::lookup                Value: [ServerDescription]
main::trim                  Entering sub:trim args: [ServerDescription
+]
main::lookup                getting val for $col '[ServerLocation]'
main::lookup                Value: [ServerLocation]
main::trim                  Entering sub:trim args: [ServerLocation]
main::lookup                getting val for $col '[ServerDomain]'
main::lookup                Value: [ServerDomain]
main::trim                  Entering sub:trim args: [ServerDomain]
main::lookup                getting val for $col '[ServerServiceNo]'
main::lookup                Value: [ServerServiceNo]
main::trim                  Entering sub:trim args: [ServerServiceNo]
main::lookup                getting val for $col '[AsstNo]'
main::lookup                Value: [AsstNo]
main::trim                  Entering sub:trim args: [AsstNo]
main::lookup                getting val for $col '[ApproxUsers]'
main::lookup                Value: [ApproxUsers]
main::trim                  Entering sub:trim args: [ApproxUsers]
main::lookup                getting val for $col '[IPAddress1]'
main::lookup                Value: [IPAddress1]
main::trim                  Entering sub:trim args: [IPAddress1]
main::lookup                getting val for $col '[SvrStatus]'
main::lookup                Value: [SvrStatus]
main::trim                  Entering sub:trim args: [SvrStatus]
main::lookup                getting val for $col '[OSVersion]'
main::lookup                Value: [OSVersion]
main::trim                  Entering sub:trim args: [OSVersion]
main::lookup                Closing ADODB connection
main::lookup                $VAR1 = {
          '\'[ServerServiceNo]\'' => '[ServerServiceNo]',
          '\'[ServerDomain]\'' => '[ServerDomain]',
          '\'[ServerLocation]\'' => '[ServerLocation]',
          '\'[OSVersion]\'' => '[OSVersion]',
          '\'[ApproxUsers]\'' => '[ApproxUsers]',
          '\'[AsstNo]\'' => '[AsstNo]',
          '\'[ServerDescription]\'' => '[ServerDescription]',
          '\'[SvrStatus]\'' => '[SvrStatus]',
          '\'[IPAddress1]\'' => '[IPAddress1]'
        };
main::lookup_NTSD_val       lookup_NTSD_val got result ""
[download]

I was expecting to see a list of values and not fieldnames. (Which appear to be in a different order to the order in the SELECT)

Any pointers in the right direction would be appreciated.

Cheers! Mych

I have not failed... just found 100 ways that don't work YET!

Comment on Problems with my DB lookup script Select or Download Code

Replies are listed 'Best First'.
Re: Problems with my DB lookup script by kennethk (Abbot) on Apr 27, 2009 at 14:50 UTC
Note that in the data dumper output (the $VAR1 = ...) section, you have multiple entries with escaped ' characters. I believe your issue is that when you pass your list, you are double quoting it, as opposed to the sample you provided. It looks to me like you'll get the results you are looking for with: `$EVENT->{'slots'}->{'NTSD_VALUES'} = lookup_NTSD_val('[Cover],[UseSubType],[Building],[Network],[Environment],[AssetNo],[Primary_IP_Address],[ServiceID],[OS],[Team]', '[ServerComp]', "left([ServerName], $L) = '$hostN'");` See the docs on string interpolation. As a side note, for future code you may want to look into DBI and using placeholders to simplify your life and to return more useful diagnostics when you query your database.	[reply] [d/l]
Re^2: Problems with my DB lookup script by Mych (Initiate) on Apr 27, 2009 at 15:23 UTC
Kenneth, Thanks for taking time to go through my post... You hit the nail on the head... $VAR1 now returns: `$VAR1 = { '[SvrStatus]' => 'DEVELOPMENT', '[ApproxUsers]' => 151, '[AsstNo]' => 'AS012345', '[ServerLocation]' => 'XDC 1 5 34', '[OSVersion]' => '5.0', '[ServerServiceNo]' => 'CD123DC321', '[ServerDescription]' => 'App Server', '[IPAddress1]' => 'XXX.XXX.XXX.XXX', '[ServerDomain]' => 'XYZ' };` [download] Now all I have to do is figure out how to split this up into the individual fields. I need them to be assigned to: `$EVENT->{'slots'}->{'NTSD_SvrStatus'} $EVENT->{'slots'}->{'NTSD_ApproxUsers'} $EVENT->{'slots'}->{'NTSD_AsstNo'}etc...` [download] Onwards and upward... Many thanks Mych I have not failed... just found 100 ways that don't work YET!	[reply] [d/l] [select]