MySQL Connector/C++: Connector/C++ X DevAPI Example (original) (raw)

Connector/C++ implements the X DevAPI, as described in the X DevAPI User Guide. The X DevAPI allows one to work with the document store of MySQL Server 8 or later, communicating over the X protocol. It is also possible to execute plain SQL queries using this API.

To get started, check out some of the main X DevAPI classes:

A more complete example of code that access MySQL Document Store using the X DevAPI is presented below. See also the list of X DevAPI classes.

Sample code which uses Connector/C++ with X DevAPI

The following Connector/C++ application connects to a MySQL Server over X protocol, creates a document collection, adds a few documents to it, queries the collection and displays the result. The sample code can be found in file testapp/devapi_test.cc in the source distribution of Connector/C++. See Using Connector/C++ for instructions on how to build the sample code.

Code which uses X DevAPI should include the <[mysqlx/xdevapi.h](xdevapi%5F8h.html "The main header for MySQL Connector/C++ DevAPI.")> header. The API is declared within the mysqlx namespace:

#include

using ::std::cout;

using ::std::endl;

using namespace ::mysqlx;

The main header for MySQL Connector/C++ DevAPI.

To create a [mysqlx::Session](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Session.html "Represents a session which gives access to data stored in a data store.") object pass a connection string in URI format such as "mysqlx://mike:s3cr3t!@localhost:13009". It specifies the host and port of the MySQL Server (port can be skipped in which case the default port will be used) and the MySQL account credentials.

int main(int argc, const char* argv[])

try {

const char *url = (argc > 1 ? argv[1] : "mysqlx://root@127.0.0.1");

cout << "Creating session on " << url

<< " ..." << endl;

Session sess(url);

If the session could not be established the [mysqlx::Session](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Session.html "Represents a session which gives access to data stored in a data store.") constructor throws an error derived from [mysqlx::Error](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Error.html "Base class for connector errors.") class (which also derives from std::exception). Otherwise the session is ready to be used once created.

Note

There are alternative ways of specifying session options, for example:

Session s1("mysqlx://mike:s3cr3t!@localhost:13009");

Session s2("localhost", 13009, "mike", "s3cr3t!");

Session s3(13009, "mike", "s3cr3t!"); // session on localhost

Session s4(

SessionOption::USER, "mike",

SessionOption::PWD, "s3cr3t!",

SessionOption::HOST, "localhost",

SessionOption::PORT, 13009

);

In general the [mysqlx::Session](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Session.html "Represents a session which gives access to data stored in a data store.") constructor uses its arguments to create a [mysqlx::SessionSettings](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1SessionSettings.html "Represents session options to be passed at session creation time.") instance – see documentation of that class for possible arguments to the constructor. Enumeration [mysqlx::SessionOption](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1SessionOption.html "Session creation options.") defines all session options recognized by the connector.

Next a test schema is created and a c1 collection within that schema. They are represented by [mysqlx::Schema](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Schema.html "Represents a database schema.") and [mysqlx::Collection](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Collection.html "Represents a collection of documents in a schema.") objects, respectively:

cout << "Session accepted, creating collection..." << endl;

Schema sch = sess.createSchema("test", true);

Collection coll= sch.createCollection("c1", true);

The true parameter to the `Session::createSchema()`/`Schema::createCollection()` method specifies that the schema/collection should be re-used if it already exists (rather than throwing an error which is the default behavior).

Note

It is also possible to create a [mysqlx::Collection](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Collection.html "Represents a collection of documents in a schema.") object directly, without explicitly creating a [mysqlx::Schema](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Schema.html "Represents a database schema.") instance:

Collection coll = sess.getSchema("test").createCollection("c1",true)

Before adding documents to the collection, all the existing documents are removed first using the `Collection::remove()` method. The argument to this method is an expression which selects documents to be removed, in this case expression "true" selects all documents:

coll.remove("true").execute();

Note that the remove() method returns an operation that must be explicitly executed to take effect. When executed, operation returns a result (ignored here; the results are used later).

To insert documents use the `Collection::add()` method. Documents are described by JSON strings. Note that double quotes are required around field names and they must be escaped inside C strings unless the R"(...)" raw string literal syntax is used as in the example below. Note also how internal code block is used to delete the result when it is no longer needed:

{

Result add;

add= coll.add(R"({ "name": "foo", "age": 1 })").execute();

std::vector ids = add.getGeneratedIds();

cout << "- added doc with id: " << ids[0] << endl;

add= coll.add(R"({ "name": "bar", "age": 2, "toys": [ "car", "ball" ] })")

.execute();

ids = add.getGeneratedIds();

if (ids.size() != 0)

cout << "- added doc with id: " << ids[0] << endl;

else

cout << "- added doc" << endl;

add= coll.add(R"({

"name": "baz",

"age": 3,

"date": { "day": 20, "month": "Apr" }

})").execute();

ids = add.getGeneratedIds();

if (ids.size() != 0)

cout << "- added doc with id: " << ids[0] << endl;

else

cout << "- added doc" << endl;

add= coll.add(R"({ "_id": "myuuid-1", "name": "foo", "age": 7 })")

.execute();

ids = add.getGeneratedIds();

if (ids.size() != 0)

cout << "- added doc with id: " << ids[0] << endl;

else

cout << "- added doc" << endl;

}

