Re: [JDBC] JDBC drive 42 release breaks seriously for RETURNING? - Mailing 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 github

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:

Certainly not intended. Thanks for the report!

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:

Something like below

sqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})

pstmt.executeUpdate()
resultSet = pstmt.getGeneratedKeys()     --->  resultset  is empty using 42 series, but one row returned using 94. series

On 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órzano

On Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:

Hello:


I have a working  call using

connection.prepareStatement(sqlInsert, generatedIds)

and get the expected resultset after insertion  using 9.4 series driver.

I just upgraded to 42 series latest,  it is not getting the resultset anymore.

I downgraded to 9.4 drive and it worked again.  It is a bit shocking.

Is it a bug or change?

Thanks