Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

ResultSet is not updatable.

103 views
Skip to first unread message

xixi

unread,
Jun 16, 2003, 7:03:08 PM6/16/03
to
hello i am using db2jcc.jar on db2 v8.1 on NT.

i create this
statement =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

then execute query


select id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE where id = 8
ORDER BY brnum ASC for update with rr


this will give me a result set, then i want to do a positioned update,

with cursorResultSet.updateObject(fieldname, object);

but gives me exception com.ibm.db2.jcc.c.SQLException: ResultSet is
not updatable.

does the positioned update support on db2 NT? thanks

Paul Dembry

unread,
Jun 16, 2003, 7:43:28 PM6/16/03
to
"xixi" <dai...@yahoo.com> wrote in message
news:c0f33a17.03061...@posting.google.com...

> hello i am using db2jcc.jar on db2 v8.1 on NT.
>
> i create this
> statement =
> conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
> then execute query
> select id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE where id = 8
> ORDER BY brnum ASC for update with rr
Try it without the order by clause.
Paul


sha...@us.ibm.com

unread,
Jun 17, 2003, 3:59:06 PM6/17/03
to
Order by is ok for scrollable cursor, and nt does support updatable cursors.
So its not updatable for some other reason ...
maybe its a composed row (view over join), etc. As documented in the SQL Refr.

xixi

unread,
Jun 17, 2003, 4:21:25 PM6/17/03
to
i have changed the query to this

select id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE where id = 8
for update with rr

but still get the same errors, please advise.

sha...@us.ibm.com

unread,
Jun 17, 2003, 5:07:53 PM6/17/03
to
Hi xixi,
Can you post the ddl for DDAI.NOINDEX_TABLE ?
Or, compare it with the UPDATABLE criteria in DECLARE CURSOR.

FYI, the jdbc driver expects that a cursor delcared for update
that is not updatable will be downgraded to read only.
I think there is a way for you to query if the cursor is updatable
or not in jdbc (an api to get statement attributes?) after you
prepare that cursor.

xixi

unread,
Jun 18, 2003, 4:35:36 PM6/18/03
to
here it is

CREATE TABLE "DDAI "."NOINDEX_TABLE" (
"BRNUM" INTEGER ,
"ITMNUM" INTEGER ,
"DESC" VARCHAR(20) ,
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1
, INCREMENT BY +1 , NO CACHE ) )
IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "DDAI "."NOINDEX_TABLE"

ALTER TABLE "DDAI "."NOINDEX_TABLE"
ADD CONSTRAINT "CC1055802895125" PRIMARY KEY
("ID");

sha...@us.ibm.com wrote in message news:<3EEF8329...@us.ibm.com>...

xixi

unread,
Jun 18, 2003, 4:48:36 PM6/18/03
to
i open a statement with

statement =
conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

the query is
select id, brnum, ITMNUM, DESC from DDAI.index_table where id = 8
for update with rr

then resultset.next(), then try to do
cursorResultSet.updateObject(fieldname, object); get the sql error.

before do updateobject, i print System.out.println("currency:" +
statement.getResultSetConcurrency());
it prints 1008(i don't know what that's means)

sha...@us.ibm.com

unread,
Jun 19, 2003, 2:50:30 PM6/19/03
to
Hi xixi,
A jdbc expert tells me:
<quote/>
The API to get the warning is:

java.sql.SQLWarning w = stmt2.getWarnings();
System.out.println(w.getMessage());

Instead of getting the concurrency on the Statement, xixi should be
getting the concurrency on the ResultSet, after the executeQuery() call,
ResultSet.getConcurrency(), this gives the actual concurrency of the
resultset. 1008 is the constant for CONCUR_UPDATABLE.

I noticed resultset.next(), then cursorResultSet.updateObject(fieldname,
object), I hope resultset and cursorResultSet are not two different
ResultSet objects.

</quote>

xixi

unread,
Jun 25, 2003, 3:54:36 PM6/25/03
to
hi,

this is the query to open updatable resultset

sqlStr = select id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE where


id = 8 for update with rr

statement =
conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);


resultSet = statement.executeQuery(sqlStr);
System.out.println("result set currency:" +
resultSet.getConcurrency());

it gives me result set currency:1008

then i do

resultSet .updateObject(fieldname, object);

this time gives me error

(com.ibm.db2.jcc.c.SQLException) com.ibm.db2.jcc.c.SQLException: under
construction

please advise

sha...@us.ibm.com

