9.4 Fetching All Data Items at Once

In addition to the pattern of using fetchOne() explained at Section 9.3, “Working with Data Sets”, which enables applications to consume data items one by one, X DevAPI also provides a pattern using fetchAll(), which passes all data items of a data set as a list to the application. The different X DevAPI implementations use appropriate data types for their programming language for the list. Because different data types are used, the language's native constructs are supported to access the list elements. The following example assumes that the test schema exists and that the employee table exists in myTable.

MySQL Shell JavaScript Code

var myResult = myTable.select(['name', 'age']).
  where('name like :name').bind('name','L%').
  execute();

var myRows = myResult.fetchAll();

for (index in myRows){
  print (myRows[index].name + " is " + myRows[index].age + " years old.");
}

MySQL Shell Python Code

myResult = myTable.select(['name', 'age']) \
  .where('name like :name').bind('name','L%') \
  .execute()

myRows = myResult.fetch_all()

for row in myRows:
  print("%s is %s years old." % (row.name, row.age))

Node.js JavaScript Code

myTable.select(['name', 'age'])
  .where('name like :name')
  .bind('name', 'L%')
  .execute()
  .then(myResult => {
    var myRows = myResult.fetchAll();

    myRows.forEach(row => {
      console.log(`${row[0]} is ${row[1]} years old.`);
    });
  });

C# Code

var myRows = myTable.Select("name", "age")
  .Where("name like :name").Bind("name", "L%")
  .Execute();
var rows = myRows.FetchAll();

Python Code

result = myTable.select(['name', 'age']) \
    .where('name like :name').bind('name', 'L%') \
    .execute()

rows = result.fetch_all()

for row in rows:
    print("{0} is {1} years old.".format(row["name"], row["age"]))

Java Code

RowResult myRows = myTable.select("name, age")
  .where("name like :name").bind("name", "L%")
  .execute();

List<Row> rows = myRows.fetchAll();
for (Row row : rows) {
  // Accessing the fields
  System.out.println(" Age: " + row.getInt("age") + "\n");
}

C++ Code

RowResult myRows = myTable.select("name, age")
                          .where("name like :name")
                          .bind("name", "L%")
                          .execute();

std::list<Row> rows = myRows.fetchAll();
for (Row row : rows)
{
  cout << row[1] << endl;
}

// Directly iterate over rows, without storing them in a container

for (Row row : myRows.fetchAll())
{
  cout << row[1] << endl;
}

When mixing fetchOne() and fetchAll() to read from one data set keep in mind that every call to fetchOne() or fetchAll() consumes the data items returned. Items consumed cannot be requested again. If, for example, an application calls fetchOne() to fetch the first data item of a data set, then a subsequent call to fetchAll() returns the second to last data item. The first item is not part of the list of data items returned by fetchAll(). Similarly, when calling fetchAll() again for a data set after calling it previously, the second call returns an empty collection.

The use of fetchAll() forces a Connector to build a list of all items in memory before the list as a whole can be passed to the application. The life time of the list is independent from the life of the data set that has produced it.