Data Resource - SQL Request

About

A SQL request is a request against a SQL database. It's the runtime data resource form of SQL.

In a SQL request processing, Tabulify will:

• parse the Sql scripts (a file or string)
• extract the SQL statements
• execute them one by one
• and returns the result

Type

Sql scripts can be stored in

• sql_files - one file, one script
• any other_resources where one record is one script

SQL Files

SQL files will become SQL Request if they are represented in an runtime data uri

For instance:

(top-5-clients.sql@cd)@sqlite

where:

• top-5-clients.sql is a sql file
• located in the current directory (cd)
• and should be executed against a sqlite database

Other resources

Sql scripts are generally stored in a SQL File but you can get them from any resource.

If the resource has:

• only one column, this column is supposed to store the SQL Scripts
• otherwise we search for the column named sql

Example:

• a view where all scripts are returned in the sql column

(statements_view@sqlite)@sqlite

• a select where all scripts are returned in the sql column

((select_statement.sql@cd)@sqlite)@sqlite

• a csv file where all statements are on one line

(statements.csv@cd)@sqlite

List

Tabulify supports all SQL Statements:

• Data Manipulation Language (DML) - ie SELECT/UPDATE/INSERT/MERGE statement
• Data Definition Language (DDL) - ie CREATE/ALTER/DROP statement
• SQL Procedures- ie CALL/DO/DECLARE/BEGIN/END statement

Manifest

Example of kind manifest

kind: sql-request
spec:
  data-uri: (script.sql@cd)@database
  data-def:
    parameters:
      - value: value1
        direction: IN
        type: varchar
      - value: value2
        direction: OUT
        type: integer
      - ...
    strict-execution: true

where:

• data-uri is an runtime data-uri
• data-def is a data definition that accepts
  • parameters - A list of parameters if the script contains SQL Parameterized Statement
  • strict-execution: if true, the execution will terminate when a SQL statement returns an error. Default to the global tabulify value.

Execution

See Runtime Execution

Limitation: 1 SQL Request, 1 Result Set

Tabulify is based on resource chaining (ie one resource, one response). Therefore, a SQL Request should return only one result set.

It means that in a SQL Request, you:

• cannot have:
  • multiple Select statements
  • multiple DML statements that are using the RETURNING clause
  • multiple Sql procedure with OUT parameters
• can have:
  • multiple DML statements that are NOT using the RETURNING clause
  • multiple DDL statements
  • multiple DCL statements (GRANT/REVOKE)
  • multiple Sql procedure without OUT parameters

If you want to execute a SQL Request with this limitation, you need to split the statements before running them in a pipeline

Note that not all database supports returning multiple result set. For instance, SQL Server does not support it.

Note

Execution Warnings

When executing a query, the database engine may return warnings.

A warning may come from:

• the database engine
• the user code (For example, the sql server print statement create a warning)

Tabulify sends this warnings to the standard error stream so that:

• the user will see them in a terminal
• the warnings will not interfere with the normal standard output stream

Why Request?

When creating this runtime, we needed to give it a name.

We went through the following names in order:

• query because this is the most common name used in the documentation
• script because we wanted it to be the counterpart of the file system script
• fetch because we wanted to indicate that it was a remote call with a result

They were not selected:

• query because in the SQL world, it's an alias for a SELECT statement as seen in the executeQuery method.
• script because it has a local meaning and does not reflect the fact that this is a call to a remote service
• fetch because it's a web word and is not well-known in the data world.

Finally, we ended up with request because:

• it reflects the fact that this is a call to a remote service
• the result given by the java driver is known as a result set