[firebird-support] Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error

['Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support]]

Hi all,

I’m unsure if this is an error or the intended behaviour.

Got a simply table “dummy_rows_10”, single column, integer values 1..10

Next, this procedure:

SET TERM ^^ ;
CREATE OR ALTER PROCEDURE DEBUG_CURSOR_TESTS returns (
  P_OUT1 TYPE OF COLUMN DUMMY_ROWS_10.V, 
  RC Integer)
AS
declare c  cursor for (select v from dummy_rows_10 order by 1);
begin
  for select v from dummy_rows_10 order by 1
  into p_out1
  do suspend;
  open c;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend; 
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend; 
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;

  /*fetch prior from c into :p_out1;
  suspend;  
 
  /*suspend;
  fetch prior from c into :p_out1;
  suspend;
  fetch last from c into :p_out1;
  suspend;
  fetch prior from c into :p_out1;
  suspend;  
  fetch prior from c into :p_out1;  
  /*fetch relative p_out1 - 1 from c into :p_out1;
  suspend; */  
end ^^
SET TERM ; ^^


The FOR SELECT loop returns 1..10 as the values.

The fetch/suspend lines return 1..10, with a row count of 1, and another ‘10’ 
with a row count of 0.

If I add another FETCH, I get this error while executing the routine:
>>attempt to fetch past the last record in a record stream At procedure 
>>'DEBUG_CURSOR_TESTS' line: 42, col: 3

And here’s my question: the last fetch has a row_count of 0, another fetch 
returns an error. However, if I make this cursor a scrollable cursor, and my 
first ‘fetch’ is a ‘fetch prior’, I immediately get this error:
>>attempt to fetch before the first record in a record stream At procedure 
>>'DEBUG_CURSOR_TESTS' line: 9, col: 3

I get the ‘past last record’ error when I use: 
fetch absolute 11 from c into :p_out1;


I wonder, should the first fetch next that returns no data, return row_count=0, 
or also the ‘fetch past record stream’ error?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.