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.
Thanks
Thomas
