Topic
5 replies Latest Post - ‏2013-11-11T15:50:43Z by krmilligan
jdaniels
jdaniels
2 Posts
ACCEPTED ANSWER

Pinned topic Embedded SQL statement not populating SQLDA on ODP Reuse

‏2013-09-09T16:21:09Z |

Hello,

We are running into an SQL issue in embedded RPG. We have reworked several RPG programs using embedded SQL taking them from dynamic statements to static. These statements are always the same, so they did not need to be dynamic. We are now reusing ODPs which is the desired result, but it appears that in the course of doing this the SQLDA stops being populated once the ODP is reused. Is this normal behavior? I would appreciate your input. Below is a snippet of code:

 

  // --- Declare the data selection cursor                
    exec sql                                              
      Declare SPMANUR02_List                              
        Cursor For                                        
          Select * from SPMANUV      
-- This is a view                    
          Where EQTY_ID = :pieqty_Id                      
          Order by POVNAM                                 
                     ;                                    
                                                          
  // --- Open the cursor                                  
    exec sql                                              
      open SPMANUR02_List                                 
               ;                                          
                                                          
  if SQLCOD = 0;                                          
    // --- Save the number of rows, if open was successful
    liEntries = SQLER2 + 1;                                  
-- here is where we are having the issue. All entries in SQLDA are clear. 
  endif;   

  • krmilligan
    krmilligan
    378 Posts
    ACCEPTED ANSWER

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-09-09T18:27:45Z  in response to jdaniels

    Are you referring to the SQLER2 field in the SQLCA (not DA)?

    • jdaniels
      jdaniels
      2 Posts
      ACCEPTED ANSWER

      Re: Embedded SQL statement not populating SQLDA on ODP Reuse

      ‏2013-09-09T18:52:15Z  in response to krmilligan

      Yes, SQLCA. Thanks.

  • krmilligan
    krmilligan
    378 Posts
    ACCEPTED ANSWER

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-11-08T17:17:09Z  in response to jdaniels

    If you have the latest Database Group PTF applied and are still experiencing this problem, then you should contact IBM Support to open a PMR.

    • B.Hauser
      B.Hauser
      230 Posts
      ACCEPTED ANSWER

      Re: Embedded SQL statement not populating SQLDA on ODP Reuse

      ‏2013-11-09T13:49:58Z  in response to krmilligan

      Isn't SQLER2 retuning the same information as DB2_ROW_NUMBERS in the Diagonstics Area?

      According the SQL Reference, DB2_ROW_NUMBERS may return *Zeros.

      Exerpt from the SQL Reference:

      If the previous SQL statement was an OPEN or a FETCH which caused the size of the result table to be known, returns the number of rows in the result table. For SENSITIVE cursors, this value can be thought of as an approximation since rows inserted and deleted will affect the next retrieval of this value. Otherwise, the value zero is returned.

      Birgitta

      Updated on 2013-11-09T13:50:45Z at 2013-11-09T13:50:45Z by B.Hauser
      • krmilligan
        krmilligan
        378 Posts
        ACCEPTED ANSWER

        Re: Embedded SQL statement not populating SQLDA on ODP Reuse

        ‏2013-11-11T15:50:43Z  in response to B.Hauser

        The issue is that a non-zero value was returned when ODP is created and zero is returned when the ODP is reused.