Search code examples
jdbcinsertinformixinsert-select

INSERT SELECT not working

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.

Solution

  • 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.