<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="Function: rexecute - Virtuoso Functions Guide" />
<meta name="dc.subject" content="Function: rexecute - Virtuoso Functions Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="functions.html" title="Chapter Contents" />
<link rel="prev" href="appendixa.html" title="Appendix" />
<link rel="next" href="functions.html#.html" title="" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>Function: rexecute - Virtuoso Functions Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="fn_rexecute" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>22. Virtuoso Functions Guide - rexecute</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="functions.html">Chapter Contents</a> | <a class="link" href="fn_rclose.html" title="rclose">Prev</a> | <a class="link" href="fn_rstmtexec.html" title="rstmtexec">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div>
<a class="selected" href="functions.html">Virtuoso Functions Guide</a>
</div>
<br />
<div>
<a href="functions.html#admin">Administration</a>
</div>
<div>
<a href="functions.html#aggr">Aggregate Functions</a>
</div>
<div>
<a href="functions.html#array">Array Manipulation</a>
</div>
<div>
<a href="functions.html#bpel">BPEL APIs</a>
</div>
<div>
<a href="functions.html#backup">Backup</a>
</div>
<div>
<a href="functions.html#compression">Compression</a>
</div>
<div>
<a href="functions.html#cursors">Cursor</a>
</div>
<div>
<a href="functions.html#time">Date & Time Manipulation</a>
</div>
<div>
<a href="functions.html#debug">Debug</a>
</div>
<div>
<a href="functions.html#dict">Dictionary Manipulation</a>
</div>
<div>
<a href="functions.html#encoding">Encoding & Decoding</a>
</div>
<div>
<a href="functions.html#file">File Manipulation</a>
</div>
<div>
<a href="functions.html#ft">Free Text</a>
</div>
<div>
<a href="functions.html#hash">Hashing / Cryptographic</a>
</div>
<div>
<a href="functions.html#ldap">LDAP</a>
</div>
<div>
<a href="functions.html#localization">Locale</a>
</div>
<div>
<a href="functions.html#mail">Mail</a>
</div>
<div>
<a href="functions.html#misc">Miscellaneous</a>
</div>
<div>
<a href="functions.html#number">Number</a>
</div>
<div>
<a href="functions.html#phrz">Phrases</a>
</div>
<div>
<a href="functions.html#rdf">RDF data</a>
</div>
<div>
<a class="selected" href="functions.html#rmt">Remote SQL Data Source</a>
</div>
<div class="selected">
<div>
<a href="fn_att_local_name.html">att_local_name</a>
</div>
<div>
<a href="fn_quote_dotted.html">quote_dotted</a>
</div>
<div>
<a href="fn_rclose.html">rclose</a>
</div>
<div>
<a class="selected" href="fn_rexecute.html">rexecute</a>
</div>
<div>
<a href="fn_rmoreresults.html">rmoreresults</a>
</div>
<div>
<a href="fn_rnext.html">rnext</a>
</div>
<div>
<a href="fn_rstmtexec.html">rstmtexec</a>
</div>
<div>
<a href="fn_sql_columns.html">sql_columns</a>
</div>
<div>
<a href="fn_sql_config_data_sources.html">sql_config_data_sour...</a>
</div>
<div>
<a href="fn_sql_data_sources.html">sql_data_sources</a>
</div>
<div>
<a href="fn_sql_driver_connect.html">sql_driver_connect</a>
</div>
<div>
<a href="fn_sql_get_installed_drivers.html">sql_get_installed_dr...</a>
</div>
<div>
<a href="fn_sql_get_private_profile_string.html">sql_get_private_prof...</a>
</div>
<div>
<a href="fn_sql_gettypeinfo.html">sql_gettypeinfo</a>
</div>
<div>
<a href="fn_sql_primary_keys.html">sql_primary_keys</a>
</div>
<div>
<a href="fn_sql_procedures.html">sql_procedures</a>
</div>
<div>
<a href="fn_sql_remove_dsn_from_ini.html">sql_remove_dsn_from_...</a>
</div>
<div>
<a href="fn_sql_special_columns.html">sql_special_columns</a>
</div>
<div>
<a href="fn_sql_statistics.html">sql_statistics</a>
</div>
<div>
<a href="fn_sql_tables.html">sql_tables</a>
</div>
<div>
<a href="fn_sql_transact.html">sql_transact</a>
</div>
<div>
<a href="fn_sql_write_file_dsn.html">sql_write_file_dsn</a>
</div>
<div>
<a href="fn_sql_write_private_profile_string.html">sql_write_private_pr...</a>
</div>
<div>
<a href="fn_vd_remote_data_source.html">vd_remote_data_sourc...</a>
</div>
<div>
<a href="fn_vd_remote_proc_wrapper.html">vd_remote_proc_wrapp...</a>
</div>
<div>
<a href="fn_vd_remote_table.html">vd_remote_table</a>
</div>
<div>
<a href="fn_vd_statistics.html">vd_statistics</a>
</div>
<div>
<a href="fn_vdd_disconnect_data_source.html">vdd_disconnect_data_...</a>
</div>
<div>
<a href="fn_vdd_measure_rpc_time.html">vdd_measure_rpc_time</a>
</div>
</div>
<div>
<a href="functions.html#repl">Replication</a>
</div>
<div>
<a href="functions.html#soap">SOAP</a>
</div>
<div>
<a href="functions.html#sql">SQL</a>
</div>
<div>
<a href="functions.html#string">String</a>
</div>
<div>
<a href="functions.html#txn">Transaction</a>
</div>
<div>
<a href="functions.html#type">Type Mapping</a>
</div>
<div>
<a href="functions.html#uddi">UDDI</a>
</div>
<div>
<a href="functions.html#udt">User Defined Types & The CLR</a>
</div>
<div>
<a href="functions.html#vad">VAD</a>
</div>
<div>
<a href="functions.html#jvmpl">Virtuoso Java PL API</a>
</div>
<div>
<a href="functions.html#bif">Virtuoso Server Extension Interface (VSEI)</a>
</div>
<div>
<a href="functions.html#ws">Web & Internet</a>
</div>
<div>
<a href="functions.html#xml">XML</a>
</div>
<div>
<a href="functions.html#XPATH">XPATH & XQUERY</a>
</div>
<br />
<div>
<a href="functionidx.html">Functions Index</a>
</div>
<br />
</div>
<div id="text">
<h2>rexecute</h2>
<div class="refpurpose">execute a SQL statement on a remote DSN</div>
<div class="funcsynopsis">
<span class="funcdef">
<span class="function">rexecute</span>
</span>
(<span class="paramdef">in <span class="parameter">dsn</span> varchar</span>,
<span class="paramdef">in <span class="parameter">sql_stmt</span> varchar</span>,
<span class="paramdef">out <span class="parameter">sql_state</span> varchar</span>,
<span class="paramdef">out <span class="parameter">error_message</span> varchar</span>,
<span class="paramdef">in <span class="parameter">in_params</span> vector</span>,
<span class="paramdef">out <span class="parameter">num_cols</span> integer</span>,
<span class="paramdef">out <span class="parameter">stmt_meta</span> vector</span>,
<span class="paramdef">out <span class="parameter">result_set</span> vector</span>,
<span class="paramdef">out <span class="parameter">cursor_handle</span> long</span>);
</div>
<div class="refsect1">
<div class="refsect1title">Description</div>
<p>This function can be used to execute SQL on a remote data source
directly.</p>
<p>The <span class="computeroutput">result_set</span> 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.</p>
<p>A more efficient way is to obtain a cursor handle and iterate
through the result set one row at a time:</p>
<ul>
<li>Use rexecute() to get the cursor handle.</li>
<li>Iterate over the results with rnext()</li>
<li>If rmoreresults() does not return an error or <span class="computeroutput">SQL_NO_DATA_FOUND</span>
continue the loop with rnext(), otherwise</li>
<li>close the cursor with rclose()</li>
</ul>
<p>To keep Virtuoso from obtaining the whole result set from the
remote, pass NULL as the <span class="computeroutput">result_set</span> parameter
when calling rexecute.</p>
<p>
<span class="computeroutput">Rexecute()</span> 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). <span class="computeroutput">Rexecute</span> supports INOUT and
OUT parameters by extending the <span class="computeroutput">in_params</span> vector
and requiring it to be a PL variable so values can be set back to it.</p>
<p>Unless explicitly granted, only the DBA group is permitted to use the
<span class="computeroutput">rexecute()</span> to maintain security. Caution is required
here since any user granted use of <span class="computeroutput">rexecute()</span> 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:</p>
<div>
<pre class="programlisting">
GRANT REXECUTE ON '<attached_dsn_name>' TO <user_name>
REVOKE REXECUTE ON '<attached_dsn_name>' FROM <user_name>
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>
<a href="fn_rstmtexec.html">rstmtexec()</a> provides a short-hand to this function.</p>
</div>
</div>
<div class="refsect1">
<div class="refsect1title">Parameters</div>
<div class="refsect2">
<span class="refsect2title">dsn – </span>
The data source where the SQL statement should be executed.
You must make sure that you have already defined the data source
using the <span class="computeroutput">
<a href="fn_vd_remote_data_source.html">
vd_remote_data_source</a>
</span> function or by attaching tables from it.
</div>
<div class="refsect2">
<span class="refsect2title">sql_stmt – </span>
the SQL statement to execute.
</div>
<div class="refsect2">
<span class="refsect2title">sql_state – </span>
A <span class="computeroutput">varchar</span> containing the SQL State returned from the
remote data source.
</div>
<div class="refsect2">
<span class="refsect2title">error_message – </span>
A <span class="computeroutput">varchar</span> containing any error message returned from
the remote.
</div>
<div class="refsect2">
<span class="refsect2title">in_params – </span>
A <span class="computeroutput">vector</span> 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:
<ul>
<li>[0] - the type of the parameter ('OUT' or 'INOUT')</li>
<li>[1] - the datatype that the parameter is bound to on the remote</li>
<li>[2] - the buffer length for the output parameter</li>
<li>[3] - (INOUT only) the input value of the parameter</li>
</ul>
Non-vector parameters in the in_params parameter of rexecute are
considered IN parameters, so the rexecute remains backwards compatible.
</div>
<div class="refsect2">
<span class="refsect2title">num_cols – </span>
Number of columns in the result set if the statement returned
one.
</div>
<div class="refsect2">
<span class="refsect2title">stmt_meta – </span>
A <span class="computeroutput">vector</span> containing result metadata, etc.
<table class="data">
<caption>Table: 22.1. The stmt_meta array</caption>
<tr>
<th class="data">Element</th>
<th class="data">Name</th>
<th class="data">Description</th>
</tr>
<tr>
<td class="data">0</td>
<td class="data">COLS</td>
<td class="data">An array containing description of each column in the
result set (see table below for contents)</td>
</tr>
<tr>
<td class="data">1</td>
<td class="data">RES_AVAIL</td>
<td class="data">An <span class="computeroutput">integer</span> 1 indicates that results are
available.</td>
</tr>
<tr>
<td class="data">2</td>
<td class="data">N/A</td>
<td class="data">Not used</td>
</tr>
<tr>
<td class="data">3</td>
<td class="data">N/A</td>
<td class="data">Not used</td>
</tr>
</table>
<br />
<table class="data">
<caption>Table: 22.2. Columns array of stmt_meta</caption>
<tr>
<th class="data">Element</th>
<th class="data">Name</th>
<th class="data">Description</th>
</tr>
<tr>
<td class="data">0</td>
<td class="data">name</td>
<td class="data">Column name</td>
</tr>
<tr>
<td class="data">1</td>
<td class="data">type</td>
<td class="data">Column type as an internal type code corresponding,
but not equal to ODBC SQL type codes.</td>
</tr>
<tr>
<td class="data">2</td>
<td class="data">scale</td>
<td class="data">column scale</td>
</tr>
<tr>
<td class="data">3</td>
<td class="data">precision</td>
<td class="data">column precision</td>
</tr>
<tr>
<td class="data">4</td>
<td class="data">nullable</td>
<td class="data">indicates nullable column</td>
</tr>
<tr>
<td class="data">5</td>
<td class="data">updatable</td>
<td class="data">indicates updatable column</td>
</tr>
<tr>
<td class="data">6</td>
<td class="data">searchable</td>
<td class="data">indicates searchable column</td>
</tr>
</table>
<br />
</div>
<div class="refsect2">
<span class="refsect2title">result_set – </span>
A <span class="computeroutput">vector</span> of vectors containing each row in the
result set.
</div>
<div class="refsect2">
<span class="refsect2title">cursor_handle – </span>
The cursor handle (<span class="computeroutput">long</span>).
</div>
</div>
<div class="refsect1">
<div class="refsect1title">Return Values</div>
<p />
</div>
<div class="refsect1">
<div class="refsect1title">Examples</div>
<a name="ex_rexecute_1" />
<div class="example">
<div class="exampletitle">Remote execute example 1</div>
<p>Remote execute, retrieving the whole result from remote at once.</p>
<div>
<pre class="screen">
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.
</pre>
</div>
</div>
<a name="ex_rexecute_1" />
<div class="example">
<div class="exampletitle">Remote execute example 2</div>
<p>Remote execute, retrieving the result using returned cursor handle.</p>
<div>
<pre class="screen">
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.
</pre>
</div>
</div>
<a name="ex_rexecute_4" />
<div class="example">
<div class="exampletitle">Remote execute example 3</div>
<p>Remote DBMS native SQL execution</p>
<div>
<pre class="screen">
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 ();
};
</pre>
</div>
</div>
<a name="ex_rexecute_2" />
<div class="example">
<div class="exampletitle">Remote procedures with OUT params.</div>
<p>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
<span class="computeroutput">rexecute()</span> to execute it.</p>
<div>
<pre class="screen">
--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];
};
</pre>
</div>
<p>
This should return as follows:
</p>
<div>
<pre class="screen">
select call_FUNCRET ('abc', 'Local Virtuoso Demo');
---------------------------
abcOut
</pre>
</div>
</div>
<a name="ex_rexecute_3" />
<div class="example">
<div class="exampletitle">Remote procedures with INOUT params.</div>
<p>As well as OUT parameters INOUT parameters can be used. Consider
the following variation of the above example:</p>
<div>
<pre class="screen">
--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];
};
</pre>
</div>
<p>
This, when called returns:
</p>
<div>
<pre class="screen">
select call_INOUT_PROC ('abc', 'Local Virtuoso Demo');
----------------------
abcInOut
</pre>
</div>
</div>
</div>
<div class="refsect1">
<div class="refsect1title">See Also</div>
<p>
<a href="fn_rstmtexec.html">rstmtexec()</a>,
<a href="fn_rnext.html">rnext()</a>,
<a href="fn_rmoreresults.html">rmoreresults()</a>,
<a href="fn_rclose.html">rclose()</a>
</p>
</div>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
510
