DB2 java存储过程调用返回错误SQLCODE = -440,SQLSTATE = 42884
发布时间:2020-12-15 04:12:59 所属栏目:Java 来源:网络整理
导读:我正在对DB2进行简单的存储过程调用.虽然它调用存储过程,但它总是返回此错误: DB2 SQL Error: SQLCODE=-440,SQLSTATE=42884,SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE,DRIVER=3.66.46 ========== Java代码: String JDBC_DRIVER = "com.ibm.db2.jcc.DB2Driver"
我正在对DB2进行简单的存储过程调用.虽然它调用存储过程,但它总是返回此错误:
DB2 SQL Error: SQLCODE=-440,SQLSTATE=42884,SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE,DRIVER=3.66.46 ========== 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(); ============== 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 ================ 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,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”表示无法找到存储过程.
我看到一个非常常见的原因是参数不匹配. 对于我的情况,我注意到在java代码中,我必须将模式名称放在存储过程名称的前面,例如,而不是median_result_set(?),我应该做SCHEMANAME.median_result_set(?) 可以使用某些数据库管理工具找到此SP的SCHEMANAME. 我不需要从命令行指定模式名称的原因:似乎当我在创建该SP时使用同一用户从CLP命令行调用SP时,不需要模式名称(因为内部他们匹配).当然,如果在命令行指定架构,它总是正确的.我观察到DB2内部使用用户名作为模式名称.例如,如果“管理员”创建了SP,则只要我在Windows上看到,字符串“ADMINISTRATOR”就是其架构名称. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |