Skip to main content

6.1 SQL execution

Configure the SQL executor and database connections through SQL executor before running these scripts.

Declare a fragment with its arguments and SQL, then call it as a function. Its registered name selects the operation and input format.

OperationPlain SQLDynamic XMLResult
QueryselectSqlselectXmlQuery result or page object
InsertinsertSqlinsertXmlAffected rows
UpdateupdateSqlupdateXmlAffected rows
DeletedeleteSqldeleteXmlAffected rows
ProcedurecallSqlcallXmlResult sets, counts or selected outputs
General executionexecuteSqlexecuteXmlStatement result

Prefer the operation-specific name when known. executeSql infers the operation from the beginning of the SQL; explicit names are preferable for complex statements.

Sample data​

The examples use this H2 schema. Use equivalent DDL on other databases.

CREATE TABLE people (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(100),
age INT,
enabled INT DEFAULT 1
);
INSERT INTO people(name, age) VALUES ('Alice', 25), ('Bob', 30);

Runnable scripts are in dataql/dataql-sqlproc/src/test/resources/sql-manual; the corresponding documentation test package verifies them against H2.

Query​

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

This returns Alice and Bob. off preserves a list and lower normalizes column names. See Results.

Insert, update and delete​

var add = @@insertSql(name, age)<% INSERT INTO people(name, age) VALUES (#{name}, #{age}) %>;
var change = @@updateSql(name, age)<% UPDATE people SET age = #{age} WHERE name = #{name} %>;
var find = @@selectSql(name)<% SELECT age FROM people WHERE name = #{name} %>;
var remove = @@deleteSql(name)<% DELETE FROM people WHERE name = #{name} %>;
var inserted = add('Carol', 20);
var updated = change('Carol', 21);
var age = find('Carol');
var deleted = remove('Carol');
return {'inserted': inserted, 'updated': updated, 'age': age, 'deleted': deleted};

Each write returns its affected-row count. These calls execute separately; use a transaction when they must be atomic.

Batch calls​

var add = @@insertSql[](person)<%
INSERT INTO people(name, age) VALUES (#{person.name}, #{person.age})
%>;
return add([{'name':'Carol','age':20}, {'name':'Dave','age':22}]);

The [] suffix processes list arguments by matching index. All argument lists must have the same size. The result is [1, 1]. This implementation calls JDBC for each item rather than using JDBC executeBatch; wrap the whole call in a transaction for atomicity.

Execution options​

hint timeout = 5;
hint fetchSize = 100;
var find = @@selectSql()<% SELECT id, name FROM people %>;
return find();

PreparedStatement is the default. Timeout is measured in seconds; fetch size is a driver hint. See SQL hints. Execution failures propagate to the DataQL caller.

Parameter scopes​

Outside a fragment, DataQL ${id} reads a script parameter. Inside SQL, #{id} binds a fragment argument and ${id} performs text substitution. See Parameters.