unread,
Jun 25, 2003, 6:30:35 PM6/25/03
to
advise about what?
Your error changed from "not updatable" to "under construction" ?
... I guess that is some client side issue. I will forward to my jdbc
expert and see if they can guess what is going on.

PM (pm3iinc-nospam)

unread,
Jun 25, 2003, 8:44:43 PM6/25/03
to
This is a good place to look at what's supported and what's not. (also
readme + fixpack files.)
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8infoce
nter.d2w/report?target=mainFrame&fn=r0010267.htm


since some features are available only for universal JDBC type 2 and type 4
drivers,
can you provide the url you connect with?

You're on nt but what's the server? as-400 ... ?

PM


xixi

unread,
Jun 26, 2003, 12:50:43 PM6/26/03
to
hi, i am using db2 v8.1 udb fix pack 2 on NT. db2jcc.jar is the jdbc
driver we use, it is type 4. the url is

DB_URL=jdbc:db2://xxx.xx.x.xx:50000/xxx

as i look , sensitive resultset and updatable resultset is supported
for type 4.


from this url

http://www-3.ibm.com/software/data/db2/udb/ad/v8/java/r0010294.htm

so would you please do the test from your side? thanks

sha...@us.ibm.com

unread,
Jun 26, 2003, 5:43:54 PM6/26/03
to
Hi,
I have the following suggestion, from my jdbc friend:
<quote/>
Can we try to find the build number by adding the following codecase:

// turn on trace
try {
DriverManager.setLogWriter(
new java.io.PrintWriter (
new java.io.BufferedOutputStream (
new java.io.FileOutputStream (traceFile), 4096), true));
// traceFile is a String which contains the name of the trace file
}
catch (java.io.FileNotFoundException e) {
}
catch (java.io.IOException e1) {
}

// Get connection 1
conn1 = DriverManager.getConnection(urlJcc,prop);

</quote>

sha...@us.ibm.com

unread,
Jun 27, 2003, 8:50:10 PM6/27/03
to
Hi xixi,
Sherry tells me:

From the trace it looks like you are using the Driver from FP1,
[ibm][db2][jcc][Connection@8c436b] Driver version: 1.1.67.

FP2 would be "1.2.XX". You may have download FP2 but failed to point
her classpath to it.
Find out where your fp2 db2jcc.jar is and set classpath to it.

set classpath=c:\blah\db2jcc.jar;%classpath%

xixi

unread,
Jun 30, 2003, 4:09:16 PM6/30/03
to
hello

i do have db2jcc.jar from fp2, but when i use it to get connection, i
got the error

com.ibm.db2.jcc.c.SqlException: The version of the IBM Universal JDBC
driver in use is not licensed for connectivity to DB2 for Unix/Windows
databases. To connect to this DB2 server, please obtain a licensed
copy of the IBM DB2 Universal Driver for JDBC and SQLJ.

we are just using trial edition of db2 udb server.

so that is why i have to use db2jcc.jar from fp1. so can i use this
version to solve my problem? thanks

sha...@us.ibm.com wrote in message news:<3EFCE642...@us.ibm.com>...

sha...@us.ibm.com

unread,
Jul 1, 2003, 11:58:26 AM7/1/03
to
Hi Xixi,
I don't know, but will try to find out for you.

sha...@us.ibm.com

unread,
Jul 1, 2003, 2:54:18 PM7/1/03
to
Hi xixi,
Here is what I have been told:
<qoute/>
look for the license jar file in the same directory where db2jcc.jar is,
sqllib\java.
If there is one, put the license jar file in her classpath,
db2jcc_license_cisuz.jar (for all servers).
Use db2jcc_license_cu.jar only to connect to luw servers.

I don't know if the trial edition comes with the license jar or not though.
</quote>

hope this helps.
Dave.

xixi

unread,
Jul 10, 2003, 1:20:07 PM7/10/03
to
hello,

i do put the db2jcc_license_cisuz.jar in the classpath, but still not
work, i just want to know whether db2jcc.jar from fixpack1 support
positioned update (or delete) ? (updateObject() method, updateRow()
method)

sha...@us.ibm.com

unread,
Jul 10, 2003, 10:30:29 PM7/10/03
to
Hi xixi,
I don't think it does, you need the fixpak2 for that.

xixi

unread,
Aug 7, 2003, 6:53:57 PM8/7/03
to
hello, I was able to use db2jcc.jar fixpack2 to run the program now,
but still interfere with errors.

What I do is use a select query to position the cursor, then do a
resultset.updateObject(), so far no problem, then I do a
resultset.updateRow(), I got error code 798, I don't know why.