Result of the add() operation is stored in the add variable to be able to read identifiers of the inserted documents that were assigned by the server. They are returned by the getGeneratedIds() method of the [mysqlx::Result](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Result.html "Represents a result of an operation that does not return data.") class.

Note

Server does not generate identifiers for documents which have an "_id" field – in that case the value of the "_id" field is used as document's identifier. These explicit identifiers are not reported by getGeneratedIds() method.

It is possible to chain several add() calls as follows: coll.add(doc1).add(doc2)...add(docN).execute(). It is also possible to pass several documents to a single add() call: coll.add(doc1, ..., docN).execute(). Another option is to pass to Collection::add() an STL container with several documents.

To query documents of a collection use the `Collection::find()` method which takes a Boolean expression that selects documents as its argument:

cout << "Fetching documents..." << endl;

DocResult docs = coll.find("age > 1 and name like 'ba%'").execute();

The result of the find() operation is stored in a variable of type [mysqlx::DocResult](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1DocResult.html "Result of an operation that returns documents.") which gives access to the returned documents that satisfy the selection criteria. These documents can be iterated using a range-for loop:

int i = 0;

for (DbDoc doc : docs)

{

cout << "doc#" << i++ << ": " << doc << endl;

Note

An alternative is to fetch documents one-by-one using the `DocResult::fetchOne()` method, for example like this:

DbDoc doc = docs.fetchOne();

for (int i = 0; doc; ++i, doc = docs.fetchOne()) { ... }

Given a [mysqlx::DbDoc](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1DbDoc.html "Represents a collection of key-value pairs where value can be a scalar or another document.") object it is possible to iterate over its fields as follows:

for (Field fld : doc)

{

cout << " field `" << fld << "`: " << doc[fld] << endl;

}

Note how DbDoc::operator[] is used to access values of document fields:

string name = doc["name"];

cout << " name: " << name << endl;

The value of a field is automatically converted to a corresponding C++ type. If the C++ type does not match the type of the field value, conversion error is thrown.

Fields which are sub-documents can be converted to the [mysqlx::DbDoc](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1DbDoc.html "Represents a collection of key-value pairs where value can be a scalar or another document.") type. The following code demonstrates how to process the "date" field which is a sub-document. Methods `DbDoc::hasField()` and `DbDoc::fieldType()` are used to examine existence and type of a field within a document.

if (doc.hasField("date") && Value::DOCUMENT == doc.fieldType("date"))

{

cout << "- date field" << endl;

DbDoc date = doc["date"];

for (Field fld : date)

{

cout << " date `" << fld << "`: " << date[fld] << endl;

}

string month = doc["date"]["month"];

int day = date["day"];

cout << " month: " << month << endl;

cout << " day: " << day << endl;

}

In case of arrays currently no conversion to C++ types is defined. However, individual elements of an array value can be accessed using operator[] or they can be iterated using a range-for loop.

if (doc.hasField("toys") && Value::ARRAY == doc.fieldType("toys"))

{

cout << "- toys:" << endl;

for (auto toy : doc["toys"])

{

cout << " " << toy << endl;

}

}

Any errors thrown by Connector/C++ derive from the [mysqlx::Error](classmysqlx%5F1%5F1abi2%5F1%5F1r0%5F1%5F1Error.html "Base class for connector errors.") type and can be processed as follows:

Base class for connector errors.

Definition: common.h:84

{

cout << "ERROR: " << err << endl;

return 1;

}

The complete code of the example is presented below:

#include

using ::std::cout;

using ::std::endl;

using namespace ::mysqlx;

int main(int argc, const char* argv[])

try {

const char *url = (argc > 1 ? argv[1] : "mysqlx://root@127.0.0.1");

cout << "Creating session on " << url

<< " ..." << endl;

Session sess(url);

{

RowResult res = sess.sql("show variables like 'version'").execute();

std::stringstream version;

version << res.fetchOne().get(1).get<string>();

int major_version;

version >> major_version;

if (major_version < 8)

{

cout << "Can work only with MySQL Server 8 or later" << endl;

cout << "Done!" << endl;

return 0;

}

}

cout << "Session accepted, creating collection..." << endl;

Schema sch = sess.createSchema("test", true);

Collection coll= sch.createCollection("c1", true);

cout << "Inserting documents..." << endl;

coll.remove("true").execute();

{

Result add;

add= coll.add(R"({ "name": "foo", "age": 1 })").execute();

std::vector ids = add.getGeneratedIds();

cout << "- added doc with id: " << ids[0] << endl;

add= coll.add(R"({ "name": "bar", "age": 2, "toys": [ "car", "ball" ] })")

.execute();

ids = add.getGeneratedIds();

if (ids.size() != 0)

cout << "- added doc with id: " << ids[0] << endl;

else

cout << "- added doc" << endl;

add= coll.add(R"({

"name": "baz",

"age": 3,

"date": { "day": 20, "month": "Apr" }

})").execute();

ids = add.getGeneratedIds();

if (ids.size() != 0)

cout << "- added doc with id: " << ids[0] << endl;

else

cout << "- added doc" << endl;

add= coll.add(R"({ "_id": "myuuid-1", "name": "foo", "age": 7 })")

.execute();

ids = add.getGeneratedIds();

if (ids.size() != 0)

cout << "- added doc with id: " << ids[0] << endl;

else

cout << "- added doc" << endl;

}

cout << "Fetching documents..." << endl;

DocResult docs = coll.find("age > 1 and name like 'ba%'").execute();

int i = 0;

for (DbDoc doc : docs)

{

cout << "doc#" << i++ << ": " << doc << endl;

for (Field fld : doc)

{

cout << " field `" << fld << "`: " << doc[fld] << endl;

}

string name = doc["name"];

cout << " name: " << name << endl;

if (doc.hasField("date") && Value::DOCUMENT == doc.fieldType("date"))

{

cout << "- date field" << endl;

DbDoc date = doc["date"];

for (Field fld : date)

{

cout << " date `" << fld << "`: " << date[fld] << endl;

}

string month = doc["date"]["month"];

int day = date["day"];

cout << " month: " << month << endl;

cout << " day: " << day << endl;

}

if (doc.hasField("toys") && Value::ARRAY == doc.fieldType("toys"))

{

cout << "- toys:" << endl;

for (auto toy : doc["toys"])

{

cout << " " << toy << endl;

}

}

cout << endl;

}

cout << "Done!" << endl;

}

