Thread

1. JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-11T16:39:10Z

   Jason Southern (southern@heymax.com) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class
   
   Long Description
   ============================================================================ 
   POSTGRESQL BUG REPORT TEMPLATE 
   ============================================================================ 
   Your name : Jason Southern
   Your email address : southern@heymax.com
   
   System Configuration 
   ---------------------- 
   Architecture (example: Intel Pentium) : 600MHz Intel Pentium III, 256MB RAM
   
   Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.16 RedHat 6.2 
   
   PostgreSQL version (example: PostgreSQL-6.3) : PostgreSQL-7.0.2 
   
   Compiler used (example: gcc 2.7.2) : gcc 2.96
   
   JDBC Driver Version: 7.0.x (jdbc7.0-1.2.jar)
   
   JVM: Sun JVM 1.3
   
   Short Description
   ------------------------------------------------- 
   The PreparedStatement.setMaxRows() method affects the max row property of other objects instantiated from the statement class and it's subclasses.
   
   Please describe a way to repeat the problem. Please try to provide a 
   concise reproducible example, if at all possible:  
   ----------------------------------------------------------------------- 
   You can reproduce this behavior by loading a PostgreSQL instance with the DDL/DML script below and then compiling and running the class file below.
   
   I would have expected the setMaxRows method to only affect the object on which executed not an entire family of objects.
   
   Table for reproducing bug
   ----------------------------------------------------------------------- 
   CREATE TABLE FRUIT (
       id_fruit INTEGER,
       name VARCHAR(15)
   );
   
   INSERT INTO FRUIT VALUES (1, 'apple');
   INSERT INTO FRUIT VALUES (2, 'banana');
   INSERT INTO FRUIT VALUES (3, 'orange');
   INSERT INTO FRUIT VALUES (4, 'kumquat');
   INSERT INTO FRUIT VALUES (5, 'nectarine');
   INSERT INTO FRUIT VALUES (6, 'pear');
   INSERT INTO FRUIT VALUES (7, 'peach');
   INSERT INTO FRUIT VALUES (8, 'cantaloupe');
   INSERT INTO FRUIT VALUES (9, 'grape');
   INSERT INTO FRUIT VALUES (10, 'grapefruit');
   INSERT INTO FRUIT VALUES (11, 'avacado');
   INSERT INTO FRUIT VALUES (12, 'tomato');
   INSERT INTO FRUIT VALUES (13, 'kiwi');
   INSERT INTO FRUIT VALUES (14, 'watermelon');
   INSERT INTO FRUIT VALUES (15, 'guava');
   
   
   Sample Code
   import java.sql.*;
   
   public class MaxRowTest {
     private static Connection conn;
     private static final String DB_INSTANCE = "";
     private static final String DB_USERNAME = "";
     private static final String DB_PASSWORD = "";
   
     public static void main(String[] a) throws Exception {
       String sqlStmt;
       ResultSet rst;
       int rowCount = 0;
       Statement stmt = null;
       Statement stmt2 = null;
       PreparedStatement preStmt = null;
       PreparedStatement preStmt2 = null;
   
       System.out.println("About to connect to database...");
       connectToDatabase();
       System.out.println("Connected to database...");
   
       stmt = conn.createStatement();
       stmt2 = conn.createStatement();
   
       System.out.println("Creating prepared statement...");
       sqlStmt = "SELECT id_fruit, name FROM FRUIT WHERE id_fruit < ? ORDER BY name";
       preStmt = conn.prepareStatement(sqlStmt);
       preStmt2 = conn.prepareStatement(sqlStmt);
   
       System.out.println("Setting max rows to 5 on this prepared statement...");
       preStmt.setMaxRows(5);
   
       System.out.println("Setting argument on prepared statement to return all");
       System.out.println("fruit whose id is less than 10...");
       preStmt.setInt(1, 10);
   
       System.out.println("About to execute statement.");
       System.out.println("Expecting 5 rows...");
       rst = preStmt.executeQuery();
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Prepared statement returned ResultSet with " + rowCount + " fruit in it");
   
       System.out.println("\nUsing second prepared statement object...");
       System.out.println("Setting argument on prepared statement to return all");
       System.out.println("fruit whose id is less than 10...");
       preStmt.setInt(1, 10);
   
       System.out.println("About to execute statement.");
       System.out.println("Expecting 9 rows...");
       rst = preStmt.executeQuery();
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Prepared statement 2 returned " + rowCount + " fruit");
   
       System.out.println("\nAbout to run query to select all fruit from table.");
       System.out.println("Expecting 15 rows...");
       sqlStmt = "SELECT id_fruit, name FROM FRUIT ORDER BY name";
       rst = stmt.executeQuery(sqlStmt);
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Statement returned ResultSet with " + rowCount + " fruit in it");
   
       System.out.println("\nAbout to run query to select all fruit using second statement object.");
       System.out.println("Expecting 15 rows...");
       sqlStmt = "SELECT id_fruit,name FROM FRUIT ORDER BY name";
       rst = stmt2.executeQuery(sqlStmt);
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Statement 2 returned " + rowCount + " fruit");
   
       System.out.println("\nSetting max rows to 0 on statement object...\n");
       stmt.setMaxRows(0);
   
       System.out.println("About to run query to select all fruit from table.");
       System.out.println("Expecting 15 rows...");
       sqlStmt = "SELECT id_fruit, name FROM FRUIT ORDER BY name";
       rst = stmt.executeQuery(sqlStmt);
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Statement returned ResultSet with " + rowCount + " fruit in it");
   
       System.out.println("\nAbout to run query to select all fruit using second statement object.");
       System.out.println("Expecting 15 rows...");
       sqlStmt = "SELECT id_fruit, name FROM FRUIT ORDER BY name";
       rst = stmt2.executeQuery(sqlStmt);
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Statement 2 returned " + rowCount + " fruit");
   
       System.out.println("\nSetting argument on prepared statement to return all");
       System.out.println("fruit whose id is less than 12...");
       preStmt.setInt(1, 12);
   
       System.out.println("About to execute statement.");
       System.out.println("Expecting 5 rows...");
       rst = preStmt.executeQuery();
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Prepared statement returned ResultSet with " + rowCount + " fruit in it");
   
       System.out.println("\nUsing second prepared statement object...");
       System.out.println("Setting argument on prepared statement to return all");
       System.out.println("fruit whose id is less than 10...");
       preStmt.setInt(1, 10);
   
       System.out.println("About to execute statement.");
       System.out.println("Expecting 9 rows...");
       rst = preStmt.executeQuery();
       rowCount = 0;
   
       while (rst.next()) {
         rowCount++;
       }
       System.out.println("Prepared statement 2 returned " + rowCount + " fruit");
   
       // Close statement objects
       if (preStmt != null) {
         preStmt.close();
       }
       if (stmt != null) {
         stmt.close();
       }
     }
   
     private static void connectToDatabase() throws Exception {
       try {
         try {
           Class.forName("org.postgresql.Driver");
         } catch (ClassNotFoundException e) {
           throw new Exception("Unable to locate PostgreSQL driver.  Make sure the driver is installed and and appears in CLASSPATH.");
         }
         conn = DriverManager.getConnection(DB_INSTANCE, DB_USERNAME, DB_PASSWORD);
         conn.setAutoCommit(false);
         conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
       } catch (SQLException e) {
         throw new Exception("connectToDatabase(): [SQLException] " + e);
       }
     }
   
     protected void finalize() {
       if (conn != null) {
         try {
           conn.close();
         } catch (SQLException e) {}
       }
     }
   }
   
   No file was uploaded with this report
   
   
   

