f2s_esql_set_resultset¶

Applies to: Oracle and PostgreSQL Drivers

Syntax¶

f2s_esql_set_resultset {variable} [using {variant}]

Parameter	Description
variable	Variant type to hold the result set.
variant	Optional variant type variable that will receive a statement interface

Description¶

One way to retrieve multiple record sets from either Oracle or PostGresQL is to use a record set (in PostGres this is type REFCURSOR, in Oracle, SYS_REFCURSOR). The command f2s_esql_set_resultset is used to handle these record sets.

The f2s_esql_set_resultset command does not retrieve the data from the result set. Instead, it makes that result set the “current” result set so that it can be retrieved using f2s_esql_move_next and f2s_esql_get_row commands.

Examples¶

Oracle Example

Let’s say we have converted the Customer table from the DataFlex example tables to Oracle21c and want to retrieve the customer number, name and state for customers in particular states. First, we create the procedure on the Oracle21c database,

CREATE OR REPLACE PROCEDURE CUST_STATE (sState01 IN VARCHAR, sState02 IN VARCHAR, sState03 IN VARCHAR, RC01 OUT SYS_REFCURSOR, RC02 OUT SYS_REFCURSOR, RC03 OUT SYS_REFCURSOR)
is
Begin
    Open RC01 for select customer_number,name,state from customer WHERE state=sState01 ORDER by name;
    Open RC02 for select customer_number,name,state from customer WHERE state=sState02 ORDER by name;
    Open RC03 for select customer_number,name,state from customer WHERE state=sState03 ORDER by name;
End
CUST_STATE;

Notice that the OUT values are record sets. When we call this procedure in DataFlex, we would use the code:

Integer   iRecords
Variant[] vResultSetCustomer
Variant   vRS

Login "192.168.1.12:1234/MyDatabase" "myloginname" "myloginpassword" "f2s_or"

f2s_esql_set_proc_name 'CUST_STATE'
f2s_esql_prepare_stmt
f2s_esql_set_proc_param 1 to 'CA'
f2s_esql_set_proc_param 2 to 'FL'
f2s_esql_set_proc_param 3 to 'WA'
f2s_esql_execute_proc

f2s_esql_get_proc_param 4 to vRS
f2s_esql_set_resultset vRS

Move 0 to iRecords
Repeat
   f2s_esql_move_next
   If (Found) Begin
      f2s_esql_get_row vResultSetCustomer
      Showln vResultSetCustomer[0] ' - ' vResultSetCustomer[1] ' - ' vResultSetCustomer[2]
      Increment iRecords
   End
Until (Not(Found))
Showln
Showln 'Records: ' iRecords
Showln '----------------------------------'

f2s_esql_get_proc_param 5 to vRS
f2s_esql_set_resultset vRS

Move 0 to iRecords
Repeat
   f2s_esql_move_next
   If (Found) Begin
      f2s_esql_get_row vResultSetCustomer
      Showln vResultSetCustomer[0] ' - ' vResultSetCustomer[1] ' - ' vResultSetCustomer[2]
      Increment iRecords
   End
Until (Not(Found))
Showln
Showln 'Records: ' iRecords
Showln '----------------------------------'

f2s_esql_get_proc_param 6 to vRS
f2s_esql_set_resultset vRS

Move 0 to iRecords
Repeat
   f2s_esql_move_next
   If (Found) Begin
      f2s_esql_get_row vResultSetCustomer
      Showln vResultSetCustomer[0] ' - ' vResultSetCustomer[1] ' - ' vResultSetCustomer[2]
      Increment iRecords
   End
Until (Not(Found))
Showln
Showln 'Records: ' iRecords
Showln '----------------------------------'

f2s_esql_close_stmt

PostGreSQL Example

Let’s say we have converted the Customer table from the DataFlex example tables to PostGreSQL 16.3 and want to retrieve the customer number, name and state for customers in particular states. First, we create the procedure on the PstrGreSQL 16.3 database,

CREATE FUNCTION CUST_STATE(sState01 IN VARCHAR, sState02 IN VARCHAR, sState03 IN VARCHAR, RC01 OUT REFCURSOR, RC02 OUT REFCURSOR, RC03 OUT REFCURSOR)
AS $$
BEGIN
   OPEN RC01 FOR select customer_number,name,state from customer WHERE state=sState01 ORDER BY name;
   OPEN RC02 FOR select customer_number,name,state from customer WHERE state=sState02 ORDER BY name;
   OPEN RC03 FOR select customer_number,name,state from customer WHERE state=sState03 ORDER BY name;
END;
$$ LANGUAGE plpgsql

Notice that the OUT values are record sets. When we call this procedure in DataFlex, we would use the code:

Integer   iRecords
Variant[] vResultSetCustomer
Variant   vRS

Login "192.168.1.12" "myloginname" "myloginpassword" "f2s_pg"

f2s_esql_set_proc_name 'CUST_STATE'
f2s_esql_prepare_stmt
f2s_esql_set_proc_param 1 to 'CA'
f2s_esql_set_proc_param 2 to 'FL'
f2s_esql_set_proc_param 3 to 'WA'
f2s_esql_execute_proc

f2s_esql_get_proc_param 4 to vRS
f2s_esql_set_resultset vRS

Move 0 to iRecords
Repeat
   f2s_esql_move_next
   If (Found) Begin
      f2s_esql_get_row vResultSetCustomer
      Showln vResultSetCustomer[0] ' - ' vResultSetCustomer[1] ' - ' vResultSetCustomer[2]
      Increment iRecords
   End
Until (Not(Found))
Showln
Showln 'Records: ' iRecords
Showln '----------------------------------'

f2s_esql_get_proc_param 5 to vRS
f2s_esql_set_resultset vRS

Move 0 to iRecords
Repeat
   f2s_esql_move_next
   If (Found) Begin
      f2s_esql_get_row vResultSetCustomer
      Showln vResultSetCustomer[0] ' - ' vResultSetCustomer[1] ' - ' vResultSetCustomer[2]
      Increment iRecords
   End
Until (Not(Found))
Showln
Showln 'Records: ' iRecords
Showln '----------------------------------'

f2s_esql_get_proc_param 6 to vRS
f2s_esql_set_resultset vRS

Move 0 to iRecords
Repeat
   f2s_esql_move_next
   If (Found) Begin
      f2s_esql_get_row vResultSetCustomer
      Showln vResultSetCustomer[0] ' - ' vResultSetCustomer[1] ' - ' vResultSetCustomer[2]
      Increment iRecords
   End
Until (Not(Found))
Showln
Showln 'Records: ' iRecords
Showln '----------------------------------'

f2s_esql_close_stmt

f2s_esql_set_resultset¶

Syntax¶

Description¶

Examples¶

Related Commands¶