Thread: JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class
JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class
From
pgsql-bugs@postgresql.org
Date:
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 classand 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 runningthe 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
Re: JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class
From
Bruce Momjian
Date:
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 statementclass 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 runningthe 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 inCLASSPATH."); > } > 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