command parameters

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

command parameters

Post by 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

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: command parameters

Post by 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);
}

damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Re: command parameters

Post by 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?

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: command parameters

Post by 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.

Post Reply