RE: SQL cursor error on AS400 -- JAVA400-L

Title: RE: SQL cursor error on AS400

following my question yesterday.

I have checked my spool file, I got:

Message . . . . :   Operation sequence for member UNIT_PROD not valid. (C I)
Cause . . . . . :   An update or delete was requested before a get for update
   for member UNIT_PROD file UNIT_PROD in library AGTCOMP.
Recovery . . . :
   Either change the operation sequence in the program or delete the existing
   program and compile it again. Then try the request again. Possible choices
   for replying to message . . . . . . . . . . . . . . . :   C -- The request
   is canceled. I -- Ignore the request and continue processing.

Message . . . . :   Cursor SQLCURSOR000000003 not positioned on locked row.
Cause . . . . . :   An UPDATE or DELETE statement with a WHERE CURRENT OF
   SQLCURSOR000000003 was attempted, but the cursor is not positioned on a row
   or is positioned on a row, but the row is not locked because a COMMIT HOLD
   or ROLLBACK HOLD statement released the lock on the row. A FETCH statement
   must be issued to position the cursor on a row and lock the row.
Recovery . . . :   Issue a FETCH statement to position the cursor on a row and lock the
   row; then, try the request again.

For message 1: My program sequence is to load some data into a table with some columns null, then update these coloumns by using the results from the calculation. Loading is completed before the update. Why wrong here?

For message 2: I use JDBC Statement and ResultSet objects, and use rs.next() for moving the cursor, then rs.updateXXX(); rs.updateRow(); The connection was setAutoCommit as true. It does not need to have a FETCH statement since JDBC API take care of it. Why is wrong here?

Also the loading process was done as a transaction, which means I have setAutoCommit(false), after all insertion completed, I did:

con.commit();
con.setAutoCommit(true);
This should be Ok too.

In fact, there is nothing wrong when I run the program on NT.

Is there any possible incompatible of the JDBC2.0 API between AS400 DB2 and NT? By the way I use JDK1.2.2 on my AS/400.

-----Original Message-----
From:   Xu, Weining
Sent:   Monday, April 23, 2001 1:57 PM
To:     'JAVA400-L@midrange.com'
Subject:        SQL cursor error on AS400

I am running a Java program to retrieve, insert and update data on UDB DB2 on AS/400 using JDBC drivers. The program is consist of about 18 tasks/batches. I developed the program in VisualAge for Java 3.5. I run the same program on both NT and AS/400. On NT machine, I run test and debug (using AS/400 Toolbox for Java JDBC driver). On AS/400, I run the program as production (of course, use DB2 native JDBC driver). Because on AS/400, the program is much fast (about 30 times faster).

When the program try to update a field for a table, I have no problem run on NT (with VAJ), but I got error on AS/400. The error message is:

SQL error: Cursor SQLCURSOR000000003 not positioned on locked row.

And this error only occurs when the program run at the first time. If I run the same code one more time, the error is gone! But if the program hit another update point, the error will occur again and will gone if I run it second time.

My code segment looks like this (I only list that I think are related to the SQL operation):

        .........
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        rs = stmt.executeQuery("select COLUMN1, COLUMN2 from table1 for update");
        .........

        rs.updateFloat("COLUMN2", aNumber);
        rs.updateRow();
        ..........

Does anyone have any ideas?

Thanks.

Weining (Wayne) Xu
ALICO Information Systems & Technology
Wilmington, DE. USA
Phone: +302.594.2846 Fax: +302.594.2215
Email: weining.xu@aig.com