Re: ResultSet updates are not retained - 42.2.23

On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin(at)pangburngroup(dot)com> wrote:

> 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
>
>
>
Can you tell me what the schema of plan_data is ?

I just tried and it worked fine

@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();
}

Dave