MySQL Connector/C++  2.0.4
MySQL connector library for C and C++ applications
Connector/C++ 2.0 XAPI Reference

Connector/C++ implements a plain C interface, called XAPI, which offers functionality similar to that of X DevAPI and which can be used by applications written in plain C. XAPI allows one to work with MySQL Servers implementing a document store via the X Plugin. One can also execute plain SQL queries using this API.

To get started, check out some of the main XAPI functions:

Many functions in XAPI 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:

1 mysqlx_stmt_bind(stmt, PARAM_SINT(v_sint),
2  PARAM_UINT(v_uint),
3  PARAM_FLOAT(v_float),
4  PARAM_DOUBLE(v_double),
5  PARAM_STRING(v_str),
6  PARAM_END);

There are several things to keep in mind:

A more complete example of code that access MySQL Database using the XAPI is presented below. See also the list of XAPI Functions.

Sample code which uses Connector/C++ with XAPI

The following plain C application uses XAPI to connect to a MySQL Server with X Plugin, 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 the source distribution of Connector/C++ 2.0. See Using Connector/C++ 2.0 for instructions on how to build the sample code.

Code which uses the Connector/C++ XAPI should include the mysql_xapi.h header.

1 #include <mysql_xapi.h>

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:

1 /* Error processing macros */
1 #define CRUD_CHECK(C, S) if (!C) \
2  { \
3  printf("\nError! %s", mysqlx_error_message(S)); \
4  return -1; \
5  }
6 
7 #define RESULT_CHECK(R, C) if (!R) \
8  { \
9  printf("\nError! %s", mysqlx_error_message(C)); \
10  return -1; \
11  }
12 
13 #define IS_OK(R, C) if (R != RESULT_OK) \
14  { \
15  printf("\nError! %s", mysqlx_error_message(C)); \
16  return -1; \
17  }
18 
19 #ifdef _WIN32

Start with creating a session handle using one of the mysqlx_get_node_session_xxx() functions. We create a node session to be able to execute SQL queries. Session parameters are given using mysqlx URL. Default URL specifies session on the local host as user root.

If session could not be established, mysqlx_get_node_session_from_url() returns NULL handle while error message and code are stored in provided buffers.

1  const char *url = (argc > 1 ? argv[1] : "mysqlx://root@127.0.0.1");
1  char conn_error[MYSQLX_MAX_ERROR_LEN];
1  int conn_err_code;
1  sess = mysqlx_get_node_session_from_url(url, conn_error, &conn_err_code);
1  if (!sess)
2  {
3  printf("\nError! %s. Error Code: %d", conn_error, conn_err_code);
4  return -1;
5  }

Once created, the session is ready to be used. If the session cannot be established the program prints the error and exits.

Use the session to execute SQL queries – here we create a test table. After executing a query, we check its status with the RESULT_CHECK() macro (which checks if returned result handle is not NULL).

Note
If a query is a null-terminated string the query length parameter can be MYSQLX_NULL_TERMINATED, which directs the function to determine the query string length using its own means.

1  res = mysqlx_sql(sess,
1  "DROP TABLE IF EXISTS test.crud_placeholder_test",
2  MYSQLX_NULL_TERMINATED);
3  RESULT_CHECK(res, sess);
1 
2  /* Create a test table */
3 
4  res = mysqlx_sql(sess,
5  "CREATE TABLE test.crud_placeholder_test " \
6  "(sint BIGINT, uint BIGINT UNSIGNED, flv FLOAT," \
7  "dbv DOUBLE, strv VARCHAR(255))",
8  MYSQLX_NULL_TERMINATED);
9  RESULT_CHECK(res, sess);

The plain SQL queries executed before did not have any parameters. Next thing to 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.

1  crud = mysqlx_sql_new(sess,
1  "INSERT INTO test.crud_placeholder_test " \
2  "(sint, uint, flv, dbv, strv) VALUES (?,?,?,?,?)",
3  MYSQLX_NULL_TERMINATED);
4  CRUD_CHECK(crud, sess);

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.

