Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Opening new PreparedStatement will close opened ResultSet (in AUTOCOMMIT mode) [JDBC304] #350

Closed
firebird-automations opened this issue Mar 24, 2013 · 18 comments

Comments

@firebird-automations
Copy link

Submitted by: Česnek Michal (cesnek)

Relate to JDBC305
Is related to JDBC209

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!

Commits: 3f98f54 a7df803 68fa2d2 72ff1fd FirebirdSQL/fbt-repository@84490d6 FirebirdSQL/fbt-repository@b689545 FirebirdSQL/fbt-repository@611977c FirebirdSQL/fbt-repository@005e6c5

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Closed as 'Won't fix', described behaviour is not a bug but as designed.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Author

Commented by: Česnek Michal (cesnek)

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!

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Commented by: Česnek Michal (cesnek)

I use DBPool, http://www.snaq.net/java/DBPool/. I will try to use with another Connection pool manager.
Thanks for quick response.

@firebird-automations
Copy link
Author

Commented by: Česnek Michal (cesnek)

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 <http://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)

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.2.3 [ 10510 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC305 [ JDBC305 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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 JDBC305 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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC209 [ JDBC209 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Committed fix for this problem. I noticed some additional problems surrounding ResultSets (see JDBC305 and JDBC307), 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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment