Db2 (On Premises and Cloud)

Expand all | Collapse all

Array support in embedded sql issues

  • 1.  Array support in embedded sql issues

    Posted 30 days ago
    I've tried pre-compiling a COBOL source with both dynamic SQL and static SQL and the code generated gets the same results. I get a SQLCODE of -4951 after call to sqlgsetasz since the SQL-INPUT-SQLDA-ID is set to 0 by default: - see attached screenshot (beta1.png) If I set it to a value of 1 - the program returns only one row per fetch. I've tried tweaking SQL-ARRAY-SIZE parameter but it doesn't make any difference. For example C:\edtest\fetchtest>fetch2 AFTER OPEN SQLERRD(3) = +0000000000 SQLERRD(3) = +0000000001 000010 CHRISTINE 4220.00 .00 .00 SQLERRD(3) = +0000000001 000020 MICHAEL 3300.00 .00 .00 SQLERRD(3) = +0000000001 000030 SALLY 3060.00 .00 .00 SQLERRD(3) = +0000000001 000050 JOHN 3214.00 .00 .00 I can give you sample programs if you like. It would help if you could provide some documentation on what values should be provided to sqlgsetasz API


  • 2.  RE: Array support in embedded sql issues

    Posted 30 days ago

    Hi,

       From the sample testcases we have, pasting the examples : We are supporting only static SQL and array declaration mentioned in the example.  And testing the example in IBM AIX COBOL compiler.

    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.




  • 3.  RE: Array support in embedded sql issues

    Posted 30 days ago

    I found the problem.  If you precompile with TARGET IBMCOB, it generates the correct code, with TARGET MFCOB, the info is moved to the wrong parameters.  When I copied the correct code to generate CBL program, it ran okay.

    The code on left is with IBMCOB and on right is with MFCOB:

    See attached screenshot




  • 4.  RE: Array support in embedded sql issues

    Posted 30 days ago

    Hi Thanks for pointing out  error in generated cobol code. Here we have tested only with IBM COBOL,  hence we missed this part.

     

    Have raised the defect for IBM developer to fix this.




  • 5.  RE: Array support in embedded sql issues

    Posted 30 days ago
    There is also a couple issues with array insert generated code for both IBMCOB and MFCOB for the first sqlstlv call there is an extra "(0)" generated in call statement which is invalid - see screenshot. In addition, if you use an host variable for the number of rows to insert, it generates code and gets error "DB21018E A system error occurred. The command line processor could not
    continue processing." using TARGET MFCOB - try attached program


  • 6.  RE: Array support in embedded sql issues

    Posted 30 days ago

    I noticed that array support made it into FP3 of 11.1 that just went GA but only for the PRECOMPILE command.   Are there plans in some future beta to add support for the IBM pre-compiler services API db2CompileSql ?

    When I try to pre-compile the following SQL statement  I get a -104 invalid token FOR error using db2CompileSql:

     EXEC SQL FETCH c1 FOR :cnt ROWS INTO :pname,                                                              
                        :dept END-EXEC.           
          

     




  • 7.  RE: Array support in embedded sql issues

    Posted 30 days ago
    @venkateshbabu.ks ?