Обсуждение: [pgjdbc/pgjdbc]
Branch: refs/tags/REL42.2.23 Home: https://github.com/pgjdbc/pgjdbc
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:
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
On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin@pangburngroup.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
Thank you for the quick response. Created a new test table and tried the same on that and it is working as expected. So probably something unique about this table.
Test on new table
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2020-01-01
Test on plan_data
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2021-01-01
Below are the details about the columns in question. The table has 100+ columns.
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+--------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
10525600 | accounting_current_start | 1082 | -1 | 4 | 26 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | | |
10525600 | plan_id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | t | f | | | f | t | 0 | 0 | | | |
(2 rows)
Thanks,
Prasanth
Test on new table
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2020-01-01
Test on plan_data
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2021-01-01
Below are the details about the columns in question. The table has 100+ columns.
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+--------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
10525600 | accounting_current_start | 1082 | -1 | 4 | 26 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | | |
10525600 | plan_id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | t | f | | | f | t | 0 | 0 | | | |
(2 rows)
Thanks,
Prasanth
On 7/8/21 12:08 PM, Dave Cramer wrote:
On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin@pangburngroup.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/pgjdbcCan 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
On Thu, 8 Jul 2021 at 14:13, Prasanth <dbadmin@pangburngroup.com> wrote:
Thank you for the quick response. Created a new test table and tried the same on that and it is working as expected. So probably something unique about this table.
Can I get the entire table definition as in create table .... Something in .23 broke it.
Thanks,
Dave
Test on new table
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2020-01-01
Test on plan_data
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2021-01-01
Below are the details about the columns in question. The table has 100+ columns.
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+--------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
10525600 | accounting_current_start | 1082 | -1 | 4 | 26 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | | |
10525600 | plan_id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | t | f | | | f | t | 0 | 0 | | | |
(2 rows)
Thanks,
PrasanthOn 7/8/21 12:08 PM, Dave Cramer wrote:On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin@pangburngroup.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/pgjdbcCan 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
This table is more than 20 years old I don't have the original create table DDL. I can pull the DDL from DBeaver would that help?
Thanks,
Prasanth
Thanks,
Prasanth
On 7/8/21 1:18 PM, Dave Cramer wrote:
On Thu, 8 Jul 2021 at 14:13, Prasanth <dbadmin@pangburngroup.com> wrote:Thank you for the quick response. Created a new test table and tried the same on that and it is working as expected. So probably something unique about this table.Can I get the entire table definition as in create table .... Something in .23 broke it.Thanks,Dave
Test on new table
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2020-01-01
Test on plan_data
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2021-01-01
Below are the details about the columns in question. The table has 100+ columns.
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+--------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
10525600 | accounting_current_start | 1082 | -1 | 4 | 26 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | | |
10525600 | plan_id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | t | f | | | f | t | 0 | 0 | | | |
(2 rows)
Thanks,
PrasanthOn 7/8/21 12:08 PM, Dave Cramer wrote:On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin@pangburngroup.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/pgjdbcCan 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
pg_dump -t plan_data -s
Dave Cramer
www.postgres.rocks
On Thu, 8 Jul 2021 at 14:24, Prasanth <dbadmin@pangburngroup.com> wrote:
This table is more than 20 years old I don't have the original create table DDL. I can pull the DDL from DBeaver would that help?
Thanks,
PrasanthOn 7/8/21 1:18 PM, Dave Cramer wrote:On Thu, 8 Jul 2021 at 14:13, Prasanth <dbadmin@pangburngroup.com> wrote:Thank you for the quick response. Created a new test table and tried the same on that and it is working as expected. So probably something unique about this table.Can I get the entire table definition as in create table .... Something in .23 broke it.Thanks,Dave
Test on new table
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2020-01-01
Test on plan_data
Starting Value: 2021-01-01
After Update: 2020-01-01
After Requery: 2021-01-01
Below are the details about the columns in question. The table has 100+ columns.
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+--------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
10525600 | accounting_current_start | 1082 | -1 | 4 | 26 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | | |
10525600 | plan_id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | t | t | f | | | f | t | 0 | 0 | | | |
(2 rows)
Thanks,
PrasanthOn 7/8/21 12:08 PM, Dave Cramer wrote:On Thu, 8 Jul 2021 at 12:00, Prasanth <dbadmin@pangburngroup.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/pgjdbcCan 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