The sql is select id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE
where id = 8

id is identity column, but my updateobject() doesn't update id field,
but desc field. Please see the log. thanks
DriverManager.getConnection("jdbc:db2://172.16.4.10:50000/hd1100pd:traceFile=c:/foobar.txt;traceLevel=550;")
trying driver[className=com.ibm.as400.access.AS400JDBCDriver,AS/400
Toolbox for Java JDBC Driver]
trying driver[className=com.ibm.db2.jcc.DB2Driver,com.ibm.db2.jcc.DB2Driver@1db7df8]
[ibm][db2][jcc] BEGIN TRACE_CONNECTS
[ibm][db2][jcc] Attempting connection to 172.16.4.10:50000/hd1100pd
[ibm][db2][jcc] Using properties: { traceLevel=550,
traceFile=c:/foobar.txt, user=ddai, password=<escaped> }
[ibm][db2][jcc] END TRACE_CONNECTS
[ibm][db2][jcc][Connection@adb1d4] BEGIN TRACE_CONNECTS
[ibm][db2][jcc][Connection@adb1d4] Successfully connected to server
jdbc:db2://172.16.4.10:50000/hd1100pd
[ibm][db2][jcc][Connection@adb1d4] User: ddai
[ibm][db2][jcc][Connection@adb1d4] Database product name: DB2/NT
[ibm][db2][jcc][Connection@adb1d4] Database product version: SQL08012
[ibm][db2][jcc][Connection@adb1d4] Driver name: IBM DB2 JDBC Universal
Driver Architecture
[ibm][db2][jcc][Connection@adb1d4] Driver version: 1.2.117
[ibm][db2][jcc][Connection@adb1d4] DB2 Correlator:
AC100436.GE38.00F6675277DF
[ibm][db2][jcc][Connection@adb1d4] END TRACE_CONNECTS
[ibm][db2][jcc][t4] DRDA manager levels: { SQLAM=7, AGENT=7,
CMNTCPIP=5, RDB=7, SECMGR=6 }
getConnection returning
driver[className=com.ibm.db2.jcc.DB2Driver,com.ibm.db2.jcc.DB2Driver@1db7df8]
[ibm][db2][jcc][Thread:main][Statement@1b60280] executeQuery (select
NAME from sysibm.sysschemata where name = 'DDAI') called
[ibm][db2][jcc][Thread:main][Statement@1b60280] executeQuery ()
returned ResultSet@1d0fafc
[ibm][db2][jcc][Thread:main][ResultSet@1d0fafc] next () called
[ibm][db2][jcc][Thread:main][ResultSet@1d0fafc] next () returned true
[ibm][db2][jcc][Thread:main][PreparedStatement@f6438d] setString (1,
NOINDEX_TABLE) called
[ibm][db2][jcc][Thread:main][PreparedStatement@f6438d] executeQuery ()
called
[ibm][db2][jcc][Thread:main][PreparedStatement@f6438d] executeQuery ()
returned ResultSet@1eb2c1b
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] next () called
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] next () returned true
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] getString (DBKFLD)
called
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] getString (2) called
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] getString () returned
BRNUM
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] getString (DBKORD)
called
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] getString (3) called
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] getString () returned
A
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] next () called
[ibm][db2][jcc][Thread:main][ResultSet@1eb2c1b] next () returned false
[ibm][db2][jcc][Thread:main][PreparedStatement@1632847] setString (1,
DDAI) called
[ibm][db2][jcc][Thread:main][PreparedStatement@1632847] setString (2,
NOINDEX_TABLE) called
[ibm][db2][jcc][Thread:main][PreparedStatement@1632847] executeQuery
() called
[ibm][db2][jcc][Thread:main][PreparedStatement@1632847] executeQuery
() returned ResultSet@e95a56
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () returned true
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (COLTYPE)
called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (2) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString () returned
INTEGER
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt (LENGTH) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt (4) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt () returned 4
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (NAME) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (1) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString () returned
BRNUM
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () returned true
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (COLTYPE)
called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (2) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString () returned
INTEGER
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt (LENGTH) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt (4) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt () returned 4
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (NAME) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (1) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString () returned
ITMNUM
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () returned true
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (COLTYPE)
called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (2) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString () returned
VARCHAR
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt (LENGTH) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt (4) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getInt () returned 20
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (NAME) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString (1) called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] getString () returned
DESC
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] next () returned false
[ibm][db2][jcc][Thread:main][Statement@1b60280] close () called
[ibm][db2][jcc][Thread:main][ResultSet@e95a56] close () called
[ibm][db2][jcc][Thread:main][PreparedStatement@1632847] close ()
called
[ibm][db2][jcc][Thread:main][Statement@1ffc686] executeQuery (select
id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE WHERE brnum=4 ORDER
BY brnum ASC) called
[ibm][db2][jcc][Thread:main][Statement@1ffc686] executeQuery ()
returned ResultSet@961dff
[ibm][db2][jcc][Thread:main][ResultSet@961dff] isBeforeFirst () called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] isBeforeFirst ()
returned true
[ibm][db2][jcc][Thread:main][ResultSet@961dff] next () called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] isAfterLast () called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] isAfterLast () returned
false
[ibm][db2][jcc][Thread:main][ResultSet@961dff] next () returned true
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (brnum)
called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (2) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject () returned 4
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (ITMNUM)
called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (3) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject () returned 5
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (DESC) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (4) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject () returned
5th Item
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (id) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject (1) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getObject () returned 8
[ibm][db2][jcc][Thread:main][Statement@1aaf0b3] executeQuery (select
id, brnum, ITMNUM, DESC from DDAI.NOINDEX_TABLE where id = 8) called
[ibm][db2][jcc][Thread:main][Statement@1aaf0b3] executeQuery ()
returned ResultSet@1a082e2
[ibm][db2][jcc][Thread:main][ResultSet@1a082e2] getConcurrency ()
returned 1008
[ibm][db2][jcc][Thread:main][ResultSet@1a082e2] next () called
[ibm][db2][jcc][Thread:main][ResultSet@1a082e2] isAfterLast () called
[ibm][db2][jcc][Thread:main][ResultSet@1a082e2] isAfterLast ()
returned false
[ibm][db2][jcc][Thread:main][ResultSet@1a082e2] next () returned true
[ibm][db2][jcc][Thread:main][ResultSet@961dff] getConcurrency ()
returned 1007
[ibm][db2][jcc][Thread:main][ResultSet@961dff] updateObject (desc,
Fifth Item) called
[ibm][db2][jcc][Thread:main][ResultSet@961dff] updateObject (4, Fifth
Item) called
SQLException: SQLState(null) vendor code(-99999)
com.ibm.db2.jcc.c.SqlException: ResultSet is not updatable.
at com.ibm.db2.jcc.c.cc.i(cc.java:3000)
at com.ibm.db2.jcc.c.cc.updateObject(cc.java:2078)
at com.ibm.db2.jcc.c.cc.updateObject(cc.java:2204)
at com.mincron.framework.translated.file.db.AS400Cursor.updateObject(AS400Cursor.java:156)
at com.mincron.framework.translated.file.db.DB2DbFile.resultSetUpdate(DB2DbFile.java:2669)
at com.mincron.framework.translated.file.db.DB2DbFile.updat(DB2DbFile.java:2494)
at com.mincron.framework.translated.file.db.Db2DbFileTester1.chain_Test(Db2DbFileTester1.java:179)
at com.mincron.framework.translated.file.db.Db2DbFileTester1.main(Db2DbFileTester1.java:34)
[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][SQLException@210b5b] java.sql.SQLException
[ibm][db2][jcc][SQLException@210b5b] SQL state = null
[ibm][db2][jcc][SQLException@210b5b] Error code = -99999
[ibm][db2][jcc][SQLException@210b5b] Message = ResultSet is not
updatable.
[ibm][db2][jcc][SQLException@210b5b] Stack trace follows
com.ibm.db2.jcc.c.SqlException: ResultSet is not updatable.
at com.ibm.db2.jcc.c.cc.i(cc.java:3000)
at com.ibm.db2.jcc.c.cc.updateObject(cc.java:2078)
at com.ibm.db2.jcc.c.cc.updateObject(cc.java:2204)
at com.mincron.framework.translated.file.db.AS400Cursor.updateObject(AS400Cursor.java:156)
at com.mincron.framework.translated.file.db.DB2DbFile.resultSetUpdate(DB2DbFile.java:2669)
at com.mincron.framework.translated.file.db.DB2DbFile.updat(DB2DbFile.java:2494)
at com.mincron.framework.translated.file.db.Db2DbFileTester1.chain_Test(Db2DbFileTester1.java:179)
at com.mincron.framework.translated.file.db.Db2DbFileTester1.main(Db2DbFileTester1.java:34)
[ibm][db2][jcc] END TRACE_DIAGNOSTICS

0 new messages