Connector/C++ implements a variant of X DevAPI that can be used by applications written in plain C - the X DevAPI for C. It 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 for C functions:
- To access data first create a session using one of `mysqlx_get_session_xxx()` functions. These functions return a pointer to an opaque session handle of type
mysqlx_session_t.
- To work with documents in a collection or rows in a table, create a handle to the collection or the table using the
mysqlx_get_collection() or the mysqlx_get_table() function. One first needs to obtain a schema handle with the mysqlx_get_schema() function.
- Given a handle to a collection or a table object, one can execute queries and statements on that object directly, using functions like
mysqlx_collection_find(), or one can create a statement handle with a function like mysqlx_collection_find_new() and execute it later with mysqlx_execute() after specifying additional statement parameters.
- Collection or table statements can use named parameters. Values of these parameters must be defined using
mysqlx_stmt_bind() before statement is executed.
- To execute SQL, use the function
mysqlx_sql(). SQL queries can contain ? placeholders whose values need to be specified either with mysqlx_stmt_bind(), prior to query execution, or directly in the function mysqlx_sql_param() which executes such a query.
- Executing a statement produces a result handle of type
mysqlx_result_t. It can be used to fetch result's data with functions mysqlx_row_fetch_one() or mysqlx_json_fetch_one(). It can be also used to examine the result metadata (in case of results of table queries) with the `mysqlx_column_get_xxx` family of functions.
- Documents are represented as JSON strings. When getting data from rows with `mysqlx_get_xxx()` functions, conversion from database types to the indicated C native type is performed. Both types must match, otherwise result might be corrupted (there are no automatic type conversions). It is also possible to get raw bytes representing the value using the
mysqlx_get_bytes() function.
- Data items are fetched from the result one-by-one without storing complete result in memory. It is however possible to buffer a complete result with the
mysqlx_store_result() function.
Many functions in X DevAPI for C have a variable parameters list allowing passing arguments in a flexible way. A good illustration of this approach is binding values to ? placeholders in an SQL query. The function mysqlx_stmt_bind() can bind the corresponding values to all parameters in just one call. For example:
mysqlx_stmt_bind(stmt, PARAM_SINT(v_sint),
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
There are several things to keep in mind:
- In order to be able to correctly recognize the native parameter type in the parameter list each parameter must carry the type information. This is done using
PARAM_TTT() macros. It is important to use the correct macro for each type such as PARAM_DOUBLE(v_double) can only be used if v_double is declared to have type double or PARAM_STRING(v_str) can only be used on v_str declared as char*.
PARAM_END must be given as the last parameter to indicate the end of the variable parameters list. Failure to do so cannot be detected at the build time, but it will most likely result in an abnormal program termination.
A more complete example of code that access MySQL Database using the X DevAPI for C is presented below. See also the list of X DevAPI for C Functions.
Sample code which uses Connector/C++ with X DevAPI for C
The following plain C code uses X DevAPI for C to connect to a MySQL Server over X protocol, create a table, add a few rows into it using different ways such as plain SQL with parameters and table INSERT statements. Next, the code reads the table rows and displays the result.
The sample code can be found in file testapp/xapi_test.cc in Connector/C++ source tree. See Using Connector/C++ for instructions on how to build the sample code.
Code which uses the Connector/C++ X DevAPI for C should include the <mysqlx/xapi.h> header.
The main header for MySQL Connector/C++ X DevAPI for C.
Checking for errors is an essential part of any program, but in this sample code it can take too much space, therefore we introduce the convenience macros that check for statement errors, result errors and general errors:
#define CRUD_CHECK(C, S) if (!C) \
{ \
printf("\nError! %s", mysqlx_error_message(S)); \
return -1; \
}
#define RESULT_CHECK(R, C) if (!R) \
{ \
printf("\nError! %s", mysqlx_error_message(C)); \
return -1; \
}
#define IS_OK(R, C) if (R != RESULT_OK) \
{ \
printf("\nError! %s", mysqlx_error_message(C)); \
return -1; \
}
We start with creating a session handle using one of the `mysqlx_get_session_xxx()` functions. Session parameters are specified within a mysqlx 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. If session could not be established, mysqlx_get_session_from_url() returns NULL handle while error message and code are stored in provided buffers.
const char *url = (argc > 1 ? argv[1] : "mysqlx://root@127.0.0.1");
mysqlx_session_t * mysqlx_get_session_from_url(const char *conn_string, mysqlx_error_t **error)
Create a session using connection string or URL.
if (!sess)
{
return -1;
}
const char * mysqlx_error_message(void *obj)
Get the error message from the object.
unsigned int mysqlx_error_num(void *obj)
Get the error number from the object.
void mysqlx_free(void *obj)
Free the allocated handle explicitly.
- Note
- There are alternative ways of creating a session and specifying its options such as
mysqlx_get_session() and mysqlx_get_session_from_options() functions. For example: sess = mysqlx_get_session("localhost", 13009, "mike", "s3cr3t!", "db", &error);
// Using a handle to an options object
opts = mysqlx_session_options_new();
mysqlx_session_option_set(opts,
OPT_HOST("localhost"), OPT_PORT(13009),
OPT_USER("mike"), OPT_PWD("s3cr3t!"),
PARAM_END
);
sess = mysqlx_get_session_from_options(opts, &error);
Next we execute SQL statements to create the test schema and (re-)create the crud_placeholder_test table in it. After executing a query, we check its status with the RESULT_CHECK() macro (which checks if returned result handle is not NULL).
"CREATE SCHEMA IF NOT EXISTS test",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
"DROP TABLE IF EXISTS test.crud_placeholder_test",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
"CREATE TABLE test.crud_placeholder_test " \
"(sint BIGINT, uint BIGINT UNSIGNED, flv FLOAT," \
"dbv DOUBLE, strv VARCHAR(255))",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
printf("\nTable created...");
mysqlx_result_t * mysqlx_sql(mysqlx_session_t *sess, const char *query, size_t query_len)
Execute a plain SQL query.
- Note
- If a query is a null-terminated string the query length parameter can be
MYSQLX_NULL_TERMINATED, in which case query length is determined by the function.
The plain SQL queries executed before did not have any parameters. Next thing we will do is to execute an SQL insert statement with ? placeholders. One way of doing this is to first create a statement handle with mysqlx_sql_new(). Such statement is not executed right away – it will be executed later, after binding the placeholder values first.
"INSERT INTO test.crud_placeholder_test " \
"(sint, uint, flv, dbv, strv) VALUES (?,?,?,?,?)",
MYSQLX_NULL_TERMINATED);
CRUD_CHECK(crud, sess);
mysqlx_stmt_t * mysqlx_sql_new(mysqlx_session_t *sess, const char *query, uint32_t length)
Create a statement which executes a plain SQL query.
The placeholder values for the above statement are specified through a call to the mysqlx_stmt_bind() function. Parameters list consists of <type, value> pairs. We use convenience macros PARAM_TTT() to generate such pairs. The list of placeholder values is terminated with PARAM_END.
int mysqlx_stmt_bind(mysqlx_stmt_t *stmt,...)
Bind values for parametrized statements.
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
IS_OK(rc, crud);
- Note
- The type identifier of the parameter must correspond to the actual C type of the value. Otherwise the parameters stack might be corrupted. For instance
MYSQLX_TYPE_SINT indicates that the next parameter will have the type of signed 64-bit integer (in this case v_sint declared as int64_t).
Once the placeholder values are specified, the statement can be executed with mysqlx_execute(). Attempt to execute statement with placeholders without binding values to these placeholders will result in an error. Similar, an error will be reported if the number of bound values is less than the number of placeholders. The fact that all placeholders are bound to values is checked only when the statement is executed, not when binding the values.
mysqlx_result_t * mysqlx_execute(mysqlx_stmt_t *stmt)
Execute a statement.
RESULT_CHECK(res, crud);
For the purpose of demonstration we will insert the next row using a specialized table INSERT statement. First, get a handle to the table object with mysqlx_get_schema() and mysqlx_get_table() functions. The third parameter to these functions is a Boolean flag specifying if an error should be reported if indicated objects do not exist in the database. Without such a check it is possible to create a handle to a non-existing object but then the handle creation is cheap and does not involve any communication with the database. Errors would be reported later, when one attempts to execute a statement for such a non-existent handle.
mysqlx_schema_t * mysqlx_get_schema(mysqlx_session_t *sess, const char *schema_name, unsigned int check)
Get a schema object and optionally check if it exists on the server.
RESULT_CHECK(db, sess);
RESULT_CHECK(table, db);
mysqlx_table_t * mysqlx_get_table(mysqlx_schema_t *schema, const char *tab_name, unsigned int check)
Get a table object and optionally check if it exists in the schema.
Create a table INSERT statement using mysqlx_table_insert_new().
mysqlx_stmt_t * mysqlx_table_insert_new(mysqlx_table_t *table)
Create a statement executing a table INSERT operation.
The next step is to provide the row data using the mysqlx_set_insert_row() function, which can be called multiple times on the same statement handle before the statement is executed. In this way a multi-row insert will be performed. In our example we will do a single-row insert. Same as in case of mysqlx_stmt_bind() the row data is specified as <type, value> pairs and the list of function parameters is terminated by PARAM_END. After specifying the row data, the insert statement is executed:
v_sint = -232;
v_uint = 789;
v_float = 99.34f;
v_double = 2.84532E-5;
v_str = "some more text";
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
IS_OK(rc, crud);
RESULT_CHECK(res, crud);
printf("\nRows inserted...");
int mysqlx_set_insert_row(mysqlx_stmt_t *stmt,...)
Specify a row to be added by an INSERT statement.
After the inserts are finished the code will read the rows. To do so we execute table SELECT statement using the mysqlx_table_select_limit() function. We pass the following arguments to the function:
- the handle of the table from which we want to select rows
- a Boolean expression that selects the rows
- limit on the number of returned rows (500)
- how many initial rows to skip in the result (do not skip any rows)
- sorting order specification (no sorting).
The sorting specification can consist of several sort expressions added as additional function arguments terminated by PARAM_END macro. Here we do not specify any particular sorting order so there are no sort expressions.
mysqlx_result_t * mysqlx_table_select_limit(mysqlx_table_t *table, const char *criteria, uint64_t row_count, uint64_t offset,...)
Execute a table SELECT statement with a WHERE, ORDER BY and LIMIT clauses.
"(sint < 10) AND (UINT > 100)", 500, 0, PARAM_END);
RESULT_CHECK(res, table);
Note that mysqlx_table_select_limit() executes the statement right away, returning its result. Before we were first creating a statement handle, then adding information to the statement and then executing it.
Now we fetch rows one by one from the result returned by mysqlx_table_select_limit(). Function mysqlx_row_fetch_one() returns NULL handle when there are no more rows in the result:
mysqlx_row_t * mysqlx_row_fetch_one(mysqlx_result_t *res)
Fetch one row from the result.
The actual data is read using `mysqlx_get_xxx()` functions. These functions return the values through the output parameters. Along with the column data the code can get metadata such as the column names. In this example we just print the column name returned by mysqlx_column_get_name():
{
int64_t v_sint2 = 0;
uint64_t v_uint2 = 0;
float v_float2 = 0;
double v_double2 = 0;
char v_str2[256];
const char *col_name;
size_t buf_len = sizeof(v_str2);
printf("\nRow # %d: ", ++row_num);
printf(format_64, col_name, (long long int)v_sint2);
printf(format_64, col_name, (long long int)v_uint2);
printf("[%s: %f]", col_name, v_float2);
printf("[%s: %f]", col_name, v_double2);
printf("[%s: %s [%u bytes]]", col_name, v_str2, (unsigned)buf_len);
}
const char * mysqlx_column_get_name(mysqlx_result_t *res, uint32_t pos)
Get column name.
int mysqlx_get_float(mysqlx_row_t *row, uint32_t col, float *val)
Get a float number from a row.
int mysqlx_get_uint(mysqlx_row_t *row, uint32_t col, uint64_t *val)
Get an unsigned integer number from a row.
int mysqlx_get_bytes(mysqlx_row_t *row, uint32_t col, uint64_t offset, void *buf, size_t *buf_len)
Read bytes stored in a row into a pre-allocated buffer.
int mysqlx_get_double(mysqlx_row_t *row, uint32_t col, double *val)
Get a double number from a row.
int mysqlx_get_sint(mysqlx_row_t *row, uint32_t col, int64_t *val)
Get a signed integer number from a row.
Finally we close the session using mysqlx_session_close(). This implicitly frees all handles associated with the session (results, rows, etc):
void mysqlx_session_close(mysqlx_session_t *session)
Close the session.
The complete code of the example is presented below:
#include <stdio.h>
#include <string.h>
#define CRUD_CHECK(C, S) if (!C) \
{ \
printf("\nError! %s", mysqlx_error_message(S)); \
return -1; \
}
#define RESULT_CHECK(R, C) if (!R) \
{ \
printf("\nError! %s", mysqlx_error_message(C)); \
return -1; \
}
#define IS_OK(R, C) if (R != RESULT_OK) \
{ \
printf("\nError! %s", mysqlx_error_message(C)); \
return -1; \
}
#ifdef _WIN32
#define format_64 "[%s: %I64d] "
#else
#define format_64 "[%s: %lld] "
#endif
int main(int argc, const char* argv[])
{
const char *url = (argc > 1 ? argv[1] : "mysqlx://root@127.0.0.1");
int64_t v_sint = -17;
uint64_t v_uint = 101;
float v_float = 3.31f;
double v_double = 1.7E+08;
const char *v_str = "just some text";
int rc = 0;
int row_num = 0;
if (!sess)
{
return -1;
}
printf("\nConnected...");
{
"show variables like 'version'",
MYSQLX_NULL_TERMINATED);
size_t len=1024;
char buffer[1024];
return -1;
int major_version;
major_version = atoi(buffer);
if (major_version < 8)
{
printf("\nWorks only with MySQL Server 8 or later\n");
return 0;
}
}
"CREATE SCHEMA IF NOT EXISTS test",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
"DROP TABLE IF EXISTS test.crud_placeholder_test",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
"CREATE TABLE test.crud_placeholder_test " \
"(sint BIGINT, uint BIGINT UNSIGNED, flv FLOAT," \
"dbv DOUBLE, strv VARCHAR(255))",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
printf("\nTable created...");
"INSERT INTO test.crud_placeholder_test " \
"(sint, uint, flv, dbv, strv) VALUES (?,?,?,?,?)",
MYSQLX_NULL_TERMINATED);
CRUD_CHECK(crud, sess);
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
IS_OK(rc, crud);
RESULT_CHECK(res, crud);
RESULT_CHECK(db, sess);
RESULT_CHECK(table, db);
v_sint = -232;
v_uint = 789;
v_float = 99.34f;
v_double = 2.84532E-5;
v_str = "some more text";
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
IS_OK(rc, crud);
RESULT_CHECK(res, crud);
printf("\nRows inserted...");
"(sint < 10) AND (UINT > 100)", 500, 0, PARAM_END);
RESULT_CHECK(res, table);
printf("\n\nReading Rows:");
{
int64_t v_sint2 = 0;
uint64_t v_uint2 = 0;
float v_float2 = 0;
double v_double2 = 0;
char v_str2[256];
const char *col_name;
size_t buf_len = sizeof(v_str2);
printf("\nRow # %d: ", ++row_num);
printf(format_64, col_name, (long long int)v_sint2);
printf(format_64, col_name, (long long int)v_uint2);
printf("[%s: %f]", col_name, v_float2);
printf("[%s: %f]", col_name, v_double2);
printf("[%s: %s [%u bytes]]", col_name, v_str2, (unsigned)buf_len);
}
printf("\nSession closed");
return 0;
}
#define RESULT_OK
Return value indicating function/operation success.
Definition: xapi.h:103
struct mysqlx_result_struct mysqlx_result_t
Type of result handles.
Definition: xapi.h:301
struct mysqlx_row_struct mysqlx_row_t
Type of row handles.
Definition: xapi.h:288
struct mysqlx_stmt_struct mysqlx_stmt_t
Type of statement handles.
Definition: xapi.h:277
struct mysqlx_session_struct mysqlx_session_t
Type of session handles.
Definition: xapi.h:205
struct mysqlx_error_struct mysqlx_error_t
Type of error handles.
Definition: xapi.h:196
struct mysqlx_schema_struct mysqlx_schema_t
Type of database schema handles.
Definition: xapi.h:244
struct mysqlx_table_struct mysqlx_table_t
Type of table handles.
Definition: xapi.h:261
A sample output produced by this code:
Connected...
Table created...
Rows inserted...
Reading Rows:
Row # 1: [sint: -17] [uint: 101] [flv: 3.310000][dbv: 170000000.000000][strv: just some text [15 bytes]]
Row # 2: [sint: -232] [uint: 789] [flv: 99.339996][dbv: 0.000028][strv: some more text [15 bytes]]
Session closed