Batch Operations with MariaDB Connector/C

MariaDB Connector/C supports batch (bulk) operations by binding an array of parameter sets to a single prepared statement, sending many rows to the server in one execution.

C and C++ developers can use MariaDB Connector/C to perform batch (bulk) operations with MariaDB database products. A batch operation binds an array of parameter sets to a single prepared statement and sends them to the server in one execution, which is more efficient than executing the statement once per row.

Array Binding

MariaDB Connector/C implements batch operations through array binding. Instead of binding a single value to each statement parameter, the application binds an array of values per parameter and tells the connector how many rows to send:

• mysql_stmt_attr_set() with STMT_ATTR_ARRAY_SIZE sets the number of rows in the batch. This is the only attribute required for a batch.

The connector supports two ways of laying out the bound values in memory:

• Column-wise binding (the default). Without STMT_ATTR_ROW_SIZE, each parameter's buffer is treated as an array of values, one per row — a contiguous array for fixed-length types, or an array of pointers for variable-length types such as strings.

• Row-wise binding. Setting mysql_stmt_attr_set() with STMT_ATTR_ROW_SIZE to the size, in bytes, of one row makes the connector stride through an array of structures, where each row's values are stored together in a single struct.

Each bound parameter uses an indicator variable to describe its value. For null-terminated strings, the indicator is STMT_INDICATOR_NTS; other indicators include STMT_INDICATOR_NULL for NULL values and STMT_INDICATOR_DEFAULT for default values.

Code Example: Column-wise Binding

The following code inserts several contacts into the example table in a single batch using column-wise binding, the default. Each parameter is bound to its own array — for the string columns, an array of pointers — with a matching array of indicators. STMT_ATTR_ROW_SIZE is not set:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

int main(int argc, char *argv[])
{
   // Initialize Connection
   MYSQL *conn;
   if (!(conn = mysql_init(0)))
   {
      fprintf(stderr, "unable to initialize connection struct\n");
      exit(1);
   }

   // Connect to the database
   if (!mysql_real_connect(
         conn, "mariadb.example.net", "db_user", "db_user_password",
         "test", 3306, NULL, 0))
   {
      fprintf(stderr, "Error connecting to Server: %s\n", mysql_error(conn));
      mysql_close(conn);
      exit(1);
   }

   // One array per parameter. For the string columns the buffer is an array
   // of pointers, and each column has its own array of indicators.
   const char *first_names[] = { "John", "Jon", "Johnny" };
   const char *last_names[]  = { "Smith", "Smith", "Smith" };
   const char *emails[]      = { "[e-mail]", "[e-mail]", "[e-mail]" };
   char first_ind[] = { STMT_INDICATOR_NTS, STMT_INDICATOR_NTS, STMT_INDICATOR_NTS };
   char last_ind[]  = { STMT_INDICATOR_NTS, STMT_INDICATOR_NTS, STMT_INDICATOR_NTS };
   char email_ind[] = { STMT_INDICATOR_NTS, STMT_INDICATOR_NTS, STMT_INDICATOR_NTS };
   unsigned int array_size = 3;

   // Initialize and prepare the statement
   MYSQL_STMT *stmt = mysql_stmt_init(conn);
   if (mysql_stmt_prepare(stmt,
         "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)", -1))
   {
      fprintf(stderr, "Error preparing statement: %s\n", mysql_stmt_error(stmt));
      exit(1);
   }

   // Bind each parameter to its array
   MYSQL_BIND bind[3];
   memset(bind, 0, sizeof(bind));
   bind[0].buffer_type = MYSQL_TYPE_STRING;
   bind[0].buffer = first_names;
   bind[0].u.indicator = first_ind;
   bind[1].buffer_type = MYSQL_TYPE_STRING;
   bind[1].buffer = last_names;
   bind[1].u.indicator = last_ind;
   bind[2].buffer_type = MYSQL_TYPE_STRING;
   bind[2].buffer = emails;
   bind[2].u.indicator = email_ind;

   // Set only the batch size; without STMT_ATTR_ROW_SIZE the binding is column-wise
   mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &array_size);

   // Bind and execute the batch in a single call
   if (mysql_stmt_bind_param(stmt, bind) || mysql_stmt_execute(stmt))
   {
      fprintf(stderr, "Error executing batch: %s\n", mysql_stmt_error(stmt));
      exit(1);
   }

   // Close the statement and the connection
   mysql_stmt_close(stmt);
   mysql_close(conn);

   return 0;
}

