ESQL / ODBC / Windows / Cursors dynamic

Hi.

I am trying to make an central cobol-programm to cakk only for db-actions.

I just allways read the whole columns and switch it to cobol like in FD. So I can use my "sentence" again.

It works. Reading, writing, deleting, updating. BUT:

1. If I call this "module" from a secound cobol-programm it says me: Cursor in use (SHARE).

I tried dynamic cursor but this is still the same cursor-name and show me the same error.

2. I made 2 programs with div. cursornames on same table. I select a cursor with the same PRIMKEY for UPDATE. Normally first sentence must be locked. But I can open the sec. curs with same PRIMKEY and get NO SQLCODE. I need a information, that this sentence is been locked.

Anyone an ideas?

Can you please provide more details on how exactly this is setup?
What database vendor are you using?
What ODBC driver are you using?
Where is the cursor defined?
What directives are you using?
Is the cursor open in both programs?

If you could provide a code snippet that demonstrates the problem this would be very useful.

Thanks

      $set sql(dbman=odbc, targetdb=mssqlserver, autocommit)
       IDENTIFICATION DIVISION.
       PROGRAM-ID.     dbtest4.
       AUTHOR.         Kahlenberg.
       DATE-WRITTEN.   21-05.
      *
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SOURCE-COMPUTER.   INTEL.
       OBJECT-COMPUTER.   INTEL.
       SPECIAL-NAMES.     DECIMAL-POINT IS COMMA.

       data division.
       working-storage section.
       EXEC SQL INCLUDE SQLCA END-EXEC
       01  MFSQLMESSAGETEXT PIC X(250).
       01  IDX              PIC X(04) COMP-5.

       COPY SDPERS.
       copy DBPERS.
       01  counter pic 9(4) value 0.
       01  t pic x.
       01  lpos.
           05 lpos-v   pic 99 value 0.
           05 lpos-h   pic 99 value 1.

       01  ASC2ANS.
           05 ASC2ANSCHAR PIC 99 COMP OCCURS 2000.
       01  ASC2ANS-ANZAHL PIC 9999 COMP.

       linkage section.
       01  uebdata.
           02 uebparam                  PIC X(60).
           02 uebdata-funka.
            03 uebdata-funk             pic 9.
       procedure division using uebdata.
      * EXEC SQL
      *     WHENEVER SQLERROR perform ESQL-ERROR
      * END-EXEC.

       programmstart section.
       ps00.
           display space upon crt.
           EXEC SQL
             CONNECT TO 'softweb' user 'xxxxxx'
           END-EXEC.

           display "Personal:" at 0101.
           accept counter at 0115.
           move counter to pers-nr.
           move pers-nr to db-pers-nr.
           EXEC SQL
             DECLARE curspersupd Cursor For
             SELECT *
             from vip_pers
             where nr = :db-pers-nr
             for update
           END-EXEC.
           IF SQLCODE not = zero   
                 go pe2
           end-if.

           EXEC SQL OPEN curspersupd END-EXEC.
           IF SQLCODE not = zero   
                 go pe2
           end-if.
           EXEC SQL
             FETCH curspersupd into :db-pers-satz
           END-EXEC.
           IF SQLCODE not = zero   
                 go pe2
           end-if.
           perform pers-db2cobol.
           display "Name:" at 0201.
           display pers-name at 0210.
           accept pers-name at 0210.
           exec sql close curspersupd end-exec.
           perform rewrite-pers.

       pe2.
           goback.
       PENDE.
           exit.


       REWRITE-PERS SECTION.
           perform pers-cobol2db.
           exec sql
             UPDATE  vip_pers
            SET KURZ         = :DB-PERS-KURZ              
           ,NAME             = :DB-PERS-NAME              
           ,VNAME            = :DB-PERS-VNAME             
           ,TITEL            = :DB-PERS-TITEL             
           ,STRASSE          = :DB-PERS-STRASSE           
           ,LKZ              = :DB-PERS-LKZ               
           ,PLZ              = :DB-PERS-PLZ               
           ,ORT              = :DB-PERS-ORT               
           ,TELNR            = :DB-PERS-TELNR             
           ,GEBDAT           = :DB-PERS-GEBDAT            
           ,EINDAT           = :DB-PERS-EINDAT            
           ,AUSDAT           = :DB-PERS-AUSDAT            
           ,PASSWORT         = :DB-PERS-PASSWORT          
           ,ESSENZUSCHUSS    = :DB-PERS-ESSENZUSCHUSS     
           ,GEHALT           = :DB-PERS-GEHALT            
           ,STDLOHN          = :DB-PERS-STDLOHN           
           ,STUNDEN          = :DB-PERS-STUNDEN           
           ,ABTEILUNG        = :DB-PERS-ABTEILUNG         
           ,BERUF            = :DB-PERS-BERUF             
           ,TAETIGKEIT       = :DB-PERS-TAETIGKEIT        
           ,STATUS           = :DB-PERS-STATUS            
           ,KUNDNR           = :DB-PERS-KUNDNR            
           ,LIEFNR           = :DB-PERS-LIEFNR            
           ,RESERVE_NUM_1    = :DB-PERS-RESERVE-NUM-1     
           ,RESERVE_NUM_2    = :DB-PERS-RESERVE-NUM-2     
           ,RESERVE_NUM_3    = :DB-PERS-RESERVE-NUM-3     
           ,RESERVE_NUM_4    = :DB-PERS-RESERVE-NUM-4     
           ,RESERVE_NUM_5    = :DB-PERS-RESERVE-NUM-5     
           ,RESERVE_NUM_6    = :DB-PERS-RESERVE-NUM-6     
           ,RESERVE_NUM_7    = :DB-PERS-RESERVE-NUM-7     
           ,RESERVE_NUM_8    = :DB-PERS-RESERVE-NUM-8     
           ,RESERVE_NUM_9    = :DB-PERS-RESERVE-NUM-9     
           ,RESERVE_NUM_10   = :DB-PERS-RESERVE-NUM-10    
           ,RESERVE_NUM_11   = :DB-PERS-RESERVE-NUM-11    
           ,RESERVE_NUM_12   = :DB-PERS-RESERVE-NUM-12    
           ,RESERVE_NUM_13   = :DB-PERS-RESERVE-NUM-13    
           ,RESERVE_NUM_14   = :DB-PERS-RESERVE-NUM-14    
           ,RESERVE_NUM_15   = :DB-PERS-RESERVE-NUM-15    
           ,RESERVE_NUM_16   = :DB-PERS-RESERVE-NUM-16    
           ,RESERVE_NUM_17   = :DB-PERS-RESERVE-NUM-17    
           ,RESERVE_NUM_18   = :DB-PERS-RESERVE-NUM-18    
           ,RESERVE_NUM_19   = :DB-PERS-RESERVE-NUM-19    
           ,RESERVE_NUM_20   = :DB-PERS-RESERVE-NUM-20    
           ,FIRMA            = :DB-PERS-FIRMA             
           ,EMAILNAME        = :DB-PERS-EMAILNAME         
           ,ABTEILUNG_NR     = :DB-PERS-ABTEILUNG-NR      
           ,VERZEICHNIS      = :DB-PERS-VERZEICHNIS       
           WHERE NR = :DB-PERS-NR
           end-exec
           IF SQLCODE not = zero   
                 display "REWRITE1 PERS" at 2401
                 perform ESQL-Error go pe
           end-if.
       REWRITE-PERS-ENDE.
           EXIT.

