NHibernate CreateSQLQuery() with named parameters issue

Question

Why does not named or positional query parameters work with NHibernate in my case?

Consider the following statements to be true:

On Oracle database X and Y version 11.2.0.3.0 the role "MyRole" exists identified by "MyPassword" and is granted to the user I am connected as.

Here is some code:

public void SetRole(string roleName, string rolePassword)
{
    if (HasRoleBeenSet) return;
    try
    {
        session.CreateSQLQuery("SET ROLE ? IDENTIFIED BY ?")
               .SetString(0, roleName)
               .SetString(1, rolePassword)
               .ExecuteUpdate();
        HasRoleBeenSet = true;
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

SetRole("MyRole", "MyPassword");

Throws the following Exception:

NHibernate.Exceptions.GenericADOException: could not execute native bulk manipulation query: SET ROLE ? IDENTIFIED BY ? [SQL: SET ROLE :p0 IDENTIFIED BY :p1] ---> System.Data.OracleClient.OracleException: ORA-01937: missing or invalid role name

When i use SQLMonitor included in the Toad suite, the SQL sent to the database looks like this SET ROLE ? IDENTIFIED BY ? with the error Error occurred: [1937] (ORA-01937: missing or invalid role name) showing under.

When I look at FNH's own generated queries with parameters they look like this:

SchemaName.errorHandler.logError(:v0);

:1=['The error message']

But thats not the case when I manually create the query with CreateSQLQuery()

okay the next code sample is this:

...
session.CreateSQLQuery("SET ROLE :roleName IDENTIFIED BY :rolePassword")
       .SetString("roleName", roleName)
       .SetString("rolePassword", rolePassword)
       .ExecuteUpdate();
...

Which outputs the following error (The same error):

NHibernate.Exceptions.GenericADOException: could not execute native bulk manipulation query: SET ROLE :roleName IDENTIFIED BY :rolePassword [SQL: SET ROLE :p0 IDENTIFIED BY :p1] ---> System.Data.OracleClient.OracleException: ORA-01937: missing or invalid role name

Third code sample:

...
session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY {1}", 
                       roleName, 
                       rolePassword))
       .ExecuteUpdate();
...

On Oracle Database X this works wonders, on Oracle Database Y this does not work so well and gives me this error:

NHibernate.Exceptions.GenericADOException: could not execute native bulk manipulation query: SET ROLE MyRole IDENTIFIED BY MyPassword [SQL: SET ROLE MyRole IDENTIFIED BY MyPassword] ---> System.Data.OracleClient.OracleException: ORA-00933: SQL command not properly ended

I tried adding a semicolon ; to the end of the statement but that gives invalid character error.

If I add double quotes around the password like this it suddenly works for Oracle Database Y also

 ...
session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY \"{1}\"", 
                       roleName, 
                       rolePassword))
       .ExecuteUpdate();
...

The problem is this is not a very good solution, as FNH now spills out the password in the exception which gets logged. I have no idea whats the problem here, there is no clean question here because i don't know what to ask other then scream for help and hope somebody can shed some light on this.

After some discussion in the comments I tried the following:

 ...
session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY :rolePassword", 
                       roleName)) 
       .SetString("rolePassword", rolePassword)
       .ExecuteUpdate();
...

I tried with both :named and ? (positional) parameters, with single quotes, double quotes, nothing seems to do the trick.

This code throws the famous ORA-00933: SQL command not properly ended error

I guess the password can be made a parameter, where the role name can not (like trying to pass a table name as parameter of a FROM clause). The solution should be a mix of your first and last attempt (String.Format for the role name, parameter for the password) Also, maybe there exists another oracle syntax allowing to pass the role name as string. — jbl
That sounded like the solution or at least part of it, I tried it and now I get the SQL command not properly ended error, I tried adding double quotes to the query, same error, tried adding double quotes around the password, same error. — furier
Tried this ? session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY ?",roleName)) .SetString(0, rolePassword) .ExecuteUpdate(); — jbl
tried the same with named parameter and positional parameters — furier
Try creating a simpler test that doesn't use NHibernate - just a plain old ADO.NET parameterized DbCommand. That will remove some of the unknowns while you're diagnosing the issue. Once you've figured out how to get Oracle and ADO.NET to play together, then try re-adding NHibernate to the mix. — Daniel Schilling

Papa Burgundy Papa Burgundy · Accepted Answer · 2016-02-25T16:52:41

Try using .SetParameter() instead of SetString(). I am currently using something like this and it works:

var cases = Session.CreateSQLQuery(sql)
            .SetParameter("someID",thisIsMyValue)
            .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<SomeDTO>())
            .List<SomeDTO>();

And the SQL looks like this:

var sql = "SELECT fieldA, fieldB FROM myTable WHERE myTable.ID = :someID"

NHibernate CreateSQLQuery() with named parameters issue

1 Answers