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
– Etan Reisner, Commented May 19, 2015 at 17:28
possible duplicate of Referencing Powershell array index produces unexpected results when referenced with string — Etan Reisner
– Etan Reisner, Commented May 19, 2015 at 17:29
@EtanReisner This works, can you explain why. First time I am seeing this notation. — Glowie
– Glowie, Commented May 19, 2015 at 17:29
See the linked issue. You need to show powershell how to parse the variable (it stops early by default). — Etan Reisner
– Etan Reisner, Commented May 19, 2015 at 17:31
Another useful link: stackoverflow.com/questions/1145704/… — dugas
– dugas, Commented May 19, 2015 at 17:58

dugas · Accepted Answer · 2015-05-19 17:30:16Z

1

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
"

answered May 19, 2015 at 17:30

dugas

12.6k3 gold badges48 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell not referencing array elements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related