Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Fri, 27 Jun 2008 06:40:18 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: commit and close needs specific ordering ?
In-reply-to: <7b813ada0806270441p2288d34fv18739c075a0be75d@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4864EDC2.8020102@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <7b813ada0806270441p2288d34fv18739c075a0be75d@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)

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)