I have code that queries an mdb file, and returns multiple rows. For some reason, it’s all showing in one row of an array. Does anyone know how to get this into a format that I can return from my Function, and that will be easy to access each row returned?
This is my Function code:
Function ProcessHelpMDB{
[cmdletbinding()]
Param ([string]$mdbLookupError, [string]$mdbFilePath, [string]$mdbPrinterSeries)
Process
{
$adOpenStatic = 3
$adLockOptimistic = 3
$deviceTable = $mdbPrinterSeries + "PP"
$mdbLookupError -Match '[0-9]*-([0-9]*)'
$errLookup = $Matches[1]
$selectQuery = “SELECT [$($deviceTable)].[HelpCode],
[$($deviceTable)].[ScreenNumber],
[$($deviceTable)].[TextID],
[$($deviceTable)].[PictureID]
FROM [$($deviceTable)]
WHERE
[$($deviceTable)].[HelpCode] = $($errLookup)"
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset
$cn.Open("Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $mdbFilePath")
$rs.Open($selectQuery,
$cn, $adOpenStatic, $adLockOptimistic)
$returnStr = ""
if($rs.EOF)
{
Write-Host "$errLookup not in $mdbFileName...record set returned empty for query"
$returnArr = ""
$returnStr = "|"
}
else
{
Write-Host "record set returned from mdb query not empty...found $errLookup in $mdbFilePath"
$returnArr = $rs.GetRows()
$returnStr = $($returnArr[1,0]) + "|" + $($returnArr[1,1]) #this was from last mdb query I did which returned 2-d array
}
Write-Host "-retArr[0,1] $($returnArr[0,1])" #this was used in my last mdb query, which returned 2-d array...this time it's 1-d
Write-Host "-retArr[1,0] $($returnArr[1,0])"
Write-Host "-retArr[0,0] $($returnArr[0,0])"
Write-Host "-retArr[1,1] $($returnArr[1,1])"
$cn.Close()
return $returnStr #Items queried from db #################################################
} #end Process
}# End of Function process mdb's
When I search online, they aren’t doing any storage of the data other than in csv or xlsx or sql db. I just want to hold it in maybe a multi-dimensional array of objects so I can pull the data out and put in a string in another order, and know what column I’m dealing with.
The data looks like this in the mdb table:
HelpCode ScreenNumber TextID PictureID
1000 1 HELP_INFO HELP_BLAH
1000 2 HELP_INFO2 HELP_BLAH2
...
When I look at the array of data returned, it looks like this - a single list:
> 1000
> 1000
> 1
> 2
> HELP_INFO
> HELP_INFO2
> HELP_BLAH
> HELP_BLAH2
Any idea how I can process the results so I can more easily access the data and know I'm traversing across a row, etc? And return it from my function in a way that won't get flattened on the receiving end?
Update, I'm trying this:
$rs.Open($selectQuery,
$cn, $adOpenStatic, $adLockOptimistic)
$returnStr = ""
$i=0
$ret = [ordered]@{}
if($rs.EOF)
{
Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
$returnArr = ""
$returnStr = "|"
}
else
{
while($rs.EOF -ne $True)
{
$hash = @{}
foreach ($field in $rs.Fields)
{
$hash.$($field.name) = $field.value
}
$tmp = New-Object PSObject -Property $hash
$ret.Add($i,$tmp)
$i++
$rs.MoveNext()
}
#Write-Host "record set returned from mdb query not empty...found $mdbLookupString in $mdbFileName"
}
Write-Host "retArr[] $($ret)" #prints retArr[] System.Collections.Specialized.OrderedDictionary
Write-Host "retArr[0] $($ret[0,"TextID"])" #prints retArr[0]
I'm not sure if I'm heading in the right direction and will eventually be able to print, return, and access the returned $ret. So my question is, how do I get this last Write-Host to work? I can see it's all nicely stored in $ret. If I return $ret, will it be accessible this way in the main code, or will the data structure change when returned?
This recent idea partially comes from [powershell objects from rs](https://community.idera.com/database-tools/powershell/powertips/b/tips/posts/converting-database-records-into-powershell-objects)
Update2...
I'm trying the following for a data structure but when I try to Write-Host, it says
'cannot index into a null array',
and looking closely in VSCode, each row in $result is null.
while($rs.EOF -ne $True)
{
$hash = @{}
$result = foreach ($field in $rs.Fields)
{
$hash.$($field.name) = $field.value
$hash.[PSCustomObject][ordered]@{
$($field.name) = $field.value
}#hash
}#result
$tmp = New-Object PSObject -Property $hash
$ret.Add($i,$tmp)
#$ret2.Add($tmp)
$i++
$rs.MoveNext()
} #while
foreach($item in $result) #
{
Write-Host $item #print nothing
Write-Host $item[0] #cannot index into null array
Write-Host $item[0].'TextID' #cannot index into null array
Write-Host $item.'TextID' #prints nothing
}#foreach
Update3..
I seem to have the data in a structure that works. But I'm having trouble accessing it to write-Host the info. It's only printing TextID in the first row and exits in the foreach $item loop below.
while($rs.EOF -ne $True)
{
$result = [ordered]@{}
#$hash = @{}
foreach ($field in $rs.Fields)
{
$result[$field.name] = $field.Value
}#result
$newObject = [PSCustomObject]$result
$ret.Add($i,$newObject) ###ordered dictionary needs key to add values
$i++
$rs.MoveNext()
} #while
Write-Host "retArr[] $($ret)" #prints retArr[] System.Collections.Specialized.OrderedDictionary
Write-Host "retArr[0] $($ret[0,"TextID"])" #prints retArr[0]
Write-Host "retArr[0] $($ret[0])" #prints retArr[0] @{PictureID=HELP_BLAH; TextID=HELP_INFO; HelpCode=9; ScreenNumber=1}
foreach($item in $ret) #
{
Write-Host $item #print System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry System.Collections.DictionaryEntry
Write-Host $item[0] #prints whole data struc row
Write-Host $item[0].'TextID' #prints TextID value
Write-Host $item.'TextID' #print nothing
}#foreach