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

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
    450 Posts

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-09-09T18:27:45Z  

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

  • jdaniels
    jdaniels
    2 Posts

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-09-09T18:52:15Z  

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

    Yes, SQLCA. Thanks.

  • krmilligan
    krmilligan
    450 Posts

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-11-08T17:17:09Z  

    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
    283 Posts

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-11-09T13:49:58Z  

    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.

    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
    450 Posts

    Re: Embedded SQL statement not populating SQLDA on ODP Reuse

    ‏2013-11-11T15:50:43Z  
    • B.Hauser
    • ‏2013-11-09T13:49:58Z

    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

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