Weblogic:调用没有模式名的DB2存储过程(属性currentSchema) [英] Weblogic: Call DB2 stored procedure without schema name (property currentSchema)
问题描述
数据源:
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
orUse the
currentFunctionPath
JDBC property.
这篇关于Weblogic:调用没有模式名的DB2存储过程(属性currentSchema)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!