command parameters

command parameters

Postby damon.cognito » Tue 04 Aug 2015 15:58

I'm trying to change the password of a login role ('test'):

Code: Select all
            using (var command = new PgSqlCommand("alter role @user test with password @pw", MyConnection))
            {
                command.Parameters.AddWithValue("@user", user);
                command.Parameters.AddWithValue("@pw", pw);
                result = (command.ExecuteNonQuery() == -1);
            }


But this fails with: syntax error at or near \"$1\"

I tried hardcoding the password (i.e. i.e. alter role @user test with 'test') and this fails too. It works fully hardcoded (i.e. alter role test with 'test').

c#, VS2013, latest dotConnect
damon.cognito
 
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Re: command parameters

Postby Pinturiccio » Wed 05 Aug 2015 14:57

You can use parameters in static SQL statements only in positions where the SQL syntax allows a parameter. For example, you cannot specify table names with parameters.

Therefore, parameters can send only query values, not the values of its part.

Parameters cannot be used for building query as a query part. The alter role query cannot be used with parameters, since parameters for a given query are not applicable.

As a workaround, you can use the following code:
Code: Select all
using (var command = new PgSqlCommand("alter role "+user+" with password "+pw+";", MyConnection)) {
   bool result = (command.ExecuteNonQuery() == -1);
}
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Re: command parameters

Postby damon.cognito » Wed 05 Aug 2015 15:26

Hi, thanks for the reply. The reason I was using parameters for the password is because it may have characters in it that cause a problem in the command string (", /, '). I now understand the role is not a parameter but is the password not a parameter?
damon.cognito
 
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Re: command parameters

Postby Pinturiccio » Thu 06 Aug 2015 10:44

damon.cognito wrote:I now understand the role is not a parameter but is the password not a parameter?

The password is not a parameter too.
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL