SQL Parser

The parser driver handles SQL parsing and supports various operators and functions that can be applied to the queried data. This chapter describes the exact language scope as well as the offered operators, functions, and pseudo-columns in detail.

Most drivers currently only support reading via SQL SELECT. INSERT, UPDATE, and DELETE are already implemented in some drivers.

In the following syntax descriptions, square brackets mark optional parts. A vertical bar separates alternatives: exactly one of the listed variants may be used. Placeholders in angle brackets such as <expression> or <identifier> stand for expressions or names to be substituted at that position.

SELECT

SELECT reads data from the source or evaluates expressions without a source. The FROM clause has several variants for files, directories, and objects.

SELECT <expression-1> [ AS <identifier-1> ]
     , <expression-2> [ AS <identifier-2> ]
       ...
     , <expression-n> [ AS <identifier-n> ]
 [
   FROM <identifier>.FILES() | <identifier>[.<identifier>] [tableColumns] [tableOptions]
   [ WHERE <expression> ]
   [ FILTER <filter-text> [ ENDFILTER ] ]
   [ ORDERBY <orderby-text> [ ENDORDERBY ] ]
 ]

There are the following variants for the table specification:

No FROM Section

No file is required. The query returns one row and the result consists of the specified expressions based on literals.

FROM <identifier>.FILES()

  • The specified identifier points to a directory.
  • The result returns one row per found file.
  • This syntax is only supported by file-based JDBC drivers.

The metadata of the pseudo-columns can then be used.

FROM <identifier>

  • With the Excel driver this syntax is not allowed
  • With the other file-based drivers the identifier points to a file
  • With the Salesforce driver the identifier points to an object

As a rule, the filename must be specified in double quotes, since the period for the file extension is not part of an unprotected identifier.

FROM <identifier-1>.<identifier-2>

  • With the CSV driver the 1st identifier is ignored; the 2nd identifier is the filename
  • With the JSON, XML, and YAML drivers the 1st identifier is the file and the 2nd identifier is the path inside the document
  • With the Excel driver the 1st identifier specifies the Excel file and the 2nd the worksheet name
  • With the Salesforce driver the 1st identifier is the schema and the 2nd the object

As a rule, the filename must be specified in double quotes, since the period for the file extension is not part of an unprotected identifier.

INSERT

INSERT writes a new record into the source. The column list and the VALUES list must have the same number of entries. Not all drivers support INSERT.

INSERT INTO <identifier>[.<identifier>] ( <identifier-1>, ... <identifier-n> )
VALUES ( <expression-1>, ... <expression-n> )

UPDATE

UPDATE changes existing records in the source. FILTER is required so that the source can identify the records to change. Not all drivers support UPDATE.

UPDATE <identifier>[.<identifier>]
   SET <identifier-1> = <expression-1>
     [ , <identifier-2> = <expression-2> ]
       ...
 FILTER <filter-text> [ ENDFILTER ]

DELETE

DELETE removes records in the source. FILTER is optional: with FILTER, only the records identified by the source from the filter text are deleted. Without FILTER, the statement deletes all records of the given table. Not all drivers support DELETE.

DELETE FROM <identifier>[.<identifier>]
 [ FILTER <filter-text> [ ENDFILTER ] ]

Table Columns

COLUMNS specifies the column list of the source. The names must match the source; with CSV and Excel files they can be matched against the column headers.

COLUMNS ( <identifier-1>, ... <identifier-n> ) [ HEADLINE <integer> ]

The optional specification

HEADLINE <integer>

can only be used with the CSV and Excel drivers.

Table Options

SEPARATED BY, QUOTED BY, and ENCODING control how a file-based text source is read. The following options may appear in any order:

SEPARATED BY <single character in single quotes>
QUOTED BY <single character in single quotes>
ENCODING <string in single quotes>

If SEPARATED BY is not specified, a comma is used.

If QUOTED BY is not specified, the double quote is used.

If ENCODING is not specified, UTF-8 is used.

FILTER and ORDERBY

FILTER and ORDERBY pass vendor-specific expressions through to the source unchanged. Not every driver supports both keywords.

Some drivers support the FILTER and / or the ORDERBY keyword. These include:

  • Salesforce
  • Eloqua (all APIs)
  • LDAP
  • REST

The syntax inside FILTER ... ENDFILTER (or until the end of the statement) and likewise ORDERBY ... ENDORDERBY (or until the end) is not interpreted, but passed through to the source.

The keyword is ORDERBY, not ORDER BY.

Not all sources allow comments. To comment something out, place that block above FILTER or ORDERBY, because only there the SQL parser recognizes and strips comments.

Identifiers

Identifiers are used for table names, column names in the source or as aliases.

Identifiers in double quotes are treated case-sensitively and allow special characters.

Identifiers without double quotes are converted to lowercase.

When comparing column names in CSV and Excel files to the column headers, the case is ignored (case insensitive).

Literals

Literals are values written directly in the SQL. There are numbers, character strings, and the keywords NULL, TRUE, and FALSE.

Numbers

Integer literals are of data type BIGINT. They have a value range from -263 to 263-1 and are specified as a sequence of digits, e.g. 42.

Decimal literals are of data type DOUBLE. They contain a decimal point, e.g. 3.14, 3. or .5.

Character Strings

Character strings can be of any length and have the data type VARCHAR.

Character string literals are enclosed in single quotes.

To include a single quote in a character string, two single quotes must be specified.

Character strings can also be multiline.

SELECT 'Karens'' Backstube', '!hallo
Du da!';

Output

 column_1 column_2
-------- --------
Karens' Backstube !hallo
Du da!

Null Value

NULL

The null value can be specified as a literal via the NULL keyword.

True

TRUE

The boolean value True is specified with the TRUE keyword.

False

FALSE

The boolean value False is specified with the FALSE keyword.

Pseudo-Columns

The following pseudo-columns can be used in expressions without parentheses:

  • ROWNUMBER — row number in the source
  • CURRENT_DATE — current date
  • CURRENT_TIMESTAMP — current local date and time without time zone
  • DIRECTORY, FILENAME, FILEDATE — file metadata (file-based JDBC drivers only)

Details can be found in the section Pseudo-Columns.

Expressions

Expressions are combinations of literals, column names from the source, pseudo-columns, operators, case distinctions or built-in functions.

Instead of an expression, the character * can be specified. Then no alias is allowed.

The priorities correspond to ANSI-SQL.

To change the priority, expressions can be enclosed in parentheses. The expression within a parenthesis pair is evaluated before the outer operations.

Parameters

A question mark is a JDBC parameter placeholder. The values are bound later via the JDBC PreparedStatement setter methods, in the order of the question marks.

?