{execute procedurename(arguments ...)}
The execute command supports SQL stored procedures and ODBC catalog
functions. procedurename is the name of a stored procedure for the
currently connected SQL data source. It is followed by a list of arguments.
The arguments may be Jive variable references and, for some data sources,
SQL expressions. The return value from a stored procedure may be retrieved
into a Jive variable, for example:
{:return = execute procedure(:param1, :param2)}
The Jive variable, return, receives the return value from the stored
procedute, procedure.
The execute command is an external command and must be immediately enclosed in braces. Since some stored procedures (and all catalog functions) return a result set, the execute command must be followed by a {end}. The {end} is required even when the stored procedure has no result set.
When the execute does have a result set, the execute is followed by a block of Jive code, like select. For example, if the stored procedure - scan, returns a result set containing the result column - name:
{execute scan(:key)}
{name}<br>
{end}
| tables(qual,owner,table,type) | select a single table or view name or a set of table and/or view names (SQLTables). |
| columns(qual,owner,table,column) | select descriptions for a column or columns for a table or set of tables (SQLColumns). |
| procedures(qual,owner,procedure) | select a single stored procedure name or a set of procedure names (SQLProcedures). |
| procedurecolumns(qual,owner,procedure,column) | select descriptions for a column or columns from a procedure or set of procedures (SQLProcedureColumns). |
| primarykeys(qual,owner,table) | select the primary key columns for a table or set of tables (SQLPrimaryKeys). |
| foreignkeys(pkqual,pkowner,pktable,fkqual,fkowner,fktable) | select foreign key columns for a table linked to primary key columns for a second table (SQLForeignKeys). |
| statistics(qual,owner,table) | select indexes and index keys for a table or set of tables (SQLStatistics). |
Agruments to catalog functions can be quoted strings, references to Jive variables or empty (null). Variable values are converted to string. Certain arguments may contain wild card characters (%, _) as defined by ODBC documentation. For the statistics catalog function, the numeric parameters are set to SQL_INDEX_ALL and SQL_QUICK.
The catalog functions return their information as result sets. The content and format of the result sets are defined by ODBC documentation.
Example (returning all tables and views):
{execute tables(,,'%')}
{:return = execute lookup(:input{i}, :output{o})}
When output parameters are string variables, the maximum length for the
output string is determined by the current size of the variable. This
can be overridden by following o or io with an integer
expression defining the maximum length for output. For example:
{execute lookup(:arg{io 255})}