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.
Table 24.28. 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.29. 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.
![[Tip]](images/tip.png)
|
See Also: |
|---|---|
Examples
Example 24.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;
}