6. SQL executor

The SQL executor is a DataQL extension module. A fragment declares database statements and parameters, then runs as a function. Query results can be used in expressions and transformations.

Requirements

The application must load dataql-sqlproc and provide database connections. See DataQL engine: SQL executor for setup. This chapter covers script usage.

hint FRAGMENT_SQL_OPEN_PACKAGE = 'off';
hint FRAGMENT_SQL_COLUMN_CASE = 'lower';
var find = @@selectSql(minAge)<%
    SELECT name, age FROM people WHERE age >= #{minAge} ORDER BY id
%>;
return find(25) => [{"name", "nextAge": age + 1}];

Using the people table in SQL execution, this returns [{"name":"Alice","nextAge":26},{"name":"Bob","nextAge":31}].

Guide

SQL execution: query, update and batch fragment calls.
Parameter binding: positional and named parameters, text replacement, rules, options and marker escaping.
Dynamic rules: SQL generation, collections, branches and nested templates.
XML dynamic SQL: tag attributes, complete examples and generated SQL.
Results and keys, pagination and dialects: result shapes, keys and page navigation.
Type handling: Java/JDBC mappings, basic types, JSON, binary streams, arrays and vectors.
Transactions: commit, rollback, seven propagation modes, isolation and data-source scope.
Procedures and multiple results: input/output parameters and named results.
SQL hints: execution options.

For custom rules, dialects and handlers, see engine extensions.

Guide​

Guide