Name

rexecute — execute a SQL statement on a remote DSN

Synopsis

rexecute ( in dsn varchar ,
  in sql_stmt varchar ,
  out sql_state varchar ,
  out error_message varchar ,
  in in_params vector ,
  out num_cols integer ,
  out stmt_meta vector ,
  out result_set vector ,
  out cursor_handle long );
 

Description

This function can be used to execute SQL on a remote data source directly.

The result_set parameter is useful for obtaining a result-set quickly and easily. However, if the result-set is going to be large, this comes at a cost in terms of time and resources, particularly memory, since Virtuoso will have to obtain all results from the statement and build the result-set arrays in memory before returning back to the caller.

A more efficient way is to obtain a cursor handle and iterate through the result set one row at a time:

Use rexecute() to get the cursor handle.
Iterate over the results with rnext()
If rmoreresults() does not return an error or SQL_NO_DATA_FOUND continue the loop with rnext() , otherwise
close the cursor with rclose()

To keep Virtuoso from obtaining the whole result set from the remote, pass NULL as the result_set parameter when calling rexecute.

Rexecute() supports IN parameters and can also support INOUT and OUT parameters. If INOUT and OUT parameters are to be used then cursors should not be used with this function (as the values of output parameters in ODBC are not guaranteed to be set before SQLMoreResults() returns SQL_NO_DATA_FOUND). Rexecute supports INOUT and OUT parameters by extending the in_params vector and requiring it to be a PL variable so values can be set back to it.

Unless explicitly granted, only the DBA group is permitted to use the rexecute() to maintain security. Caution is required here since any user granted use of rexecute() has full control of the remote data source set-up by the DBA, however limited to the overall abilities of the remote user on the remote data source. Users can be granted and denied access to this function using the following commands:

GRANT REXECUTE ON '<attached_dsn_name>' TO <user_name>
REVOKE REXECUTE ON '<attached_dsn_name>' FROM <user_name>
[Note] Note:

rstmtexec() provides a short-hand to this function.

Parameters

dsn

The data source where the SQL statement should be executed. You must make sure that you have already defined the data source using the vd_remote_data_source function or by attaching tables from it.

sql_stmt

the SQL statement to execute.

sql_state

A varchar containing the SQL State returned from the remote data source.

error_message

A varchar containing any error message returned from the remote.

in_params

A vector of parameters to the statement if the executed statement has parameters. IN input parameters are specified as literals whereas OUT and INOUT parameters are specified as vectors of 3 elements for OUT and 4 elements for INOUT as follows:

[0] - the type of the parameter ('OUT' or 'INOUT')
[1] - the datatype that the parameter is bound to on the remote
[2] - the buffer length for the output parameter
[3] - (INOUT only) the input value of the parameter

Non-vector parameters in the in_params parameter of rexecute are considered IN parameters, so the rexecute remains backwards compatible.

num_cols

Number of columns in the result set if the statement returned one.

stmt_meta

A vector containing result metadata, etc.

Table 24.77. The stmt_meta array

Element Name Description
0 COLS An array containing description of each column in the result set (see table below for contents)
1 RES_AVAIL An integer 1 indicates that results are available.
2 N/A Not used
3 N/A Not used

Table 24.78. Columns array of stmt_meta

Element Name Description
0 name Column name
1 type Column type as an internal type code corresponding, but not equal to ODBC SQL type codes.
2 scale column scale
3 precision column precision
4 nullable indicates nullable column
5 updatable indicates updatable column
6 searchable indicates searchable column

result_set

A vector of vectors containing each row in the result set.

cursor_handle

The cursor handle ( long ).

Examples

Example 24.359. Remote execute example 1

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.

Example 24.360. Remote execute example 2

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.


Example 24.361. Remote execute example 3

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 ();
};

Example 24.362. Remote procedures with OUT params.

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

Example 24.363. Remote procedures with INOUT params.

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