Weblogic：调用没有模式名的DB2存储过程（属性currentSchema）

问题描述

我有一个在Weblogic上运行的Java应用程序。应用程序需要访问DB2数据库中的存储过程，因此JDBC数据源由其JNDI名称进行配置和访问。

数据源：

  ClassDriver：com.ibm.db2.jcc.DB2Driver 
 
属性：
 user = MYUSER 
 DatabaseName = MYDB 
  

以下示例按预期工作。

 上下文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（）; 
 
 //使用模式名称调用存储过程
 String procName =MYSCHEMA.MYSTOREDPROCEDURE; 
 String sql =CALL+ procName +（？）; 
 callStmt = conn.prepareCall（sql）; 
 
 callStmt.setString（1，1）; 
 callStmt.execute（）; 
  

但是现在我需要调用没有模式名称的存储过程，而不是使用JDBC驱动程序属性。

数据源：

  ClassDriver：com.ibm.db2。 jcc.DB2Driver 
 
属性：
 user = MYUSER 
 DatabaseName = MYDB 
 db2.jcc.override.currentSchema = MYSCHEMA 
 com.ibm.db2。 jcc.DB2BaseDataSource.currentSchema = MYSCHEMA 
  

以下SQL调用导致错误

  //调用没有模式名的存储过程
 String procName =MYSTOREDPROCEDURE; 
 String sql =CALL+ procName +（？）; 
 callStmt = conn.prepareCall（sql）; 
  

SQL错误：

  SQLCODE = -440，错误：没有程序由名称MYSTOREDPROCEDURE有
相容的参数在当前路径中找到
  

我假设currentSchema属性是错误的。

编辑：看起来我错了：属性 currentSchema 不是问题！ SQL语句select current_schema fromsysibm.sysdummy1返回正确的模式（ MYSCHEMA ）。现在的问题是，为什么CALL MYSCHEMA.MYSTOREDPROCEDURE（？）的作品和CALL MYSTOREDPROCEDURE（？）导致错误...

任何建议？谢谢！

解决方案

存储过程（和函数）的分辨率不受CURRENT SCHEMA特殊寄存器控制。它由CURRENT PATH专用寄存器控制。

所以，你可以：

• 执行SQL语句 SET CURRENT PATH = MYSCHEMA
  
  或




• 使用 currentFunctionPath JDBC属性。

I have a Java application that runs on Weblogic. The application needs to access a stored procedure in a DB2 data base, therefore a JDBC data source is configured and accessed 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 the schema name and use a 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 wrong.

Edit: It looks like I was wrong: the property currentSchema is not the problem! The SQL statement "select current_schema fromsysibm.sysdummy1" returns the correct schema (MYSCHEMA). The question is now, why "CALL MYSCHEMA.MYSTOREDPROCEDURE(?)" works and "CALL MYSTOREDPROCEDURE(?)" results in an error...

Any suggestions? Thanks!

解决方案

Stored procedure (and function) resolution is not controlled by the CURRENT SCHEMA special register. It is controlled by the CURRENT PATH special register.

So, you can either:

• Execute the SQL statement SET CURRENT PATH = MYSCHEMA
  or

• Use the currentFunctionPath JDBC property.

这篇关于Weblogic：调用没有模式名的DB2存储过程（属性currentSchema）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！