1. I use a mysql-db

2. directive only in Source in first line

3. Standard mysql ODBC 5.3 ANSI Driver (32 bit)

4.

IF I run this application twice and input the same pers-nr, both application get's the row. Normally i tought that the FETCH will give back an error, because the same row is read in the first application with "for update" but there is NO SQLSTATUS <> 0.

First off the Net Express product was never tested nor certified with MySQL. The first product that was certified with MySQL is Visual COBOL.

In NX I get the behavior you describe if I use the ANSI version of the MySQL ODBC driver. If I use the Unicode version then it works if you also get rid of the autocommit directive. I tested with the MySQL 8.0 drivers.

I use the unicode MySql 8.0 driver. Same sh.......t.

           EXEC SQL SET SCROLLOPTION STATIC END-EXEC
           EXEC SQL SET OPTION querytime 1 END-EXEC
           EXEC SQL SET CONCURRENCY OPTCCVAL END-EXEC
      *     EXEC SQL SET BEHAVIOR UNOPTIMIZED END-EXEC

           display "Personal:" at 0201.
           accept m-pers-nr at 0215.
           move m-pers-nr to pers-nr.
           move pers-nr to db-pers-nr.

      *     exec sql begin transation end-exec
           EXEC SQL
             DECLARE curspersupd Cursor For
             SELECT *
             from vip_pers
             where nr = :db-pers-nr
             for update
           END-EXEC.
           display "01 " at 0101.
           perform ESQL-Error.
           IF SQLCODE not = zero   
                 go pe2
           end-if.

           EXEC SQL OPEN curspersupd END-EXEC.
           display "02 " at 0101.
           perform ESQL-Error.
           IF SQLCODE not = zero   
                 go pe2
           end-if.

           EXEC SQL
             FETCH curspersupd into :db-pers-satz
           END-EXEC.
           display "03 " at 0101.
           perform ESQL-Error.
           IF SQLCODE not = zero   
                 go pe2
           end-if.

           perform pers-db2cobol.
           display "Name:" at 0301.
           display pers-name at 0310.
           accept pers-name at 0310.
           display "04 " at 0101.
           perform rewrite-pers.
           exec sql close curspersupd end-exec.
           display "09 " at 0101.
           perform ESQL-Error.

           EXEC SQL COMMIT END-EXEC.
           perform ESQL-Error.
           goback.

  rewrite-pers section.
           exec sql
             UPDATE  vip_pers
             SET
            NR               = :DB-PERS-NR
           ,KURZ             = :DB-PERS-KURZ            
           WHERE NR = :DB-PERS-NR
           end-exec
 rewrite-pers-ende.
       exit.

If I run both in same time is goes on till rewrite-pers. The first ist going do make the update and the secound appl. ist waiting then. After x secounds I get: "Error -1205 (Lock wait timeout exceeded, try restarting transaction", if the first app ist not doing the commit. If the commit is done, the secound app ist going on, but then I have wrong data, because first updates are gone.

Is their no chance to get an error/status at the moment i do the fetch of the cursor, or any time before and incl. fetch? (like in Isam: read with lock. Error Status 9A)

This behavior appears to be controlled using MySQL configuration options. I Googled for this and found that in the file:

C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini

add or modify the setting innodb_lock_wait_timeout

I added one that looked like:

innodb_lock_wait_timeout=1

and then the timeout occurs after 1 second instead of waiting the default 50 seconds.