On 30-11-2018 08:23, Mark Rotteveel mark@... [Firebird-Java]
wrote:

> On 2018-11-29 20:36, Felipe Jaekel fkjaekel@... [Firebird-Java]
> wrote:
>> Hi,
>>
>> After upgrading from Firebird 2.5.4 + Jaybird 2.2...14 to Firebird
>> 3.0.4 + Jaybird 3.0.5, places that use
>> Statement..RETURN_GENERATED_KEYS stopped working.
>>
>> On a SQL like this:
>>
>> update or insert into SIRES01
>> (id,
>> <<fields>>)
>>
>> values
>> (:id ,
>> <<parameters>>)
>>
>> matching (id) returning id;
>>
>> I get the following error:

...

>> GRAVE: EjbTransactionUtil.handleSystemException: Error preparing
>> statement - Dynamic SQL Error; SQL error code = -104; Token unknown -
>> line 170, column 11; "ID" [SQLState:42000, ISC error code:335544634]
>> org.sql2o.Sql2oException: Error preparing statement - Dynamic SQL
>> Error; SQL error code = -104; Token unknown - line 170, column 11;
>> "ID" [SQLState:42000, ISC error code:335544634]
>
> This error suggests your actual query is using RETURNING "ID", while
> using connection dialect 1 (or the query is otherwise syntactically
> invalid).
>
>> I thought it could be related to Sql2o, but the problem happens with
>> plain JDBC too. I also updated antlr-runtime from 3.4 to 4.7.
>>
>> Do I need additional configuration for Firebird 3?
>
> Jaybird 2.2 and earlier ignored the connection dialect in a number of
> places and then used dialect 3 anyway. The behavior suggests you use
> connection property sqlDialect=1 (or one of its aliases). Try removing
> it, or using sqlDialect=3.
>
> Otherwise, could you please provide me with a reproducible (and fully
> executable) case so I can test it (preferably in plain JDBC).

I have managed to reproduce the problem, it seems to be a bug in Jaybird
3. For some reason the returning clause is not correctly detected by the
grammar, leading Jaybird to unconditionally add the RETURNING
<column-list> itself. Given it is now possible to alias columns in the
RETURNING list, the Firebird parser only chokes when it gets to the
first column in that list (yielding token unknown "ID" instead of token
unknown RETURNING).

The workaround is to not include `returning id` to your own query and
instead let Jaybird generate it for you.

My reproduction was:

public static void main(String[] args) throws SQLException {
try (Connection connection = DefaultDb.createDefaultConnection();
Statement stmt = connection.createStatement()) {
stmt.execute("update or insert into person (id, name)
values (3, 'henk') "
+ "matching (id) "
+ "returning id", Statement.RETURN_GENERATED_KEYS);

try (ResultSet keys = stmt.getGeneratedKeys()) {
while (keys.next()) {
System.out.println(keys.getInt(1));
}
}
}
}

--
Mark Rotteveel