exec — dynamic execution of SQL returning state and result set


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


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.



A varchar containing arbitrary SQL using ?'s for parameter markers.


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.


An output parameter of type varchar set to SQL error message associated with the error. Not set if an error is not present.


A vector containing the parameters for the SQL being executed. Element 0 corresponding to first ?, etc.


The integer maximum number of rows to retrieve in case of a statement returning a result set.


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


An output array with one element per result row containing an array with the leftmost column as element 0 and so forth.


The cursor handle for use with related functions.


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;

    return 0;