Обсуждение: Boolean column stick to "false" after calling updateRow() onupdateable ResultSet
Boolean column stick to "false" after calling updateRow() onupdateable ResultSet
От
Clemens Eisserer
Дата:
Hi, I've observed something unexpected when migrating an application from PostgreSQL-8.4.21 & postgresql-8.4-701.jdbc3.jar to PostgreSQL-12 & postgresql-42.2.9. With the updated software-stack boolean columns always seem to stick to "false" after updateRow() is called on updateable ResultSets. The value stored however is correct, and calling refreshRow() right after updateRow() seems to restore the "old" behaviour and restores the value set a few lines aboce. I could fix the issue simply by forcing the additional roundtrip caused by refreshRow - however I still wonder - is the observed behaviour conformat to the spec / to be spected? Thank you in advance & best regards, Clemens //DDL CREATE TABLE sometable (id integer NOT NULL, someprop boolean DEFAULT false); //JDBC access Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = st.executeQuery("SELECT * FROM sometable WHERE id=..."); rs.next(); System.out.println(rs.getBoolean("someprop")); // value stored in DB rs.updateBoolean("vereinsFlugzeug", true); System.out.println(rs.getBoolean("someprop")); //Matches the value set above (true) rs.updateRow(); // rs.refreshRow(); //fetches the value stored System.out.println(rs.getBoolean("someprop")); // always returns false.
Re: Boolean column stick to "false" after calling updateRow() onupdateable ResultSet
От
Clemens Eisserer
Дата:
Hi again, I debugged a bit into the driver and it seems I've found the cause for the behaviour (quite sure it is a bug). I've filed id #1623 regarding this issue. In PgResultSet.getBoolean() there is manual type conversion from byte[] to boolean going on: int col = columnIndex - 1; if (Oid.BOOL == fields[col].getOID()) { final byte[] v = thisRow[col]; return (1 == v.length) && (116 == v[0]); // 116 = 't' } ... so in case the value of thisRow[col] is a single character with the letter 't' the check succeeds. This is the case when the value was sent from the server (right after the select or after calling refreshRow()). However after updateRow(), fields[col]. contains the string "true", because of length != 1, the check fails. A quick&dirty hack would be to extent the condition to also check for "true" - however I lack the in-depth-knowledge wether it would be cleaner to avoid the "true" lurking around in the first place. Best regards, Clemens Am Sa., 25. Jan. 2020 um 17:43 Uhr schrieb Clemens Eisserer <linuxhippy@gmail.com>: > > Hi, > > I've observed something unexpected when migrating an application from > PostgreSQL-8.4.21 & postgresql-8.4-701.jdbc3.jar to PostgreSQL-12 & > postgresql-42.2.9. > > With the updated software-stack boolean columns always seem to stick > to "false" after updateRow() is called on updateable ResultSets. > The value stored however is correct, and calling refreshRow() right > after updateRow() seems to restore the "old" behaviour and restores > the value set a few lines aboce. > > I could fix the issue simply by forcing the additional roundtrip > caused by refreshRow - however I still wonder - is the observed > behaviour conformat to the spec / to be spected? > > Thank you in advance & best regards, Clemens > > > //DDL > CREATE TABLE sometable (id integer NOT NULL, someprop boolean DEFAULT false); > > //JDBC access > Statement st = > connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, > ResultSet.CONCUR_UPDATABLE); > ResultSet rs = st.executeQuery("SELECT * FROM sometable WHERE id=..."); > rs.next(); > System.out.println(rs.getBoolean("someprop")); // value stored in DB > rs.updateBoolean("vereinsFlugzeug", true); > System.out.println(rs.getBoolean("someprop")); //Matches the > value set above (true) > rs.updateRow(); > // rs.refreshRow(); //fetches the value stored > System.out.println(rs.getBoolean("someprop")); // always returns false.
Re: Boolean column stick to "false" after calling updateRow() onupdateable ResultSet
От
"David G. Johnston"
Дата:
On Sat, Jan 25, 2020 at 12:08 PM Clemens Eisserer <linuxhippy@gmail.com> wrote:
Hi again,
I debugged a bit into the driver and it seems I've found the cause for
the behaviour (quite sure it is a bug).
I've filed id #1623 regarding this issue.
Actually it was PostgreSQL Bug #16230 - and I have noted there the bug report went to the wrong project. pgJDBC has its own process.
David J.