f2s_esql_next_resultset¶
Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers
Syntax¶
f2s_esql_next_resultset [using {variant}]
Parameter |
Description |
---|---|
variant |
Optional variant type variable that will receive a statement interface |
Description¶
This command is used when working with SQL statements that return multiple result sets. In that scenario, calling this command will move to the next result set.
Please note, multiple statements separated by “;” will not work with f2s_esql_create_stmt. Only the first statement will be executed. For example:
string Statement
variant [] vRow
move "select Name, City from Customer where Customer_Number=1;" to Statement
append Statement "select Name, City from Vendor where ID=1;"
f2s_esql_create_stmt Statement
f2s_esql_prepare_stmt
f2s_esql_execute_stmt
f2s_esql_move_next
if (Found) begin
f2s_esql_get_row vRow
Showln (SFormat("Customer Name: %1, City: %2", vRow[0], vRow[1]))
f2s_esql_next_resultset
f2s_esql_get_row vRow
Showln (SFormat("Vendor Name: %1, City: %2", vRow[0], vRow[1]))
end
f2s_esql_close_stmt
will only return the customer name and city. The statement retrieving the vendor name and city will not be executed. However, stored procedures that return multiple result sets will work using the f2s_esql_next_resultset command (see the example below).
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¶
Error Code |
Error Message |
---|---|
25004 |
No more result sets available on this query. |
Examples¶
- 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 under the ‘.dbo’ schema
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