Thomas Hill <thomas.k.h...@t-online.de> writes:
> Hi,
>
> I would need help in understanding how to escape a quoted identifier in JDBC
> as this seems to be different from how to do it in interactive SQL using ij.
>
> What I am trying to do is to compare CURRENT_ROLE to constant string.
>
> Scenario 1)
> ===========
> Please see the following output in which I am using ij and where I achieve
> what
> I want, i.e. there is one row returned as the comparison in the
> where clause is satisfied:
>
> ij> connect 'jdbc:derby://localhost:1527/dummydb;bootPassword=xy;
> user=dbo;password=derby;create=true';
> ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
> ('derby.database.sqlAuthorization', 'true');
> Statement executed.
> ij> connect 'jdbc:derby://localhost:1527/dummydb;shutdown=true;';
> ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006,
> SQLERRMC: Database 'dummydb' shutdown.
> ij> disconnect;
> ij> connect 'jdbc:derby://localhost:1527/dummydb;bootPassword=xy;
> user=dbo;password=derby;create=true';
> ij> create role db_reader;
> 0 rows inserted/updated/deleted
> ij> set role db_reader;
> 0 rows inserted/updated/deleted
> ij> select IBMREQD FROM SYSIBM.SYSDUMMY1 WHERE CURRENT_ROLE='"DB_READER"';
> IBM&
> ----
> Y
>
> 1 row selected ===> so success here!
> ij> disconnect;
> ij>
>
> Scenario 2)
> ===========
> Now I would like the query to be embedded into a stored procedure.
> However I am struggling to find out what the correct syntax might be here.
>
> Here is my java code for the stored procedure:
> public static void SP_getRole(String dummy[]) throws SQLException
> {
> Connection conn = DriverManager.getConnection("jdbc:default:connection");
>
> Statement stmt = conn.createStatement();
> String cSQL = "SELECT ibmreqd FROM sysibm.sysdummy1"+
> " WHERE CURRENT_ROLE='\"DB_READER\"'";
> ResultSet rs = stmt.executeQuery(cSQL);
> while (rs.next()) {
> System.out.println(rs.getString(1));
> };
>
> rs.close();
> stmt.close();
>
> return;
> }
>
> and the java stub to call the procedure:
> try {
> Connection conn = DriverManager.getConnection(connectionURL);
> System.out.println("Successfully connected to Database");
>
> Statement stmt = conn.createStatement();
> String cSQL = "SET ROLE db_reader";
> stmt.executeUpdate(cSQL);
> stmt.close();
>
> cSQL = "SELECT CURRENT_ROLE FROM SYSIBM.SYSDUMMY1 \n";
> PreparedStatement ps = conn.prepareStatement(cSQL);
> ResultSet rs = ps.executeQuery();
> String rsString = "";
> while (rs.next()) {
> rsString = rs.getString(1);
> System.out.println(rsString);
> };
> rs.close();
> ps.close();
>
> CallableStatement cstmt =
> conn.prepareCall("{ CALL rte.\"SP_getRole\"(?) }");
> cstmt.setString(1, "dummy");
> cstmt.executeUpdate();
>
> and the create procedure statement
> CREATE PROCEDURE rte."SP_getRole"(OUT "vcRole" varchar(128))
> LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL SECURITY DEFINER
> MODIFIES SQL DATA EXTERNAL NAME '...SP_getRole'
> ;
>
> HOWEVER when running this now row is returned!
>
> I also tried
> " WHERE CURRENT_ROLE='\\\"DB_READER\\\"'";
>
> but this also did not lead to success.
>
> Any hints appreciated.
I think you need to invoke SET ROLE inside the stored procedure, since
the procedure is defined with EXTERNAL SECURITY DEFINER.
See http://db.apache.org/derby/docs/10.8/ref/rrefcreateprocedurestatement.html:
,----
| When the procedure is first invoked, no role is set; even if the
| invoker has set a current role, the procedure running with definer's
| rights has no current role set initially.
`----
--
Knut Anders
