DB2 java存储过程调用返回错误SQLCODE = -440，SQLSTATE = 42884 [英] DB2 java Stored Procedure call return error SQLCODE=-440, SQLSTATE=42884

问题描述

我正在对DB2进行简单的存储过程调用。当它调用存储过程时，它总是返回此错误：

  DB2 SQL错误：SQLCODE = -440，SQLSTATE = 42884， SQLERRMC = MEDIAN_RESULT_SET; PROCEDURE，DRIVER = 3.66.46 
  

======== Java代码：

  String JDBC_DRIVER =com.ibm.db2.jcc.DB2Driver; 
 //步骤2：注册JDBC驱动
 Class.forName（JDBC_DRIVER）; 
 
 //步骤3：打开连接
 System.out.println（连接数据库...）; 
 conn = DriverManager.getConnection（DB_URL，USER，PASS）; 
 
 //执行存储过程。 
 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 execution+ sql）; 
 stmt1.close（）; 
 
 conn.close（）; 
  

==============
db2 clp命令行工作：

  c：SP> db2 call median_result_set（？）
输出参数值
 -------------------------- 
参数名称：MEDIANSALARY 
参数值：+ 7.68582000000000E + 004 
 
结果集1 
 -------------- 
 NAME JOB SALARY 
 --------- ----- --------- 
 Marenghi Mgr 77506.75 
奥布莱恩销售78006.00 
  

================
存储过程定义：

  CREATE PROCEDURE median_result_set 
  - 将medianSalary声明为OUT，因此可用于返回值
（OUT medianSalary DOUBLE）
结果集2 
语言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）; 
 
  - 在DECLARE CURSOR中使用WITH RETURN返回结果集
 DECLARE c2 CURSOR WITH RETURN FOR 
 SELECT name，job，salary 
 FROM staff 
 WHERE CAST（工资AS DOUBLE）>中位数
 ORDER BY工资; 
 
  - 在DECLARE CURSOR中使用WITH RETURN返回另一个结果集
 DECLARE c3 CURSOR WITH RETURN FOR 
 SELECT name，job，salary 
 FROM staff 
 WHERE CAST（工资AS DOUBLE）< medianSalary 
 ORDER BY SALARY DESC; 
 
 DECLARE CONTINUE HANDLER FOR NOT FOUND 
 SET medianSalary = 6666; 
 
  - 初始化OUT参数
 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; 
 
  - 返回第一个结果集，不要CLOSE光标
 OPEN c2; 
 
  - 返回第二个结果集，不要关闭游标
 OPEN c3; 
 END @ 
  

解决方案

基本上SQLCODE = 440，SQLSTATE = 42884意味着无法找到存储过程。

我看到一个很常见的原因是参数不匹配。对于我的情况，我注意到，在java代码中，我必须将模式名称放在存储过程名称的前面，例如，而不是median_result_set（？），我应该做SCHEMANAME.median_result_set （？）

可以使用某些DB管理工具找到此SP的SCHEMANAME。

我不需要从命令行指定模式名称：似乎当我创建该SP时，从同一用户的CLP命令行调用SP时，不需要模式名称（因为它们在内部匹配） ）。当然，如果在命令行中指定模式，它总是正确的。我观察到DB2内部使用用户名作为模式名称。例如，如果ADMINISTRATOR创建了一个SP，则字符串ADMINISTRATOR是其模式名称，只要我在Windows上看到。

I am doing a simple stored procedure call to DB2. While it calls the stored procedure, it always returns this error:

DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE, DRIVER=3.66.46

========== Java code:

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:

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 @

解决方案

Basically "SQLCODE=-440, SQLSTATE=42884" means that stored procedure can not be found.

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

The SCHEMANAME for this SP can be found with some DB admin tools.

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.

这篇关于DB2 java存储过程调用返回错误SQLCODE = -440，SQLSTATE = 42884的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！