Re: [BUGS] Bug #886: jdbc "update row" can mess up other columns - Mailing list pgsql-jdbc

Half of this bug was fixed at some point, but an exception is still thrown
when calling rs.afterLast() and then rs.previous().  Attached is a
regression test addition and the appropriate fix.  Sorry for the extreme
delay in response and thanks for the thorough report.

Kris Jurka

On Fri, 24 Jan 2003 pgsql-bugs@postgresql.org wrote:

> Andrew Fyfe (afyfe@bigtribe.com) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> jdbc "update row" can mess up other columns
>
> Long Description
> The attached program when run gives me the following output
>
> DatabaseProductName: PostgreSQL
> DatabaseProductVersion: 7.3.1
> DriverName: PostgreSQL Native Driver
> DriverVersion: PostgreSQL 7.3.1 JDBC3 jdbc driver build 106
> --- Forward ---
> 1:    Colombian    7.99
> 2:    French_Roast    9.99
> --- Backward ---
> 2:    French_Roast    9.99
> 1:    Colombian    7.99
> --- Update Row 2 ---
> --- Forward ---
> 1:    Colombian    7.99
> 2:    Colombian    10.99
>
> Note that the update of the price in row 2 caused jdbc to misrepesent the name.  If one does not run through the rows
backward(give the arg "b" when running the code), then the output is correct: 
>
> --- Forward ---
> 1:    Colombian    7.99
> 2:    French_Roast    9.99
> --- Update Row 2 ---
> --- Forward ---
> 1:    Colombian    7.99
> 2:    French_Roast    10.99
>
> Suppressing the first forward pass through the data (giving "f" or "fb" to suppress both) will cause an exception to
bethrown. 
>
> Note that another forward pass after the backward makes the problem go away.
>
> Sample Code
> import java.util.*;
> import java.sql.*;
>
> class pgbug
> {
>     static String url = "jdbc:postgresql://localhost/cdb";
>     static String driver = "org.postgresql.Driver";
>     static String userName = "cdb";
>     static String password = "cdb";
>
>     public static void print(ResultSet rs) {
>     try {
>         String s = rs.getString("COF_NAME");
>         float n = rs.getFloat("PRICE");
>         int rowNum = rs.getRow();
>         System.out.println(rowNum + ":\t" + s + "\t" + n);
>     } catch (SQLException sqlEx) { /* ignore */
>         System.out.println("print ResultSet failed");
>     }
>     }
>
>     public static void forward(ResultSet rs) {
>     try {
>         System.out.println("--- Forward ---");
>         rs.beforeFirst();
>         while (rs.next()) {
>         print(rs);
>         }
>     } catch (SQLException sqlEx) { /* ignore */
>         System.out.println("print ResultSet failed");
>     }
>     }
>
>     public static void backward(ResultSet rs) {
>     try {
>         System.out.println("--- Backward ---");
>         rs.afterLast();
>         while (rs.previous()) {
>         print(rs);
>         }
>     } catch (SQLException sqlEx) { /* ignore */
>         System.out.println("print ResultSet failed");
>     }
>     }
>
>     public static void main(String args [])
>     throws Exception
>     {
>
>     Connection conn = null;
>     Statement stmt = null;
>
>     try {
>         // Load the mysql JDBC driver
>         Class.forName(driver).newInstance();
>
>         // Connect to the database
>         Properties props = new Properties();
>         props.put("user", userName);
>         props.put("password", password);
>         props.put("loglevel", "0");
>         conn = DriverManager.getConnection(url, props);
>
>         // Create a Statement
>         stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>         ResultSet.CONCUR_UPDATABLE);
>
>         DatabaseMetaData meta = conn.getMetaData();
>         System.out.println("DatabaseProductName: " +
>         meta.getDatabaseProductName());
>         System.out.println("DatabaseProductVersion: " +
>         meta.getDatabaseProductVersion());
>         System.out.println("DriverName: " +
>         meta.getDriverName());
>         System.out.println("DriverVersion: " +
>         meta.getDriverVersion());
>
>         try {
>         stmt.executeUpdate("drop table coffees");
>         } catch (SQLException sqlEx) { /* ignore */
>         System.out.println("drop table failed");
>         }
>
>         // create the table
>         String createTableCoffees = "create table coffees " +
>         "(COF_NAME VARCHAR(32) primary key, PRICE FLOAT)";
>         try {
>         stmt.executeUpdate(createTableCoffees);
>         } catch (SQLException sqlEx) { /* ignore */
>         System.out.println("create table failed");
>         }
>
>         stmt.executeUpdate("insert into coffees " +
>         "VALUES ('Colombian', 7.99)");
>         stmt.executeUpdate("insert into coffees " +
>         "VALUES ('French_Roast', 9.99)");
>
>         // Select from the test table
>         String query = "select cof_name, price from coffees";
>         ResultSet rs = stmt.executeQuery(query);
>
>         if (args.length == 0 || args[0].indexOf('f') < 0) {
>         forward(rs);
>         }
>         if (args.length == 0 || args[0].indexOf('b') < 0) {
>         backward(rs);
>         }
>
>         if (rs.absolute(2)) {
>         System.out.println("--- Update Row 2 ---");
>         rs.updateFloat("PRICE", 10.99f);
>         rs.updateRow();
>         }
>
>         forward(rs);
>     } finally {
>         if (stmt != null) {
>         try {
>             stmt.close();
>         }
>         catch (SQLException sqlEx) {
>             System.out.println("closing statement failed");
>         }
>         }
>         if (conn != null) {
>         try {
>             conn.close();
>         }
>         catch (SQLException sqlEx) {
>             System.out.println("closing connection failed");
>         }
>         }
>     }
>
>     }
> }
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>