CODEWITHSUNDEEP
jdbcdb2prepared-statement
Chris Snow
Chris Snow

Reputation: 24636

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

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

Upvotes: 0

Views: 426

Answers (1)

mao
mao

Reputation: 12297

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.

Upvotes: 1

Related Questions