db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: using set role in a stored procedure / Error 25001
Date Thu, 22 Jul 2010 19:41:01 GMT
Thomas <Thomas.K.Hill@t-online.de> 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

> 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

View raw message