Here are some general answers and guidance for using scrollable cursors in an environment such as yours.
1) You'll want to tell the CocoBase runtime that you need to use cursors, by adding the proper :cocoprop qualifier to your url - such as:
2) You'll need to connect with CocoPowderPlugin20 instead of CocoPowder as your SQL runtime class.
3) If you call setPos() past the end of the result set, it will simply set the cursor to the end of the result set - so no more records would be returned, and hasNext() would return false.
4) The result set is a snapshot of the database at the point the query was executed, so changes to the database would not be reflected in that result set until a new query is issued.
5) Result set size can be determined in different ways, each of which has different implications.
- You can use a different map with a select count(*) as the field entries, and issue that query before initiating the actual one. This approach requires a second map that matches the same query criteria, which isn't optimal from a maintenance perspective.
- You can use a CBQuery or CBQueryBuilder facility to issue an equivalent query to your current QBE/EJBQL, and those advanced Query APIs have CBQueryBuilder.selectCount() and CBQuery.setExtentIsCountOnly(true) which both modify the generated query to return a select count(*).
- You can use EJBQL select count(...) syntax such as:
SELECT COUNT( c )
FROM Customers AS c
WHERE c.address.state = 'CA'
- You can also call CBCursor.size() - but in the case of Oracle it might produce a very slow result. If you intend to page through the entire list, or are likely to jump to the end of the list, this is not an unreasonable choice. If you typically start near the beginning of the list, and only read small pages, then this is likely to be the slowest option.
These are some of your options, and you might want to prototype the choices that seem most suitable to your application to see which performs best for your particular application usage. You may find you use different options for different queries if your application requirements are diverse enough.