CODEWITHSUNDEEP
cursorfirebirdfirebird2.5
May12
May12

Reputation: 2520

How to insert rows into table using cursor in Firebird?

Collegues, I am new in Firebird. I'am trying to call procedure (p_procedure) in cursor cycle and insert result into table (tmp_port). 

execute block
as
declare contr integer;
declare IN$DT date;
declare cur_list_of_cont cursor for (select first 100 contracts.doc from TABLE_1);

begin

delete from  tmp_port;
IN$DT = getdate()-2;

open cur_list_of_cont;
  while (ROW_COUNT > 0) do
  begin
    fetch cur_list_of_cont into contr;


     insert into tmp_port (
        DT,
        ....)

        select
            :IN$DT as DT,
            ...
        from p_procedure (0,  :contr , :IN$DT);


    if (ROW_COUNT = 0) then leave;
    suspend;
  end
  close cur_list_of_cont;

end;

The problem is that only single, fisrt row processed from cur_list_of_cont. Why other 99 rows are not processed?

UPDATE

FireBird Server version is 2.5

UPDATE

In this realization it works fine:

begin

IN$DT = getdate()-2;

FOR select first 100 contracts.doc from TABLE_1
 INTO :contr 
 DO
  BEGIN
  insert into tmp_port (
        DT,
        ....)

        select
            :IN$DT as DT,
            ...
        from p_procedure (0,  :contr , :IN$DT);

  END
  SUSPEND;

end;

It will be better if the first example works too. How to do it?

Upvotes: 3

Views: 2153

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

The problem is that you are using ROW_COUNT incorrectly. As documented in the Firebird 2.5 language reference:

Description: The ROW_COUNT context variable contains the number of rows affected by the most recent DML statement (INSERT, UPDATE, DELETE, SELECT or FETCH) in the current trigger, stored procedure or executable block.

...

  • After a FETCH from a cursor, ROW_COUNT is 1 if a data row was retrieved and 0 otherwise. Fetching more records from the same cursor does not increment ROW_COUNT beyond 1.

The while (ROW_COUNT > 0) might be false if nothing was deleted, you might also exit the loop if your procedure returns no rows and therefor nothing was inserted.

If you look at the example of using FETCH, you can modify your code to:

open cur_list_of_cont;
fetch cur_list_of_cont into contr;
while (row_count > 0) do
begin
    insert into tmp_port (
          DT,
          ....)
      select
          :IN$DT as DT,
          ...
      from p_procedure (0,  :contr , :IN$DT);
    fetch cur_list_of_cont into contr;
end
close cur_list_of_cont;

However, given your code, you should consider using FOR SELECT as it is easier in most cases.

Upvotes: 4

Related Questions