When you execute an SQL operation on a Session using the
sql()
method, an SqlResult
is returned. Iterating over an SqlResult
is
identical to working with results from CRUD operations. The
following example assumes that the users table exists.
var res = mySession.sql('SELECT name, age FROM users').execute();
var row;
while (row = res.fetchOne()) {
print('Name: ' + row['name'] + '\n');
print(' Age: ' + row.age + '\n');
}
SqlResult
differs from results returned by CRUD
operations in the way how result sets and data sets are
represented. An SqlResult
combines a result set
produced by, for example, INSERT
,
and a data set, produced by, for example,
SELECT
in one. Unlike with CRUD
operations, there is no distinction between the two types for
SqlResult
. An SqlResult
instance exports methods for accessing data and to retrieving the
last inserted ID or number of affected rows.
Use the hasData()
method to learn whether an
SqLResult
is a data set or a result. The method
is useful when code is to be written that has no knowledge about
the origin of an SqlResult
. This can be the
case when writing a generic application function to print query
results or when processing stored procedure results. If
hasData()
returns true
, then
the SqlResult
origins from a
SELECT
or similar command that can
return rows.
A return value of true
does not indicate
whether the data set contains any rows. The data set can be empty
if, for example, fetchOne()
returns
NULL
or fetchAll()
returns
an empty list. And if multiple result sets are returned, any of
the result sets may be empty too. The following example assumes
that the procedure my_proc
exists.
var res = mySession.sql('CALL my_proc()').execute();
if (res.hasData()){
var row = res.fetchOne();
if (row){
print('List of rows available for fetching.');
do {
print(row);
} while (row = res.fetchOne());
}
else{
print('Empty list of rows.');
}
}
else {
print('No row result.');
}
It is an error to call either fetchOne()
or
fetchAll()
when hasData()
indicates that an SqlResult
is not a data set.
function print_result(res) {
if (res.hasData()) {
// SELECT
var columns = res.getColumns();
var record = res.fetchOne();
while (record){
for (index in columns){
print (columns[index].getColumnName() + ": " + record[index] + "\n");
}
// Get the next record
record = res.fetchOne();
}
} else {
// INSERT, UPDATE, DELETE, ...
print('Rows affected: ' + res.getAffectedItemsCount());
}
}
print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute());
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute());
Calling a stored procedure might result in having to deal with
multiple result sets as part of a single execution. As a result
for the query execution an SqlResult
object is
returned, which encapsulates the first result set. After
processing the result set you can call
nextResult()
to move forward to the next
result, if there is any. Once you advanced to the next result set,
it replaces the previously loaded result which then becomes
unavailable.
function print_result(res) {
if (res.hasData()) {
// SELECT
var columns = res.getColumns();
var record = res.fetchOne();
while (record){
for (index in columns){
print (columns[index].getColumnName() + ": " + record[index] + "\n");
}
// Get the next record
record = res.fetchOne();
}
} else {
// INSERT, UPDATE, DELETE, ...
print('Rows affected: ' + res.getAffectedItemsCount());
}
}
var res = mySession.sql('CALL my_proc()').execute();
// Prints each returned result
var more = true;
while (more){
print_result(res);
more = res.nextResult();
}
The number of result sets is not known immediately after the query execution. Query results can be streamed to the client or buffered at the client. In the streaming or partial buffering mode a client cannot tell whether a query emits more than one result set.