0

I am writing a script in PowerShell Core 7.2. I get a list of files from a folder that I check against Oracle db. I need the data of Description, and NC_Name column if the file is in db. The issue is that even when the file is not in db it still returns the data but of some other file. For example: I have a list of files, File#1, File#2,File#3. If File#2 is not in the db it still returns the data of File#1.

I have tried counting the number of rows and putting it as a condition. As in

 $rowNum = $connection.count

The issue with this is that $rowNum is never zero because it returns data for some other file; because the variable $fileName is never empty.

I also tried checking for the file name in the query itself but it gave a lot of errors. The query was

$query="DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM NC_PROGRAMS WHERE NC_PROGRAMS.NC_NAME  = '$fileName' AND ROWNUM = 1; IF record_exists = 1 THEN Select DESCRIPTION, NC_NAME"

The code is:

#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)
    }
  
    $connection.Close()
}
Brute
  • 121
  • 1
  • 10
  • `$connection.Count` will always return 1 if you are connected to the database. You need to get the count from `$reader`: https://stackoverflow.com/questions/3959511/row-count-from-oracledatareader – mjsqu May 31 '22 at 02:10
  • 1
    @mjsqu I have tried that already. Getting count from the $reader doesn't work. $reader.count -ge 1 breaks the whole thing; the loop moves forward with only one filename. – Brute May 31 '22 at 02:49
  • Looking at the documents and some other similar questions, `$reader` should be an `OracleDataReader` object which has a `HasRows` property you can access. Check what members are in `$reader` using `$reader | Get-Member` – mjsqu May 31 '22 at 03:08
  • @mjsqu Thanks for replying. Even if I get the rows I don't think it would do anything because it always gets a row(when it shouldn't). What I have noticed is that when a file from the folder is not present in the database it substitutes the previously found file name and gives me the data for it. It seems like I need to clear the value of a variable or dispose it but that hasn't worked either – Brute May 31 '22 at 06:19

0 Answers0