問題描述

我有一個在 Weblogic 上運行的 Java 應用程序.應用程序需要訪問 DB2 數據庫中的存儲過程，因此通過其 JNDI 名稱配置和訪問 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.

數據來源:

ClassDriver: com.ibm.db2.jcc.DB2Driver

Properties:
user=MYUSER
DatabaseName=MYDB

以下示例按預期工作.

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();

但現在我需要調用不帶架構名稱的存儲過程，并改用 JDBC 驅動程序屬性.

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

數據來源:

ClassDriver: com.ibm.db2.jcc.DB2Driver

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

以下 SQL 調用導致錯誤

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 錯誤:

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

我認為currentSchema"屬性是錯誤的.

I assume that the "currentSchema" properties are wrong.

看起來我錯了:屬性 currentSchema 不是問題！SQL 語句 "select current_schema fromsysibm.sysdummy1" 返回正確的模式 (MYSCHEMA).現在的問題是，為什么 "CALL MYSCHEMA.MYSTOREDPROCEDURE(?)" 有效，而 "CALL MYSTOREDPROCEDURE(?)" 會導致錯誤...

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

有什么建議嗎?謝謝！

推薦答案

存儲過程(和函數)解析不受 CURRENT SCHEMA 特殊寄存器控制.它由 CURRENT PATH 特殊寄存器控制.

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

所以，你可以:

• 執行SQL語句SET CURRENT PATH = MYSCHEMA
  或

使用 currentFunctionPath JDBC 屬性.

Use the currentFunctionPath JDBC property.

這篇關于Weblogic:調用沒有模式名稱的 DB2 存儲過程(屬性 currentSchema)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！