2. Re: JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-12T05:16:33Z

   Can you please try the current beta from our ftp site.  I think this is
   fixed in 7.1beta.
   
   
   > Jason Southern (southern@heymax.com) reports a bug with a severity of 2
   > The lower the number the more severe it is.
   > 
   > Short Description
   > JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class
   > 
   > Long Description
   > ============================================================================ 
   > POSTGRESQL BUG REPORT TEMPLATE 
   > ============================================================================ 
   > Your name : Jason Southern
   > Your email address : southern@heymax.com
   > 
   > System Configuration 
   > ---------------------- 
   > Architecture (example: Intel Pentium) : 600MHz Intel Pentium III, 256MB RAM
   > 
   > Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.16 RedHat 6.2 
   > 
   > PostgreSQL version (example: PostgreSQL-6.3) : PostgreSQL-7.0.2 
   > 
   > Compiler used (example: gcc 2.7.2) : gcc 2.96
   > 
   > JDBC Driver Version: 7.0.x (jdbc7.0-1.2.jar)
   > 
   > JVM: Sun JVM 1.3
   > 
   > Short Description
   > ------------------------------------------------- 
   > The PreparedStatement.setMaxRows() method affects the max row property of other objects instantiated from the statement class and it's subclasses.
   > 
   > Please describe a way to repeat the problem. Please try to provide a 
   > concise reproducible example, if at all possible:  
   > ----------------------------------------------------------------------- 
   > You can reproduce this behavior by loading a PostgreSQL instance with the DDL/DML script below and then compiling and running the class file below.
   > 
   > I would have expected the setMaxRows method to only affect the object on which executed not an entire family of objects.
   > 
   > Table for reproducing bug
   > ----------------------------------------------------------------------- 
   > CREATE TABLE FRUIT (
   >     id_fruit INTEGER,
   >     name VARCHAR(15)
   > );
   > 
   > INSERT INTO FRUIT VALUES (1, 'apple');
   > INSERT INTO FRUIT VALUES (2, 'banana');
   > INSERT INTO FRUIT VALUES (3, 'orange');
   > INSERT INTO FRUIT VALUES (4, 'kumquat');
   > INSERT INTO FRUIT VALUES (5, 'nectarine');
   > INSERT INTO FRUIT VALUES (6, 'pear');
   > INSERT INTO FRUIT VALUES (7, 'peach');
   > INSERT INTO FRUIT VALUES (8, 'cantaloupe');
   > INSERT INTO FRUIT VALUES (9, 'grape');
   > INSERT INTO FRUIT VALUES (10, 'grapefruit');
   > INSERT INTO FRUIT VALUES (11, 'avacado');
   > INSERT INTO FRUIT VALUES (12, 'tomato');
   > INSERT INTO FRUIT VALUES (13, 'kiwi');
   > INSERT INTO FRUIT VALUES (14, 'watermelon');
   > INSERT INTO FRUIT VALUES (15, 'guava');
   > 
   > 
   > Sample Code
   > import java.sql.*;
   > 
   > public class MaxRowTest {
   >   private static Connection conn;
   >   private static final String DB_INSTANCE = "";
   >   private static final String DB_USERNAME = "";
   >   private static final String DB_PASSWORD = "";
   > 
   >   public static void main(String[] a) throws Exception {
   >     String sqlStmt;
   >     ResultSet rst;
   >     int rowCount = 0;
   >     Statement stmt = null;
   >     Statement stmt2 = null;
   >     PreparedStatement preStmt = null;
   >     PreparedStatement preStmt2 = null;
   > 
   >     System.out.println("About to connect to database...");
   >     connectToDatabase();
   >     System.out.println("Connected to database...");
   > 
   >     stmt = conn.createStatement();
   >     stmt2 = conn.createStatement();
   > 
   >     System.out.println("Creating prepared statement...");
   >     sqlStmt = "SELECT id_fruit, name FROM FRUIT WHERE id_fruit < ? ORDER BY name";
   >     preStmt = conn.prepareStatement(sqlStmt);
   >     preStmt2 = conn.prepareStatement(sqlStmt);
   > 
   >     System.out.println("Setting max rows to 5 on this prepared statement...");
   >     preStmt.setMaxRows(5);
   > 
   >     System.out.println("Setting argument on prepared statement to return all");
   >     System.out.println("fruit whose id is less than 10...");
   >     preStmt.setInt(1, 10);
   > 
   >     System.out.println("About to execute statement.");
   >     System.out.println("Expecting 5 rows...");
   >     rst = preStmt.executeQuery();
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Prepared statement returned ResultSet with " + rowCount + " fruit in it");
   > 
   >     System.out.println("\nUsing second prepared statement object...");
   >     System.out.println("Setting argument on prepared statement to return all");
   >     System.out.println("fruit whose id is less than 10...");
   >     preStmt.setInt(1, 10);
   > 
   >     System.out.println("About to execute statement.");
   >     System.out.println("Expecting 9 rows...");
   >     rst = preStmt.executeQuery();
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Prepared statement 2 returned " + rowCount + " fruit");
   > 
   >     System.out.println("\nAbout to run query to select all fruit from table.");
   >     System.out.println("Expecting 15 rows...");
   >     sqlStmt = "SELECT id_fruit, name FROM FRUIT ORDER BY name";
   >     rst = stmt.executeQuery(sqlStmt);
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Statement returned ResultSet with " + rowCount + " fruit in it");
   > 
   >     System.out.println("\nAbout to run query to select all fruit using second statement object.");
   >     System.out.println("Expecting 15 rows...");
   >     sqlStmt = "SELECT id_fruit,name FROM FRUIT ORDER BY name";
   >     rst = stmt2.executeQuery(sqlStmt);
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Statement 2 returned " + rowCount + " fruit");
   > 
   >     System.out.println("\nSetting max rows to 0 on statement object...\n");
   >     stmt.setMaxRows(0);
   > 
   >     System.out.println("About to run query to select all fruit from table.");
   >     System.out.println("Expecting 15 rows...");
   >     sqlStmt = "SELECT id_fruit, name FROM FRUIT ORDER BY name";
   >     rst = stmt.executeQuery(sqlStmt);
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Statement returned ResultSet with " + rowCount + " fruit in it");
   > 
   >     System.out.println("\nAbout to run query to select all fruit using second statement object.");
   >     System.out.println("Expecting 15 rows...");
   >     sqlStmt = "SELECT id_fruit, name FROM FRUIT ORDER BY name";
   >     rst = stmt2.executeQuery(sqlStmt);
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Statement 2 returned " + rowCount + " fruit");
   > 
   >     System.out.println("\nSetting argument on prepared statement to return all");
   >     System.out.println("fruit whose id is less than 12...");
   >     preStmt.setInt(1, 12);
   > 
   >     System.out.println("About to execute statement.");
   >     System.out.println("Expecting 5 rows...");
   >     rst = preStmt.executeQuery();
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Prepared statement returned ResultSet with " + rowCount + " fruit in it");
   > 
   >     System.out.println("\nUsing second prepared statement object...");
   >     System.out.println("Setting argument on prepared statement to return all");
   >     System.out.println("fruit whose id is less than 10...");
   >     preStmt.setInt(1, 10);
   > 
   >     System.out.println("About to execute statement.");
   >     System.out.println("Expecting 9 rows...");
   >     rst = preStmt.executeQuery();
   >     rowCount = 0;
   > 
   >     while (rst.next()) {
   >       rowCount++;
   >     }
   >     System.out.println("Prepared statement 2 returned " + rowCount + " fruit");
   > 
   >     // Close statement objects
   >     if (preStmt != null) {
   >       preStmt.close();
   >     }
   >     if (stmt != null) {
   >       stmt.close();
   >     }
   >   }
   > 
   >   private static void connectToDatabase() throws Exception {
   >     try {
   >       try {
   >         Class.forName("org.postgresql.Driver");
   >       } catch (ClassNotFoundException e) {
   >         throw new Exception("Unable to locate PostgreSQL driver.  Make sure the driver is installed and and appears in CLASSPATH.");
   >       }
   >       conn = DriverManager.getConnection(DB_INSTANCE, DB_USERNAME, DB_PASSWORD);
   >       conn.setAutoCommit(false);
   >       conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
   >     } catch (SQLException e) {
   >       throw new Exception("connectToDatabase(): [SQLException] " + e);
   >     }
   >   }
   > 
   >   protected void finalize() {
   >     if (conn != null) {
   >       try {
   >         conn.close();
   >       } catch (SQLException e) {}
   >     }
   >   }
   > }
   > 
   > No file was uploaded with this report
   > 
   > 
   
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026