Обсуждение: [JDBC] RETURN_GENERATED_KEYS does not work when insert statement is using a"with" query.
[JDBC] RETURN_GENERATED_KEYS does not work when insert statement is using a"with" query.
От
Frédéric Trégon
Дата:
Greetings,
I believe I found a defect in the latest postgresql driver (42.1.4) which did not occur with the earlier version 9.3-1102-jdbc41.
For a PreparedStatement used for inserting with the option RETURN_GENERATED_KEYS, if the sql contains a "with" query, then the generated keys are not returned.
For example:
---
create table testjdbckey (
id SERIAL PRIMARY KEY,
mydate date
);
---
String sql = "WITH t AS (SELECT NOW() AS thedate) INSERT INTO testjdbckey (mydate) VALUES ((SELECT thedate FROM t))";
Connection conn =...;
PreparedStatement ps = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
// Will not enter the while loop with postgresql-42.1.4
System.out.println("Key: " + rs.getInt(1));
}
---
Of course if I remove the "with" and inline my subrequest it works. This used to work perfectly with the 9.3-1102-jdbc41 driver.
What do you think?
Best Regards,
Re: RETURN_GENERATED_KEYS does not work when insert statementis using a "with" query.
От
Vladimir Sitnikov
Дата:
Hi,
Frédéric>What do you think?
This is known as https://github.com/pgjdbc/pgjdbc/issues/845
Feel free to chime in an update the parser.
I think the idea there is to keep looking for "insert/update/delete/select" keywords on a top level (outside of the braces) in case the first keyword was "with".
Vladimir
Vladimir
Re: [JDBC] RETURN_GENERATED_KEYS does not work when insert statementis using a "with" query.
От
Vladimir Sitnikov
Дата:
Hi,
Frédéric>What do you think?
This is known as https://github.com/pgjdbc/pgjdbc/issues/845
Feel free to chime in an update the parser.
I think the idea there is to keep looking for "insert/update/delete/select" keywords on a top level (outside of the braces) in case the first keyword was "with".
Vladimir
Vladimir