Re: Error while using JDBCR4 Service Program

Is the MS SQL Server running in "auto-commit"?

I don't have Scott's code handy...but look for a START_TRANSACTION
operation or perhaps there's something you can set on the connection
when it is created.

Also, I don't know if Scott's code supports it, but JDBC itself offers
a "batch insert" that would perform much better than multiple single
row inserts.
http://www.roseindia.net/jdbc/Jdbc-batch-insert.shtml

Charles

On Thu, Sep 8, 2011 at 3:47 PM, Gary Thompson <GThompson@xxxxxxxxxxx> wrote:

I am using Scott Klement's JDBCR4 service program to insert, select and
delete
       rows from a Microsoft Sql Server table.

Today, I ran into CEE9901 Application error.

I don't know what to do next, so any help will be appreciated!

As of yesterday; I have test program IMPORT_SKU,
       subprocedure $LodArtImp doing the following:

       1) delete all rows from a table in ms sql db.

       2) select columns from system i table and insert these
            values into table "cleared" in step 1)

            2) is a loop within a loop:

               EXEC SQL FETCH  csr1  FOR  :mo_dsMax  ROWS INTO  :mo_ds;

               stmt = jdbc_prepstmt(LodConn : 'typical insert');

               2)a Dow (Sqlcod = *Zero) And (counter < counterMax);

                       2)b FOR index = 1 TO  mo_dsMax;
                               typical code to set parms;
                               rc = jdbc_execprepupd(stmt);
                               If (rc < 0);
                                       // print rpt error msg, etc
                               Else;
                                       // increment counter
                               Endif;
                       2)b Endfor;

                       FETCH NEXT FROM csr1 FOR  :mo_dsMax  ROWS INTO
:mo_ds;

               2)a Enddo;

       3) print summary/detail rpt depending on parm

For the last several runs, 2a) was run with parm > system i table rows,
       so all system i rows are inserted in ms sql server table.

At this point, results meet requirements, except that updates must be
       performed as a "transaction".

Early today, I added the following after 2)b Endfor; and
       before 2)a FETCH NEXT:

       // start: added code #1 ---------------------------
       If LodRtnCd;
               jdbc_commit(LodConn);
       Else;
               jdbc_rollback(LodConn);
       Endif;
       // end: added code #1 ---------------------------

Re-running the program work as intended; inserting all i table rows in ms
sql server.

To test rollback, I added the following in loop 2)b

       rc = jdbc_execprepupd(stmt);      // the insert

       if (rc < 0);
               // print rpt error msg, etc
       else;
               // increment counter
               // start: added code #2 ---------------------------
               if counter > max;
                       // print 'Test Rollback'
                       LodRtnCd = *Off;
                       Leave;          // exit 2/b for loop
               endif;
               // end: added code #2 ----------------------------
       endif;

The test program runs through the Leave; failing as follows:

       If LodRtnCd;
               jdbc_commit(LodConn);
       Else;
               jdbc_rollback(LodConn);         // CEE9901 Application
error. RNX0301
       Endif;

My joblog shows:
RNX0301 unmonitored by JDBCR4 at stmt 216500, instruction X'0000'

An RPG dump shows

ILE RPG FORMATTED DUMP
Program Status Area:
Procedure Name . . . . . . . . . . . . :   IMPORT_SKU
Program Name . . . . . . . . . . . . . :   IMPORT_SKU
  Library . . . . . . . . . . . . . . :   USRGDT
Module Name. . . . . . . . . . . . . . :   IMPORT_SKU
Program Status . . . . . . . . . . . . :   00202
            The call to  ended in error (C G D F).
Previous Status  . . . . . . . . . . . :   00000
Statement in Error . . . . . . . . . . :   00018800
RPG Routine  . . . . . . . . . . . . . :   $LODARTI
Number of Parameters . . . . . . . . . :   000
Message Type . . . . . . . . . . . . . :   RNX
Additional Message Info  . . . . . . . :   0301
Message Data . . . . . . . . . . . . . :
            Java exception received when calling Java method.
Status that caused RNX9001 . . . . . . :

Line 18800 in IMPORT_SKU is:

        RtnCd = $LodArtImp(conn : 5000 : LoopSec : LodRows);

the following is, I think, the relevant JDBCR4 code....

2157.00  *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2158.00  *  JDBC_Rollback():  Rollback transaction
2159.00  *
2160.00  *     conn = (input) Connection to rollback on
2161.00  *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2162.00 P JDBC_Rollback   B                   export
2163.00 D JDBC_Rollback   PI
2164.00 D   conn                              like(Connection)
2165.00  /free
2166.00      rollbackTrn(conn);
2167.00  /end-free
2168.00 P                 E









--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.