Direct Query Procedure native()

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

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 ;;

SQL

Insert Example

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

SQL

Update Example

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

SQL

Delete Example

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

SQL