What is PDO scrollable cursor?

Question

What is "Fetching rows with a scrollable cursor" all about?

Answer 1

It creates a cursor for the query, which allows you to iterate over the result set without fetching the whole result at once. A scrollable cursor, specifically, is one that allows iterating backwards.

Example use: You can scroll forward until you find the record you need and iterate back to fetch the previous records, if you need them, too.

Answer 2

Wikipedia gives this :

With a non-scrollable cursor, also known as forward-only, one can FETCH each row at most once, and the cursor automatically moves to the immediately following row. A fetch operation after the last row has been retrieved positions the cursor after the last row and returns SQLSTATE 02000 (SQLCODE +100).

And this :

A program may position a scrollable cursor anywhere in the result set using the FETCH SQL statement.

You should read the article linked earlier, but this looks like some interesting information too :

Scrollable cursors can potentially access the same row in the result set multiple times. Thus, data modifications (insert, update, delete operations) from other transactions could have an impact on the result set.

In PHP, you can use scrollable cursors with PDO by using prepared statements (see PDOStatement::fetch) :

To request a scrollable cursor for your PDOStatement object, you must set the PDO::ATTR_CURSOR attribute to PDO::CURSOR_SCROLL when you prepare the SQL statement with PDO::prepare().

(there is also an example further down that page)

What seems interesting is the ability to "scroll" through the resultset, without having to get all the data in memory to iterate over it.