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. kl. 18:27:18 UTC+1 tirsdag 5. november 2013 skrev Gunnar Liljas følgende: > > 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 <[email protected] <javascript:>> > >> *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] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> Visit this group at http://groups.google.com/group/nhusers. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- 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.
