Documentation Home
X DevAPI User Guide
Download this Manual
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb


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

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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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.

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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:

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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

Press CTRL+C to copy
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; }