Separating the index and value when the function returns the processed data from the query

PowerShell Help
1

I’m having trouble separating the index and value when the function returns the processed data from the query. I found GetRows, and I think I’m doing it right. If anyone here has an idea how I can assign just the value to the $mdb_KMM or $mdb_DDD_SHORT[LONG] variables in main, that would be appreciated.

Function ProcessMDBs{
[cmdletbinding()]
  Param ([string]$mdbLookupString, [string]$mdbFileName) 
  Process
  {
   Write-Host "mdbLookupString: $mdbLookupString" -ForegroundColor Cyan
   $adOpenStatic = 3
   $adLockOptimistic = 3

   Write-Host "In ProcessMDB" -ForegroundColor darkRed
   $pathViewBase = 'C:\Data\EndToEnd_view\' #View dir. 

      $pathToMdb = Join-Path -Path $pathViewBase -ChildPath $mdbFileName
      if(Test-Path $pathToMdb)
      {
         if($mdbFileName -Match 'KMM')
         {
            Write-Host "kmm" -ForegroundColor DarkMagenta
            $selectQuery = “SELECT [TableName].[Message Description],
            [Messages].[English]
               FROM [TableName]
               WHERE [TableName].[Message Description] = '$($mdbLookupString)'”
         }
         elseif($mdbFileName -Match 'DDD')
         {
            Write-Host "ddd" -ForegroundColor DarkMagenta
            $mdbLookupStringLong = ("ALARMTEXT.LONG." + $($mdbLookupString))
            $mdbLookupStringShort = ("ALARMTEXT.SHORT." + $($mdbLookupString))
            $selectQuery = “SELECT [TableName].[Message Description],
            [TableName].[English]
               FROM [TableName]
               WHERE 
                  [TableName].[Message Description] = '$($mdbLookupStringLong)'
               OR
                  [TableName].[Message Description] = '$($mdbLookupStringShort)'”
         }
         #Write-Host "selectQuery: $selectQuery" -ForegroundColor Cyan
         #Write-Host "MDB Open db next" -ForegroundColor DarkCyan
         
        $cn = new-object -comobject ADODB.Connection

        #Write-Host "MDB Open next" -ForegroundColor DarkCyan

        $cn.Open("Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $pathToMdb") 
        #Write-Host "Open done" -ForegroundColor DarkCyan
        $rs.Open($selectQuery,
        $cn, $adOpenStatic, $adLockOptimistic)

        #Write-Host "select done"
        if($rs.EOF)
        {
           Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
           $returnArr = ""
        }
        else
        {
           Write-Host "record set returned from mdb query not empty...found $mdbLookupString in $mdbFileName"
           $returnArr = $rs.GetRows() #see error when it prints this and GetRows()
        }
        #Write-Host "here"
        Write-Host "-retArr[0,1] $($returnArr[0,1])"
        Write-Host "-retArr[1,0] $($returnArr[1,0])"
        Write-Host "-retArr[0,0] $($returnArr[0,0])"
        Write-Host "-retArr[1,1] $($returnArr[1,1])"
      }#test-Path
      else
      {
          Write-Host "No such path $pathToMdb"
      }
       
      $cn.Close()
      return $returnArr #Item(s) queried from db 
  } #end Process
}# End of Function process mdb's

######################main##################################################################
...
    $mdbAppTextKMM = ProcessMDBs -mdbLookupString $omAlarmKMM -mdbFileName 'KMMAppText.mdb' 
    if($mdbAppTextKMM -ne "") #if it found the string
    {          
		$mdb_KMM = $($mdbAppTextKMM[1,0])
        Write-Host "mdb_KMM: $($mdb_KMM[1,0])" #question: this prints index and value and not just value...same as if I printed $($mdb_KMM). $mdb_KMM prints System.Object[]
        Break ########################
    }
    else #handle if it didn't return anything from query to avoid null indexing
    {
        $mdb_KMM = ""
    }

    $mdbAppTextDDD = ProcessMDBs -mdbLookupString $omAlarmKMM -mdbFileName 'DDDAppText.mdb'
    if($mdbAppTextKMM -ne "") #if it found the string
    {          
        $mdb_DPS_SHORT= $($mdbAppTextDDD[1,1])
        $mdb_DPS_LONG= $($mdbAppTextDDD[1,0])
        Write-Host "mdb_DDD_SHORT: $mdb_DDD_SHORT"
        Write-Host "mdb_DDD_LONG: $mdb_DDD_LONG"
        Break ########################
    }
    else #handle if it didn't return anything from query to avoid null indexing
    {
        $mdb_DDD_SHORT = ""
        $mdb_DDD_LONG = ""
    }
...

**The code prints this to screen but the assignment when it returns isn't separating the index and value anymore like I expect:

-retArr[0,1] 
-retArr[1,0] An error occurred.
-retArr[0,0] ERROR
-retArr[1,1] 
mdb_KMM: An error occurred. ERROR

I would expect mdb_KMM printed out to show

An error occurred.

without the ERROR index after it

2

I wound up doing this “workaround” since it looks like I can access the array items properly in the function and not when I return the array. So I’m appending the items with | and splitting on | later to retrieve them. I’m sure there’s a better way but I’m not that familiar with powershell.

       if($rs.EOF)
        {
           Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
           $returnArr = ""
           $returnStr = "|"
        }
        else
        {
           #Write-Host "record set returned from mdb query not empty...found $mdbLookupString in $mdbFileName"
           $returnArr = $rs.GetRows()
           $returnStr = $($returnArr[1,0]) + "|" + $($returnArr[1,1])
        }
        #Write-Host "here"
        Write-Host "-retArr[0,1] $($returnArr[0,1])"
        Write-Host "-retArr[1,0] $($returnArr[1,0])"
        Write-Host "-retArr[0,0] $($returnArr[0,0])"
        Write-Host "-retArr[1,1] $($returnArr[1,1])"

   }#test-Path
   else
   {
        Write-Host "No such path $pathToMdb"
   }
       
   $cn.Close()
   return $returnStr 


####main####

    $mdbAppTextKMM_Arr = $mdbAppTextKMM.Split("|")
    Write-Host "mdbAppTextKMM_Arr: $($mdbAppTextKMM_Arr[0])"
3

Hi Michele,

The formatting here in this forum is quite easy. Simply have your code copied and ready to paste, place your cursor where you want your code, press the preformatted text button (it looks like this </>) and immediately paste your code. It makes it hard to read, copy, and help when it’s not formatted properly.

Thanks in advance!

1 Like