Exception when doing "insert into ... " with two "select" combined by "union"

[GoogleCodeExporter]

What steps will reproduce the problem?

1. h2 embedded connection
2. work over prepared statement
3. make update with next sql

INSERT INTO Trx (id, owner_id, started_at, trx_class_id, parent_id) 
SELECT ?, ?, ?, ?, ? 
UNION ALL 
SELECT ?, ?, ?, ?, ?  

What is the expected output? What do you see instead?

Correct result --- two record must be inserted into table Trx, but i got
org.h2.jdbc.JdbcSQLException: Неизвестный тип данных: "?, ?"
Unknown data type: "?, ?"; SQL statement:
INSERT INTO Trx (id, owner_id, started_at, trx_class_id, parent_id) 
SELECT ?, ?, ?, ?, ? 
UNION ALL 
SELECT ?, ?, ?, ?, ?  [50004-126]
    at org.h2.message.Message.getSQLException(Message.java:110)
    at org.h2.message.Message.getSQLException(Message.java:121)
    at org.h2.message.Message.getSQLException(Message.java:74)
    at org.h2.value.Value.getHigherOrder(Value.java:312)
    at org.h2.command.dml.SelectUnion.prepare(SelectUnion.java:226)
    at org.h2.command.dml.Insert.prepare(Insert.java:215)
    at org.h2.command.Parser.prepareCommand(Parser.java:236)
    at org.h2.engine.Session.prepareLocal(Session.java:416)
    at org.h2.engine.Session.prepareCommand(Session.java:377)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1066)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
...
    at java.lang.Thread.run(Thread.java:619)

Original issue reported on code.google.com by 7316...@gmail.com on 29 Dec 2009 at 3:48

[GoogleCodeExporter]

Why don't you use

INSERT INTO Trx (id, owner_id, started_at, trx_class_id, parent_id) 
VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?)

Original comment by thomas.t...@gmail.com on 2 Jan 2010 at 3:13

[GoogleCodeExporter]

I use h2database in unit-test.
Main database is MS SQLServer.
Your SQL construction doesn't work in MS SQL :-(

Original comment by 7316...@gmail.com on 11 Jan 2010 at 9:58

[GoogleCodeExporter]

Hi,

Thanks! I understand the problem now. I will add a feature request for "Issue 
156:
Support SELECT ? UNION SELECT ?.". However, I don't plan to implement this 
feature
currently. I suggest to use single-row insert statements, or batch updates.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 14 Jan 2010 at 7:26

[GoogleCodeExporter]

This feature request is now tracked in the roadmap.

Original comment by thomas.t...@gmail.com on 15 Jan 2010 at 8:17

• Changed state: InRoadmap