db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: UpdatableResultSet Problem
Date Fri, 27 Jun 2008 13:27:39 GMT
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) 


Mime
View raw message