Pre-General Availability Draft: 2017-10-20

9.6 Working with SQL Result Sets

When executing an SQL operation on a NodeSession with NodeSession.sql() an SqlResult is returned.

Result iteration is identical to working with results from CRUD operations. The example assumes that the users table exists.

MySQL Shell JavaScript Code

var res = nodeSession.sql('SELECT name, age FROM users').execute();

var row;
while (row = res.fetchOne()) {
  print('Name: ' + row['name'] + '\n');
  print(' Age: ' + row.age + '\n');
}

MySQL Shell Python Code

res = nodeSession.sql('SELECT name, age FROM users').execute()

row = res.fetch_one()

while row:
        print 'Name: %s\n' % row[0]
        print ' Age: %s\n' % row.age
        row = res.fetch_one()

Node.js JavaScript Code

var res = nodeSession.sql('SELECT name, age FROM users').execute(function (row) {
  console.log('Name: ' + row['name']);
  console.log(' Age: ' + row.age);
});

C# Code

var res = nodeSession.SQL("SELECT name, age FROM users").Execute();

while (res.Next())
{
  Console.WriteLine("Name: " + res.Current["name"]);
  Console.WriteLine("Age: " + res.Current["age"]);
}

Java Code

SqlResult res = nodeSession.sql("SELECT name, age FROM users").execute();

Row row;
while ((row = res.fetchOne()) != null) {
  System.out.println(" Name: " + row.getString("name") + "\n");
  System.out.println(" Age: " + row.getInt("age") + "\n");
}

C++ Code

RowResult res = nodeSession.sql("SELECT name, age FROM users").execute();

Row row;
while ((row = res.fetchOne())) {
  cout << "Name: " << row[0] << endl;
  cout << " Age: " << row[1] << endl;
}

SqlResult differs from results returned by CRUD operations in the way how result sets and data sets are represented. A 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. A SqlResult exports methods for data access and to retrieve the last inserted id or number of affected rows.

Use the hasData() method to learn whether a 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 a 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 may be empty. It is empty if fetchOne() returns NULL or fetchAll() returns an empty list. The example assumes that the procedure my_proc exists.

MySQL Shell JavaScript Code

var res = nodeSession.sql('CALL my_proc()').execute();

if (res.hasData()){

  var row = res.fetchOne();
  if (row){
    print('List of row available for fetching.');
    do {
      print(row);
    } while (row = res.fetchOne());
  }
  else{
    print('Empty list of rows.');
  }
}
else {
  print('No row result.');
}

MySQL Shell Python Code

res = nodeSession.sql('CALL my_proc()').execute()

if res.has_data():

        row = res.fetch_one()
        if row:
                print 'List of row available for fetching.'
                while row:
                        print row
                        row = res.fetch_one()
        else:
                print 'Empty list of rows.'
else:
        print 'No row result.'

C# Code

var res = nodeSession.SQL("CALL my_proc()").Execute();

if (res.HasData)
{

  var row = res.FetchOne();
  if (row != null)
  {
    Console.WriteLine("List of row available for fetching.");
    do
    {
      PrintResult(row);
    } while ((row = res.FetchOne()) != null);
  }
  else
  {
    Console.WriteLine("Empty list of rows.");
  }
}
else
{
  Console.WriteLine("No row result.");
}

Java Code

SqlResult res = nodeSession.sql("CALL my_proc()").execute();

if (res.hasData()){

  Row row = res.fetchOne();
  if (row != null){
    print("List of row available for fetching.");
    do {
      System.out.println(row);
    } while ((row = res.fetchOne()) != null);
  }
  else{
    System.out.println("Empty list of rows.");
  }
}
else {
  System.out.println("No row result.");
}

C++ Code

SqlResult res = nodeSession.sql("CALL my_proc()").execute();

if (res.hasData())
{
  Row row = res.fetchOne();
  if (row)
  {
    cout << "List of row available for fetching." << endl;
    do {
      cout << "next row: ";
      for (unsigned i=0 ; i < row.colCount(); ++i)
        cout << row[i] << ", ";
      cout << endl;
    } while ((row = res.fetchOne()));
  }
  else
  {
    cout << "Empty list of rows." << endl;
  }
}
else
{
  cout << "No row result." << endl;
}

It is an error to call either fetchOne() or fetchAll() when hasResult() indicates that a SqlResult is not a data set.

MySQL Shell JavaScript Code

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.getAffectedRowCount());
  }
}

print_result(nodeSession.sql('DELETE FROM users WHERE age > 40').execute());
print_result(nodeSession.sql('SELECT * FROM users WHERE age = 40').execute());

MySQL Shell Python Code

