Deleted from N records in delete 'where current of' in cursor for update and SQLCODE = -100

I have a cursor for update in microfocus, with connection to a database of db2.

In the readings of the fetch, always read a single row, but when I'm going to do the delete / update of this with a where current of the value of the SQLCODE is a -100 and N rows are deleted or modified, respectively, instead of the corresponding current . Any solution? Thanks.

For example:

$set sql(CHECKSINGLETON)
$set constant driverClass "com.ibm.db2.jcc.DB2Driver"
$set constant databaseURL "jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;user=MFADMIN;password=MicroFocus42;"
IDENTIFICATION DIVISION.
*-----------------------.

PROGRAM-ID. ATB984.
AUTHOR. AA
DATE-WRITTEN. 20180412
DATE-COMPILED.

DATA DIVISION.
*-------------.

WORKING-STORAGE SECTION.
*-----------------------.

01 FILLER PIC X(25) VALUE
'COMIENZO WORKING ATB984'.

01 NOMBRE-MODULO PIC X(08) VALUE 'ATB984 '.

01 ATC990 PIC X(08) VALUE 'ATC990 '.
01 db2-data.
05 campo1 pic X(8).
05 campo2 pic X(8).
05 campo3 pic X(8).
05 campo4 pic X(8).

01 VARIABLES-WORKING.

05 LONG-MENS PIC 9(02) VALUE 77.
05 MENS-SEND-TEXT PIC X(77) VALUE SPACES.

05 LIT-BD-INF-REA PIC X(24) VALUE
'B.D. INFO. REARRANQUES'.
05 LIT-TBINFREA PIC X(08) VALUE 'TBINFREA'.

05 TEXTO-ERROR-BD.
10 NOMBRE-BD PIC X(24).
10 NOMBRE-TABLA PIC X(11).
10 OPERACION PIC X(20).
10 CODIGO-ERROR.
15 FILLER PIC X(10) VALUE 'SQLCODE : '.
15 NUMERO-ERROR PIC S9(10) SIGN IS LEADING SEPARATE.

* ----------------------------------------------
* VARIABLES PROPIAS DEL PROGRAMA
* ----------------------------------------------

01 DATOS-SYSIN.
05 NOMBRE-PROG PIC X(08) VALUE SPACES.
05 CADENA-PROG PIC X(04) VALUE SPACES.

01 CONTA-REG PIC 9(04) VALUE ZERO.

* --------------------------------------------------------------
* FLAG DEL PROGRAMA
* --------------------------------------------------------------
*
01 FG-NO-MAS-REGISTROS PIC 9 VALUE 0.
88 NO-MAS-REGISTROS VALUE 1.
*

* --------------------------------------------------------------
* INCORPORACION DE LA AREA DE COMUNICACION DEL 'SQL'
* --------------------------------------------------------------

EXEC SQL INCLUDE SQLCA END-EXEC.

* --------------------------------------------------------------
* DECLARACION DEL CURSOR1
* --------------------------------------------------------------

EXEC SQL
DECLARE CURSOR1 CURSOR FOR
SELECT campo1,
campo2,
campo3,
campo4
FROM TABLADB2.ADMIN_EMP
where campo2 = 100
FOR UPDATE
END-EXEC.

* VARIABLES-AUXILIARES

01 FILLER PIC X(25) VALUE 'FINAL WORKING ATB984'.

* --------------------------------------------------------------
* P R O C E D U R E D I V I S I O N
* ===================================
* --------------------------------------------------------------

PROCEDURE DIVISION.

1-INICIO-ATB984.
*---------------.

EXEC SQL CONNECT USING "DRIVER=com.ibm.db2.jcc.DB2Driver;URL=jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;user=MFADMIN;password=MicroFocus42;"
END-EXEC

if sqlcode not equal 0
display "Connection Error. SQLCODE=" sqlcode
stop run
end-if

PERFORM 3-PROCESO
THRU 3-PROCESO-FIN
UNTIL NO-MAS-REGISTROS.

3-PROCESO.
*---------.

PERFORM 31-ABRIR-CURSOR
THRU 31-ABRIR-CURSOR-FIN.

PERFORM 32-FETCH-CURSOR
THRU 32-FETCH-CURSOR-FIN.

PERFORM 33-BORRAR-REGISTROS
THRU 33-BORRAR-REGISTROS-FIN
UNTIL NO-MAS-REGISTROS.

PERFORM 34-CERRAR-CURSOR
THRU 34-CERRAR-CURSOR-FIN.

