9.3 Working with Data Sets

Operations that fetch data items return a cursor that can be used to consume those data items from the result set. Data items can be read from the database using Collection.find(), Table.select() and Session.sql(). Collection.find() returns a data set with documents and Table.select() respectively Session.sql() return a data set with rows.

All result sets implement a unified way of iterating their data items. The unified syntax supports fetching items one by one using fetchOne() or retrieving a list of all items using fetchAll(). fetchOne() and fetchAll() follow forward-only iteration semantics. Connectors implementing the X DevAPI can offer more advanced iteration patterns on top to match common native language patterns.

The following example shows how to access the documents returned by a Collection.find() operation by using fetchOne() to loop over all documents.

The first call to fetchOne() returns the first document found. All subsequent calls increment the internal data item iterator cursor by one position and return the item found making the second call to fetchOne() return the second document found, if any. When the last data item has been read and fetchOne() is called again, a NULL value is returned. This ensures that the basic while loop shown works with all languages that support such an implementation.

When using fetchOne(), it is not possible to reset the internal data item cursor to the first data item to start reading the data items again. A data item (here a Document) that has been fetched once using fetchOne() can be discarded by the Connector. The data item's life time is decoupled from the data set. From a Connector perspective items are consumed by the caller as they are fetched. This example assumes that the test schema exists.

myColl = db.get_collection('my_collection')

res = myColl.find('name like :name').bind('name','L%').execute()

doc = res.fetch_one()
while doc:
        print(doc)
        doc = res.fetch_one()

The following example shows how to directly access the rows returned by a Table.select() operation. The basic code pattern for result iteration is the same. The difference between the following and the previous example is in the data item handling. Here, fetchOne() returns Rows. The exact syntax to access the column values of a Row is language dependent. Implementations seek to provide a language native access pattern. The example assumes that the test schema exists and that the employee table exists in myTable.

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

row = myRows.fetch_one()
while row:
        # Accessing the fields by array
        print('Name: %s\n' % row[0])
        # Accessing the fields by dynamic attribute
        print('Age: %s\n' % row.age)
        row = myRows.fetch_one()