# 22. Virtuoso Functions Guide - exec

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
Replication
SOAP
SQL
__any_grants
close
collation_define
complete_table_name
delay
end_result
exec
exec_next
exec_result
exec_result_names
identity_value
name_part
registry_get
registry_get_all
registry_name_is_pro...
registry_remove
registry_set
result
result_names
row_count
sequence_get_all
sequence_next
sequence_remove
sequence_set
set_identity_column
set_row_count
set_user_id
signal
sinv_create_inverse
sinv_create_key_mapp...
sinv_drop_inverse
sys_stat_analyze
sys_stat_histogram
table_drop_policy
table_set_policy
username
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY
Functions Index

## 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);
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: 22.1. 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: 22.2. 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.
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.

Examples
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, 1, 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;
    }

See Also

rexecute()

exec_next()

exec_close()