Thomas <[email protected]> writes: > when calling commit I am no longer getting error 25001. > > Now I have a different problem: the lifetime of the set role seems to be > limited > to the stored procedure itself - after returning to the main program my role > is > NULL again. I have double-checked using the debugger and confirmed that the > set > role within the procedure actually was carried out successfully. > Using an already established connection (see Connection conn = > DriverManager.getConnection("jdbc:default:connection") at the beginning of the > procedure) I would have expected that the role is still set after returning.
I think this is as designed. See this paragraph in the Using SQL roles section of the developer's guide (http://db.apache.org/derby/docs/10.6/devguide/cdevcsecureroles.html): > Within stored procedures and functions that contain SQL, the current > role is on the authorization stack. Initially, inside a nested > connection, the current role is set to that of the calling > context. Upon return from the stored procedure or function, the > authorization stack is popped, so the current role of the calling > context is not affected by any setting of the role inside the called > procedure or function. If the stored procedure opens more than one > nested connection, these all share the same (stacked) current role > state. Any dynamic result set passed out of a stored procedure sees > the current role of the nested context. -- Knut Anders
