Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  Working with Result Sets  /  Fetching All Data Items at Once

9.4 Fetching All Data Items at Once

Data sets feature two iteration patterns available with all Connectors. The first pattern using fetchOne() enables applications to consume data items one by one. The second pattern using fetchAll() passes all data items of a data set as a list to the application. Drivers use appropriate data types of 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 example assumes that the test schema exists and that the employee table exists in myTable

MySQL Shell JavaScript Code

var myResult =['name', 'age']).
  where('name like :name').bind('name','S%').

var myRows = myResult.fetchAll();

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

MySQL Shell Python Code

myResult =['name', 'age']) \
  .where('name like :name').bind('name','S%') \

myRows = myResult.fetch_all()

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

C# Code

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

Python Code

result =['name', 'age']) \
    .where('name like :name').bind('name', 'S%') \

rows = result.fetch_all()

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

Java Code

RowResult myRows ="name, age")
  .where("name like :name").bind("name", "S%")

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

C++ Code

RowResult myRows ="name, age")
                          .where("name like :name")
                          .bind("name", "S%")

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

// Directly iterate over rows, without stroing 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.

Asynchronous query executions return control to caller once a query has been issued and prior to receiving any reply from the server. Calling fetchAll() to read the data items produced by an asynchronous query execution may block the caller. fetchAll() cannot return control to the caller before reading results from the server is finished.