how to pass a variable number of parameters using a jdbc prepared statement?

Question

The following statement is used on db2 to perform an UPSERT operation:

MERGE INTO mytable AS mt USING (
    SELECT * FROM TABLE (
        VALUES 
            (?, ?),
            (?, ?),
            —- ^ repeated many times, one for each row to be upserted
    )
) AS vt(id, val) ON (mt.id = vt.id)
WHEN MATCHED THEN
    UPDATE SET val = vt.val
WHEN NOT MATCHED THEN
    INSERT (id, val) VALUES (vt.id, vt.val)
;

Every time I call this statement, I will have a different number of rows to be inserted. Is it possible to make this call using a prepared statement? What would that look like?

Ref: https://stackoverflow.com/a/23784606/1033422

If the number of ? parameter-markers varies per run, then you must re-prepare if the number of parameter-markers changes. I would use a DGTT especially if there are very large numbers of rows. Yes, more statements, but easier to scale because can dynamically index the dgtt. — mao, Oct 06 '17 at 06:47
Thanks @mao. If you would like to convert this to an answer, I’d gladly accept it. — Chris Snow, Oct 07 '17 at 06:17

score 1 · Accepted Answer · edited Oct 07 '17 at 13:20

1

If the number of ? parameter-markers varies per run then you must re-prepare if the number of parameter-markers changes. I would use a Declared Global Temporary Table (DGTT) especially if there are very large numbers of rows. Yes, more statements, but easier to scale because you can dynamically index the DGTT.

For more information on temporary tables in DB2, see this question.

edited Oct 07 '17 at 13:20

Gord Thompson

116,920
32
215
418

answered Oct 07 '17 at 09:36

mao

11,321
2
13
29

how to pass a variable number of parameters using a jdbc prepared statement?

1 Answers1