You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Albert Kam <mo...@gmail.com> on 2008/06/27 13:41:43 UTC
commit and close needs specific ordering ?
Dear Derby,
Again .. I'm using Derby 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)
Re: commit and close needs specific ordering ?
Posted by Rick Hillegas <Ri...@Sun.COM>.
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)