Native Query Usage

To enable native queries for a specific data source, set this data source's SupportsNativeQueries translator parameter to TRUE. This can be done via the CData Virtuality Studio or a special SQL statement.

Via the CData Virtuality Studio

Via SQL Statement

When adding a new data source, include the property in the SYSADMIN.createDataSource() procedure:

CODE

CALL SYSADMIN.createDataSource(name => ..., 
translatorProperties => 'SupportsNativeQueries=TRUE') ;;

As a result, the data source will have another procedure called native to work with. The easiest way to execute a native query is to call the procedure and send the query itself:

CODE

CALL "<dataSource>"."native"( "request" => '<myQuery>');;

The query gets executed on the source, and the result set is sent to the CData Virtuality Server. Please note that each result set row is represented as one column entry with a comma-separated list of values.

To get all columns in a proper representation, use ARRAYTABLE to split the values (see example 3 below).

Using Native Queries

Simple Query

CODE

CALL "aw_ms.native"( "request" => 'SELECT 1');;

This call will execute a simple SELECT natively on the data source.

This type of native query is ideal for sending simple commands to the source: for example, creating a view or table, or performing any other operation which does not require seeing the result set.

Selecting the First Tuple of the Result Set

CODE

SELECT tuple[1] 
	FROM (CALL "aw_ms.native"( "request" => 'SELECT 1')) AS a;;

The result set from the source comes as a list of tuples. The example above will select the first tuple and display the content.

An easy use case is performing operations on the source when you only need to know if the operation succeeded or not.

Getting All Columns From the Result Set

CODE

SELECT x.* 
	FROM(CALL "ms.native"( "request" => 'SELECT 1, 2 UNION SELECT 3, 4;')) r,
    	ARRAYTABLE(r.tuple COLUMNS "first" string, "second" bigdecimal) x;;

This example focuses on splitting the tuples from the result set which the CData Virtuality Server retrieved back into regular columns.

To do this, you can apply the ARRAYTABLE construct and enumerate the columns and their data types stored within the tuples.

Supported Data Sources

As of now, native query usage is supported for the following data sources:

BigQuery
CData Virtuality
DB2
DB2 AS/400
DB2 zOS
Derby
EXASOL
Generic JDBC
Greenplum
H2
Hive
Ingres
InterSystems Cache
ksqlDB
LDAP
MS SQL
MS SQL jTDS
MySQL
Oracle
PostgreSQL
Redshift
Vertica