1  rc = mysqlx_stmt_bind(crud, PARAM_SINT(v_sint),
1  PARAM_UINT(v_uint),
2  PARAM_FLOAT(v_float),
3  PARAM_DOUBLE(v_double),
4  PARAM_STRING(v_str),
5  PARAM_END);
6  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, if the number of bound values is less than the number of placeholders, the attempt to execute such a statement will result in an error. The fact that all placeholders are bound to values is checked only when the statement is executed, not when binding the values.

1  res = mysqlx_execute(crud);
1  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.

1  db = mysqlx_get_schema(sess, "test", 1);
1  RESULT_CHECK(db, sess);
1 
2  table = mysqlx_get_table(db, "crud_placeholder_test", 1);
3  RESULT_CHECK(table, db);

Create a table INSERT statement using mysqlx_table_insert_new().

1  crud = mysqlx_table_insert_new(table);

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:

1  /* Change values to have some differences in inserted rows */
1 
2  v_sint = -232;
3  v_uint = 789;
4  v_float = 99.34f;
5  v_double = 2.84532E-5;
6  v_str = "some more text";
7 
8  rc = mysqlx_set_insert_row(crud, PARAM_SINT(v_sint),
9  PARAM_UINT(v_uint),
10  PARAM_FLOAT(v_float),
11  PARAM_DOUBLE(v_double),
12  PARAM_STRING(v_str),
13  PARAM_END);
14  IS_OK(rc, crud);
15 
16  /* Execute the query */
17 
18  res = mysqlx_execute(crud);
19  RESULT_CHECK(res, crud);

After the inserts are finished the code will read the rows. To do so execute table SELECT statement with mysqlx_table_select_limit(). This limits the number of returned rows to 500:

1  res = mysqlx_table_select_limit(table,
1  "(sint < 10) AND (UINT > 100)", 500, 0, PARAM_END);
2  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 fetch rows one by one. Function mysqlx_row_fetch_one() returns NULL handle when there are no more rows in the result:

1  while ((row = mysqlx_row_fetch_one(res)))

The actual data is read using mysqlx_get_xxx() functions. These functions return the values through the output parameters.

Along with the 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():

1  {
1  int64_t v_sint2 = 0;
2  uint64_t v_uint2 = 0;
3  float v_float2 = 0;
4  double v_double2 = 0;
5  char v_str2[256];
6  const char *col_name;
7  size_t buf_len = sizeof(v_str2);
8 
9  printf("\nRow # %d: ", ++row_num);
10 
11  IS_OK(mysqlx_get_sint(row, 0, &v_sint2), crud);
12  col_name = mysqlx_column_get_name(res, 0);
13  printf(format_64, col_name, (long long int)v_sint2);
14 
15  IS_OK(mysqlx_get_uint(row, 1, &v_uint2), crud);
16  col_name = mysqlx_column_get_name(res, 1);
17  printf(format_64, col_name, (long long int)v_uint2);
18 
19  IS_OK(mysqlx_get_float(row, 2, &v_float2), crud);
20  col_name = mysqlx_column_get_name(res, 2);
21  printf("[%s: %f]", col_name, v_float2);
22 
23  IS_OK(mysqlx_get_double(row, 3, &v_double2), crud);
24  col_name = mysqlx_column_get_name(res, 3);
25  printf("[%s: %f]", col_name, v_double2);
26 
27  IS_OK(mysqlx_get_bytes(row, 4, 0, v_str2, &buf_len), crud);
28  col_name = mysqlx_column_get_name(res, 4);
29  printf("[%s: %s [%u bytes]]", col_name, v_str2, (unsigned)buf_len);
30  }

Close the session using mysqlx_session_close(). This implicitly frees all handles associated with the session (results, rows, etc):

1  mysqlx_session_close(sess);

The complete code of the example is presented below:

