f2s_esql_move_next¶

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

Syntax

f2s_esql_move_next [{ Boolean variable}] [using {variant}]

Description

This command is used to fetch the next row returned by the associated SQL statement.

The command will set the Found property, if the optional Boolean variable is not used.

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.

Note that the columns are fetched in the order that the SQL statement generates them, so the variables must follow the same order.

The command f2s_split_array_helper can be used in conjunction with f2s_esql_move_next to move the results into variables.

Error Codes

Examples

** A simple example without using the optional Boolean variable or the “using” syntax**

Variant[] vRow
f2s_esql_create_stmt "select * from orderheader where SalesPerson_ID = ?"
f2s_esql_prepare_stmt
f2s_esql_set_param 1 to "NONE"
f2s_esql_execute_stmt
f2s_esql_move_next
While (Found)
    f2s_esql_get_row vRow
    Showln (SFormat("First Column: %1 Second Column:  %2",vRow[0], vRow[1]))
    f2s_esql_move_next
Loop
f2s_esql_close_stmt

SQL Server Code to create a simple Stored Procedure ‘MULTI_RESULT_SET_TEST_SP’ that has two select statements and thus two Result Sets

CREATE PROCEDURE MULTI_RESULT_SET_TEST_SP
AS
BEGIN
    SET NOCOUNT ON;
    --First of two select statements and will have two result sets
    SELECT CUSTOMER_NUMBER,NAME,STATE FROM customer WHERE STATE = 'CA';
    --First of two select statements and will have two result sets
    SELECT * FROM salesperson order by ID;
END

DataFlex code to call the SQL Server ‘MULTI_RESULT_SET_TEST_SP’ Stored Procedure with a Boolean variable and the “using” syntax

Procedure ShowIt Global Variant[] vData
    Integer iSize i
    Move (SizeOfArray(vData)) to iSize
    For i from 0 to (iSize-1)
        Showln "item #" i " = " vData[i]
    Loop
End_Procedure

Procedure MultiResultSetStoredProcCall
    Variant vStat
    Variant[] vRow
    Boolean bWorked
    String sName
    Integer i

    Showln "Calling the first Result set in the MULTI_RESULT_SET_TEST_SP"
    Showln "This will read the first selected row from the CUSTOMER Table"
    f2s_esql_set_proc_name "dbo.MULTI_RESULT_SET_TEST_SP" using vStat
    f2s_esql_set_cursor_type f2s_olecursor_client using vStat
    f2s_esql_prepare_stmt using vStat

    f2s_esql_execute_proc using vStat
    f2s_esql_move_next bWorked using vStat
    If (bWorked) Begin
        f2s_esql_get_row vRow using vStat
        Send ShowIt vRow
    End
    Showln "Change to the second Result Set in the Stored Procedure"
    Showln "This will read the first selected row from the SALESPERSON Table"
    f2s_esql_next_resultset using vStat
    f2s_esql_get_row vRow
    Send ShowIt vRow
    Showln ""
    f2s_esql_close_stmt using vStat
    inkey windowindex
End_Procedure

Send MultiResultSetStoredProcCall

Related Commands

• f2s_esql_create_stmt

• f2s_esql_prepare_stmt

• f2s_esql_set_param

• f2s_esql_execute_stmt

• f2s_esql_close_stmt

• f2s_esql_next_resultset

Replaces: Command SQL_FETCH_NEXT_ROW