PowerShell not referencing array elements

Question

I created array $SQL_output. If I do a foreach loop, it displays the contents

foreach ($j in $SQL_output){
    write-host $j
}

Output

When I add it to SQL statement

$SQL_UPDATE = "BEGIN
                  IF NOT EXISTS (SELECT 1
                                 FROM dbo.Stats
                                 WHERE Date_of_Download = CAST(GETDATE() AS DATE))
                  BEGIN
                      INSERT INTO dbo.Stats (Date_of_Download, Windows_SEP_11, Mac_SEP_11, Windows_SEP_12, Mac_SEP_12)
                      VALUES (CAST(GETDATE() AS DATE), $SQL_output[0],$SQL_output[1],$SQL_output[2],$SQL_output[3])
                  END
              END
"

The string expands to

BEGIN
                  IF NOT EXISTS (SELECT 1
                                 FROM dbo.Stats
                                 WHERE Date_of_Download = CAST(GETDATE() AS DATE))
                  BEGIN
                      INSERT INTO dbo.Stats (Date_of_Download, Windows_SEP_11, Mac_SEP_11, Windows_SEP_12, Mac_SEP_12)
                      VALUES (CAST(GETDATE() AS DATE), 118543 44021 84992 46888[0],118543 44021 84992 46888[1],118543 44021 84992 46888[2],118543 44021 84992 46888[3])
                  END
              END

How to fix?

Use `$($SQL_output[0])`. Or use `-f` to format the string and use the expansions as arguments. — Etan Reisner, May 19 '15 at 17:28
possible duplicate of [Referencing Powershell array index produces unexpected results when referenced with string](http://stackoverflow.com/questions/8592677/referencing-powershell-array-index-produces-unexpected-results-when-referenced-w) — Etan Reisner, May 19 '15 at 17:29
@EtanReisner This works, can you explain why. First time I am seeing this notation. — Glowie, May 19 '15 at 17:29
See the linked issue. You need to show powershell how to parse the variable (it stops early by default). — Etan Reisner, May 19 '15 at 17:31
Another useful link: http://stackoverflow.com/questions/1145704/how-can-you-use-an-objects-property-in-a-string — dugas, May 19 '15 at 17:58

score 1 · Accepted Answer · answered May 19 '15 at 17:30

Replace $SQL_output[x] with $(SQL_output[x])

$SQL_UPDATE = "BEGIN
              IF NOT EXISTS (SELECT 1
                             FROM dbo.Stats
                             WHERE Date_of_Download = CAST(GETDATE() AS DATE))
              BEGIN
                  INSERT INTO dbo.Stats (Date_of_Download, Windows_SEP_11, Mac_SEP_11, Windows_SEP_12, Mac_SEP_12)
                  VALUES (CAST(GETDATE() AS DATE), $($SQL_output[0]),$($SQL_output[1]),$($SQL_output[2]),$($SQL_output[3]))
              END
          END
"

PowerShell not referencing array elements

1 Answers1