Highlighted
Absent Member.
Absent Member.
571 views

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

Jump to solution

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.

 
0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

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 .
        

       

View solution in original post

0 Likes
1 Reply
Highlighted
Micro Focus Expert
Micro Focus Expert

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 .
        

       

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.