Subject Re: [firebird-support] Cursor not Executing Properly
Author Svein Erling Tysvær
Post date 2017-02-17T10:51:32Z
I don't use cursors myself, but are you sure "open cur_list_of_contracts;" is enough to set a value different from 0 for row_count? Maybe you need to do the first fetch earlier, e.g.

execute block as                                  
  declare contractno CHAR(20);
  declare cur_list_of_contracts cursor for (select CU.CONTRACTNO 
                                            from CLASSIC_UPGRADE CU 
                                            join CONTRACT C on C.CONTRACTNO = CU.CONTRACTNO 
                                            where C.CONTRACTSTATUS = 'A'
                                              and CU.INSERTDATE > '09/01/2016'
                                              and CU.INSERTDATE < '09/10/2016' 
                                              and (CU.STATUS in ('authorized', 'Authorized') 
                                              and C.CONVERT2YRDATE IS NULL );
begin
  open cur_list_of_contracts;
  fetch cur_list_of_contracts into contractno;
  while (ROW_COUNT > 0) do
  begin
    UPDATE CONTRACT C
    SET CONVERT2YRDATE = (SELECT CU.INSERTDATE From CLASSIC_UPGRADE CU where CU.CONTRACTNO = :contractno)
    where C.CONTRACTNO = :contractno;
    if (ROW_COUNT = 0) then leave;
    fetch cur_list_of_contracts into contractno;
  end
  close cur_list_of_contracts;
end;

HTH,
Set

2017-02-17 10:04 GMT+01:00 Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com>:


Hi All, 

I have created the below cursor on Firbird DB (version 2.5). The update statement in below cursor is not updating value.
 
execute block
as                                   
declare contractno CHAR(20);
declare cur_list_of_contracts cursor for (Select CU.CONTRACTNO From CLASSIC_UPGRADE CU, CONTRACT C where C.CONTRACTNO = CU.CONTRACTNO AND C.CONTRACTSTATUS = 'A' AND CU.INSERTDATE > '09/01/2016' AND CU.INSERTDATE < '09/10/2016' AND (CU.status = 'authorized' or CU.status = 'Authorized') AND C.CONVERT2YRDATE IS NULL  );
 
begin
open cur_list_of_contracts;
  while (ROW_COUNT > 0) do
  begin
    fetch cur_list_of_contracts into contractno;
    UPDATE CONTRACT C
    SET CONVERT2YRDATE = (SELECT CU.INSERTDATE From CLASSIC_UPGRADE CU where CU.CONTRACTNO = :contractno)
    where C.CONTRACTNO = :contractno;
 
    if (ROW_COUNT = 0) then leave;
    suspend;
  end
  close cur_list_of_contracts;
end;
 
  
The block is getting executed without any errors.

But, the output is not as expected. The records in the table are not getting updated.


With Best Regards.

Vishal