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!

In reply to Problems with my DB lookup script by Mych

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.