GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW_FROM_QUERY¶
Description
The stored procedure GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW_FROM_QUERY
returns the VQL statements necessary to create a JDBC base view for a given SQL sentence that will be executed against the underlying database of a JDBC data source. Note that it does not actually creates the view, only returns the VQL statements to do so.
In combination with the procedure GET_JDBC_DATASOURCE_TABLES, you can automate the process of having a base view for all the tables/views of a source database.
Note that this procedure does not create the base view, it only returns the VQL statements to do so.
To obtain the VQL statements to create base views over other types of data sources (DF, XML, JSON…) use the DenodoConnect component Denodo VQL Generation. It is available on the Support Site and its documentation, in the Denodo Community.
Syntax
GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW_FROM_QUERY (
data_source_name : text
, sql_sentence : text
, interpolation_vars : array
, base_view_name : text
, folder : text
, i18n : text
, database_name : text
)
data_source_name
: name of the data source.sql_sentence
: SQL query to execute. See section Creating Base Views from SQL Queries for more information about the query syntax.interpolation_vars
: array of register with the values of the query interpolation variables (see section Paths and Other Values with Interpolation Variables). If the query does not use any variables, set it tonull
or leave it empty. Each register must have these fields:Name of the interpolation variable.
Value of the interpolation variable specified.
base_view_name
: name of the base view to be created. Ifnull
, the name will be auto-generated.folder
: folder in which to place the created base view. The result will include the VQL statements to create this folder(s). Ifnull
, the VQL will not specify a folder.i18n
: i18n of the base view to be created. Ifnull
, the procedure will assign the i18n of the Virtual DataPort database to which the data source belongs. We recommend setting this tonull
.database_name
: name of the database to which the JDBC data source belongs. Ifnull
, the procedure will use the current database.
The procedure returns one row for each VQL statement necessary to create the base view:
creation_vql
: statements necessary to create the desired base view.
The procedure returns an error if the schema, catalog or table do not exist in the database.
Privileges Required
The user must have execute privileges over the data source.
Examples
Example 1
CALL GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW_FROM_QUERY (
'ds_mydatasource'
, 'SELECT * FROM vdp.emp'
, null
, 'base_view_emp')
It returns the VQL to create the view ‘base_view_emp’ with the results obtained from executing ‘SELECT * FROM vdp.emp’ against the underlying database of ‘ds_mydatasource’.
Example 2
CALL GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW_FROM_QUERY (
'ds_mydatasource'
, 'SELECT * FROM emp WHERE empno > @{in_empno} AND depnto > @{in_deptno}'
, {
ROW('in_empno', 1000),
ROW('in_deptno', 10)
}
, 'base_view_emp_vars')
);
It does the same as the previous example, but using interpolation variables in the SQL query.