Select... With
The gateway supports native SELECT statements that include the WITH NATIVE_SELECT clause. It is used to issue SELECT statements to the host DBMS without gateway processing. These statements are typically host-specific. The syntax is as follows:
SELECT * FROM tablename WITH NATIVE_SELECT;
The WITH NATIVE_SELECT clause specifies that the SELECT statement should be passed through the gateway "as is." Therefore, you can specify the SELECT statement in the syntax of the native DBMS.
The WITH NATIVE_SELECT clause must be placed at the very end of the statement, after any other WITH clauses. Other restrictions are listed in
Clause Restrictions. Examples are provided in
Examples--WITH NATIVE SELECT.
Clause Restrictions
• The NATIVE_SELECT keyword must be placed at the very end of the WITH clause, after any other keywords.
• Using bind parameters is not supported. For example, the following statements are not supported:
Static SQL
SELECT * from mytable where col1 = :host_variable WITH NATIVE_SELECT;
Dynamic SQL
SELECT * from mytable where col1 = ? WITH NATIVE_SELECT;
• The gateway removes the WITH clauses in the case of CREATE and DROP statements, but sends WITH clauses other than NATIVE_SELECT "as is" to the host DBMS in all other cases. Therefore, unrecognized WITH clauses in statements other than CREATE and DROP will trigger host DBMS errors.
• When using date literals in the query with NATIVE_SELECT, they must be in the native format that conforms to the host DBMS date format. Using Ingres date format will result in errors or provide unpredictable results.
• Only data types that are supported by the gateway can be returned.