MySQL C API tutorial

The MySQL database has support for the C programming language via the libmysqlclient library. The library defines the MySQL C API which C applications (called MySQL clients) may use for all types of interactions with a MySQL database server.

This tutorial presents how to use the MySQL C API with several examples.

Note
Today, the MariaDB database is used as an alternative to MySQL on most Linux distributions. The two are relatively compatible and whether using library software from MySQL or MariaDB, the C API stays the same. The examples in this tutorial use development files from MariaDB.

Development files and compiler flags

Installation of the development files

Using the MySQL C API requires the installation of the development files package which provides the headers, object files, …

On RPM-based Linux distributions (Fedora, CentOS, …), the package may be installed using the following command:

yum install mariadb-devel

On Deb-based Linux distributions (Debian, Ubuntu, …), the package may be installed using the following command:

apt-get install libmariadb-dev

Compiler flags

When working with the MySQL C API, the compiler needs to be configured to add the location of the API header files (mysql.h, …) and link the output against the libmysqlclient library.

The compiler flags may be set manually like the following:

gcc -I/usr/include/mysql -L/usr/lib64/mysql -lmysqlclient -lpthread -lz -lm -ldl -lssl -lcrypto -o mysql_client mysql_client.c

The recommended way is to use the mysql_config command to generate the flags dynamically like the following:

gcc `mysql_config --cflags --libs`  -o mysql_client mysql_client.c

Sample database

The examples in this tutorial use a sample database that is called USERS_DB. It has a table called USERS_TBL with the following columns:

  • ID: an automatically generated integer
  • LOGIN: a string of characters
  • PASSWORD: a string of characters
  • PHOTO: a binary image file

The examples use a database user to interact with the database server (root user may be used in the development environment). It is called USERS_DB_OWNER and has the password 1234.

The database, the table and the user may be created using the following SQL statements (to be executed using the root user on the MySQL console or the phpMyAdmin portal for example):

CREATE DATABASE USERS_DB;
USE USERS_DB;
CREATE TABLE USERS_TBL(
  ID       INTEGER AUTO_INCREMENT PRIMARY KEY,
  LOGIN    VARCHAR(100) UNIQUE NOT NULL,
  PASSWORD VARCHAR(100) NOT NULL,
  PHOTO    LONGBLOB
);
GRANT ALL ON USERS_DB.* to USERS_DB_OWNER@'%' IDENTIFIED BY '1234';

First example

The first example presents how to include the necessary headers and call an API function.
The function mysql_get_client_info() returns a string that contains the client’s (library) version information.

#include <my_global.h>
#include <mysql.h>

int main(int argc, char *argv[]){
  printf("The MySQL client version is: %s\n", mysql_get_client_info());
}

The header my_global.h is not necessary but it includes some useful headers like the standard C library headers (stdio.h, …). The header mysql.h is mandatory as it defines the API.

Error management

The API defines two functions to report the error code and message from the most recently invoked API function that failed.

The function mysql_errno() returns the error code. Server error message numbers are listed in mysqld_error.h. It takes a connection handle as an argument ( described in a later section).

unsigned int mysql_errno(MYSQL *mysql);

The function mysql_error() returns a string containing the error message. It takes a connection handle as an argument.

const char *mysql_error(MYSQL *mysql);

The remaining sections provide several examples.

Connection and authentication

The first step to communicate with a MySQL server is to open a connection. The connection requires authentication via a user and a password (Unix sockets are also supported).

The connection handle

The connection is represented by an object of type MYSQL (called a handle) and must first be initialized by the function mysql_init() before it is used.

MYSQL *mysql_init(MYSQL *mysql);

When mysql_init() is called with the NULL argument, it allocates a new MYSQL object then initializes it. When called with a pointer to an existing MYSQL object, this later is initialized.
It returns a pointer to the connection handle.

Opening and closing a connection

After initializing the connection handle, the function mysql_real_connect() is called to open a connection to the MySQL server. If the authentication succeeds, the handle is used by the other API functions to execute SQL statements and retrieve results.

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag);

The arguments are:

  • mysql: a pointer to the connection handle
  • host: a string containing the IP address or DNS name of the MySQL server
  • user: a string containing the database user to use
  • password: a string containing the user’s password
  • db: a string containing the database name to connect to
  • port: the MySQL server’s TCP port number (3306 by default)
  • unix_socket: a string containing the path to the MySQL server Unix socket file. This is used when the connection is open via a Unix socket (generally this is set to NULL)
  • client_flag: this is used to set some additional parameters. It is discussed in a later section (generally set to 0)

It returns a pointer to the connection handle if the operation succeeds, otherwise the NULL value.

When the connection handle is no longer used, it may be closed by calling the function mysql_close() to release resources (memory, …).

void mysql_close(MYSQL *mysql);

Example: mysql_init() / mysql_real_connect() / mysql_close()

#include <my_global.h>
#include <mysql.h>