Confirm the rows were inserted by using MariaDB Client to execute a SELECT statement:

SELECT * FROM test.contacts;

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  1 | John       | Smith     | [e-mail]   |
|  2 | Jon        | Smith     | [e-mail]    |
|  3 | Johnny     | Smith     | [e-mail] |
+----+------------+-----------+--------------------------+

Code Example: Row-wise Binding

The following code inserts the same contacts using row-wise binding. Each contact is stored in a struct, an indicator variable accompanies each string to mark it as null-terminated, and STMT_ATTR_ROW_SIZE is set to the size of one row:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

// One row of parameters, with an indicator variable for each string
struct st_contact {
   char first_name[25]; char first_ind;
   char last_name[25];  char last_ind;
   char email[100];     char email_ind;
};

int main(int argc, char *argv[])
{
   // Initialize Connection
   MYSQL *conn;
   if (!(conn = mysql_init(0)))
   {
      fprintf(stderr, "unable to initialize connection struct\n");
      exit(1);
   }

   // Connect to the database
   if (!mysql_real_connect(
         conn, "mariadb.example.net", "db_user", "db_user_password",
         "test", 3306, NULL, 0))
   {
      fprintf(stderr, "Error connecting to Server: %s\n", mysql_error(conn));
      mysql_close(conn);
      exit(1);
   }

   // The batch of rows to insert. Each string is null-terminated,
   // so its indicator is STMT_INDICATOR_NTS.
   struct st_contact data[] = {
      { "John",   STMT_INDICATOR_NTS, "Smith", STMT_INDICATOR_NTS, "[e-mail]",   STMT_INDICATOR_NTS },
      { "Jon",    STMT_INDICATOR_NTS, "Smith", STMT_INDICATOR_NTS, "[e-mail]",    STMT_INDICATOR_NTS },
      { "Johnny", STMT_INDICATOR_NTS, "Smith", STMT_INDICATOR_NTS, "[e-mail]", STMT_INDICATOR_NTS }
   };
   unsigned int array_size = 3;
   size_t row_size = sizeof(struct st_contact);

   // Initialize and prepare the statement
   MYSQL_STMT *stmt = mysql_stmt_init(conn);
   if (mysql_stmt_prepare(stmt,
         "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)", -1))
   {
      fprintf(stderr, "Error preparing statement: %s\n", mysql_stmt_error(stmt));
      exit(1);
   }

   // Bind the first row; the connector strides through the array using row_size
   MYSQL_BIND bind[3];
   memset(bind, 0, sizeof(bind));
   bind[0].buffer_type = MYSQL_TYPE_STRING;
   bind[0].buffer = &data[0].first_name;
   bind[0].u.indicator = &data[0].first_ind;
   bind[1].buffer_type = MYSQL_TYPE_STRING;
   bind[1].buffer = &data[0].last_name;
   bind[1].u.indicator = &data[0].last_ind;
   bind[2].buffer_type = MYSQL_TYPE_STRING;
   bind[2].buffer = &data[0].email;
   bind[2].u.indicator = &data[0].email_ind;

   // Set the batch size and the row stride
   mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &array_size);
   mysql_stmt_attr_set(stmt, STMT_ATTR_ROW_SIZE, &row_size);

   // Bind and execute the batch in a single call
   if (mysql_stmt_bind_param(stmt, bind) || mysql_stmt_execute(stmt))
   {
      fprintf(stderr, "Error executing batch: %s\n", mysql_stmt_error(stmt));
      exit(1);
   }

   // Close the statement and the connection
   mysql_stmt_close(stmt);
   mysql_close(conn);

   return 0;
}

This produces the same result as the column-wise example above — the two approaches differ only in how the values are laid out in memory, not in what is inserted.

See Also

For additional batch insert examples, see Prepared Statement Examples:

• Bulk Insert (Row-wise Binding)

• Bulk Insert (Column-wise Binding)