def print_result(res):
  if res.has_data():
    # SELECT
    columns = res.get_columns()
    record = res.fetch_one()

    while record:
      index = 0

      for column in columns:
        print "%s: %s \n" % (column.get_column_name(), record[index])
        index = index + 1

      # Get the next record
      record = res.fetch_one()

  else:
    #INSERT, UPDATE, DELETE, ...
    print 'Rows affected: %s' % res.get_affected_row_count()


print_result(nodeSession.sql('DELETE FROM users WHERE age > 40').execute())
print_result(nodeSession.sql('SELECT * FROM users WHERE age = 40').execute())

C# Code

private void print_result(SqlResult res)
{
  if (res.HasData)
  {
    // SELECT
  }
  else
  {
    // INSERT, UPDATE, DELETE, ...
    Console.WriteLine("Rows affected: " + res.RecordsAffected);
  }
}

print_result(nodeSession.SQL("DELETE FROM users WHERE age > 40").Execute());
print_result(nodeSession.SQL("SELECT COUNT(*) AS oldies FROM users WHERE age = 40").Execute());

Java Code

private void print_result(SqlResult res) {
  if (res.hasData()) {
    // SELECT
  } else {
    // INSERT, UPDATE, DELETE, ...
    System.out.println("Rows affected: " + res.getAffectedItemsCount());
  }
}

print_result(nodeSession.sql("DELETE FROM users WHERE age > 40").execute());
print_result(nodeSession.sql("SELECT COUNT(*) AS oldies FROM users WHERE age = 40").execute());

C++ Code

void print_result(SqlResult &&_res)
{
  // Note: We need to store the result somewhere to be able to process it.

  SqlResult res(std::move(_res));

  if (res.hasData())
  {
    // SELECT
    std::list<Column> columns = res.getColumns();
    Row record = res.fetchOne();

    while (record)
    {
      for (unsigned index=0; index < columns.size(); ++index)
      {
        cout << columns[index].getColumnName() << ": "
             << record[index] << endl;
      }

      // Get the next record
      record = res.fetchOne();
    }

  }
  else
  {
    // INSERT, UPDATE, DELETE, ...
    // Note: getAffectedRowCount() not yet implemented in Connector/C++.
    cout << "No rows in the result" << endl;
  }
}

print_result(nodeSession.sql("DELETE FROM users WHERE age > 40").execute());
print_result(nodeSession.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 a 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 any. Once you advanced to the next result set, it replaces the previously loaded result which then becomes unavailable.

MySQL Shell JavaScript Code

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.getAffectedRowCount());
  }
}


var res = nodeSession.sql('CALL my_proc()').execute();

// Prints each returned result
var more = true;
while (more){
  print_result(res);

  more = res.nextDataSet();
}

MySQL Shell Python Code

def print_result(res):
  if res.has_data():
    # SELECT
    columns = res.get_columns()
    record = res.fetch_one()

    while record:
      index = 0

      for column in columns:
        print "%s: %s \n" % (column.get_column_name(), record[index])
        index = index + 1

      # Get the next record
      record = res.fetch_one()

  else:
    #INSERT, UPDATE, DELETE, ...
    print 'Rows affected: %s' % res.get_affected_row_count()

res = nodeSession.sql('CALL my_proc()').execute()

# Prints each returned result
more = True
while more:
  print_result(res)

  more = res.next_data_set()

C# Code

var res = nodeSession.SQL("CALL my_proc()").Execute();

if (res.HasData)
{
  do
  {
    Console.WriteLine("New resultset");
    while (res.Next())
    {
      Console.WriteLine(res.Current);
    }
  } while (res.NextResult());
}

Java Code

SqlResult res = nodeSession.sql("CALL my_proc()").execute();

C++ Code

SqlResult res = nodeSession.sql("CALL my_proc()").execute();

while (true)
{
  if (res.hasData())
  {
    cout << "List of rows in the resultset." << endl;
    for (Row row; (row = res.fetchOne());)
    {
      cout << "next row: ";
      for (unsigned i = 0; i < row.colCount(); ++i)
        cout << row[i] << ", ";
        cout << endl;
    }
  }
  else
  {
    cout << "No rows in the resultset." << endl;
  }

  if (!res.nextResult())
    break;

  cout << "Next resultset." << endl;
}

When using Node.js individual rows are returned to a callback, which has to be provided to the execute() method. To identify individual result sets you can provide a second callback, which will be called for meta data which marks the beginning of a result set.

Node.js JavaScript Code

var resultcount = 0;
var res = nodeSession.sql('CALL my_proc()').execute(function (
  function (row) {
    console.log("Row: ", row);
  }, function (meta) {
    resultcount++;
    cosole.log("Begin of result set number ", resultcount);
  }
);

The number of result sets is not know immediately after the query execution. Query results may be streamed to the client or buffered at the client. In the streaming or partial buffering mode a client cannot tell whether a query will emit more than one result set.