int main(int argc, char *argv[]){
  MYSQL *con;
  con = mysql_init(NULL);

  if(mysql_real_connect(con, "127.0.0.1", "USERS_DB_OWNER", "1234", "USERS_DB", 3306, NULL, 0) == NULL)
  {
    printf("The authentication failed with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  printf("The connection is open\n");

  mysql_close(con);
}

Execution of SQL statements

The API has two functions to execute SQL statements. The function mysql_query() is used to execute statements that contain only textual data. For statements that contain binary data, the function mysql_real_query() is used.

These two functions execute only one statement per call. A later section explains how to execute multiple statements (separated by “;”).

This section presents how to execute SQL statements. A later section presents how to fetch the result rows.

Execution of textual SQL statements

The function mysql_query() is called with the connection handle and a string that contains the SQL statement to execute. It returns zero on success, otherwise a non-zero value.

int mysql_query(MYSQL *mysql, const char *stmt_str);

Example: mysql_query()

The following example presents how to insert a new row in the USERS_TBL table.

#include <my_global.h>
#include <mysql.h>

int main(int argc, char *argv[]){
  MYSQL *con;
  con = mysql_init(NULL);
  char login[] = "John";
  char password[] = "1234";
  char sql_statement[2048];

  if( mysql_real_connect(con, "127.0.0.1", "USERS_DB_OWNER", "1234", "USERS_DB", 3306, NULL, 0) == NULL)
  {
    printf("The authentication failed with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  // Construct the SQL statement
  sprintf(sql_statement, "INSERT INTO USERS_TBL(LOGIN, PASSWORD) VALUES('%s', '%s')", login, password);
  if(mysql_query(con, sql_statement) !=0)
  {
    printf("Query failed  with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  printf("User %s inserted into the database\n", login);

  mysql_close(con);
}

Execution of SQL statements with binary data

The mysql_real_query() function has a similar function as mysql_query() except that the query argument may contain binary data and has a third argument that specifies the length of this later. The query argument is not a null-terminated string (may contain binary data), which explains the need for the length argument.

int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length);

Before adding binary data into the SQL statement, it has to be escaped by the helper function mysql_real_escape_string().

unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length);

This function takes four arguments:

  • mysql : the connection handle
  • to : the memory buffer where the result will be stored (escaped sequence)
  • from : the memory buffer that contains the binary data
  • length : the size of the binary data to escape

The principal is that we put the binary data in the middle of the SQL statement only after it has been escaped, as it may contain some special characters that would make the syntax invalid.

The memory that will hold the escaped sequence should have the double size of the original data plus one additional byte.

Example: mysql_real_query() / mysql_real_escape_string()

The following example presents how to insert an image (binary file) into the USERS_TBL table (PHOTO column).

The program reads the file John_photo.jpg into memory, escapes the data into a second memory buffer then builds the UPDATE SQL statement to update the John’s row to add his photo.

#include <my_global.h>
#include <mysql.h>

int main(int argc, char *argv[]){
  char *file_name = "John_photo.jpg";
  char *photo_data;
  long  photo_size;
  FILE *photo = NULL;
  MYSQL *con;
  char *sql_statement;
  long  sql_statement_size;
  char *p;

  con = mysql_init(NULL);
  if( mysql_real_connect(con, "127.0.0.1", "USERS_DB_OWNER", "1234", "USERS_DB", 3306, NULL, 0) == NULL)
  {
    printf("The authentication failed with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  photo = fopen(file_name, "rb");
  if(photo == NULL)
  {
    printf("Could not open the photo file: %s\n", file_name);
    exit(1);
  }

  // Get the photo size and allocate memory for it
  fseek(photo, 0, SEEK_END);
  photo_size = ftell(photo);
  photo_data = malloc(photo_size);

  // Read the photo into memory
  fseek(photo, 0, SEEK_SET);
  fread(photo_data, 1, photo_size, photo);

  // Build the SQL statement
  // Allocate enough memory to build the SQL statement
  sql_statement = malloc(photo_size * 2 + 1 + 4096);
  p = sql_statement;

  p += sprintf(p, "UPDATE USERS_TBL ");
  p += sprintf(p, "SET PHOTO = '");
  p += mysql_real_escape_string(con, p, photo_data, photo_size);
  p += sprintf(p, "' WHERE LOGIN = 'John'");

  sql_statement_size = p - sql_statement;
  if(mysql_real_query(con, sql_statement, sql_statement_size) !=0)
  {
    printf("Query failed  with the following message:\n");
    printf("%s\n", mysql_error(con));
  }

  fclose(photo);
  mysql_close(con);
}

Query results

The previous section presented how to execute SQL statements. This section presents how to fetch query results.

Last auto-incremented ID

Some database tables have a column with the AUTO_INCREMENT option set. Each time a new row is inserted, the column gets a new auto-generated value.

The API provides the function mysql_insert_id() to fetch the auto-generated value after each execution of an INSERT statement.

uint64_t mysql_insert_id(MYSQL *mysql);

Example: mysql_insert_id()

The following example presents how to fetch the last auto-generated ID after the insertion of a new user into the table USERS_TBL.

#include <my_global.h>
#include <mysql.h>

int main(int argc, char *argv[]){
  MYSQL *con;
  con = mysql_init(NULL);

  if( mysql_real_connect(con, "127.0.0.1", "USERS_DB_OWNER", "1234", "USERS_DB", 3306, NULL, 0) == NULL)
  {
    printf("The authentication failed with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  if(mysql_query(con, "INSERT INTO USERS_TBL(LOGIN, PASSWORD) VALUES('Jack', '4321')") !=0)
  {
    printf("Query failed  with the following message:\n");
    printf("%s\n", mysql_error(con));
  }
  else
  {
    printf("The auto-generated ID is: %ld\n", (long) mysql_insert_id(con));
  }

  mysql_close(con);
}

The result set

The following is a description of the most used functions to fetch rows and fields. A later section provides an example of how they are used.

The result of a SQL statement may be captured in two ways: all the rows in one shot (which is faster but consumes more memory) or row by row (which consumes less memory but relatively slower). In both methods, the result is represented by an object of type MYSQL_RES.

The function mysql_store_result() is used to fetch all the rows in one shot. The function mysql_use_result() is used to fetch only one row at a time. Both functions are used in the same way. They are called with a connection handle and return a pointer to the result object (called result set).

MYSQL_RES *mysql_store_result(MYSQL *mysql);
MYSQL_RES *mysql_use_result(MYSQL *mysql);

When the function mysql_store_result() is used to fetch all the rows, the function mysql_num_rows() may be used to get the number of rows in the result set.

uint64_t mysql_num_rows(MYSQL_RES *result);

Each row is represented by an object of type MYSQL_ROW. The function mysql_fetch_row() is used to extract rows from the result. Each time it is called it returns the next row. It is called with the result set object.

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

The idea is that the function mysql_fetch_row() is called several times to process the rows till it returns the NULL value.

The type MYSQL_ROW is in reality an array of pointers to the row fields. The first field is accessed using row[0], the second row[1] and so on. An empty field is represented by the NULL value. Regular values are represented as strings but binary values are not. To get the length of a field (especially a binary one), the function mysql_fetch_lengths() is used. It is called with the result argument and returns a pointer to an array containing the length of each field.

unsigned long *mysql_fetch_lengths(MYSQL_RES *result);

Example: mysql_store_result() / mysql_use_result() / mysql_fetch_row() / mysql_fetch_lengths()

The following example presents how to fetch the ID, LOGIN, PASSWORD, and PHOTO from the table USERS_TBL. The PHOTO data will be saved into a file in the current directory.

#include <my_global.h>
#include <mysql.h>

int main(int argc, char *argv[]){
  MYSQL *con;
  MYSQL_ROW row;
  MYSQL_RES *result;
  int number_of_rows;
  char file_name[200];
  FILE *photo;
  long photo_size;
  con = mysql_init(NULL);

  if( mysql_real_connect(con, "127.0.0.1", "USERS_DB_OWNER", "1234", "USERS_DB", 3306, NULL, 0) == NULL)
  {
    printf("The authentication failed with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  if(mysql_query(con, "SELECT ID, LOGIN, PASSWORD, PHOTO FROM USERS_TBL") !=0)
  {
    printf("Query failed  with the following message:\n");
    printf("%s\n", mysql_error(con));
    exit(1);
  }

  result = mysql_store_result(con);

 printf("Number of rows: %ld\n", (long) mysql_num_rows(result));

 while((row = mysql_fetch_row(result)) != NULL)
 {
   printf("ID: %s\n", row[0]);
   printf("LOGIN: %s\n", row[1]);
   printf("PASSWORD: %s\n", row[2]);

   photo_size = mysql_fetch_lengths(result)[3];

   if(photo_size > 0)
   {
     sprintf(file_name, "./%s.jpg", row[1]);
     photo = fopen(file_name, "wb");
     fwrite(row[3], photo_size, 1, photo);
     fclose(photo);
     printf("The user's photo is saved to : %s\n", file_name);
   }
   else
   {
      printf("The user has no photo\n");
   }

   printf("\n");
 }

  mysql_close(con);
}

Additional connection parameters

Multiple statements

By default, the function mysql_query() and mysql_real_query() accept only one statement on each call. The ability to have multiple statements (separated by ‘;’) is possible using the parameter CLIENT_MULTI_STATEMENTS when a connection is open with the function mysql_real_connect().

mysql_real_connect(con, "127.0.0.1", "USERS_DB_OWNER", "1234", "USERS_DB", 3306, NULL, CLIENT_MULTI_STATEMENTS);

References

Complete list and description of the MySQL C API : https://dev.mysql.com/doc/refman/8.0/en/c-api-function-overview.html

The MySQL C API reference manual : https://dev.mysql.com/doc/refman/8.0/en/c-api.html