Retrieving the current record count of an executing SQL operation

[Hoethe]

If an RPG program executes an SQL statement such as "DELETE FROM table1", is there any way to retrieve the number of records that it has currently deleted similar to how the interactive SQL session on the iSeries manages it?

I might be deleting from large files and it would be good to give users feedback about how far through the current file they are.

[TedHolt]

Re: Retrieving the current record count of an executing SQL operation

The SQLERR3 variable tells you how many records were deleted. It also works for fetch, insert and update. You can only check it after the operation finishes, so I doubt you could use it to report progress.

[DeadManWalks]

Re: Retrieving the current record count of an executing SQL operation

here is an example of what i do;

d RowCount        s             10i 0                            
 /FREE                                                           
  // Update the control record                                   
  Exec Sql                                                       
     Update Control                                              
        Set Con_Date = (Cast(cast(year(Current_date) as Char(4)) 
                       as Dec(4,0)) * 10000  )  +                
                       (MONTH(Current Date) * 100)+              
                       DAY(current date)                         
     Where Con_System = :ThisProgram;                            
                                                                 
  Exec Sql                                                       
     Get Diagnostics                                             
        :RowCount = ROW_COUNT;                                   
  If RowCount <> 1;    
......

[Hoethe]

Re: Retrieving the current record count of an executing SQL operation

Thanks for your replies but in your example what I'm looking to report on is the situation where you have 100,000,000 rows in your control table and the update takes a significant length of time. In this case I'd like to be able to display the current number of records updated so far on screen. I know this or something similar is possible because STRSQL manages to display this kind of information but I can't find any documentation on how they manage this.