Geek Answers Handbook

Weblogic: calling a DB2 stored procedure without a schema name (currentSchema property)

I have a Java application that runs on Weblogic. The application must access the stored procedure in the DB2 database, so the JDBC data source is configured and accessible by its JNDI name.

Data source:

ClassDriver: com.ibm.db2.jcc.DB2Driver Properties: user=MYUSER DatabaseName=MYDB

The following example works as expected.

 Context env = null; DataSource pool = null; Hashtable ht = new Hashtable(); ht.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory"); ht.put(Context.PROVIDER_URL,"t3://myserver:7777"); env = new InitialContext(ht); pool = (DataSource) env.lookup("jdbc/myjndiname"); conn = pool.getConnection(); // call stored procedure with schema name String procName = "MYSCHEMA.MYSTOREDPROCEDURE"; String sql = "CALL " + procName + "(?)"; callStmt = conn.prepareCall(sql); callStmt.setString(1, "1"); callStmt.execute();

But now I need to call the stored procedure without a schema name and use the JDBC driver property instead.

Data source:

 ClassDriver: com.ibm.db2.jcc.DB2Driver Properties: user=MYUSER DatabaseName=MYDB db2.jcc.override.currentSchema=MYSCHEMA com.ibm.db2.jcc.DB2BaseDataSource.currentSchema=MYSCHEMA

The following SQL call results in an error

 // call stored procedure without schema name String procName = "MYSTOREDPROCEDURE"; String sql = "CALL " + procName + "(?)"; callStmt = conn.prepareCall(sql);

SQL error:

 SQLCODE = -440, ERROR: NO PROCEDURE BY THE NAME MYSTOREDPROCEDURE HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

I assume that the currentSchema properties are incorrect.

Edit: it looks like I was wrong: the currentSchema property currentSchema not a problem! The SQL statement "select current_schema fromsysibm.sysdummy1" returns the correct schema ( MYSCHEMA ). The question is why "CALL MYSCHEMA.MYSTOREDPROCEDURE(?)" Works, and "CALL MYSTOREDPROCEDURE(?)" Leads to an error ...

Any suggestions? Thanks!

+6
java db2 weblogic jndi
source share
2 answers

The permission of the stored procedure (and function) is not controlled by the special CURRENT SCHEMA register. It is managed by a special CURRENT PATH register.

So you can:

  • Execute SQL statement SET CURRENT PATH = MYSCHEMA
    or

  • Use the currentFunctionPath JDBC property.

+9
source share

You can specify it at the highest level in the JDBC URL of the data source. For example.

 jdbc:db2://hostname:port/DBNAME:currentSchema=MYSCHEMA;

This, however, affects all connections coming from the same data source.

0
source share

More articles:


All Articles