Exceptions with multiple connections

Mon, 20 Aug 2007 07:10:09 -0700 

Hi,
I'm testing various JDBC drivers. My unit tests (see AbstractSqlTest.java) work with the SQLite db and the SQLite JDBC driver from http://www.ch-werner.de/javasqlite/ (see JDBCDriverTest.java). 


With Derby there seems to be a problem when a second statement tries to 
read its result set while a first statement did not commit/ rollback yet.



Using the Derby network server and the ClientDriver (see 
ClientDriverTest.java) I get the following exceptions:

---------------------------------------------------------------------------------------------
Testsuite: org.apache.derby.jdbc.ClientDriverTest
Tests run: 2, Failures: 0, Errors: 2, Time elapsed: 131.199 sec


Testcase: 
testUpdateSelectCommitSelect(org.apache.derby.jdbc.ClientDriverTest):   
Caused an ERROR

DERBY SQL error: SQLCODE: -1, SQLSTATE: 40XL1, SQLERRMC: 40XL1

org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1, 
SQLSTATE: 40XL1, SQLERRMC: 40XL1
       at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown 
Source)
       at 
org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown 
Source)
       at 
org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown 
Source)
       at 
org.apache.derby.client.net.NetResultSetReply.readFetch(Unknown Source)
       at org.apache.derby.client.net.ResultSetReply.readFetch(Unknown 
Source)
       at org.apache.derby.client.net.NetResultSet.readFetch_(Unknown 
Source)

       at org.apache.derby.client.am.ResultSet.flowFetch(Unknown Source)

       at org.apache.derby.client.net.NetCursor.getMoreData_(Unknown 
Source)

       at org.apache.derby.client.am.Cursor.next(Unknown Source)
       at org.apache.derby.client.am.ResultSet.nextX(Unknown Source)
       at org.apache.derby.client.am.ResultSet.next(Unknown Source)
       at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)

       at 
common.AbstractSqlTest.testUpdateSelectCommitSelect(AbstractSqlTest.java:94)




Testcase: 
testUpdateSelectRollbackSelect(org.apache.derby.jdbc.ClientDriverTest):   
Caused an ERROR

DERBY SQL error: SQLCODE: -1, SQLSTATE: 40XL1, SQLERRMC: 40XL1

org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1, 
SQLSTATE: 40XL1, SQLERRMC: 40XL1
       at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown 
Source)
       at 
org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown 
Source)
       at 
org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown 
Source)
       at 
org.apache.derby.client.net.NetResultSetReply.readFetch(Unknown Source)
       at org.apache.derby.client.net.ResultSetReply.readFetch(Unknown 
Source)
       at org.apache.derby.client.net.NetResultSet.readFetch_(Unknown 
Source)

       at org.apache.derby.client.am.ResultSet.flowFetch(Unknown Source)

       at org.apache.derby.client.net.NetCursor.getMoreData_(Unknown 
Source)

       at org.apache.derby.client.am.Cursor.next(Unknown Source)
       at org.apache.derby.client.am.ResultSet.nextX(Unknown Source)
       at org.apache.derby.client.am.ResultSet.next(Unknown Source)
       at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)

       at 
common.AbstractSqlTest.testUpdateSelectRollbackSelect(AbstractSqlTest.java:103)

---------------------------------------------------------------------------------------------

I also tried to set timeSlice to 2000, but it didn't help.


Using the embedded Derby db and the EmbeddedDriver (see 
EmbeddedDriverTest.java) I get the following exceptions:


---------------------------------------------------------------------------------------------
There were 2 errors:

1) 
testUpdateSelectCommitSelect(org.apache.derby.jdbc.EmbeddedDriverTest)ERROR 
40XL1: A lock could not be obtained within the time requested
       at 
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
       at 
org.apache.derby.impl.services.locks.LockSet.lockObject(Unknown Source)
       at 
org.apache.derby.impl.services.locks.SinglePool.zeroDurationlockObject(Unknown 
Source)
       at 
org.apache.derby.impl.store.raw.xact.RowLocking2nohold.lockRecordForRead(Unknown 
Source)
       at 
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(Unknown 
Source)
       at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown 
Source)
       at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown 
Source)
       at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown 
Source)
       at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown 
Source)
       at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
       at 
org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)

       at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
       at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)

       at 
common.AbstractSqlTest.testUpdateSelectCommitSelect(AbstractSqlTest.java:94)

       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

       at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
2) 
testUpdateSelectRollbackSelect(org.apache.derby.jdbc.EmbeddedDriverTest)ERROR 
40XL1: A lock could not be obtained within the time requested
       at 
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
       at 
org.apache.derby.impl.services.locks.LockSet.lockObject(Unknown Source)
       at 
org.apache.derby.impl.services.locks.SinglePool.zeroDurationlockObject(Unknown 
Source)
       at 
org.apache.derby.impl.store.raw.xact.RowLocking2nohold.lockRecordForRead(Unknown 
Source)
       at 
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(Unknown 
Source)
       at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown 
Source)
       at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown 
Source)
       at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown 
Source)
       at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown 
Source)
       at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
       at 
org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)

       at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
       at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)

       at 
common.AbstractSqlTest.testUpdateSelectRollbackSelect(AbstractSqlTest.java:103)

       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

       at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)


FAILURES!!!
Tests run: 2,  Failures: 0,  Errors: 2
---------------------------------------------------------------------------------------------

Can somebody help me to get it work?

Thanks!

-Florian

/*
 * SqlTest.java
 *
 * Created on 4. Juli 2007, 15:47
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.TestCase;


/**
 *
 * @author florian.brunner
 */