EXEC SQL COMMIT END-EXEC

MOVE ZERO TO CONTA-REG.

3-PROCESO-FIN.
*-------------.
EXIT.

31-ABRIR-CURSOR.
*---------------.
EXEC SQL OPEN CURSOR1 END-EXEC.

IF SQLCODE NOT EQUAL 0
PERFORM 4-FINAL
THRU 4-FINAL-FIN.

31-ABRIR-CURSOR-FIN.
*-----------------------.
EXIT.

32-FETCH-CURSOR.
*---------------.
EXEC SQL
FETCH CURSOR1
INTO :campo1,
:campo2,
:campo3,
:campo4
END-EXEC.

DISPLAY 'FETCH CURSOR'
DISPLAY sqlerrd(3) --> 1 every time

IF SQLCODE EQUAL 100
MOVE 1 TO FG-NO-MAS-REGISTROS
ELSE
IF SQLCODE NOT EQUAL 0
PERFORM 4-FINAL
THRU 4-FINAL-FIN.

32-FETCH-CURSOR-FIN.
*---------------------.
EXIT.

33-BORRAR-REGISTROS.
*-------------------.

EXEC SQL
DELETE FROM TABLADB2.admin_emp
WHERE CURRENT OF CURSOR1
END-EXEC.

DISPLAY 'DELETE CURSOR'
DISPLAY sqlerrd(3) --> n every time

IF SQLCODE EQUAL 0
ADD 1 TO CONTA-REG
ELSE
PERFORM 4-FINAL
THRU 4-FINAL-FIN
end-if
PERFORM 32-FETCH-CURSOR
THRU 32-FETCH-CURSOR-FIN.
33-BORRAR-REGISTROS-FIN.
*-----------------------.
EXIT.

34-CERRAR-CURSOR.
*----------------.

EXEC SQL CLOSE CURSOR1 END-EXEC.

IF SQLCODE NOT EQUAL 0
PERFORM 4-FINAL
THRU 4-FINAL-FIN.

34-CERRAR-CURSOR-FIN.
*----------------------.
EXIT.

4-FINAL.
*-------.
MOVE LIT-BD-INF-REA TO NOMBRE-BD.
MOVE LIT-TBINFREA TO NOMBRE-TABLA.
exec sql commit end-exec
exec sql disconnect end-exec.

MOVE SQLCODE TO NUMERO-ERROR.
DISPLAY 'PROGRAMA ATB984 HA FINALIZADO: ', NUMERO-ERROR.
STOP RUN.

4-FINAL-FIN.
*-----------.
EXIT.

OpenESQL-Error.
*---------------.
display "SQL Error = " sqlstate " " sqlcode
stop run
exit.

The index of the table is campo1,campo2, campo3 and the delete "where current" deletes all records that meet the where, in a single time and the SQLCODE = -100, something that for me is wrong.

Could you help me get the delete done by the current and get a correct SQLCODE? Thanks.

Page 1 / 1

I have a cursor for update in microfocus, with connection to a database of db2.

In the readings of the fetch, always read a single row, but when I'm going to do the delete / update of this with a where current of the value of the SQLCODE is a -100 and N rows are deleted or modified, respectively, instead of the corresponding current . Any solution? Thanks.

For example:

$set sql(CHECKSINGLETON)
$set constant driverClass "com.ibm.db2.jcc.DB2Driver"
$set constant databaseURL "jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;user=MFADMIN;password=MicroFocus42;"
IDENTIFICATION DIVISION.
*-----------------------.

PROGRAM-ID. ATB984.
AUTHOR. AA
DATE-WRITTEN. 20180412
DATE-COMPILED.

DATA DIVISION.
*-------------.

WORKING-STORAGE SECTION.
*-----------------------.

01 FILLER PIC X(25) VALUE
'COMIENZO WORKING ATB984'.

01 NOMBRE-MODULO PIC X(08) VALUE 'ATB984 '.

01 ATC990 PIC X(08) VALUE 'ATC990 '.
01 db2-data.
05 campo1 pic X(8).
05 campo2 pic X(8).
05 campo3 pic X(8).
05 campo4 pic X(8).

01 VARIABLES-WORKING.

05 LONG-MENS PIC 9(02) VALUE 77.
05 MENS-SEND-TEXT PIC X(77) VALUE SPACES.

