SQL Cursors

Server-Side SQL Cursors

Server-side Cursors in OpenBase SQL enable clients to perform a select or stored procedure call and cache a snapshot of the results on the server. This allows clients to incrementally request portions of the result set or start fetching again from the beginning.

SQL Cursors are named so that clients can navigate the results of several cursors simultaneously. Clients use the cursor name to specify which cursor you want to fetch.

Video: OpenBase Cursors

Creating SQL Cursors

To create an SQL Cursor, start with an SQL Select or stored procedure call. Here is an SQL statement that uses the pubs database:

SELECT au_fname, au_lname FROM authors ORDER BY au_lname

Then, to create a cursor that uses this SQL result set, precede the SQL with the CREATE CURSOR command. Here is an example that creates a cursor named myAuthors:

CREATE CURSOR myAuthors AS SELECT au_fname, au_lname FROM authors
    ORDER BY au_lname

Creating a cursor will return nothing. Before you can get the results you need to FETCH.

Fetching SQL Cursor Results

Once a SQL cursor is created, you retrieve the data using the FETCH command. Here is an example of a FETCH command that specifies five result rows:

FETCH 5 FROM myAuthors

The label ‘myAuthors’ is the name of the cursor as specified in the CREATE CURSOR statement. A second call to FETCH will get the next five in the result set.

Positioning the Cursor

If you need to set the read position of the cursor to a specific record, you can use the SET POSITION command. A call to FETCH will then start reading from the position specified using SET POSITION.

Here is an example using the myAuthors cursor:

SET POSITION 50 FOR myAuthors

Rewinding Cursor Results

There may be cases where you want to rewind a result set to the beginning so it can be fetched again. OpenBase SQL Cursors allow you to do this using the REWIND command. Here is an example using the myAuthors cursor:

REWIND myAuthors

After a cursor result set has been reset using REWIND, the FETCH command will begin at the very beginning.

Closing Cursors

It is very important to close SQL cursors after you are finished with them. To close a cursor, issue the CLOSE CURSOR command. Here is an example that closes the myAuthors cursor:

CLOSE CURSOR myAuthors