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: JDBC escape syntax
Date Fri, 22 Jul 2011 10:47:55 GMT
Thomas Hill <Thomas.K.Hill@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

Mime
View raw message