db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Hill <Thomas.K.H...@t-online.de>
Subject JDBC escape syntax
Date Fri, 22 Jul 2011 07:05:59 GMT
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




Mime
View raw message