Re: HQLDB 2.0 and setFetchSize causes a hang

A lot of issues with earlier versions have been fixed in later
versions.

Your test case looks fine, therefore it must be a bug in version 2.0
that has been fixed.

If you require support or bug fixes for an old version of the software,
you can join SupportWare at the appropriate level and sponsor a special
release.

Fred Toussi

On Thu, Feb 6, 2014, at 15:34, Geoff Watters wrote:

Recently I have noticed that the use of Statement.setFetchSize() can
cause a hang when using HSQLDB 2.0. This hang occurs if the number of
rows being read from the source is greater than a non-zero FetchSize.
This problem does not reproduce when using HSQL Db 2.3.1.
I am not sure if anyone else has seen this problem.
The thread stack when the hang occurs is as follows:-
"main" prio=10 tid=0x000000001d45e800 nid=0xfb4 runnable
[0x0000000040931000]
   java.lang.Thread.State: RUNNABLE
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)
    at java.net.SocketInputStream.read(SocketInputStream.java:122)
    at java.io.BufferedInputStream.fill(BufferedInputStream.java:235)
    at java.io.BufferedInputStream.read(BufferedInputStream.java:254)
    - locked <0x00000007af818d20> (a java.io.BufferedInputStream)
    at java.io.DataInputStream.readByte(DataInputStream.java:265)
    at org.hsqldb.result.Result.newResult(Unknown Source)
    at org.hsqldb.ClientConnection.read(Unknown Source)
    at org.hsqldb.ClientConnection.execute(Unknown Source)
    - locked <0x00000007af817148> (a org.hsqldb.ClientConnection)
    at org.hsqldb.ClientConnection.getRows(Unknown Source)
    - locked <0x00000007af817148> (a org.hsqldb.ClientConnection)
    at org.hsqldb.navigator.RowSetNavigatorClient.getBlock(Unknown
Source)
    at org.hsqldb.navigator.RowSetNavigatorClient.getCurrent(Unknown
Source)
    at org.hsqldb.jdbc.JDBCResultSet.getCurrent(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getLong(Unknown Source)
    at HsqlFetchTest.testFetchSize(HsqlFetchTest.java:33)
    at HsqlFetchTest.main(HsqlFetchTest.java:64)
A Test case that I have been using is shown below:-
//  javac -classpath .:./hsqldb.jar src/HsqlFetchTest.java
//  java -classpath ./src:./hsqldb.jar HsqlFetchTest
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class HsqlFetchTest {
    private static void createData() throws SQLException
    {
        Connection conn = getConnection();
        Statement stmt=conn.createStatement();
        try {
            stmt.execute("CREATE TABLE TEST1(ID INTEGER)");
        }
        catch (SQLException ex) {
            System.out.println("Error creating table "+ex);
        }
        finally {
            stmt.close();
        }
        PreparedStatement preStmt=conn.prepareStatement("INSERT INTO
TEST1 VALUES(?)");
        for (int i=0; i<25; i++)
        {
            preStmt.setInt(1, i);
            preStmt.execute();
        }
        conn.commit();
        preStmt.close();
        conn.close();
    }
    private static void dropData() throws SQLException
    {
        Connection conn = getConnection();
        Statement stmt=conn.createStatement();
        try {
            stmt.execute("DROP TABLE TEST1");
        }
        catch (SQLException ex) {
            System.out.println("Error dropping table "+ex);
        }
        finally {
            stmt.close();
        }
        conn.close();
    }
    private static Connection getConnection() throws SQLException
    {
        new org.hsqldb.jdbc.JDBCDriver();
        String url="jdbc:hsqldb:hsql://localhost/testdb";
        String user="SA";
        String pwd=null;
        return DriverManager.getConnection(url,user,pwd);
    }
    private static void testFetchSize(int pFetch) {
        System.out.println("Test with fetchsize="+pFetch);
        String stmtText="select ID from    TEST1 where    (1=1)";
        Connection hsqlConn = null;
        Statement stmt=null;
        ResultSet rs=null;
        try {
            hsqlConn = getConnection();
            stmt = hsqlConn.createStatement();
            if (pFetch>0) {
                System.out.println("Set fetch size to "+pFetch);
                stmt.setFetchSize(pFetch);
            }
            rs = stmt.executeQuery(stmtText);
            int nRows=0;
            while (rs.next()) {
                System.out.println("fetch="+pFetch+" rows="+nRows);
                long age = rs.getLong(1);
                nRows++;
            }
        }
        catch (Exception ex) {
            System.out.println(""+ex);
            ex.printStackTrace();
        }
        finally {
                try {
                    if (rs!=null)
                        rs.close();
                }
                catch (SQLException ex) {/* ignore */}
                try {
                    if (stmt!=null)
                        stmt.close();
                }
                catch (SQLException ex) {/* ignore */}
                try {
                    if (hsqlConn!=null)
                        hsqlConn.close();
                }
                catch (SQLException ex) {/* ignore */}
        }
    }
    public static void main(String pArgv[]) {
        try {
            dropData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }
        try {
            createData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }
        testFetchSize(0);
        testFetchSize(40);
        testFetchSize(10);
        try {
            dropData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }
    }
}

-----------------------------------------------------------------------
-------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
[1]http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ost
g.clktrk
_______________________________________________
Hsqldb-user mailing list
[2][email protected]
[3]https://lists.sourceforge.net/lists/listinfo/hsqldb-user

References

1. http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk
2. mailto:[email protected]
3. https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user