NHibernate CreateSQLQuery() with named parameters issue

[Sander Struijk]

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.

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

[Gunnar Liljas]

I don't use Oracle, so I'm not sure, but it's quite likely (and common for many DBMS:es) that a non-SQL command like SET ROLE doesn't support parametrization.

If you need, you can access the "raw" connection from the session and execute using standard OracleClient stuff, and you can catch any exceptions to prevent logging.

2013/11/5 Sander Struijk <furi...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/groups/opt_out.

[Sander Struijk]

Hi Gunnar

My "workaround" while waiting for this to be solved was just that, to just run the SQL in plain text.

If any exceptions then catch it and hand it over to a function I wrote for stripping away the NHibernate Exception (which contains the password in the message), but keeping the Inner Oracle Exception, and throw that as an inner exception of one of my own Exceptions.

You might be right, that there are no other way to deal with this.