Issue Details (XML | Word | Printable)

Key: JDBC-304
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Mark Rotteveel
Reporter: Česnek Michal
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

Opening new PreparedStatement will close opened ResultSet (in AUTOCOMMIT mode)

Created: 23/Mar/13 11:16 PM   Updated: 11/May/13 03:12 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.2, Jaybird 2.2.1, Jaybird 2.2.2
Fix Version/s: Jaybird 2.2.3

Time Tracking:
Not Specified

Issue Links:
Relate


 Description  « Hide
From jaybird version 2.2 and up (2.2.1, 2.2.2) is now this BUG!

When I want in AUTOCOMMIT mode read data from opened ResultSet and in the same thread I open next PreparedStatement for inserting data, so calling function "prepareStatement" internaly close previously opened ResuldSet!

This BUG is only in AUTOCOMMIT mode in transaction processing it work fine!

Example writed code:

//I get connection from connection poll. This connection is in autocommit mode.
Connection conn = ConnectionPoolManager.getConnection();
Statement stmt1 = conn.createStatement();
ResultSet rs = stmt1.executeQuery("SELECT table_name.column_name FROM table_name");
//Calling this function internaly close result set "rs"!! But only in AUTOCOMMIT in transaction processing it work fine!
Statement stmt2 = conn.prepareStatement("INSERT INTO ...");
while (rs.next()) {
   //reading data from "rs"
   //writing data to "stmt2"
}

PLEASE HELP!

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 24/Mar/13 07:32 AM
This behaviour is identical to the behaviour in Jaybird 2.1.6. It is also the behavior implied / specified by the JDBC specification:

In the Javadoc of Connection.setAutoCommit(boolean):
" If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions"

Chapter 15.2.5:
"A ResultSet object is implicitly closed when
■ ...
■ The ResultSet is created with a Holdability of CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs"

When Jaybird creates a new statement in autocommit mode it does an implicit commit, therefor the ResultSet of the other Statement is closed. This behavior is also documented in chapter 6 of the Jaybird 2.1 JDBC driver manual: http://www.firebirdsql.org/file/documentation/drivers_documentation/Jaybird_2_1_JDBC_driver_manual.pdf

For the behaviour you want, you need to explicitly create a ResultSet that is holdable over commit:
Statement stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);

Or you need to specify the connection property defaultHoldable=true Doing this has memory and performance implications for all ResultSet: the entire ResultSet is loaded into memory.

Mark Rotteveel added a comment - 24/Mar/13 07:33 AM
Closed as 'Won't fix', described behaviour is not a bug but as designed.

Česnek Michal added a comment - 25/Mar/13 07:51 AM
Yes teoretically by manual it work out, but in practice it does not.

I will change my example code by your and driver manual council:

//I get connection from connection poll. This connection is in autocommit mode.
Connection conn = ConnectionPoolManager.getConnection();
Statement stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);

// it return: 'stmt' Holdability: true, but it return true also if I create a statement without any parameters!
System.out.println("'stmt' Holdability: " + (stmt.getResultSetHoldability()==ResultSet.HOLD_CURSORS_OVER_COMMIT));

ResultSet rs = stmt1.executeQuery("SELECT table_name.column_name FROM table_name");

//This will return false - it is fine :-)
System.out.println(rs.isClosed());

//Calling this function internaly close result set "rs"!! But only in AUTOCOMMIT in transaction processing it work fine!
Statement stmt2 = conn.prepareStatement("INSERT INTO ...");

//This will return true - it isnot fine :-(
System.out.println(rs.isClosed());

while (rs.next()) {
   //reading data from "rs"
   //writing data to "stmt2"
}

In manual all works fine, but in practice it doesnot!

Mark Rotteveel added a comment - 25/Mar/13 08:22 AM
What does ConnectionPoolManager.getConnection(); actually do? Are you sure you are directly using the Jaybird implementation, and not a proxy or wrapper provided by the connection pool? Because in that case, the proxy or wrapper might have its own logic. I did a quick test of my previous comment (with a Connection obtained through DriverManager) before, and the resultset wasn't being closed.

Česnek Michal added a comment - 25/Mar/13 08:34 AM
I use DBPool, http://www.snaq.net/java/DBPool/. I will try to use with another Connection pool manager.
Thanks for quick response.

Česnek Michal added a comment - 25/Mar/13 09:01 AM
OK so I try simplify my code to minimum and this is my pure test code without using any Connection Pool Managers:

    public static void main(String[] args) throws SQLException {
        try {
            Class.forName("org.firebirdsql.jdbc.FBDriver").newInstance();
        } catch (Exception E) {
            System.err.println("Unable to load driver.");
            E.printStackTrace();
        }
        
        Connection conn1 = DriverManager.getConnection("jdbc:firebirdsql:cesnek-pc/3050:C:\\UniPOS.NBp\\run\\firebird\\UNIPOS.FDB?userName=UniPOS&password=UniPOS&lc_ctype=UTF8");
        
        DatabaseMetaData metaData = conn1.getMetaData();

        System.out.println("JDBC Driver Name: " + metaData.getDriverName());
        //std-out: JDBC Driver Name: Jaybird JCA/JDBC driver
        System.out.println("JDBC Driver Version: " + metaData.getDriverVersion());
        //std-out: JDBC Driver Version: 2.2
      
        Statement stmt = conn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
        
        System.out.println("'stmt' Holdability: " + (stmt.getResultSetHoldability()==ResultSet.HOLD_CURSORS_OVER_COMMIT));
        //std-out: 'stmt' Holdability: true
        
        String query = "SELECT client.id FROM client";
        ResultSet rs = stmt.executeQuery(query);
                
        String insertQuery = "INSERT INTO client (id, clent_pc_name) values (?, ?)";
        
        System.out.println("RS is closed: "+rs.isClosed());
        //std-out: RS is closed: false
        Statement stmt2 = conn1.prepareStatement(insertQuery,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
        System.out.println("RS is closed: "+rs.isClosed());
        //std-out: RS is closed: true
        
        int count = 0;
        while (rs.next()) { count++; }
        System.out.println("Readed rows: "+count);
        //std-out: Readed rows: 0
        
        System.exit(0);
    }

STD-OUT:
run:
JDBC Driver Name: Jaybird JCA/JDBC driver
JDBC Driver Version: 2.2
'stmt' Holdability: true
RS is closed: false
RS is closed: true
Readed rows: 0
BUILD SUCCESSFUL (total time: 0 seconds)

Mark Rotteveel added a comment - 25/Mar/13 09:42 AM
That looks a lot like what I tested. I will test it again and compare behaviour between your test and mine. For the time being I suggest you disable autocommit.

Mark Rotteveel added a comment - 25/Mar/13 02:02 PM
I am able to reproduce the problem; my quick test just checked if rs.next() would throw an exception or not (and I assumed because it didn't that everything else was ok). The problem did not occur in 2.1.6.

I will schedule it to be fixed for 2.2.3.

Mark Rotteveel added a comment - 30/Mar/13 09:05 AM
The behavior of holdable ResultSets was a bit weird in 2.1.6 as well (they were closed, but you could still scroll through them). I have created JDBC-305 for the larger scope of problems with ResultSet behavior which might require a more thorough investigation and rewrite than just fixing the problem of this issue.

Mark Rotteveel added a comment - 30/Mar/13 03:40 PM
Committed fix for this problem. I noticed some additional problems surrounding ResultSets (see JDBC-305 and JDBC-307), but fixes for that will have to wait until Jaybird 2.3 as it requires some radical changes that I am not prepared to do for 2.2.x.