2

I am trying to run a SQL from Power Shell(which is on my windows 7 64 bit desktop) and the remote database host is MS SQL Server 2012.

The code is:

$var1 = 'string';

function Get-ODBC-Data{
   param(
   [string]$query=$('
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = ''$var1''
                                ;
'),
   [string]$username='db_user_name',
   [string]$password='db_password'
   )
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

 $result = Get-ODBC-Data

Write-Host "SQL_Output: " $result[0];

If I use 'string' in the SQL's where clause instead of $var1 then te script works fine and gives expected result.

Quetion But I want to be able to pass any string as $var1 to the script as parameter. Then use it in the where clause of the SQL. How can I achieve this?

What I tried I have tried to enclose $var1 in 1,2 or 3 single quotes in the where clause in attempt to escape the single quote. Also tried adding/removing single quote from 'string' when $var1 is assigned value. I did try [string]$var1 = 'string' as well but none of these worked and I keep getting error mostly related to SQL syntax.

Improve this question
2
  • Not sure if you can do that in a parameters default value. You will probably need a backtick to escape the single quotes. You can concat strings with + so... $query ='Where col = `'' + $var1 + '`'; should work. why don't you create the query Inc value and then execute the statement. Commented May 15, 2018 at 23:38
  • Thank you for your response. Using $query ='Where col = '' + $var1 + ''; gives syntax error: Unexpected token ''' in expression or statement`. I am open to try different approach. Can you give me some idea about creating query in Inc and then executing the statement? I don't know about it. Commented May 16, 2018 at 15:28

1 Answer 1

Reset to default
2

Try this:

function Get-ODBC-Data{
   param(
   [string]$query=$("
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = '$($var1)'
                                ;
"),
   [string]$username='db_user_name',
   [string]$password='db_password'
   )
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

 $result = Get-ODBC-Data

Write-Host "SQL_Output: " $result[0];

The following runs fine on my setup, and shows the correct results:

$var1 = "test22"

function Get-ODBC-Data{
   param(
   [string]$query=$("
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = '$($var1)'
                                ;
"),
   [string]$username='db_user_name',
   [string]$password='db_password'
   )

   return $query

}

$result = Get-ODBC-Data

Write-Host " ################### Query ######################## "
Write-Host $result

However, you may have a much easier time just passing the entire query into the function as a parameter rather than just one variable part of the query.

Or setting it inside the function and passing $var1 as a mandatory parameter like so:

function Get-ODBC-Data{
   param(
   [parameter(Mandatory=$true)][string]$var1,
   [string]$username='db_user_name',
   [string]$password='db_password'
   )
   $query="
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = '$($var1)'
                                ;"

   return $query

}

$result = Get-ODBC-Data -var1 "working"

Write-Host " ################### Query ######################## "
Write-Host $result
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for your response. Using WHERE col2 = '$($var1)' gives syntax error: Unexpected token '$(' in expression or statement. I also tried to use WHERE col2 = ''$($var1)'' which doesn't give syntax error but shows error: Cannot index into null array which I think indicates that SQL didn't fetch any data.
Did you also use " instead of ' to wrap the query?
That's perfect! I had single quotes around the SQL instead of double quotes and that was reason for the syntax errors. The following runs fine on my setup, and shows the correct results: I am using that version and it is working as expected for me as well. Thank you so much for reverting back with the suggestion.
Not a problem, I would still suggest using the last piece of code as it gives you more control over what $var1 is. You won't have to set it every time just pass it to the function as a parameter.
I had to upgrade this script and now I am using second and third version of your answer. I have asked a new question about it at: stackoverflow.com/q/50418878/3209087 I would greatly appreciate if you could take a look at this question when you have some time. I think it is related with probable need for looping.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.