Purpose

The CAST_TO_NATIVE function escape can be used in a filter to select or insert a value of a specific native type.

In some cases, a value with a specific native type can be concealed or obscured because the driver describes a field with inconsistent native types as a column with a single SQL type. For example, the driver maps a MongoDB _id field with the native types String and ObjectId to a relational _ID column with the SQL_WVARCHAR type. In this context, the CAST_TO_NATIVE function escape allows users to send a value as it is defined in the MongoDB database, rather than how it is described in the relational model of the data.

Currently, CAST_TO_NATIVE can only be used with the ObjectID type in SELECT statement filters and literal INSERT values.

Syntax

{fn CAST_TO_NATIVE('value','native_type')}

where:

value
is the value to be selected or inserted.
native_type
is the native type that in part defines the value.

Example Select

The following statement selects records from Account where the _ID field has an ObjectID value of 507f1f77bcf86cd799439011.

SELECT * FROM Account WHERE _ID = {fn CAST_TO_NATIVE('507f1f77bcf86cd799439011','objectid')}

Example Insert

The following statement inserts the ObjectID value 507f1f77bcf86cd799439011 into the _ID field.

INSERT INTO Account(_ID) VALUES ({fn CAST_TO_NATIVE('507f1f77bcf86cd799439011','objectid')})