ODBC escape sequences are a set of standard patterns that are recognized by both ODBC and JDBC drivers. The sequences are used for creating SQL statements that are platform-independent. When sent to a JDBC or ODBC driver, the driver converts the escape sequences to SQL expressions that are recognized by the database. An ODBC escape sequence is enclosed in braces and starts with FN, which stands for function. An ODBC escape sequence has the following pattern:
{FN functionName(parameters)}
For example, the following query sent through a JDBC driver for SQL Server converts the values from the column Name to uppercase:
SELECT {FN UCASE (Name)} FROM Customers
The JDBC driver translates the expression to:
SELECT UPPER (Name) FROM Customers
The Integration service uses ODBC escape sequences to map functions. However, not all drivers implement all the ODBC escape sequences. In some cases, a database has no equivalent of an escape sequence. When a driver does not have an implementation for an escape sequence, the driver returns an error. When your driver does not support an escape sequence, you must provide a function mapping in mappings.xml. Additionally, when the escape sequence implementation is incompatible with the Actuate SQL specification, you must also edit mappings.xml.
The following example demonstrates one possible transformation of an Actuate SQL query into a database query, in this case SQL Server. The Integration service reads the mappings.xml file for function mappings but may determine that it can do some of the query operations more efficiently than the database. Thus, the query that the Integration service sends to the database may not use all mappings.
The following Actuate SQL query selects three columns from a single table, TOPDEALS:
SELECT CUSTID, CUSTOMNAME, FLOOR(AMOUNT) AS AMOUNT
FROM "../TOPDEALS.sma" AS TOPDEALS
WHERE Upper(CUSTOMNAME) LIKE Upper('DES%')
For the AMOUNT column, you use the FLOOR function to round down the values returned. In the WHERE clause, you define a filter condition so that the query only returns customers whose name starts with DES. With the exception of the FROM clause, which refers to a map file, the query looks like a generic SQL query.
To translate the query into a database query, the Integration service loads function templates from the mappings.xml file for the database type. The Integration service finds mappings for the Actuate SQL functions used in the query, as shown in the following plan:
The Integration service determines an optimal query execution plan after parsing the query and assessing the mappings. FLOOR() is not sent to the database. Instead, the Integration service performs this operation on the returned data. The following query is sent to the database:
SELECT CUSTID, CUSTOMNAME, AMOUNT
FROM TOPDEALS
WHERE UPPER( CUSTOMNAME ) LIKE Upper('DES%')
You can see the query sent to the database in the IO Design perspective Query Profiler.
Once the database returns results, the Integration service uses the data type mappings you defined in mappings.xml to convert the data in the result set to Actuate SQL data types. Finally, the Integration service performs any remaining operations, in this case the FLOOR function, before sending the data to the Factory service to generate the report.