/*
* Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
*
* The MySQL Connector/C++ is licensed under the terms of the GPLv2
* <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most
* MySQL Connectors. There are special exceptions to the terms and
* conditions of the GPLv2 as it is applied to this software, see the
* FLOSS License Exception
* <http://www.mysql.com/about/legal/licensing/foss-exception.html>.
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published
* by the Free Software Foundation; version 2 of the License.
*
* This program is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
* for more details.
*
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*/
#include <stdio.h>
#include <mysql_xapi.h>
/* Error processing macros */
#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");
char conn_error[MYSQLX_MAX_ERROR_LEN];
int conn_err_code;
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;
/*
Connect and create session.
We use node session to be able to execute SQL queries.
*/
sess = mysqlx_get_node_session_from_url(url, conn_error, &conn_err_code);
if (!sess)
{
printf("\nError! %s. Error Code: %d", conn_error, conn_err_code);
return -1;
}
printf("\nConnected...");
/* Drop test table if exists */
res = mysqlx_sql(sess,
"DROP TABLE IF EXISTS test.crud_placeholder_test",
MYSQLX_NULL_TERMINATED);
RESULT_CHECK(res, sess);
/* Create a test table */
res = mysqlx_sql(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...");
/* Do insert as a plain SQL with parameters */
crud = mysqlx_sql_new(sess,
"INSERT INTO test.crud_placeholder_test " \
"(sint, uint, flv, dbv, strv) VALUES (?,?,?,?,?)",
MYSQLX_NULL_TERMINATED);
CRUD_CHECK(crud, sess);
/* Provide the parameter values */
rc = mysqlx_stmt_bind(crud, PARAM_SINT(v_sint),
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
IS_OK(rc, crud);
/* Execute the query */
res = mysqlx_execute(crud);
RESULT_CHECK(res, crud);
/*
Query table using CRUD operations.
*/
db = mysqlx_get_schema(sess, "test", 1);
RESULT_CHECK(db, sess);
table = mysqlx_get_table(db, "crud_placeholder_test", 1);
RESULT_CHECK(table, db);
crud = mysqlx_table_insert_new(table);
/* Change values to have some differences in inserted rows */
v_sint = -232;
v_uint = 789;
v_float = 99.34f;
v_double = 2.84532E-5;
v_str = "some more text";
rc = mysqlx_set_insert_row(crud, PARAM_SINT(v_sint),
PARAM_UINT(v_uint),
PARAM_FLOAT(v_float),
PARAM_DOUBLE(v_double),
PARAM_STRING(v_str),
PARAM_END);
IS_OK(rc, crud);
/* Execute the query */
res = mysqlx_execute(crud);
RESULT_CHECK(res, crud);
printf("\nRows inserted...");
/* Read the rows we have just inserted, limit to 500 rows, no sorting. */
"(sint < 10) AND (UINT > 100)", 500, 0, PARAM_END);
RESULT_CHECK(res, table);
printf("\n\nReading Rows:");
while ((row = mysqlx_row_fetch_one(res)))
{
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);
IS_OK(mysqlx_get_sint(row, 0, &v_sint2), crud);
col_name = mysqlx_column_get_name(res, 0);
printf(format_64, col_name, (long long int)v_sint2);
IS_OK(mysqlx_get_uint(row, 1, &v_uint2), crud);
col_name = mysqlx_column_get_name(res, 1);
printf(format_64, col_name, (long long int)v_uint2);
IS_OK(mysqlx_get_float(row, 2, &v_float2), crud);
col_name = mysqlx_column_get_name(res, 2);
printf("[%s: %f]", col_name, v_float2);
IS_OK(mysqlx_get_double(row, 3, &v_double2), crud);
col_name = mysqlx_column_get_name(res, 3);
printf("[%s: %f]", col_name, v_double2);
IS_OK(mysqlx_get_bytes(row, 4, 0, v_str2, &buf_len), crud);
col_name = mysqlx_column_get_name(res, 4);
printf("[%s: %s [%u bytes]]", col_name, v_str2, (unsigned)buf_len);
}
printf("\nSession closed");
return 0;
}

A sample output produced by this code:

1 Connected...
2 Table created...
3 Rows inserted...
4 
5 Reading Rows:
6 Row # 1: [sint: -17] [uint: 101] [flv: 3.310000][dbv: 170000000.000000][strv: just some text [15 bytes]]
7 Row # 2: [sint: -232] [uint: 789] [flv: 99.339996][dbv: 0.000028][strv: some more text [15 bytes]]
8 Session closed