Re: commit and close needs specific ordering ?

[Rick Hillegas]

Hi Albert,

I believe this is what is going on here:
1) By default, ResultSets remain open after commits. You have to 
explicitly close your ResultSets. See the section titled "Holdable 
result sets" in the Derby Developer's Guide: 
http://db.apache.org/derby/docs/10.4/devguide/ This section explains how 
you can change this default behavior.

2) An open ResultSet means that you have an in-flight transaction.

3) Before closing a connection, you have to state what you want done 
with your uncommitted work.

Hope this helps,
-Rick

Albert Kam wrote:
Dear Derby,

Again .. I'm using Derby 10.4.1.3 <http://10.4.1.3/> with ClientDriver. :)
Anyway, now i'm trying to insert a row as can be looked from this 
source code (you can run it also, i'll give the ddl and the only row data)

    private static void testInsert() throws Exception {
        Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
        Connection con = 
DriverManager.getConnection("jdbc:derby://localhost:1527/sms");
        con.setAutoCommit(false);
        Statement cmd = 
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = cmd.executeQuery("select * from smssvr_calls 
where id = -1");
        rs.moveToInsertRow();
        rs.updateTimestamp("call_date", new Timestamp(new 
java.util.Date().getTime()));
        rs.updateString("gateway_id", "Nokia30");
        rs.updateString("caller_id", "+62626262");
        rs.insertRow();
        con.commit();
        rs.close();
        cmd.close();
        con.close();
    }

The DDL :
CREATE TABLE smssvr_calls (
    id        int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    call_date    timestamp,
    gateway_id    varchar(64) NOT NULL,
    caller_id    varchar(64) NOT NULL
);

The only data :
INSERT INTO "APP"."SMSSVR_CALLS" (ID,CALL_DATE,GATEWAY_ID,CALLER_ID) 
VALUES (1,{ts '2008-06-27 16:42:22.565'},'N30Modem','+6281xxx');

And the exception that results :
Exception in thread "main" java.sql.SQLException: Cannot close a 
connection while a transaction is still active.
    at 
org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown 
Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown 
Source)
    at org.apache.derby.client.am.Connection.closeResourcesX(Unknown 
Source)
    at org.apache.derby.client.am.Connection.closeX(Unknown Source)
    at org.apache.derby.client.net.NetConnection.closeX(Unknown Source)
    at org.apache.derby.client.am.Connection.close(Unknown Source)
    at org.apache.derby.client.net.NetConnection.close(Unknown Source)
    at sofco.Test.testInsert(Test.java:31)
    at sofco.Test.main(Test.java:14)
Caused by: org.apache.derby.client.am.SqlException: Cannot close a 
connection while a transaction is still active.
    at 
org.apache.derby.client.am.Connection.checkForTransactionInProgress(Unknown 
Source)
    ... 7 more

But one thing is for sure, that the data is really inserted. It's just 
this Exception.

But ...

If i arrange the ordering from the source above :
        con.commit();
        rs.close();
        cmd.close();
        con.close();

into
        rs.close();
        cmd.close();
        con.commit();
        con.close();

The exception doesnt happen ..

This is my first time this updatable thingy (i usually use ordinary 
dml or hibernate) ..
Am i missing anything ? :)

Regards,
Albert Kam

--
Do not pursue the past. Do not lose yourself in the future.
The past no longer is. The future has not yet come.
Looking deeply at life as it is in the very here and now,
the practitioner dwells in stability and freedom.
(Thich Nhat Hanh)