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:33:55 UTC
UpdatableResultSet Problem
Hi Derby,
I'm using Derby 10.4.1.3 with ClientDriver.
Anyway, i'm trying to update 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 testUpdate() throws Exception {
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Connection con =
DriverManager.getConnection("jdbc:derby://localhost:1527/sms");
con.setAutoCommit(false);
Statement stm = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stm.executeQuery("select * from smssvr_out where
status = 'U' order by priority, id");
if (rs.next()) {
rs.updateString("status", "Q");
rs.updateRow();
con.commit();
}
rs.close();
stm.close();
con.commit();
con.close();
}
Here's the ddl :
CREATE TABLE smssvr_out (
id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
recipient varchar(16) NOT NULL,
text long varchar NOT NULL,
create_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
originator varchar(16) NOT NULL,
encoding varchar(1) NOT NULL,
status_report int NOT NULL DEFAULT 0,
flash_sms int NOT NULL DEFAULT 0,
src_port int NOT NULL DEFAULT -1,
dst_port int NOT NULL DEFAULT -1,
sent_date timestamp,
ref_no varchar(64),
priority varchar(1) NOT NULL,
status varchar(1) NOT NULL DEFAULT 'U',
errors int NOT NULL DEFAULT 0,
gateway_id varchar(64) NOT NULL DEFAULT ''
);
And here's the only data :
INSERT INTO "APP"."SMSSVR_OUT"
(ID,RECIPIENT,TEXT,CREATE_DATE,ORIGINATOR,ENCODING,STATUS_REPORT,FLASH_SMS,SRC_PORT,DST_PORT,SENT_DATE,REF_NO,PRIORITY,STATUS,ERRORS,GATEWAY_ID)
VALUES (2,'+62818719701','From SMSServer',{ts '2008-06-27
17:37:12.328'},'PDA','7',1,1,-1,-1,null,null,'','U',0,'');
The output of this program is this :
Exception in thread "main" java.sql.SQLException: 'updateString' not allowed
because the ResultSet is not an updatable ResultSet.
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.ResultSet.updateString(Unknown Source)
at org.apache.derby.client.am.ResultSet.updateString(Unknown Source)
at sofco.Test.testUpdate(Test.java:41)
at sofco.Test.main(Test.java:13)
Caused by: org.apache.derby.client.am.SqlException: 'updateString' not
allowed because the ResultSet is not an updatable ResultSet.
at
org.apache.derby.client.am.ResultSet.checkForUpdatableResultSet(Unknown
Source)
at org.apache.derby.client.am.ResultSet.checkUpdatePreconditions(Unknown
Source)
... 4 more
Did i do anything wrong ?
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: UpdatableResultSet Problem
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Albert,
I believe that the problem is that your query contains an ORDER BY
clause. This means that the ResultSet will not be updatable. For the
details on what kinds of queries can define updatable ResultSets, please
see the section titled "SELECT statement" in the Derby Reference Manual:
http://db.apache.org/derby/docs/10.4/ref/ You will need to scroll down
to the subsection titled "Requirements for updatable cursors and
updatable ResultSets".
In this case, Derby downgrades the cursor to be non-updatable as
described in the section titled "Updatable result sets" in the Derby
Developer's Guide: http://db.apache.org/derby/docs/10.4/devguide/
Hope this helps,
-Rick
Albert Kam wrote:
> Hi Derby,
>
> I'm using Derby 10.4.1.3 <http://10.4.1.3> with ClientDriver.
> Anyway, i'm trying to update 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 testUpdate() throws Exception {
> Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
> Connection con =
> DriverManager.getConnection("jdbc:derby://localhost:1527/sms");
> con.setAutoCommit(false);
> Statement stm =
> con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
> ResultSet rs = stm.executeQuery("select * from smssvr_out
> where status = 'U' order by priority, id");
> if (rs.next()) {
> rs.updateString("status", "Q");
> rs.updateRow();
> con.commit();
> }
> rs.close();
> stm.close();
> con.commit();
> con.close();
> }
>
> Here's the ddl :
> CREATE TABLE smssvr_out (
> id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> recipient varchar(16) NOT NULL,
> text long varchar NOT NULL,
> create_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
> originator varchar(16) NOT NULL,
> encoding varchar(1) NOT NULL,
> status_report int NOT NULL DEFAULT 0,
> flash_sms int NOT NULL DEFAULT 0,
> src_port int NOT NULL DEFAULT -1,
> dst_port int NOT NULL DEFAULT -1,
> sent_date timestamp,
> ref_no varchar(64),
> priority varchar(1) NOT NULL,
> status varchar(1) NOT NULL DEFAULT 'U',
> errors int NOT NULL DEFAULT 0,
> gateway_id varchar(64) NOT NULL DEFAULT ''
> );
>
> And here's the only data :
> INSERT INTO "APP"."SMSSVR_OUT"
> (ID,RECIPIENT,TEXT,CREATE_DATE,ORIGINATOR,ENCODING,STATUS_REPORT,FLASH_SMS,SRC_PORT,DST_PORT,SENT_DATE,REF_NO,PRIORITY,STATUS,ERRORS,GATEWAY_ID)
> VALUES (2,'+62818719701','From SMSServer',{ts '2008-06-27
> 17:37:12.328'},'PDA','7',1,1,-1,-1,null,null,'','U',0,'');
>
> The output of this program is this :
> Exception in thread "main" java.sql.SQLException: 'updateString' not
> allowed because the ResultSet is not an updatable ResultSet.
> 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.ResultSet.updateString(Unknown Source)
> at org.apache.derby.client.am.ResultSet.updateString(Unknown Source)
> at sofco.Test.testUpdate(Test.java:41)
> at sofco.Test.main(Test.java:13)
> Caused by: org.apache.derby.client.am.SqlException: 'updateString' not
> allowed because the ResultSet is not an updatable ResultSet.
> at
> org.apache.derby.client.am.ResultSet.checkForUpdatableResultSet(Unknown
> Source)
> at
> org.apache.derby.client.am.ResultSet.checkUpdatePreconditions(Unknown
> Source)
> ... 4 more
>
> Did i do anything wrong ?
>
> 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)