NexusDB

In this part of the series we will look at the new CURSOR support in NexusDB.

Cursors in SQL allow the developer/user to iterate row by row through SQL result sets.

Example

Here's a small example of cursors in action:

DROP TABLE if exists table1;
DROP TABLE if exists table2;
CREATE TABLE table1 (f1 INT, f2 varchar(10));
CREATE TABLE table2 (f1 INT, f2 varchar(10));
INSERT INTO table1 values (1,'r1'), (2,'r2'), (3,'r3');

DECLARE myvar1 INT;
DECLARE myvar2 varchar(10);

DECLARE acursor CURSOR FOR SELECT f1, f2 from table1;
OPEN acursor;
FETCH NEXT FROM acursor INTO myvar1, myvar2;

WHILE @@FETCH_STATUS = 0 DO
  INSERT INTO table2 values (myvar1, myvar2);
  FETCH NEXT FROM acursor INTO myvar1, myvar2;
END WHILE;
CLOSE acursor;

So the first thing we need to do is to declare a variable of type cursor and associate it with a sql result set. Then we need to open the cursor, which will execute the associated query. After that the cursor will be positioned before the first row. To get the first record we need to do FETCH NEXT, which can optionally read the row values into local variables. Now naturally we can already be at the end of a result set (it would be empty in that case), so we need to check the result of the FETCH operation. NexusDB defines the system variable @@FETCH_STATUS which has statement scope and returns 0 for no error or a value <> 0 if EOF, BOF or any other error condition. If @@FETCH_STATUS<>0 then the variables that were passed as target for the values are undefined. Finally we also need to close the cursor to release the resources.

In the example above after the first FETCH will set FETCH_STATUS to 0, myvar1 will be 1 and myvar2 will be 'r1'.

Syntax

Of course there's not only support for FETCH NEXT. Please look at the full syntax here:

DECLARE cursorname CURSOR FOR <sqlstatement>
OPEN cursorname;
CLOSE cursorname;
FETCH [ [ <fetch orientation> ] FROM ] cursorname INTO <fetch target list>
<fetch orientation> ::= NEXT | PRIOR | FIRST | LAST | { ABSOLUTE | RELATIVE} <offset>;

The future

The initial release will see read-only cursors, but we're looking into extending that down the road with updateable cursors. Immediately I want to state the obvious, that this will have similar restrictions to the RequestLive property in TnxQuery: not all sql statements can be updateable, but we will endeavor to support as many cases as possible.

Try it yourself

We've made a test version of NexusDB V3 available for everyone interested to try out the new features. Please give it a shot and if you find any problems please report them to our new Issue Tracker (website registration required).