*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 MyRoleIDENTIFIED BY
MyPassword
[SQL: SET ROLE MyRole IDENTIFIED BY MyPassword] --->
System.Data.OracleClient.OracleException: ORA-00933: SQL command notproperly
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
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/groups/opt_out.
