Name
exec — dynamic execution of SQL returning state and result set
Synopsis
exec
( |
in str varchar , |
| out state varchar , | |
| out message varchar , | |
| in params any , | |
| in maxrows integer , | |
| out metadata vector , | |
| out rows vector , | |
out cursor_handle long
); |
Description
This function provides dynamic SQL capabilities in Virtuoso PL. The first argument is an arbitrary SQL statement, which may contain parameter placeholders. The function returns as output parameters a SQL state, error message, column metadata and result set rows if the statement is a select.
A stored procedure can be invoked by exec but a procedure's result set will not be received in the rows output parameter but rather sent to the client.
Parameters
str
A varchar containing arbitrary SQL using ?'s for parameter markers.
state
An output parameter of type varchar set to the 5 character SQL state if the exec resulted an error. Not set if an error is not present.
message
An output parameter of type varchar set to SQL error message associated with the error. Not set if an error is not present.
params
A vector containing the parameters for the SQL being executed. Element 0 corresponding to first ?, etc.
maxrows
The integer maximum number of rows to retrieve in case of a statement returning a result set.
metadata
An output parameter of type vector returning the metadata of the statement and its result.
Table24.27.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 | STMT_SELECT | An integer 1 indicates that the statement is a select, otherwise it is a DML statement. |
| 2.. | N/A | Trailing elements may appear, but they should not be used |
Table24.28.Columns array of metadata
| 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 |
rows
An output array with one element per result row containing an array with the leftmost column as element 0 and so forth.
cursor_handle
The cursor handle for use with related functions.
Examples
Example24.102.Procedure Example
This stored procedure returns 1 if a given table is empty. An error such as a timeout or deadlock would be reported back to the caller as an exception. Exec always returns, no matter the type of exception. Thus it is also useful as a universal error catcher.
create procedure tb_is_empty (in tb varchar)
{
declare state, msg, descs, rows any;
state := '00000';
exec (sprintf ('select 1 from %s', tb), state,
msg, vector (), 1, descs, rows);
if (state <> '00000')
signal (state, msg);
if (length (rows) = 0)
return 1;
else
return 0;
}