Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  Working with Result Sets  /  Working with Data Sets

Pre-General Availability Draft: 2017-10-20

9.4 Working with Data Sets

Operations that fetch data items return a data set as opposed to operations that modify data and return a result set. Data items can be read from the database using Collection.find(), Table.select() and NodeSession.sql(). All three methods return data sets which encapsulate data items. Collection.find() returns a data set with documents and Table.select() respectively NodeSession.sql() return a data set with rows.

All data 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 usning 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. Consult your language's Connector reference for more details, see Additional Documentation.

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 which implement the X DevAPI if the language supports 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. An 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','S%').
        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','S%').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", "S%")
  .Execute();

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

Java Code

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

DocResult res = myColl.find("name like :name").bind("name", "S%")
  .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", "S%").execute();
DbDoc doc;
while ((doc = res.fetchOne()))
{
  cout <<*doc <<endl;
}

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

Node.js JavaScript Code

myColl.find('name like :name').bind('S%').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 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','S%').
        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','S%').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','S%').
	execute(function (row) {

  // Accessing the fields by array
  console.log('Name: ' + row['name']);

  // Accessing the fields by dynamic attribute
  console.log(' Age: ' + row.age);
});

C# Code

var myRows = myTable.Select("name", "age")
  .Where("name like :name").Bind("name", "S%")
  .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"]);
}

Java Code

RowResult myRows = myTable.select("name, age")
  .where("name like :name").bind("name", "S%")
  .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", "S%")
                          .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 young = row[age].get<int>() < 18;
  // Alternative formulation
  bool young = (int)row[age] < 18;
}

User Comments
Sign Up Login You must be logged in to post a comment.