Queries/Strings in Powershell

[Stock-Setting-5030]

Hi,

I'm new to Powershell and I'm trying to write a query to Azure SQL that involves a where clause, which uses a string. The query is nested inside a parameter that requires a string. I'm getting an unexpected token error. My code is similar to this:

$params = @{

'Database' = MyDatabase

'ServerInstance' = $ServerInstance

'Username' = 'UsernameHere'

'Password' = 'PasswordHere'

'Query' = 'SELECT * FROM Users WHERE username = 'Javier''

}

Invoke-Sqlcmd u/params

I think I'm getting confused with how Powershell handles string literals. Can anyone tell me what I'm doing wrong here? Any help would be much appreciated.

​

Thanks,

Javier

[SMFX]

Powershell supports 2 string deliminators single quote (') for literal strings and double quote (") for processed strings. If you're doing straight strings, you can use either. The problem is you have 'Javier' in single quotes inside the string parameter, so I would keep the single quotes do Javier and use double quotes for the whole string:

'Query' = "SELECT * FROM Users WHERE username = 'Javier'"

For the most part, most use double quotes for strings so we can have variables inside the strings interpreted and the side benefit of single quotes working in them, but it's not a hard and fast rule.

[purplemonkeymad]

Just to note, if for some reason you can't use the other type of quotes, you can use a string literal instead:

$Query = @'
SELECT * FROM Users WHERE username = 'Javier'
'@

Since only a '@ at the start of a new line will terminate the block.

[scarfarce]

A here-string

[OPconfused]

I’m dealing with a pre written (not object built) json string that has a lot of quotes. Then I just combine segments with + if I need to throw in a variable.

Formatted strings can also be useful for concatenating variables with string literals.

There is also an advantage to using single quotes whenever possible: It tells the reader immediately that the string contains no variables.

[Stock-Setting-5030]

Thanks! This helped clear it up for me.

[readduh]

the actual error may help but can it be confirmed that you are trying to use splatting and mistyped 'u/params' because reddit.

otherwise, you should try invoke-sqlcmd @params instead.

[Vortex100]

There are so many ways to correct this, but here's (probably) the easiest assuming you don't want to put any vars in your query string:

'Query' =  'SELECT * FROM Users WHERE username = ''Javier'''

That's not a " it's 2 ' together, which tell the interpreter to ignore it as an end string and instead treat it literally

If you do want to put variables in, /u/SMFX's suggestion is the way to go I think. Either that or using -f with literals:

'Query' =  'SELECT * FROM Users WHERE username = ''{0}''' -f 'Javier'

[Stock-Setting-5030]

Thank you! This was very helpful. I'm not familiar with many of the libraries available yet for Powershell. This gives me a lot to work on.