DB2 java 存储过程调用返回错误 SQLCODE=-440，SQLSTATE=42884
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18367803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 java Stored Procedure call return error SQLCODE=-440, SQLSTATE=42884
提问by huican
I am doing a simple stored procedure call to DB2. While it calls the stored procedure, it always returns this error:
我正在对 DB2 进行简单的存储过程调用。当它调用存储过程时，它总是返回这个错误：
DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE, DRIVER=3.66.46
========== Java code:
========== Java代码：
String JDBC_DRIVER = "com.ibm.db2.jcc.DB2Driver";
// STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
// STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// to execute the stored procedure.
System.out.println("CALL median_result_set(?)");
String sql = "CALL median_result_set(?)";
CallableStatement stmt1 = conn.prepareCall(sql);
stmt1.registerOutParameter(1, Types.DOUBLE);
stmt1.execute();
System.out.println("jdbcadapter->callproc after execute " + sql);
stmt1.close();
conn.close();
============== The db2 clp command line worked:
============== db2 clp 命令行有效：
c:SP>db2 call median_result_set(?)
Value of output parameters
--------------------------
Parameter Name : MEDIANSALARY
Parameter Value : +7.68582000000000E+004
Result set 1
--------------
NAME JOB SALARY
--------- ----- ---------
Marenghi Mgr 77506.75
O'Brien Sales 78006.00
================ The stored procedure definition:
================ 存储过程定义：
CREATE PROCEDURE median_result_set
-- Declare medianSalary as OUT so it can be used to return values
(OUT medianSalary DOUBLE)
RESULT SETS 2
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT salary FROM staff
ORDER BY CAST(salary AS DOUBLE);
-- use WITH RETURN in DECLARE CURSOR to return a result set
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE CAST(salary AS DOUBLE) > medianSalary
ORDER BY salary;
-- use WITH RETURN in DECLARE CURSOR to return another result set
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE CAST(salary AS DOUBLE) < medianSalary
ORDER BY SALARY DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET medianSalary = 6666;
-- initialize OUT parameter
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
-- return 1st result set, do not CLOSE cursor
OPEN c2;
-- return 2nd result set, do not CLOSE cursor
OPEN c3;
END @
回答by H-Patel
The error # means 42884 No routine was found with the specified name and compatible arguments.Check on url: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.messages.doc%2Fdoc%2Frdb2stt.html
错误 # 表示42884 No routine was found with the specified name and compatible arguments.检查 url：http: //publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.messages.doc%2Fdoc%2Frdb2stt.html
and search for 42884 error code. Hope you can solve your query by yourself.
并搜索 42884 错误代码。希望您能自己解决您的疑问。
回答by huican
Basically "SQLCODE=-440, SQLSTATE=42884" means that stored procedure can not be found.
基本上“SQLCODE=-440，SQLSTATE=42884”意味着找不到存储过程。
I saw a very common cause is the argument doesn't match.
我看到一个很常见的原因是参数不匹配。
For my case, I noticed that in java code, I have to put the schema name in front of the stored procedure name, e.g, instead of median_result_set(?), I should do SCHEMANAME.median_result_set(?)
就我而言，我注意到在 Java 代码中，我必须将模式名称放在存储过程名称的前面，例如，我应该使用 SCHEMANAME.median_result_set(?)
The SCHEMANAME for this SP can be found with some DB admin tools.
可以使用一些数据库管理工具找到此 SP 的 SCHEMANAME。
The reason why I don't need to specify the schema name from the command line: it seems that when I call SP from CLP command line with the same user when I created that SP, there is no need to the schema name (because internally they match up). Of course, it is always right if you specify the schema at the command line. I observed DB2 internally uses user name as schema name. E.g, if "ADMINISTRATOR" created a SP, the string "ADMINISTRATOR" is its schema name, as long as I see on Windows.
我不需要从命令行指定架构名称的原因：似乎当我从 CLP 命令行调用 SP 时使用相同的用户创建该 SP 时，不需要架构名称（因为在内部他们匹配）。当然，在命令行中指定模式总是正确的。我观察到 DB2 在内部使用用户名作为模式名。例如，如果“ADMINISTRATOR”创建了一个 SP，字符串“ADMINISTRATOR”就是它的模式名称，只要我在 Windows 上看到。
回答by Nik
You cannot find it because it actually doesn't exist... Try to call MEDIAN_RESULT_SET instead or to create the procedure as "median_result_set"...
您找不到它，因为它实际上不存在...尝试改为调用 MEDIAN_RESULT_SET 或将过程创建为“median_result_set”...
Just in case someone is having the same issue ;)
以防万一有人遇到同样的问题;)
