Data Resource - SQL File

About

sql files are text file data resource that:

contains sql statement as record.
have the text/sql media type.

They can be:

moved or loaded (transfer)
executed

Attributes

A sql file accepts the following attributes parameter

Argument	Values	Default	Description
PARSING	sql or text	sql	The parsing mode
END_OF_RECORDS	See endofrecords	See endofrecords	The end of records for a sql statement
COLUMN_NAME	-	sql	The name of the column that stores the SQL statement

Because a SQL file is a text file, you can also set text attributes

EndOfRecords

When the parsing mode is set to text, the following default end of records values are used:

Description	Values
; followed by an end of line character	;\n, ;\r\n, ;\r
/ followed by an end of line character	/\n, /\r\n, /\r
the word GO or go on one line	\ngo\n, \r\ngo\r\n, \rgo\r \nGO\n, \r\nGO\r\n, \rGO\r

Parsing

The parsing attribute has two values:

sql
text

SQL

The sql value will extract the SQL statement with a SQL parser that is aware of the SQL language such as:

block of code (ie procedural code)
comment

In this mode, the returned data path has the following columns:

name: the statement name. Example of values:
- select
- with
- update
- create
- …
subset: the subset.
category: the category. Example of values:
- sql - pure SQL,
- psql - procedural SQL,
- comment - a script comment,
- …
and a column for the SQL statement with the name specified by the columnName argument and a clob data type.

In this mode, the end of statement characters are:

for a pure SQL statement: ;
for a SQL Procedure:
- // - MySQL Delimiter
- $$; - Postgres Delimiter
- end; - MySQL Delimiter
for a SQL comment: – or #

Text

The text mode will extract the SQL statement with the end Of Records values on text level.

In this mode, the returned data path has only one column that stores the SQL statement with the name specified by the column-name attribute and a clob data type.

Operation

Transfer

Structure Source / Target

In a transfer:

as a source, in:
- text mode: one column
- sql mode: 2 columns (type and sql)
as a target, it's considered a free form structure that accepts any number of columns.

Execution

A SQL File is executed only if you use it in a SQL Request format

Parsing Language Support

We support all SQL scripts that follows the ANSI SQL standard.

Unfortunately, every database derives from it for historical or feature purpose and there is almost one SQL language flavor by database.

Statement support

We recognize:

ANSI SQL statement (Select, Insert, Update, …)
all SQL Procedure statement flavor
third SQL statements such as:
- the Postgres DO statement
- the MySQL USE statement
- the MySQL PRAGMA statement
- …
third cli command (we don't implement them, we parse them only) such as
- the delimiter command
and if a unknown statement is found, we consider it to be a statement terminating by a ;

Comments

We support comment at the beginning of a line and in-line comment

# A supported comment
-- A supported  comment
SELECT 1 /* an in-line supported comment */ + 1;

We DON'T support comment at the end of the line (because our parser is line based, not word based)

SELECT 1+1;     # This comment is not supported
SELECT 1+1;     -- This comment is not supported