Обсуждение: 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
От
pgsql-bugs@postgresql.org
Дата:
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
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