How to refresh\reinitiate Oracle object reader in Powershell?-CodePudding

I need to get files from a folder. Then for each of these files I need to get information from (Decription, Nc_Name)columns in Oracle db. The issue is when a file is in the folder but not in the database it gives the data of the previous file it found instead of returning null. I believe this is because the something is pointing to towards the previous value and needs to be cleared out. For example, if I have a list of files say File#1,File#2,File#3,File#4. If File#4 is not in the database it will return the column data for File#3.

I have tried to reinitiate, clear, and dispose the reader but that doesn't help.

 Clear-Variabe -Name "reader"
 $reader.Dispose()

Code:

#Get all files
$result = $start.EnumerateDirectories() | ForEach-Object -Parallel {
    $_.GetFiles('*.EIA', $using:enum)
}
$result | Format-Table -AutoSize 

foreach($item in $result){
    $fileName = $item.BaseName
    #Oracle connection 
    Add-Type -Path C:\lib\netstandard2.1\Oracle.ManagedDataAccess.dll
    $query = "Select DESCRIPTION, NC_NAME From NC_PROGRAMS WHERE 
    NC_PROGRAMS.NC_NAME  = '$fileName' "
    $connectionString = "connectionString"
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $reader = $command.ExecuteReader()
    $rowNum = $connection.count
    Write-host "Number of rows-"$rowNum
    while($reader.Read()) {   
        $description=$reader.GetString(0)
        $fastemsFileName = $reader.GetString(1)
    }
    $reader.Dispose()
    $connection.Close()
}

Still new to all this so thanks in advance for helping!

CodePudding user response：

I'm assuming that there is a bit more code here around writing out the information, but $description and $fastemsFileName is not cleared.

When the file name does not exist, $reader.Read() is null, so the while statement block:

while($reader.Read()) {   
    $description=$reader.GetString(0)
    $fastemsFileName = $reader.GetString(1)
}

Doesn't get executed, so $description and $fastemsFileName will still have their old values.