public abstract class AbstractSqlTest extends TestCase {
    
    private Statement firstStmt;
    private Statement secondStmt;
    private Connection firstConnection;
    private Connection secondConnection;
    
    private final String[] ones = { "hello!", "goodbye" };
    
    private final short[] twos = { 10, 20 };
    
    private final String[] ones_updated;
    
    /** Creates a new instance of SqlTest */
    public AbstractSqlTest(String testName) {
        super(testName);
        ones_updated = new String[ones.length];
        for (int i = 0; i < ones.length; i++) {
            ones_updated[i] = ones[i] + twos[i];
        }
    }
    
    @Override
    protected void setUp() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class.forName(getDriverClassName()).newInstance();
        firstConnection = DriverManager.getConnection(getConnectionURL());
        secondConnection = DriverManager.getConnection(getConnectionURL());
        firstStmt = firstConnection.createStatement();
        firstStmt.execute("create table tbl1(one varchar(10), two smallint)");
        secondStmt = secondConnection.createStatement();
    }
    
    @Override
    protected void tearDown() throws SQLException {
        firstStmt.close();
        secondStmt.close();
        firstConnection.setAutoCommit(true);
        firstStmt = firstConnection.createStatement();
        firstStmt.execute("drop table tbl1");
        firstStmt.close();
        firstConnection.close();
        secondConnection.close();
    }
    
    private void autoCommitInsertSelect() throws SQLException {
        firstStmt.getConnection().setAutoCommit(true);
        for (int i = 0; i < ones.length; i++) {
            firstStmt.execute("insert into tbl1 values('" + ones[i] + "'," + twos[i]
                    + ")");
        }
        assertAllFromTbl1(firstStmt, ones, twos);
    }
    
    private void assertAllFromTbl1(Statement stmt, String[] ones, short[] twos)
    throws SQLException {
        ResultSet rs = stmt.executeQuery("select * from tbl1");
        int i = 0;
        for (; rs.next(); i++) {
            assertTrue(i < ones.length);
            assertEquals(ones[i], rs.getString("one"));
            assertEquals(twos[i], rs.getShort("two"));
        }
        assertTrue(i == ones.length);
    }
    
    public void testUpdateSelectCommitSelect() throws SQLException {
        autoCommitInsertSelect();
        firstStmt.getConnection().setAutoCommit(false);
        updateOnes(firstStmt, ones_updated, twos);
        assertAllFromTbl1(secondStmt, ones, twos);
        firstStmt.getConnection().commit();
        assertAllFromTbl1(secondStmt, ones_updated, twos);
    }
    
    public void testUpdateSelectRollbackSelect() throws SQLException {
        autoCommitInsertSelect();
        firstStmt.getConnection().setAutoCommit(false);
        updateOnes(firstStmt, ones_updated, twos);
        assertAllFromTbl1(secondStmt, ones, twos);
        firstStmt.getConnection().rollback();
        assertAllFromTbl1(secondStmt, ones, twos);
    }
    
    private void updateOnes(Statement stmt, String[] ones_updated, short[] twos)
    throws SQLException {
        for (int i = 0; i < ones_updated.length; i++) {
            stmt.execute("UPDATE tbl1 SET one = '" + ones_updated[i]
                    + "' WHERE two = " + twos[i]);
        }
    }
    
    protected abstract String getConnectionURL();
    
    protected abstract String getDriverClassName();
    
}

package SQLite;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


import common.AbstractSqlTest;




public class JDBCDriverTest extends AbstractSqlTest {
    
    private Connection firstConnection;
    private Connection secondConnection;
    private final File dbFile = new File("sqliteTest.db");
    private final String connectionURL = "jdbc:sqlite:/" + dbFile.getName();
    
    public JDBCDriverTest(String testName) {
        super(testName);
    }
    
    @Override
    protected void setUp() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException  {
//    	try {
//			Thread.sleep(5000);
//		} catch (InterruptedException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
        super.setUp();
    }
    
    @Override
    protected void tearDown() throws SQLException {
        super.tearDown();
        dbFile.delete();
    }
    
    
    protected String getConnectionURL() {
        return  connectionURL;
    }
    
    protected String getDriverClassName() {
        return "SQLite.JDBCDriver";
    }
    
    
}

package org.apache.derby.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import common.AbstractSqlTest;



public class EmbeddedDriverTest extends AbstractSqlTest {
    

    public EmbeddedDriverTest(String testName) {
        super(testName);
    }
    
    @Override
    protected void setUp() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        super.setUp();
    }
    
    @Override
    protected void tearDown() throws SQLException {
        super.tearDown();
        //DriverManager.getConnection("jdbc:derby:;shutdown=true");
    }
    

    protected String getConnectionURL() {
        return "jdbc:derby:res/derbyEmbeddedTest.db;create=true";
    }

    protected String getDriverClassName() {
        return "org.apache.derby.jdbc.EmbeddedDriver";
    }
    
    
}

package org.apache.derby.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import common.AbstractSqlTest;


public class ClientDriverTest extends AbstractSqlTest {
    
    
    public ClientDriverTest(String testName) {
        super(testName);
    }
    
    @Override
    protected void setUp() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        super.setUp();
    }
    
    @Override
    protected void tearDown() throws SQLException {
        super.tearDown();
    }

    protected String getConnectionURL() {
        return "jdbc:derby://localhost:1527/derbyClientTest.db;create=true";
    }

    protected String getDriverClassName() {
        return "org.apache.derby.jdbc.ClientDriver";
    }
    

    
    
}






















					Reply via email to