Name

exec_metadata — Compiles a SQL statement and returns the metadata

Synopsis

exec_metadata ( in str varchar ,
out state varchar ,
out message varchar ,
out metadata vector );

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 if the statement is a select.

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.

metadata

An output parameter of type vector returning the metadata of the statement and its result.

Table24.29.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.30.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

Errors

The function will generate a SQL 22023 error value if a supplied parameter is not of the type expected.

Examples

Example24.105.Simple Use

create procedure get_meta (in str varchar)
{
  declare state, message, mdta any;
  state := '00000';
  exec_metadata (str, state, message, mdta);
  if (state <> '00000')
    signal (state, message);
  return mdta;
}