05 LIT-BD-INF-REA PIC X(24) VALUE
'B.D. INFO. REARRANQUES'.
05 LIT-TBINFREA PIC X(08) VALUE 'TBINFREA'.

05 TEXTO-ERROR-BD.
10 NOMBRE-BD PIC X(24).
10 NOMBRE-TABLA PIC X(11).
10 OPERACION PIC X(20).
10 CODIGO-ERROR.
15 FILLER PIC X(10) VALUE 'SQLCODE : '.
15 NUMERO-ERROR PIC S9(10) SIGN IS LEADING SEPARATE.

* ----------------------------------------------
* VARIABLES PROPIAS DEL PROGRAMA
* ----------------------------------------------

01 DATOS-SYSIN.
05 NOMBRE-PROG PIC X(08) VALUE SPACES.
05 CADENA-PROG PIC X(04) VALUE SPACES.

01 CONTA-REG PIC 9(04) VALUE ZERO.

* --------------------------------------------------------------
* FLAG DEL PROGRAMA
* --------------------------------------------------------------
*
01 FG-NO-MAS-REGISTROS PIC 9 VALUE 0.
88 NO-MAS-REGISTROS VALUE 1.
*

* --------------------------------------------------------------
* INCORPORACION DE LA AREA DE COMUNICACION DEL 'SQL'
* --------------------------------------------------------------

EXEC SQL INCLUDE SQLCA END-EXEC.

* --------------------------------------------------------------
* DECLARACION DEL CURSOR1
* --------------------------------------------------------------

EXEC SQL
DECLARE CURSOR1 CURSOR FOR
SELECT campo1,
campo2,
campo3,
campo4
FROM TABLADB2.ADMIN_EMP
where campo2 = 100
FOR UPDATE
END-EXEC.

* VARIABLES-AUXILIARES

01 FILLER PIC X(25) VALUE 'FINAL WORKING ATB984'.

* --------------------------------------------------------------
* P R O C E D U R E D I V I S I O N
* ===================================
* --------------------------------------------------------------

PROCEDURE DIVISION.

1-INICIO-ATB984.
*---------------.

EXEC SQL CONNECT USING "DRIVER=com.ibm.db2.jcc.DB2Driver;URL=jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;user=MFADMIN;password=MicroFocus42;"
END-EXEC

if sqlcode not equal 0
display "Connection Error. SQLCODE=" sqlcode
stop run
end-if

PERFORM 3-PROCESO
THRU 3-PROCESO-FIN
UNTIL NO-MAS-REGISTROS.

3-PROCESO.
*---------.

PERFORM 31-ABRIR-CURSOR
THRU 31-ABRIR-CURSOR-FIN.

PERFORM 32-FETCH-CURSOR
THRU 32-FETCH-CURSOR-FIN.

PERFORM 33-BORRAR-REGISTROS
THRU 33-BORRAR-REGISTROS-FIN
UNTIL NO-MAS-REGISTROS.

PERFORM 34-CERRAR-CURSOR
THRU 34-CERRAR-CURSOR-FIN.

EXEC SQL COMMIT END-EXEC

MOVE ZERO TO CONTA-REG.

3-PROCESO-FIN.
*-------------.
EXIT.

31-ABRIR-CURSOR.
*---------------.
EXEC SQL OPEN CURSOR1 END-EXEC.

IF SQLCODE NOT EQUAL 0
PERFORM 4-FINAL
THRU 4-FINAL-FIN.

31-ABRIR-CURSOR-FIN.
*-----------------------.
EXIT.

32-FETCH-CURSOR.
*---------------.
EXEC SQL
FETCH CURSOR1
INTO :campo1,
:campo2,
:campo3,
:campo4
END-EXEC.

DISPLAY 'FETCH CURSOR'
DISPLAY sqlerrd(3) --> 1 every time

IF SQLCODE EQUAL 100
MOVE 1 TO FG-NO-MAS-REGISTROS
ELSE
IF SQLCODE NOT EQUAL 0
PERFORM 4-FINAL
THRU 4-FINAL-FIN.

32-FETCH-CURSOR-FIN.
*---------------------.
EXIT.

33-BORRAR-REGISTROS.
*-------------------.

EXEC SQL
DELETE FROM TABLADB2.admin_emp
WHERE CURRENT OF CURSOR1
END-EXEC.

DISPLAY 'DELETE CURSOR'
DISPLAY sqlerrd(3) --> n every time

