Do a DB2 insert with a select and parameters Do a DB2 insert with a select and parameters sql sql

Do a DB2 insert with a select and parameters


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.


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.