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.

MySQL Shell JavaScript Code

var myColl = db.getCollection('my_collection');

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

var doc;
while (doc = res.fetchOne()) {
  print(doc);
}

MySQL Shell Python Code

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

C# Code

var myColl = db.GetCollection("my_collection");

var res = myColl.Find("name like :name").Bind("name", "L%")
  .Execute();

DbDoc doc;
while ((doc = res.FetchOne()) != null)
{
  Console.WriteLine(doc);
}

Python Code

my_coll = db.get_collection('my_collection')

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

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

Java Code

Collection myColl = db.getCollection("my_collection");

DocResult res = myColl.find("name like :name").bind("name", "L%")
  .execute();

DbDoc doc;
while ((doc = res.fetchOne()) != null) {
  System.out.println(doc);
}

C++ Code

Collection myColl = db.getCollection("my_collection");

DocResult res = myColl.find("name like :name").bind("name", "L%").execute();
DbDoc doc;
while ((doc = res.fetchOne()))
{
  cout << doc <<endl;
}

Node.js JavaScript Code

var myColl = db.getCollection('my_collection');

myColl.find('name like :name')
  .bind('name', 'L%')
  .execute()
  .then(res => {
    while (doc = res.fetchOne()) {
      console.log(doc);
    }
  });


myColl.find('name like :name')
  .bind('name', 'L%')
  .execute(function (doc) {
    console.log(doc);
  });

When using Node.js, results can also be returned to a callback function, which is passed to execute() in an asychronous manner whenever results from the server arrive.

var myColl = db.getCollection('my_collection');

myColl.find('name like :name')
  .bind('name', 'L%')
  .execute(function (doc) {
    console.log(doc);
});

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.

MySQL Shell JavaScript Code

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

var row;
while (row = myRows.fetchOne()) {
  // Accessing the fields by array
  print('Name: ' + row['name'] + '\n');

  // Accessing the fields by dynamic attribute
  print(' Age: ' + row.age + '\n');
}

MySQL Shell Python Code

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

Node.js JavaScript Code

var myRows = myTable
  .select(['name', 'age'])
  .where('name like :name')
  .bind('name','L%')
  .execute(function (row) {
    // Connector/Node.js does not support referring to row columns by their name yet.
    // One needs to access fields by their array index.
    console.log('Name: ' + row[0]);
    console.log(' Age: ' + row[1]);
  });

Alternatively, you can use callbacks:

myTable.select(['name', 'age'])
  .where('name like :name')
  .bind('name', 'L%')
  .execute()
  .then(myRows => {
    while (var row = myRows.fetchOne()) {
      // Accessing the fields by array
      console.log('Name: ' + row[0] + '\n');
      console.log('Age: ' + row[1] + '\n');
    }
  });

C# Code

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

Row row;
while ((row = myRows.FetchOne()) != null)
{
  // Accessing the fields by array
  Console.WriteLine("Name: " + row[0]);

  // Accessing the fields by name
  Console.WriteLine("Age: " + row["age"]);
}

Python Code

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

row = rows.fetch_one()
while row:
    # Accessing the fields by array
    print('Name: {0}'.format(row[0]))

    # Accessing the fields by dynamic attribute
    print('Age: {0}'.format(row['age'])

    row = rows.fetch_one()

Java Code

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

Row row;
while ((row = myRows.fetchOne()) != null) {
  // 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();

Row row;
while ((row = myRows.fetchOne()))
{
  // Connector/C++ does not support referring to row columns by their name yet.
  cout <<"Name: " << row[0] <<endl;
  cout <<" Age: " << row[1] <<endl;
  int age = row[1];
  // One needs explicit .get<int>() as otherwise operator<() is ambiguous
  bool uforty = row[age].get<int>() < 40;
  // Alternative formulation
  bool uforty = (int)row[age] < 40;
}