Re: [JDBC] JDBC drive 42 release breaks seriously for RETURNING? - Mailing list pgsql-jdbc
From | Tom Smith |
---|---|
Subject | Re: [JDBC] JDBC drive 42 release breaks seriously for RETURNING? |
Date | |
Msg-id | CAKwSVFGGp7-K1DYxS0N1_TF7tinn8km5xLHU+F-gVy1KR=-6nw@mail.gmail.com Whole thread Raw |
In response to | Re: [JDBC] JDBC drive 42 release breaks seriously for RETURNING? (Dave Cramer <pg@fastcrypt.com>) |
Responses |
Re: [JDBC] JDBC drive 42 release breaks seriously for RETURNING?
(Dave Cramer <pg@fastcrypt.com>)
Re: JDBC drive 42 release breaks seriously for RETURNING? (Dave Cramer <pg@fastcrypt.com>) |
List | pgsql-jdbc |
If you change your code
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) {
You might see the issue.
basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.
I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result set
On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:
I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost: 5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
pgsql-jdbc by date: