f2s_esql_set_cursor_type¶

Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers

Syntax

f2s_esql_set_cursor_type { Attribute Type}

Description

This command is used to set the cursor type of the cursor. This command must be in conjunction with the required “using” variant.

The optional “using” syntax allows you to pass around the interface created by this command. The variable passed MUST be a variant. This allows for multiple statements to be active concurrently in some cases. If you use the “using” syntax, it must be used on all subsequent commands. Mixing the using syntax is not supported. So for instance, if you plan on having multiple statements active concurrently, all active statements must make use of the using syntax.

Error Codes

Supported attributes

Attribute definitions

f2s_olecursor_default (SQL Server)

MS SQL Server This will be used if you don’t specify a cursor type. This cursor has all of the default attributes that Microsoft applies to a cursor. In Microsoft parlance it is called a firehose cursor. It is read only and forward only. It can handle calls that return multiple result sets. If used inside a transaction, you must close this cursor before committing the transaction. In most cases using the default cursor is your best and fastest choice. The limitation of this cursor is that if a transaction is executed while you have an open record set, as soon as that transaction is either committed or aborted, that record set is closed.

f2s_olecursor_default (Oracle and PostgreSQL)

This is a forward only cursor and it is the default Oracle Cursor.

f2s_olecursor_client (Oracle and PostgreSQL)

A client cursor that is very similar to our default cursor. It is read only and forward only. The only difference is that this cursor will not close a record set after a transaction has been committed or aborted. This means that it is possible on this cursor to get back “dirty” data. If you know that this won’t be a problem for the code you are writing and you need to continue to loop over the rows in the result set, using a client cursor might be the right choice. It can handle calls that return multiple result sets. If used inside a transaction, you must close this cursor before committing the transaction.

f2s_olecursor_server (SQL Server)

This is a server cursor that supports scrolling. This means that you can navigate forward and backward through a result set. A statement using a server cursor can only return a single result set. Multiple result set statements are not supported with server cursors. You are able to have multiple server cursors open simultaneously. A server cursor can sometimes be faster than a client cursor. For instance, if you have to do an open-ended query that you are not able to use the LIMIT clause on, but are only interested in the first row of the results, a server cursor can be much faster as less data will be sent over connection. Additionally, server cursors can be useful when you are heavily mixing transactions and embedded SQL. A server cursor does not need to be closed within a transaction for the transaction to be committed. Some statements may require a server cursor (DDL statements and some DML statements).

f2s_olecursor_scrollable (Oracle and PostgreSQL)

This cursor type is only valid for SELECT statements. This cursor allows forward or backward navigation (move next, move previous).

Examples

** A SQL Server example **

    Variant vStat
    Variant[] vRow

    f2s_esql_create_stmt "Select * from sys.databases" using vStat
    f2s_esql_set_cursor_type f2s_olecursor_default using vStat
    f2s_esql_prepare_stmt using vStat

    f2s_esql_execute_stmt using vStat
    f2s_esql_move_next using vStat
    While (Found)
        f2s_esql_get_row vRow using vStat
        Showln "Forward direction Item: " vRow[0]
        f2s_esql_move_next using vStat
    Loop
    f2s_esql_close_stmt using vStat
    inkey windowIndex

Related Commands

• f2s_esql_move_next

• f2s_esql_move_previous

• f2s_esql_close_stmt

• f2s_esql_prepare_stmt

• f2s_esql_execute_stmt

Replaces: SQL_OPEN_CURSOR_STMT