首页 > 解决方案 > 调用具有输入参数数组行类型的 db2 过程的任何替代方法?

问题描述

我正在尝试将行类型的数组作为输入传递给来自 jdbc 的存储过程。

编写了下面的代码并且连接中不支持获取“方法 createStruct”。

桌子:

create table wspout.test (username varchar(100),location varchar(100),status varchar(100));

行类型:

CREATE OR REPLACE TYPE TEST_ROW AS ROW ANCHOR ROW OF test;

数组行类型:

CREATE OR REPLACE TYPE TEST_ROW_ARRAY AS TEST_ROW ARRAY[];

存储过程名称:

CREATE OR REPLACE PROCEDURE TEST_PROC (IN p_test_row_array TEST_ROW_ARRAY)
P1: BEGIN
DECLARE i INTEGER;
DECLARE v_test_row TEST_ROW;
SET i = 1;
WHILE i < CARDINALITY(p_test_row_array) DO
    SET v_test_row = p_test_row_array[i];
    insert into test values(v_test_row.username,v_test_row.location,v_test_row.status);
    set i = i + 1;
END WHILE;
END P1@

我的java类:从这里调用存储过程

package Test;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.List;

public class TestJdbc{

  public static void main(String[] args) {

    try {
      Class.forName("com.ibm.db2.jcc.DB2Driver");
    }
    catch (ClassNotFoundException e) {
        e.printStackTrace();
        return;
    }
    System.out.println("DB2 driver is loaded successfully");
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = DriverManager.getConnection("jdbc:db2://localhost:40000/db2inst1","test","test123");
        if (conn != null)
        {
        System.out.println("DB2 Database Connected");


        List<Object> test_row_List = new ArrayList<>();
        Struct test_row[] = new java.sql.Struct[3];
        Array test_row_arr;        

        test_row[0] = conn.createStruct("TEST_ROW", new Object[] { "user1","Location1", "status1"});
        test_row_List.add(test_row[0]);

        test_row[1] = conn.createStruct("TEST_ROW", new Object[] { "user2","Location2", "status2"});
        test_row_List.add(test_row[1]);

        test_row[2] = conn.createStruct("TEST_ROW", new Object[] { "user3","Location3", "status4"});
        test_row_List.add(test_row[2]);

        test_row_arr = conn.createArrayOf("TEST_ROW", test_row_List.toArray());

        System.out.println("Array value" + test_row_arr);

        pstmt = conn.prepareCall("CALL TEST_PROC(?)");                      
        pstmt.setArray(1, test_row_arr);
        pstmt.execute();
        }
    } catch (SQLException e) {
        System.out.println("DB2 Database connection e");
        e.printStackTrace();
        return;
    }


  }

运行时我低于异常。

*DB2 driver is loaded successfully
DB2 Database Connected
DB2 Database connection e
com.ibm.db2.jcc.am.SqlFeatureNotSupportedException: [jcc][t4][10181][12052][4.11.69] Method createStruct is not supported. ERRORCODE=-4450, SQLSTATE=0A504*
    at com.ibm.db2.jcc.am.gd.e(gd.java:605)
    at com.ibm.db2.jcc.am.gd.c(gd.java:625)
    at com.ibm.db2.jcc.am.lb.createStruct(lb.java:6761)
    at Test.TestJdbc.main(TestJdbc.java:38)

标签: javajdbcdb2db2-luw

解决方案


根据在 JDBC 应用程序中使用 ARRAY of ROW 参数调用存储过程

...
//PreparedStatement pstmt = null;
CallableStatement pstmt = null;
...
//java.util.List<Object> test_row_List = new java.util.ArrayList<>();
// Actually it's not needed at all
java.util.List<Object> test_row_List = new java.util.ArrayList<Object>();
...
//test_row_arr = con.createArrayOf("TEST_ROW", test_row_List.toArray());
test_row_arr = con.createArrayOf("TEST_ROW", test_row);
...

你应该使用以下,除非你真的想插入数组中的所有行,但最新的。

当i <= CARDINALITY(p_test_row_array)


推荐阅读