問題描述

我正在對 DB2 進行一個簡單的存儲過程調(diào)用.當(dāng)它調(diào)用存儲過程時，它總是返回這個錯誤:

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 代碼:

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

===============db2 clp 命令行有效:

============== 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 @

推薦答案

基本上SQLCODE=-440, SQLSTATE=42884"表示找不到存儲過程.

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

我看到一個很常見的原因是參數(shù)不匹配.

I saw a very common cause is the argument doesn't match.

就我而言，我注意到在 java 代碼中，我必須將架構(gòu)名稱放在存儲過程名稱的前面，例如，而不是 median_result_set(?)，我應(yīng)該使用 SCHEMANAME.median_result_set(?)

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

可以使用一些數(shù)據(jù)庫管理工具找到此 SP 的 SCHEMANAME.

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

我不需要從命令行指定架構(gòu)名稱的原因:似乎當(dāng)我在創(chuàng)建該 SP 時使用同一用戶從 CLP 命令行調(diào)用 SP 時，不需要架構(gòu)名稱(因為它們在內(nèi)部匹配).當(dāng)然，在命令行中指定模式總是正確的.我觀察到 DB2 在內(nèi)部使用用戶名作為模式名.例如，如果ADMINISTRATOR"創(chuàng)建了一個 SP，那么字符串ADMINISTRATOR"就是它的架構(gòu)名稱，只要我在 Windows 上看到.

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.

這篇關(guān)于DB2 java 存儲過程調(diào)用返回錯誤 SQLCODE=-440, SQLSTATE=42884的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！