command parameters

[damon.cognito]

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]

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]

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]

I now understand the role is not a parameter but is the password not a parameter?

The password is not a parameter too.