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.

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))

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.