Re: ResultSet updates are not retained - 42.2.23

Hi,

With the latest release 42.2.23 ResultSet updates are not propagated to the database. Below is a sample code to verify the issue. In the below code we are querying the record using the primary key in that table.

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class RowSet {

    public void test () throws SQLException, ClassNotFoundException {
        Class.forName("org.postgresql.Driver");
        Connection connection = DriverManager.getConnection("jdbc:postgresql://192.168.0.100:5432/testdb", "postgres", "xxxxxxxxxxxxxxxx");
        connection.setAutoCommit(false);
        String sql = "SELECT * FROM plan_data where plan_id = 30756";
        ResultSet rs = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ).executeQuery(sql);
        rs.next();
        System.out.println("Starting Value: " + rs.getDate("accounting_current_start"));
        rs.updateDate("accounting_current_start", Date.valueOf("2020-01-01"));
        rs.updateRow();
        System.out.println("After Update: " + rs.getDate("accounting_current_start"));
        connection.commit();
       
        sql = "SELECT * FROM plan_data where plan_id = 30756";
        rs = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ).executeQuery(sql);
        rs.next();
        System.out.println("After Requery: " + rs.getDate("accounting_current_start"));
       
        connection.close();       
    }
   
    public static void main(String args[]) {       
        try {
            new RowSet().test();
        } catch (SQLException e) {       
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

}



----------------OUTPUT  with 42.2.23 -----------
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2021-01-01


Update accounting_current_start to 2021-01-01 using a query directly on the database and rerun with 42.2.22

----------------OUTPUT  with 42.2.22 -----------
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2020-01-01

Thanks,
Prasanth

On 7/6/21 10:41 AM, Dave Cramer wrote:

  Branch: refs/tags/REL42.2.23  Home:   https://github.com/pgjdbc/pgjdbc

@Test
public void testUpdateDate() throws Exception{
  Date testDate = Date.valueOf("2021-01-01");
  TestUtil.execute( "insert into hasdate values (1,'2021-01-01'::date)", con);
  con.setAutoCommit(false);
  String sql = "SELECT * FROM hasdate where id=1";
  ResultSet rs = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ).executeQuery(sql);
  assertTrue(rs.next());
  assertEquals(testDate, rs.getDate("dt"));
  rs.updateDate("dt", Date.valueOf("2020-01-01"));
  rs.updateRow();
  assertEquals(Date.valueOf("2020-01-01"), rs.getDate("dt"));
  System.out.println("After Update: " + rs.getDate("dt"));
  con.commit();
  rs = con.createStatement().executeQuery("select dt from hasdate where id=1");
  assertTrue(rs.next());
  assertEquals(Date.valueOf("2020-01-01"), rs.getDate("dt"));
  rs.close();
}