Weblogic:调用没有模式名称的 DB2 存储过程(属性 currentSchema)

2022-01-14 00:00:00 db2 java weblogic jndi

我有一个在 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.

相关文章