[Info-Ingres] API Scrollable Cursors ... is this EVIL expected?

[Martin Bowes]

Hi All,

 

Playing with scrollable cursors in API…

 

I create a scrollable cursor which extracts 10 rows of data. I can use the relative, absolute, first and last positioning commands without a problem an can in each case use the cursor to read the set of data from the current point to the end of the set. Whoo-Hoo.

 

I then delete the 5^th row, positon the cursor to the start of the set and scroll forward until the end of the set…which is now flagged at the 4^th row!

 

I can skip the cursor to the sixth row and start reading the rest of the set to the end.

 

If I skip the cursor back to the deleted row and start reading … there is nothing to read.

 

So it looks like deleting a middle row has broken my set into two halves.

 

Is this the expected behaviour? Is it just me or is that EVIL?

 

Marty

 

 

[Ingres Forums]

What would happen if you extended your scrollable cursor testing to a
cursor that's reading from a row producing procedure? Surely there's
some potential here for further evil.


--
geraintjones
------------------------------------------------------------------------
geraintjones's Profile: http://community.actian.com/forum/member.php?userid=57763
View this thread: http://community.actian.com/forum/showthread.php?t=16138

[Ingres Forums]

geraintjones;49671 Wrote:
> What would happen if you extended your scrollable cursor testing to a
> cursor that's reading from a row producing procedure? Surely there's
> some potential here for further evil.

I'm glad you asked that particularly EVIL question....

After some testing...

You can't define an updatable cursor on unstructured data (as produced
by a table procedure). You must have a primary key or unique index
involved.

You can define a readonly cursor on the unstructured data and hence use
a scrollable readonly cursor to read the data coming from the table
procedure....basically in any order you want.

Marty


--
martymonster
------------------------------------------------------------------------
martymonster's Profile: http://community.actian.com/forum/member.php?userid=113074

[Ingres Forums]

You don't say what results you are seeing, but I assume that when you
call IIapi_getColumns() on the deleted row you are getting
IIAPI_ST_NO_DATA and thinking that marks the end of the set. That is
true for non-scrollable updatable cursors and read-only cursors. For
updatable scrollable cursors, IIAPI_ST_NO_DATA marks both deleted rows
and the end of the set.

There is additional data available for scrollable cursors using
IIapi_getQueryInfo(). After calling IIapi_getColumns() you can call
IIapi_getQueryInfo() (whether you got IIAPI_ST_NO_DATA or not) and take
a look at gq_rowStatus. Among the info bits, you can check
IIAPI_ROW_DELETED to determine if you have landed on a deleted row or
IIAPI_ROW_AFTER if you have reached the end of the set. There is also
gq_rowPosition. Both of these are indicated by IIAPI_GQ_ROW_STATUS
flag.

As for being EVIL, I personally think that anyone who mixes scrollable
and updatable cursors has bought their own ticket for the road to hell
(anyone want to discuss updating columns values such that the row no
longer matches the select criteria).

There were two basic ways to implement row deletions: remove the rows
completely from the set or leave 'holes' in the set where the rows were
deleted and provide a way to detect the deleted rows. With the first,
deleting rows 5 and 7 of 10 rows (if you deleted row 5, is row 7 still
7, or was it 8?) results in a set of 8 rows with row 6 now row 5 and row
8 now row 6. So if you want to position to row 6, do you really want to
now position at 5, or at 6 which is actually row 8. If you delete row
5, where do you end up (if next action is position to current row vs
reading next row, is row 6 (now 5) the result in both cases)? Evil is
as evil does.

Ingres chose to maintain the set intact with 'holes' where rows are
deleted. It does mean a little bit of extra gymnastics when
positioning/reading the deleted rows, but it keeps the row references
constant and consistent.


--
thogo01
------------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=4992

[Ingres Forums]

Thanks Gordy,

That explains it neatly. I'll implement that getQueryInfo() stuff you
mention and see what happens.

Marty


--
martymonster
------------------------------------------------------------------------
martymonster's Profile: http://community.actian.com/forum/member.php?userid=113074