Db2 (On Premises and Cloud)

Expand all | Collapse all

Array support in embedded sql sample

  • 1.  Array support in embedded sql sample

    Posted 30 days ago
    It would help if you could provide sample of array support - hopefully using COBOL. program I tried is based on DB2 z/OS sample


  • 2.  RE: Array support in embedded sql sample

    Posted 30 days ago
    The samples I tested are similar to examples you provided and my tests either insert or fetched only one row after I fixed one of the variables in the generated new API call. I tested using Windows 10 so that may be part of the reason.


  • 3.  RE: Array support in embedded sql sample

    Posted 30 days ago

    Hi, Looks like there is a bug  while precompiling if the target is MF cobol.  fWhile precompile use the option TARGET IBMCOB to generate  the correct code and test it.

    Detailed discussion about this problem is found on this thread.

    https://www.ibm.com/developerworks/community/forums/html/topic?id=8917afd6-656e-4b02-806c-13bb672aa3e8&ps=




  • 4.  RE: Array support in embedded sql sample

    Posted 30 days ago

    Example of FETCH :


           Identification Division.
           Program-ID. "openftch".

           Data Division.
           Working-Storage Section.

               copy "sqlca.cbl".

               EXEC SQL BEGIN DECLARE SECTION END-EXEC.
           01 dept-rec.
             03 pname             pic x(10) OCCURS 5 TIMES.
             03 dept              pic s9(9) comp-5 OCCURS 5 TIMES.
             03 cnt               pic s9(9) comp-5.
           01 passwd-rec.
             03 password-array OCCURS 5 TIMES.
               49 paswd-length   pic s9(4) comp-5 value 0.
               49 paswd-name     pic x(18).
           01 userid            pic x(8).
           01 passwd.
             49 passwd-length   pic s9(4) comp-5 value 0.
             49 passwd-name     pic x(18).
               EXEC SQL END DECLARE SECTION END-EXEC.

           77 errloc          pic x(80).

           Procedure Division.
           Main Section.
               display "Sample COBOL program: OPENFTCH".

          * Get database connection information.
               display "Enter your user id (default none): "
                    with no advancing.
               accept userid.

               if userid = spaces
                 EXEC SQL CONNECT TO sample END-EXEC
               else
                 display "Enter your password : " with no advancing
                 accept passwd-name.

          * Passwords in a CONNECT statement must be entered in a VARCHAR format
          * with the length of the input string.
               inspect passwd-name tallying passwd-length for characters
                  before initial " ".

               EXEC SQL CONNECT TO sample USER :userid USING :passwd
                   END-EXEC.
               move "CONNECT TO" to errloc.
               call "checkerr" using SQLCA errloc.

               MOVE 3 TO cnt.
               EXEC SQL DECLARE c1 CURSOR FOR SELECT  name, dept             1
                        FROM staff
                        WHERE job='Mgr' END-EXEC.

               EXEC SQL OPEN c1 END-EXEC.                                    2
               move "OPEN" to errloc.
               call "checkerr" using SQLCA errloc.

          * call the FETCH and UPDATE/DELETE loop.
               perform Fetch-Loop thru End-Fetch-Loop
                  until SQLCODE not equal 0.

               EXEC SQL CLOSE c1 END-EXEC.                                   5
               move "CLOSE" to errloc.
               call "checkerr" using SQLCA errloc.

               EXEC SQL ROLLBACK END-EXEC.
               move "ROLLBACK" to errloc.
               call "checkerr" using SQLCA errloc.
               display "On second thought -- changes rolled back.".

               EXEC SQL CONNECT RESET END-EXEC.
               move "CONNECT RESET" to errloc.
               call "checkerr" using SQLCA errloc.
           End-Main.
               go to End-Prog.

           Fetch-Loop Section.
               EXEC SQL FETCH c1 FOR 4 ROWS INTO :pname,                      3
                                        :password-array END-EXEC.

               display pname(1), " in dept", dept(1), "will be fetched".
               display pname(2), " in dept", dept(2), "will be fetched".
               display pname(3), " in dept", dept(3), "will be fetched".
               display pname(4), " in dept", dept(4), "will be fetched".
               display pname(5), " in dept", dept(5), "will be fetched".
               display "blank line........................,,,,,,,,,,,".


    ------------------------------------------------------------------------------------------------------------------------------------

     

    Example of INSERT :

           Identification Division.
           Program-ID. "openftch".

           Data Division.
           Working-Storage Section.

               copy "sqlca.cbl".

               EXEC SQL BEGIN DECLARE SECTION END-EXEC.
           01 dept-ind-rec.
             03 dept-ind          pic s9(4) comp-5 OCCURS 3 TIMES.
           01 array-rec           pic s9(4) comp-5.
           01 dept-rec.
             03 dept-array occurs 5 times.
                 05 deptname          pic x(9).
                 05 deptid            pic s9(9) comp-5 value 0.
           01 passwd-rec.
             03 password-array OCCURS 5 TIMES.
               49 paswd-length   pic s9(4) comp-5 value 0.
               49 paswd-name     pic x(18).
           01 pname             pic x(10).
           01 insert-rec.
             03 c1                pic x(18) OCCURS 5 TIMES.
             03 c2                pic s9(9) comp-5 OCCURS 5 TIMES.
           01 name.
              05 dept              pic s9(9) comp-5 OCCURS 5 TIMES.
           01 userid            pic x(8).
           01 passwd.
             49 passwd-length   pic s9(4) comp-5 value 0.
             49 passwd-name     pic x(18).

               EXEC SQL END DECLARE SECTION END-EXEC.

           77 errloc          pic x(80).

           Procedure Division.
           Main Section.
               display "Sample COBOL program: OPENFTCH".

               EXEC SQL CONNECT TO sample END-EXEC


               MOVE 3 to array-rec.
               MOVE "Wate" to c1(1).
               MOVE "BAD" to c1(2).
               MOVE "Worst" to c1(3).
               MOVE "bad ass" to c1(4).
               MOVE 1 to c2(1).
               move 100 to c2(2).
               MOVE 50 to c2(3).
               MOVE 77 to c2(4).
               EXEC SQL INSERT INTO test VALUES (:c2, :c1

                        )  FOR :array-rec ROWS END-EXEC.                             1

               EXEC SQL CONNECT RESET END-EXEC.
               move "CONNECT RESET" to errloc.
               call "checkerr" using SQLCA errloc.
           End-Main.
               go to End-Prog.

           End-Prog.
               stop run.

    -------------------------------------------

     

    We are supporting only static SQL and array declaration mentioned in the example. And testing the example in IBM AIX COBOL compiler.