Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Do a DB2 insert with a select and parameters

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 ?

like image 256
bwawok Avatar asked Sep 01 '10 19:09

bwawok


People also ask

Can we use insert and select together?

You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.

How do I insert multiple records in Db2?

To insert multiple rows into a table, you need to: First, specify the name of the table and a list of columns in parentheses. Second, use a list of comma-separated lists of column values. Each item in the list represents a row that will be inserted into the table.


2 Answers

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.

like image 157
Ian Bjorhovde Avatar answered Oct 22 '22 20:10

Ian Bjorhovde


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.

like image 2
BalusC Avatar answered Oct 22 '22 20:10

BalusC