DB2 stored procedure to run multiple insert & update SQL statements using List array from Java application

Question

I want to use a DB2 stored procedure to run multi insert & update SQL statements from my Java application. I have prepared below procedure that contains error.

In this implementation, I first want to create a temporary table to store the SQL statements. Then use a loop to insert the SQL statements into the temporary table using a prepared statement. Finally, we execute the SQL statements in batch mode.

To call this stored procedure from my Java application, I want to pass the list of SQL statements as a List array of strings.

Here is my procedure:

CREATE OR REPLACE PROCEDURE multiInsertAndUpdate(IN sqlStatements VARCHAR(2000) ARRAY)
BEGIN
DECLARE batchStmt VARCHAR(2000);
DECLARE i INTEGER DEFAULT 0;
-- Create a temporary table to store the SQL statements
CREATE TEMPORARY TABLE temp_sql_statements (
id INTEGER GENERATED ALWAYS AS IDENTITY,
statement VARCHAR(2000)
);
-- Insert the SQL statements into the temporary table using a prepared statement
DECLARE insertStmt STATEMENT;
SET insertStmt = 'INSERT INTO temp_sql_statements(statement) VALUES(?)';
FOR i IN 1..CARDINALITY(sqlStatements) DO
   EXECUTE IMMEDIATE insertStmt USING sqlStatements[i];
END FOR;
-- Execute the SQL statements in batches of 1000
SET i = 0;
WHILE i >= 0 DO
DECLARE stmtCursor CURSOR WITH HOLD FOR
SELECT statement FROM temp_sql_statements ORDER BY id FOR UPDATE SKIP LOCKED FETCH FIRST 1000 ROWS ONLY;
OPEN stmtCursor;
FETCH FROM stmtCursor INTO batchStmt;
IF batchStmt IS NOT NULL THEN
  DECLARE batchStmts VARCHAR(20000);
  SET batchStmts = batchStmt;
  SET i = i + 1;
  WHILE FETCH FROM stmtCursor INTO batchStmt DO
    SET batchStmts = batchStmts || batchStmt || ';';
    SET i = i + 1;
    IF i % 1000 = 0 THEN
      EXECUTE IMMEDIATE batchStmts;
      SET batchStmts = '';
    END IF;
  END WHILE;
  IF batchStmts <> '' THEN
    EXECUTE IMMEDIATE batchStmts;
  END IF;
ELSE
  SET i = -1;

END IF;
CLOSE stmtCursor;
END WHILE;
-- Drop the temporary table
DROP TABLE temp_sql_statements;
END

Please assist align the procedure

// JAVA Call the stored procedure with the array of SQL statements

    static void executeStatements(Connection conn, List<String> sqlStatements)
{

    try {
        CallableStatement cstmt = conn.prepareCall("{CALL multiInsertAndUpdate(?)}");

        // Convert the List<String> to a SQL Array
        Array sqlArray = conn.createArrayOf("VARCHAR", sqlStatements.toArray());

        cstmt.setArray(1, sqlArray);
        cstmt.execute();

        sqlArray.free();
        cstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The procedure can't execute due to errors

Answer 1

Interesting procedure. I assume your usage is such that you are not worried what SQL gets injected by the Java program?

I've been using arrays a lot recently, typically with row types. For a working (but more complex) example see my SAVE_ATTRIBUTES procedure at https://github.com/easydataservices/db2-auth/blob/main/db2/schema/module_attributes.sql, and then look at the Java code for saveAttributes(String sessionId, List sessionAttributes) at https://github.com/easydataservices/db2-auth/blob/main/java/src/open/auth/AuthAttributesDao.java

Note line 116:

Struct[] attributeStructs = new Struct[sessionAttributes.size()];

Also line 141:

attributeArray = connection.createArrayOf(schemaName + "ATTRIBUTES.SESSION_ATTRIBUTE", attributeStructs);

You see that is what I am passing to createArrayOf(...), Struct[] not Object[]. In JDBC any row type is a Struct, so an array of a row type is a Struct[].

In your case, VARCHAR is a String, so for a VARCHAR array you should be passing String[].

Hope this helps.