Re: using set role in a stored procedure / Error 25001

[Thomas]

The procedure is the first call to the data base after opening the connection, 
so there is no transaction open/pending from outside this procedure. 
Within the procedure itself however I need to query the catalog to see if a 
role 
is already set and only if this is not the case set the role as needed/
determined by a second select. So the set role is not and can not be the first 
SQL statement within the procedure. Attached is the java code of the procedure.
I am receiving the error when using IJ to connect via a first 
'connect jdbc:derby:....' statement, followed by a 
'Call rte."SP_setRole"();' statement.

Thanks for your help

public static void SP_setRole() throws SQLException {
   Connection conn = DriverManager.getConnection("jdbc:default:connection");
   boolean lRoleNeedsToBeSet = false;
   String vcRole = "";
   Statement stmt = conn.createStatement();
   // every user can read this system table which always holds only one row
   String cSQL = "SELECT CURRENT_ROLE FROM sysibm.sysdummy1";
   ResultSet rs = stmt.executeQuery(cSQL);
   while (rs.next()) {
      vcRole = rs.getString(1);
      // result will be NULL if no role is set
      if (rs.wasNull()) {
         lRoleNeedsToBeSet = true;
         // DB-Admin procedures will ensure each data base user will only 
         // be granted ONE role, i.e. the most priviledged role in the 
hierarchie
         // hierrachie of roles defined for the app, directly to his login 
         cSQL = "SELECT roleid FROM sys.sysroles WHERE grantee = current_user";
         ResultSet rs1 = stmt.executeQuery(cSQL);
         while (rs1.next()) {
            vcRole = rs1.getString(1);
            break; // to be on the safe side, although rs1 should always 
                   //hold only one row
         }
         rs1.close();
      }
   break; // again, just to be on the safe side
   }
   rs.close();
   // if no role is set, then set role to the role assigned to the user by the 
   // application owner/dbo; (else role already set will remain unchanged)
   if (lRoleNeedsToBeSet) {
      cSQL = "SET ROLE " + vcRole;
      stmt.executeUpdate(cSQL);
   }
   stmt.close();
   return;
}