6.5 Results and keys

Query rows are converted to objects using column labels. Unpacking determines whether the result is a list, object or scalar. Writes return affected-row counts.

Unpacking

Rows	off	row	column (default)
None	[]	{}	null
One row, one column	Object list	Object	Scalar
One row, several columns	Object list	Object	Object
Several rows	Object list	Object list	Object list

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

With the people table, this returns [{"id":1,"name":"Alice"}]. Set row to return {"id":1,"name":"Alice"}. With column, this two-column query still returns an object.

A single-column query can return the value directly:

hint FRAGMENT_SQL_OPEN_PACKAGE = 'column';
var find = @@selectSql(id)<% SELECT name FROM people WHERE id = #{id} %>;
return find(1);

This returns Alice; an ID with no matching row returns null.

Set FRAGMENT_SQL_OPEN_PACKAGE = 'off' for a stable list shape. Page data always remains a list.

Column names

FRAGMENT_SQL_COLUMN_CASE supports default, upper, lower and hump.

hint FRAGMENT_SQL_OPEN_PACKAGE = 'off';
hint FRAGMENT_SQL_COLUMN_CASE = 'hump';
var find = @@selectSql()<% SELECT id AS user_id, name AS user_name FROM people ORDER BY id %>;
return find();

The fields become userId and userName. SQL column labels take precedence over physical names. If normalized labels collide, the first value is retained; use distinct aliases.

Key queries

For H2, create a sequence before running this example:

CREATE SEQUENCE people_ids START WITH 100;

var add = @@insertXml(name, age)<%
    <selectKey keyProperty="newId" order="before">
        SELECT NEXT VALUE FOR people_ids
    </selectKey>
    INSERT INTO people(id, name, age) VALUES (#{newId}, #{name}, #{age})
%>;
var find = @@selectSql(name)<% SELECT id FROM people WHERE name = #{name} %>;
run add('Carol', 20);
return find('Carol');

selectKey runs before or after an insert on the same connection and writes its value to keyProperty in the fragment parameter map. keyColumn selects columns for multiple properties. insertXml still returns affected rows; writing a parameter does not make it the script return value.

JDBC getGeneratedKeys() is not currently connected to execution. Although the generated-key hints are parsed, they do not retrieve auto-generated IDs. Use an appropriate selectKey or explicit database query.

Selected outputs

hint bindOut = '#result-set-1';
var find = @@selectSql()<% SELECT count(*) FROM people %>;
return find();

The example returns {"#result-set-1":2}. The selected output retains its name, while its value follows the unpacking setting.

Queries and general execution can select named results with bindOut. See Procedures for numbering and output parameters. Pagination cannot be combined with bindOut.

Choose result processing

• Lists, objects and scalar values: FRAGMENT_SQL_OPEN_PACKAGE.
• Column-name case: FRAGMENT_SQL_COLUMN_CASE; use SQL aliases for explicit names.
• Date, JSON, array and binary columns: type handlers.
• Procedure outputs, result sets and update counts: select named outputs with bindOut; see stored procedures.
• Reshaping or computing query results: DataQL structure transformations.

These settings process SQL results. Dataway HTTP result handlers run at the API response stage. The SQL executor does not register resultSet, resultUpdate or defaultResult dynamic rules.