0

This line of code has - without editing it - changed it's behavior over the last several days - several times:

try {
    $testfile = "c:\users\***RedactedForSecurity***\desktop\psfcatalog\copy_conversions_weekly_report.csv"
    write-log((Get-Date -Format o) + "     get-sqlhelper")
    $sqlhelper = get-sqlhelper -serverinstance '***RedactedForSecurity***' -database '***RedactedForSecurity***' -username '***RedactedForSecurity***' -password '***RedactedForSecurity***'

    Function Create-TableString{ 
        Param(
            [Parameter(Mandatory=$true)]
            [string]$tableName,
            [Parameter(Mandatory=$true)]
            [string]$Fields
        )
        [string]$SQL = "CREATE TABLE " + $tableName + '(' + $Fields + ')'
        Write-Host $SQL
        return $SQL
    }

    write-log((Get-Date -Format o) + "     finding first line of CSV data in file")
    gc $testfile | % {
        if ($_ -match "report fields") {
            $line = $_.ReadCount
            break
        }
    }

    [string]$fieldNamesSQL = gc $testfile |
        select -Skip $line -First 10 |
        ConvertFrom-Csv |
        Out-DataTable |
        % {$fieldNamesSQL += $Col.ColumnName + " VARCAR(255)  NULL, "} -End {$fieldNamesSQL.TrimEnd(',', ' ')}

    Write-Host $fieldNamesSQL
    Write-Host $csvHeaders
} catch {
    $msg = command 2>&1
    Write-Host $_.exception.message
    Write-Host $_.exception.itemname
    Write-Host $_.exception.message
    Write-Host $msg
}

Alright here's the thing - notice the portion of the script:

gc $testfile | % {
    if ($_ -match "report fields") {
        $line = $_.ReadCount
        break
    }
}

That portion worked fine for about a day and then break started to stop execution of the entire script instead of just beak the loop. How can it work for a day and then change? Anyway, I came up with a solution where I would wrap the whole piece with {} and then recursivly execute it in my next call:

{gc $testfile | % {
    if ($_ -match "report fields") {
        $line = $_.ReadCount
        break
    }
}}

[string]$fieldNamesSQL = gc $testfile |
    select -Skip $line -First 10 |
    ConvertFrom-Csv |
    Out-DataTable |
    % {$fieldNamesSQL += $Col.ColumnName + " VARCAR(255)  NULL, "} -End {$fieldNamesSQL.TrimEnd(',', ' ')}

With this setup when $line is called, it evaluates the previous line and script execution continues seamlessly.

A day later...

Now the same script is throwing an error:

Cannot validate argument on parameter 'Skip'. The argument is null, empty, or an element of the argument collection contains a null value. Supply a collection that does not contain any null values and then try the command again.

What is happening?

Edit

This is not a duplicate. I'm aware of the foreach alias problem. The question is why the PowerShell runtime changes its evaluation between executions. Ie: I didn't change the script but the execution changed. Why would PowerShell evaluate foreach to an operator on one day and evaluate it to ForEach-Object on another day?

Things I've found:

It seems that PowerShell persists certain values between executions. For instance if I set a string to value1 and run the script. Then I set it to another value2 and run the script, but the value remains value1 when I Write-Host. I have to restart the shell and sometimes the machine to get it to evaluate correctly. It seems random so far as to when this happens - which probably just means I don't understand it.

My best guess so far is that when PowerShell interprets foreach it does so recursively so a function further down the script could cause it to evaluate differently from call to call.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Jamie Marshall
  • 1,885
  • 3
  • 27
  • 50
  • If there was no -match, then $line would be $null. Are you sure there is a -match "report fields"? What is the value of $line in the Select-Object? – lit Jul 16 '17 at 03:03
  • $line = 10, which empirically corresponds to the file. Good thought though. – Jamie Marshall Jul 16 '17 at 03:38
  • 9
    `break` is not supposed to be used for `%` = `ForEach-Object`, it is not a loop. In your case, it either breaks some surrounding loop (even in a parent scope) or the script, if there is no loop. Quick fix is to use `foreach ($_ in gc $testfile) {... break ...}`. See also https://stackoverflow.com/questions/7760013/why-does-continue-behave-like-break-in-a-foreach-object – Roman Kuzmin Jul 16 '17 at 04:12
  • @Roman Kuzmin I see... Good looking out. So what about the fact that this worked once but stopped. Does the Powershell shell recursively evalute `foreach` to be an either an object or an operator? That is all I can think of to cause spontaneous changes in an otherwise finished script.... – Jamie Marshall Jul 16 '17 at 05:12
  • 1
    `ForEach-Object` `-ne` `foreach`. I wrote about this a while back: [http://windowsitpro.com/powershell/windows-powershell-constructs](http://windowsitpro.com/powershell/windows-powershell-constructs). – Bill_Stewart Jul 16 '17 at 13:03
  • @Bill Stewart. Thanks for the article link, but I do get this. I understand the Alias gotcha now with foreach. However for a whole day why did it evaluate to an operator and then on another day evaluate to the ForEach-Object. Is there anything that would cause variables or execution environment settings to persist between executions of the script in Powershell? – Jamie Marshall Jul 16 '17 at 15:11
  • @briantist please unmark as duplicate. See my edit above. – Jamie Marshall Jul 16 '17 at 16:45
  • 1
    @RomanKuzmin: Good points, but please don't use automatic variable `$_` as a custom loop iterator variable - see https://github.com/PowerShell/PowerShell/issues/3695 and https://github.com/PowerShell/PowerShell/issues/3830. – mklement0 Jul 16 '17 at 16:55
  • 1
    @JamieMarshall: Are you running in the ISE? There, scripts are implicitly sourced, so repeated invocations of a script run in the same session, with variables lingering. Also note that enclosing code in `{ ... }` doesn't execute anything, it just creates a script block (an anonymous function, if you will). If you still have problems, please ask a _new_ question focused on just your edit, and try to present a reproducible scenario. – mklement0 Jul 16 '17 at 16:59
  • 3
    It is unlikely that the PowerShell execution engine changed overnight. No one is editing the PowerShell execution engine. As programmers, we are, however, editing and changing source code. We've all had moments when we were sure that something worked differently before now. The answer could probably be known if the source code were version controlled on every edit. About the best we can do is make it work correctly now and move on. – lit Jul 16 '17 at 18:13
  • @mklement0, see my comment there. And this question is a good example, we easily converted a pipeline to loop without having to rename the pipe/loop variable. If we decide to convert it back then we do not have to rename it, too. I do not see a problem in using `$_` as a user variable in right cases. – Roman Kuzmin Jul 17 '17 at 06:02
  • 1
    @JamieMarshall You use the construct (break without a loop) which behavior is not documented in PowerShell, as far as I know. Maybe it's not even well defined in PowerShell and may vary. Who knows? All in all, it is not quite right code and the question is about a not documented feature. – Roman Kuzmin Jul 17 '17 at 06:10
  • 1
    @RomanKuzmin: See [this suggestion](https://github.com/PowerShell/PowerShell/issues/3830) for _implicit_ definition of `$_` even for `foreach`. – mklement0 Jul 17 '17 at 10:33

0 Answers0