{

cout << "ERROR: " << err << endl;

return 1;

}

catch (std::exception &ex)

{

cout << "STD EXCEPTION: " << ex.what() << endl;

return 1;

}

catch (const char *ex)

{

cout << "EXCEPTION: " << ex << endl;

return 1;

}

A sample output produced by this code:

Creating session on localhost, port 13009 ...

Session accepted, creating collection...

Inserting documents...

- added doc with id: AA71B4BF6B72E511BD76001E684A06F0

- added doc with id: 2885B4BF6B72E511BD76001E684A06F0

- added doc with id: 3492B4BF6B72E511BD76001E684A06F0

- added doc with id: myuuid-1

Fetching documents...

doc#0: {"_id": "AEFD9C44EB77E5116134001E684A06F0", "age": 3, "date": {"day": 20, "month": "Apr"}, "name": "baz"}

field `_id`: AEFD9C44EB77E5116134001E684A06F0

field `age`: 3

field `date`:

field `name`: baz

name: baz

- date field

date `day`: 20

date `month`: Apr

month: Apr

day: 20

doc#1: {"_id": "A0ABC08DAABAD1110C120800273BD115", "age": 2, "name": "bar", "toys": ["car", "ball"]}

field `_id`: A0ABC08DAABAD1110C120800273BD115

field `age`: 2

field `name`: bar

field `toys`: <array with 2 element(s)>

name: bar

- toys:

car

ball

Done!