IF SQLCODE EQUAL 0
ADD 1 TO CONTA-REG
ELSE
PERFORM 4-FINAL
THRU 4-FINAL-FIN
end-if
PERFORM 32-FETCH-CURSOR
THRU 32-FETCH-CURSOR-FIN.
33-BORRAR-REGISTROS-FIN.
*-----------------------.
EXIT.

34-CERRAR-CURSOR.
*----------------.

EXEC SQL CLOSE CURSOR1 END-EXEC.

IF SQLCODE NOT EQUAL 0
PERFORM 4-FINAL
THRU 4-FINAL-FIN.

34-CERRAR-CURSOR-FIN.
*----------------------.
EXIT.

4-FINAL.
*-------.
MOVE LIT-BD-INF-REA TO NOMBRE-BD.
MOVE LIT-TBINFREA TO NOMBRE-TABLA.
exec sql commit end-exec
exec sql disconnect end-exec.

MOVE SQLCODE TO NUMERO-ERROR.
DISPLAY 'PROGRAMA ATB984 HA FINALIZADO: ', NUMERO-ERROR.
STOP RUN.

4-FINAL-FIN.
*-----------.
EXIT.

OpenESQL-Error.
*---------------.
display "SQL Error = " sqlstate " " sqlcode
stop run
exit.

The index of the table is campo1,campo2, campo3 and the delete "where current" deletes all records that meet the where, in a single time and the SQLCODE = -100, something that for me is wrong.

Could you help me get the delete done by the current and get a correct SQLCODE? Thanks.

I tested this with an example of my own and cannot reproduce the behavior you are reporting. I am using Visual COBOL for Eclipse 3.0 on Windows against the DB2 Express Samples database. I connect to the database, open a cursor that returns a number of rows and for each fetch I am doing a DELETE WHERE CURRENT OF statement and it results in SQLCODE=0. I am not actually committing the deletions but the DELETE does appear to work correctly.

Example:

      $set SQL(DBMAN=JDBC CHECKSINGLETON)      
      $set constant driverClass "com.ibm.db2.jcc.DB2Driver"
      $set constant databaseURL "jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;user=CRG;password=mypass;"
       working-storage section.

      *  Include the SQL Communications Area. This includes the
      *  definitions of SQLCODE, etc
           EXEC SQL INCLUDE SQLCA END-EXEC.
       01  DCLEMP.
           03 EMP-EMPNO                       PIC X(6).
           03 EMP-FIRSTNME                    PIC X(12).
           03 EMP-LASTNAME                    PIC X(15).
           
       01 connectionstring pic x(300) value spaces.
       *> after an sql error this has the full message text
       01 MFSQLMESSAGETEXT  PIC X(250). 
       
       procedure division.
	      EXEC SQL 
             WHENEVER SQLERROR perform OpenESQL-Error 
          END-EXEC
		
           move "Driver=" & driverClass & ";URL=" & databaseURL 
           to connectionstring
TYPE6      exec sql
TYPE6      connect using :connectionstring
TYPE6      end-exec
           if sqlcode not = 0
                display "Error: cannot connect "
                display sqlcode
                display sqlerrmc
                goback
           end-if
      
          move 200000 to EMP-EMPNO
	      EXEC SQL 
             DECLARE usercursor CURSOR FOR SELECT 
                A.EMPNO
               ,A.FIRSTNME
               ,A.LASTNAME
			   FROM CRG.EMP A
               WHERE (A.EMPNO >= :EMP-EMPNO)
			   for update
           END-EXEC 
           EXEC SQL 
              OPEN usercursor
           END-EXEC 
           PERFORM UNTIL SQLCODE < 0 OR SQLCODE =  100 
              EXEC SQL 
                 FETCH usercursor  INTO 
                    :EMP-EMPNO
                   ,:EMP-FIRSTNME
                   ,:EMP-LASTNAME
              END-EXEC 
       *> Process data from the Fetch
              IF SQLCODE = 0 
                 DISPLAY emp-empno
				 display emp-lastname
				 EXEC SQL 
                    DELETE FROM CRG.EMP  
                       WHERE CURRENT OF usercursor 
                 END-EXEC
              END-IF 
           END-PERFORM 
           EXEC SQL 
              CLOSE usercursor
           END-EXEC 
			  
          EXEC SQL DISCONNECT CURRENT END-EXEC 
          goback. 
       *> Default sql error routine / modify to stop program if needed 

       OpenESQL-Error Section.

          display "SQL Error = " sqlstate " " sqlcode 
          display MFSQLMESSAGETEXT .

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded