On Tuesday, 12 January, 2016 13:58, Christian Schmitz <realbasiclists at monkeybreadsoftware.de> said:
> Unless I missed something, I may suggest > * moveprev > * movefirst > * movelast > * move to record in cursor with given index There are no relational database engines on the market that can do this. Those that *appear* to be able to do this are using some sleight of hand, either entirely on the client side (most common), or using some "server side assistance" so that the result set does not have to be cached in its entirety on the client. Server side assistance was developed because of the propensity, once the client side feature was made "built-in", of the great unwashed to do things like "select * from customers;" and use the resulting client side cursor to scroll through the 200 million customer records with their 6 line GUI. Needless to say, performance was somewhat suboptimal to say the least. If you have ever used DSA.MSC to browse an Active Directory domain containing a billion objects then you know exactly what I mean. Generally speaking, you have the choice of "server side" or "client side", either "complete set" or "keyset". Client-Side Complete Set works as per the above -- the result set is sent into a row cache on the client, and the application fiddles with this rowset to make it look like you are going forward and backward (though you are not). There is no further involvement of the database server. Server-Side Set works the same way but the result set is cache in a temp table on the server indexed by ordinal position of the result row. Magically created queries against this temp table make it look like you are scrolling the result set. Keyset driven scrollable cursors work in similar fashion, except that instead caching the entire result row, only the primary keys of the source data rows are saved in the cache, with the same ordinal position primary key. This keyset is stored on either the client-side or the server-side (in a temp table). By the client submitting the correct queries, it can be made to appear that you have a scrollable cursor. The advantage of a keyset is twofold -- it is often smaller than the full result set, and since it contains the primary keys of the underlying tables, one can do an UPDATE sourcetable ...WHERE CURRENT OF SET. By design a Relational DBMS only has a concept of a result set and is not navigable except through artifice and trickery. If you want a navigable database they you want either a Hierarchical, Network, or Network Extended database model, not relational. > Looping over a recordset twice is often useful. Simply resubmit the original query. If you need repeatable read, do both inside the same transaction.