Direct Query Procedure native()

You are looking at an older version of the documentation. The latest version is found here.

The JDBC translator provides a procedure to execute any ad-hoc SQL query directly against the source without Data Virtuality Server parsing or resolving. Since the metadata of this procedure’s results are not known to Data Virtuality Server, they are returned as an object array. ARRAYTABLE can be used construct tabular output for consumption by client applications.

Select Examples

SQL

SELECT x.* FROM (call jdbc_source.native('select * from g1')) w,
ARRAYTABLE(w.tuple COLUMNS "e1" integer , "e2" string) AS x ;;
 
SELECT x.* FROM (call jdbc_source.native('select ? where 1=?','2',(select 1))) x ;;
 
SELECT x.* FROM (call jdbc_source.native(
    "request" => 'select ? where 1=?',
    "variable" => array('2',(select 1)))
) x ;;

Insert Example

SQL

SELECT x.* FROM (call jdbc_source.native('insert into g1 (e1,e2) values (?, ?)', 112, 'foo')) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

Update Example

SQL

SELECT x.* FROM (call jdbc_source.native('update g1 set e2=? where e1 = ?','blah', 112)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

Delete Example

SQL

SELECT x.* FROM (call jdbc_source.native('delete from g1 where e1 = ?', 112)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x