Name
rstmtexec — execute a SQL statement on a remote DSN, provides a result set where applicable.
Synopsis
rstmtexec
(
|
in dsn varchar , |
| in stmt varchar , | |
| in max_rows integer , | |
in
params_array
vector
); |
Description
This function can be used to execute SQL on a remote data source directly. It returns a result set where one is expected.
This function is wrapper for the rexecute()
provided for convenience as a shortcut.
Unless explicitly granted, only the DBA group is permitted to use the
rstmtexec()
to maintain security. Caution is required
here since any user granted use of rstmtexec()
has
full control of the remote data source set-up by the DBA, albeit 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>
This command directly affects the grant to the rexecute()
function, which is the underlying mechanism for providing 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.
stmt
the SQL statement to execute on the remote data source
dsn
.
max_rows
This controls the number of rows to be returned as follows:
| negative - return no rows |
| zero (0) - all rows (default) |
| positive - specified number of rows |
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 rstmtexec are considered IN parameters, so the rstmtexec remains backwards compatible.
Return Values
This function returns a result set.