Do a DB2 insert with a select and parameters

bwawok picture bwawok · Sep 1, 2010 · Viewed 17k times · Source

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 ?

Answer

Ian Bjorhovde picture Ian Bjorhovde · Sep 2, 2010

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.