www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Virtuoso Functions Guide

Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
RDF data
Remote SQL Data Source
att_local_name
quote_dotted
rclose
rexecute
rmoreresults
rnext
rstmtexec
sql_columns
sql_config_data_sour...
sql_data_sources
sql_driver_connect
sql_get_installed_dr...
sql_get_private_prof...
sql_gettypeinfo
sql_primary_keys
sql_procedures
sql_remove_dsn_from_...
sql_special_columns
sql_statistics
sql_tables
sql_transact
sql_write_file_dsn
sql_write_private_pr...
vd_remote_data_sourc...
vd_remote_proc_wrapp...
vd_remote_table
vd_statistics
vdd_disconnect_data_...
vdd_measure_rpc_time
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY

Functions Index

rexecute

execute a SQL statement on a remote DSN
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:

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:

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: 23.1. 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: 23.2. 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).
Return Values

Examples
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 Taqueraa   Antonio Moreno
Around the Horn           Thomas Hardy

4 Rows. -- 4 msec.

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 Taqueraa   Antonio Moreno
Around the Horn           Thomas Hardy
B's Beverages             Victoria Ashworth
Berglunds snabbkpp        Christina Berglund
Blauer See Delikatessen   Hanna Moos
Blondel prre et fils      Frddrrique Citeaux
Bon app'                  Laurence Lebihan
Bottom-Dollar Markets     Elizabeth Lincoln

10 Rows. -- 19 msec.


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

rstmtexec(), rnext(), rmoreresults(), rclose()