Menu
Simba Technologies
Simba Technologies

SimbaEngine X SDK 10.1.3
Developing Drivers for Data Stores Without SQL

Introduction to the MiniParser

Many SQL-enabled data stores represent data and implement SQL in slightly different ways. To allow applications to handle these differences transparently, the ODBC and JDBC standards specifies a set of escape sequences to represent functionality such as date, time, scalar functions, and procedure calls. ODBC and JDBC drivers must translate these escape sequences into a format that their data store supports.

The SimbaEngine X SDK includes the MiniParser feature to help developers parse SQL commands for escape sequences, then replace them with the command format understood by their data store. SQL commands can contain multiple escape sequences with multiple parameters, and escape sequences themselves can be nested. The MiniParser implements all of the recursive processing and the creation of complex regular expressions required to support escape sequences. Using the SimbaEngine X SDK, it is easy for your driver to translate SQL commands containing complex, nested escape sequences into a format that your data store understands.

ODBC and JDBC Escape Sequences

Escape sequences are grouped into types, making them easier to parse and process. Notice they are all enclosed in curly braces ({ }). For example, some common escape sequences are shown below:

Escape sequence type Format Example
date {d 'value'} {d '2001-01-01'}
scalar function {fn scalar-function} { fn DAYOFWEEK( DATE '2000-01-01' ) }
procedure call {[?=]call procedure-name[([parameter][,[parameter]]...)]} {?=call LENGTH('hello world')}

For information about the complete set of ODBC escape sequences, see "ODBC Escape Sequences" in the ODBC Programmer's Reference: https://msdn.microsoft.com/en-us/library/ms711838(v=vs.85).aspx. For information about JDBC escape sequences, see http://docs.oracle.com/cd/E13222_01/wls/docs91/jdbc_drivers/sqlescape.html.

Note:

The SimbaEngine X SDK handles all escape sequences in the ODBC and JDBC specification.

Converting Simple Escape Sequences

Drivers must locate escape sequences and convert them to commands that are understood by their data source.

Simple Example: Dates

Consider a SQL command that contains an escape sequence of type date:

SELECT OrderNum, OrderDate FROM Orders WHERE OrderDate = {d '2015-08-12'}

A PostgreSQL driver converts the command as:

SELECT OrderNum, OrderDate FROM Orders WHERE OrderDate = DATE '2015-08-12'.

A Microsoft SQL driver converts the command as:

SELECT OrderNum, OrderDate FROM Orders WHERE OrderDate ='08-12-2015'.

Converting Complex Escape Sequences

Escape sequences can be nested, requiring recursive programming to replace them correctly.

Complex Example: Nested Escape Sequences

Given an escape sequence with the following format:

{fn EXTRACT( YEAR FROM {ts '2001-02-03 16:17:18.987654'} ) }

A PostgreSQL driver converts the command as:

EXTRACT(YEAR FROM TIMESTAMP '2001-02-03 16:17:18.987654')

Non-Escaped Scalar Functions

Some applications use ODBC scalar functions in a SQL command without enclosing the function in an escape clause. For example, an application might use CONVERT(sqltype,value) instead of {fn CONVERT(value, odbctype)}. The miniParser handles the CONVERT scalar function in non-escaped form. Currently, other non-escaped scalar functions are not handled.

MiniParser Architecture

The miniParser is included in the Support package of the SimbaEngine X SDK. It is composed of two main classes:

ODBC Architecture:

  • ODBCEscaper: searches the SQL command for ODBC escape sequences and parameters. To use this class, pass an IReplacer implementation and the SQL command to ODBCEscaper.Apply().
  • IReplacer: converts each type of escape sequence to the format required for a particular data store. Override this class to provide your own implementation.

This architecture is shown in the figure below:

Mini parser architecture

JDBC Architecture:

  • JDBCEscaper: searches the SQL command for JDBC escape sequences and parameters. To use this class, pass an IReplacer implementation and the SQL command to JDBCEscaper.Apply().
  • IReplacer: converts each type of escape sequence to the format required for a particular data store. Override this class to provide your own implementation.

Error Handling

This section explains how ODBCEscaper handles errors and malformed SQL statements.

Text in unsupported locations is discarded

If a SQL statement is incorrectly formed and contains text in unsupported locations, ODBCEscaper will discard the text. For example, the escape sequence {fn ABS(myNum) bad string} is incorrectly formed, as no text is allowed after the function name. In this case, ODBCEscaper will discard the text bad string.

Incorrectly formatted escape sequences are not sent to IReplacer

If an escape sequence is incorrectly formatted, ODBCEscaper will not pass it through to IReplacer, and will leave it unchanged. For example, {D 2001-1-1} is incorrectly formatted because it does not contain quotation marks (''). The incorrect escape sequence is simply included in the final SQL command. This allows the data store to handle the incorrect command sequence with the appropriate error.

Example Workflow

The following diagram shows how the ODBCEscaper and a sample IReplacer implementation, PGOReplacer, work together to convert a SQL statement containing parameters and escape sequences into a SQL statement for a PostgreSQL data store.

Note:

The work flow is the same for JDBCEscaper.

 

Mini parser workflow diagram

  1. The driver calls ODBCEscaper.Apply(), passing in the SQL command SELECT {fn char(0x30 + { fn ceiling(?)} + ? + {fn sansargs}) } --!?. This command contains nested functions, a custom (non-ODBC) function, and a comment string.
  2. ODBCEscaper starts with the inner most escape sequence, {fn ceiling(?)}. First, it tells PGOReplacer to create the parameter marker.
  3. PGOReplacer creates and returns the first parameter marker in the format that the PostgreSQL data store understands.
  4. ODBCEscaper then tells PGOReplacer to handle {fn ceiling(?)}, passing in the first converted parameter, ($1).
  5. PGOReplacer converts the ceiling function to CEIL as required by the PostgreSQL data store, and uses the parameter marker ($1) in the function.
  6. This process repeats until IReplacer converts all the escape sequences and parameter markers. Then ODBCEscaper reassembles the SQL command, including the comment and the string.

The original SQL command is now converted into a SQL command that PostgreSQL data store can understand.