INSERT SELECT not working

Question

Using Informix 11.7, I'm trying to execute a INSERT SELECT query with jdbc positional parameters in the select statement like this :

INSERT INTO table1(id, code, label) 
SELECT ?, ?, ? FROM table2
WHERE ...

Parameters are set like this :

 stmt.setString(1, "auniqueid");
 stmt.setString(2, "code");
 stmt.setString(3, "coollabel");

I get the following error :

Exception in thread "main" java.sql.SQLException: A syntax error has occurred.

When positional parmeters "?" are placed elsewhere it works fine. I have not this problem using PostgreSQL. What's wrong with my query ? I use the Informix JDBC Driver v3.70 JC1.

Thanks for your help.

score 3 · Answer 1 · answered Feb 01 '13 at 19:14

3

Are you expecting to get column names specified via the placeholders? If so, you're on a hiding to nothing; you cannot use placeholders for structural elements of a query such as column or table names. They can only ever replace values. If you want dynamic SQL to specify the columns, use dynamic SQL; create a string with the content:

INSERT INTO table1(id, code, label)
    SELECT auniqueid, code, coollabel
      FROM table2
     WHERE ...

and work with that.

If those placeholders were going to be values, then you'd be inserting the same values over and over, once for each row returned by the query, and that normally isn't what you'd want; you'd simply insert one row with a VALUES clause, where placeholders are permitted:

INSERT INTO table1(id, code, label) VALUES(?, ?, ?);

That would work fine.

AFAIK, this behaviour conforms to the SQL standard. If it works differently in PostgreSQL, then PostgreSQL has provided an extension to the standard.

answered Feb 01 '13 at 19:14

Jonathan Leffler

730,956
141
904
1,278

no that's not what I am trying to do. I want to specify column values via the placeholders ? and insert many rows at a time to improve performance. In fact my SELECT query is a little bit more complex, there is a NOT EXISTS clause which filters results. Of course it could work with the INSERT VALUES syntax but I would have to split my process in two parts : first the SELECT query, which would return values I want to insert, then a the multiple INSERT VALUES queries, looping on the SELECT's results. – mishka Feb 03 '13 at 14:21
OK; I'm not sure precisely why you want to insert the same values over and over again, but if that's what you want, then casting the placeholders will work (it would have been my next suggestion if Mark Rotteveel had not already made the suggestion, though I might have been tempted to suggest `?::VARCHAR(16)` notation instead of the SQL standard `CAST(? AS VARCHAR(16))`). So, you're OK. (It can be helpful to explain briefly the effect you're aiming to achieve to head off detours like this answer. Your question doesn't mention that you're trying to insert many copies of the same data.) – Jonathan Leffler Feb 03 '13 at 22:06
Sorry for my lack of clearness. In fact that's not exactly the same values I want to insert again and again. My SELECT clause is like this : SELECT table2.id, 'fixedvalue', 'fixedvalue'. I finnaly choose to construct by hand my query, not using JDBC placeholders to avoid portability problems between SQL types, and prevent from database schema modifications. Anyway, thanks for your help ! – mishka Feb 04 '13 at 09:42

score 1 · Accepted Answer · answered Feb 01 '13 at 16:57

Warning: I have no experience with Informix, answer is based on general observations

When specifying parameters the database will need to know the type of each parameter. If a parameter occurs in the select-list, then there is no way for the database to infer the type of the parameter. Some database might be capable of delaying that decision until it actually receives the parameters, but most database will need to know this at parse time. This is probably the reason why you receive the error.

Some databases - I don't know if this applies to Informix - allow you to cast parameters. So for example:

SELECT CAST(? AS VARCHAR(20)), CAST(? AS VARCHAR(10)), CAST(? AS VARCHAR(5)) FROM ...

In that case the database will be able to infer the parameter types and be able to parse the query correctly.

With this I do assume you are not trying to specify columnnames for the select-list using parameters, as that is not possible.

INSERT SELECT not working

2 Answers2

Linked