Remote execute, retrieving the whole result from remote at once.

create procedure
test_rexecute_1 (in remote_dsn varchar)
{
  declare stmt varchar;
  declare mdta_out any;
  declare res_vec_out, param_vec any;
  declare sql_state, err_msg varchar;
  declare inx integer;
  declare num_cols_out integer;

  declare Company, Contact varchar;
  result_names (Company, Contact);

  stmt := 'SELECT CompanyName, ContactName FROM \
             Demo.Demo.Customers WHERE CompanyName BETWEEN ? and ?';
  sql_state := '00000';
  param_vec := vector ('A', 'B');

  rexecute (remote_dsn, stmt, sql_state, err_msg, param_vec,
            num_cols_out, mdta_out, res_vec_out, NULL);

  if (sql_state <> '00000')  -- See if we got an error
    {
      signal ('ZILCH',
              concat ('Remote execution returned ',
                      sql_state, ' ', err_msg));
    }

  -- now iterate through result set stored in res_vec_out

  inx := 0;
  while (inx < length (res_vec_out))
    {
      result (aref (aref (res_vec_out, inx), 0),
              aref (aref (res_vec_out, inx), 1));
      inx := inx + 1;
    }

  end_result ();
}

SQL> test_rexecute_1 ('Local Virtuoso Demo');
Company                   Contact
VARCHAR                   VARCHAR
_______________________________________________________________________________

Alfreds Futterkiste       Maria Anders
Ana Trujillo Emparedados y helados  Ana Trujillo
Antonio Moreno Taquería   Antonio Moreno
Around the Horn           Thomas Hardy

4 Rows. -- 4 msec.

Remote execute, retrieving the result using returned cursor handle.

create procedure
test_rexecute_2 (in remote_dsn varchar, in max_results integer)
{
  declare stmt varchar;
  declare mdta_out any;
  declare res_vec_out, param_vec any;
  declare sql_state, err_msg varchar;
  declare inx integer;
  declare num_cols_out integer;
  declare cursor_out any;

  declare Company, Contact varchar;
  result_names (Company, Contact);

  stmt := 'SELECT CompanyName, ContactName FROM \
             Demo.Demo.Customers WHERE CompanyName BETWEEN ? and ?';
  sql_state := '00000';
  param_vec := vector ('A', 'Z');

  rexecute (remote_dsn, stmt, sql_state, err_msg, param_vec,
            num_cols_out, mdta_out, NULL, cursor_out);

  if (sql_state <> '00000')  -- See if we got an error
    {
      signal ('ZILCH',
              concat ('Remote execution returned ',
                      sql_state, ' ', err_msg));
    }

  -- now iterate through result set with cursor

  while (0 = rnext (cursor_out, res_vec_out) and inx < max_results)
    {
      result (aref (res_vec_out, 0), aref (res_vec_out, 1));
      inx := inx + 1;
    }

  rclose (cursor_out);
  end_result ();
}

SQL> test_rexecute_2 ('Local Virtuoso Demo', 10);
Company                   Contact
VARCHAR                   VARCHAR
_______________________________________________________________________________

Alfreds Futterkiste       Maria Anders
Ana Trujillo Emparedados y helados  Ana Trujillo
Antonio Moreno Taquería   Antonio Moreno
Around the Horn           Thomas Hardy
B's Beverages             Victoria Ashworth
Berglunds snabbköp        Christina Berglund
Blauer See Delikatessen   Hanna Moos
Blondel père et fils      Frédérique Citeaux
Bon app'                  Laurence Lebihan
Bottom-Dollar Markets     Elizabeth Lincoln

10 Rows. -- 19 msec.

Remote DBMS native SQL execution

create procedure test_rexecute_3 (in remote_dsn varchar, in max_results integer)
{
    declare res_vec_out, param_vec, mdta_out, cursor_out any;
    declare url, stmt, sql_state, err_msg varchar;
    declare num_cols_out, inx, _id integer;

    result_names(_id,url);

    stmt := 'select id from mysql_pk';

    sql_state := '00000';
    param_vec := vector ('');

    rexecute (remote_dsn, stmt, sql_state, err_msg, param_vec, num_cols_out, mdta_out, NULL, cursor_out);

    _id := '';

    if (sql_state <> '00000')  -- See if we got an error
    {
      signal ('ZILCH',concat ('Remote execution returned ', sql_state, ' ', err_msg));
    }

    -- now iterate through result set stored in cursor_out
    inx := 0;
    while (0 = rnext (cursor_out, res_vec_out) and inx < max_results)
    {
      _id := aref (res_vec_out, 0);
      {
        whenever not found goto znext;
        select url into url from rdfstore_mysql where id = _id;
      }

       result(_id, url);

       if (0 = mod (inx, 5000))
       {
         log_message(sprintf ('%d rows copied, stay patient (id: %d)', inx, _id));
       }

        znext:;
        inx := inx + 1;
    }

  rclose (cursor_out);
  end_result ();
};

The following example demonstrates the use of OUT params using a sample remote procedure stored in another Virtuoso server, although this can be any database of course, and a local procedure that uses the rexecute() to execute it.

--On remote virtuoso:
create procedure FUNCRET (in data varchar) returns varchar { return concat (data, 'Out'); };

--On local virtuoso:
create procedure call_FUNCRET (in data varchar, in DSN varchar) returns integer
{
   declare params any;
   params := vector (
              vector ('out', 'varchar', 50), -- an OUT VARCHAR parameter for the result
                                             -- 50 here means receive no more than 50 bytes of
                                             -- the output value
              data                           -- the IN parameters are passed as usual
             );
   rexecute (DSN, '{?=call FUNCRET(?)}',
            null, null,
            params); -- params should be a PL variable, as it will hold the substituted
                     -- output values for the OUT and INOUT parameters upon return.
   -- retrieve the output value of the first parameter (i.e.
   -- the function return value) and return it:
   return params[0];
};

This should return as follows:

select call_FUNCRET ('abc', 'Local Virtuoso Demo');
---------------------------
abcOut

As well as OUT parameters INOUT parameters can be used. Consider the following variation of the above example:

--On remote Virtuoso:
create procedure INOUT_PROC (inout DATA varchar)
{
   DATA := concat (DATA, 'InOut');
};

--On local Virtuoso:
create procedure call_INOUT_PROC (in DATA varchar, in DSN varchar) returns varchar
{
  declare params any;
  params := (
          vector ('inout', 'VARCHAR', 50, DATA) -- note we pass 4 element array for INOUT,
                                                -- as we need the input value for the parameter
             );
  rexecute (DSN, '{call INOUT_PROC (?)}',
                null, null,
                params);
  return params[0];
};

This, when called returns:

select call_INOUT_PROC ('abc', 'Local Virtuoso Demo');
----------------------
abcInOut