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.

Table 24.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

Table 24.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

Example 24.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;
}