Do a DB2 insert with a select and parameters

Question

I want to do something like this:

INSERT INTO TABLEA
(
 COLUMN1, COLUMN2, COLUMN 3
)
SELECT FOOBAR, DOOBAR, ?
FROM TABLEB

And then send this to JDBC via Spring JDBC to update...

simpleJdbcTemplate.update( mySqlFromAbove, someVariableToReplaceQuestionMark );

Is this even possible? It would work fine if I replace the question mark with the hardcoded value when building my SQL query, but I don't want to open myself to SQL injection...

Edit -
I get
nested exception is com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null
Which seems to indicate
Invalid use of a parameter marker ?

Solution 1

You need to type-cast your parameter marker so DB2 knows what to expect.

For example:

INSERT INTO TABLEA
(
 COLUMN1, COLUMN2, COLUMN 3
)
SELECT FOOBAR, DOOBAR, cast(? as int)
FROM TABLEB

Obviously, cast to the appropriate type -- int is just an example.

Solution 2

Here's the DB2 SQL Message Reference. Here's an extract of relevance for the SQLCODE and SQLSTATE you retrieved:

SQL0418N

A statement contains a use of a parameter marker that is not valid.

Explanation:

Untyped parameter markers cannot be used:

• in a SELECT list
• as the sole argument of a datetime arithmetic operation
• in some cases as the sole argument of a scalar function
• as a sort key in an ORDER BY clause

Parameter markers can never be used:

• in a statement that is not a prepared statement
• in the fullselect of a CREATE VIEW statement
• in the triggered action of a CREATE TRIGGER statement
• in a query captured by DB2 Query Patroller

The statement cannot be processed.

User Response:

Correct the syntax of the statement. If untyped parameter markers are not allowed, use the CAST specification to give the parameter marker a data type.

sqlcode: -418

sqlstate: 42610

Unfortunately this doesn't answer your problem since your SQL seem to look fine. After Googling a bit more it look more like that the DB2 JDBC driver simply doesn't eat INSERT INTO ... SELECT ... statements in a PreparedStatement. It's unclear if that is missing in the SQL Message Reference or a bug in the JDBC driver.