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'.

BNF

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

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.

Comments

When using the cursor several times (in a procedure for example) doing FETCH NEXT after then OPEN cursor will work only the first time. This (seems to) work always :
OPEN acursor;
FETCH FIRST 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;