How to Access MySQL Database from C Program?

MySQL is an open sourcce database management system (DBMS). It is very popular among the web developers as it is part of LAMP (Linux, Apache, MySQL, PHP/Perl/Python) stack. Many popular large scale websites like WikiPedia, Facebook, Youtube use this database. Many times we need to use access MySQL database from C program. Here we’ll see how to access MySQL database from C program

Note: CentOS 7.2 operating system is used here for database installation and code compilation. These should be same for all RedHat based Linux distributions.

Installing MySql

If you already have MySQL installed on your system, then you can skip this section and use the existing database, otherwise, issue the following commands to install MySQL database.

Please note that you have to be super user (root) and need to have internet connection while issues these commands.

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server

MySQL runs as service on Linux system. To start the MySQL service, issues this command as super user (root)

systemctl start mysqld

You can also check whether the service is really running by this command.

systemctl status mysqld

If the service is running then this command will show active (running) as shown below.

Creating Table in the Database

In this section, we’ll create a sample table in the MySQL database manually right in the database server. We’ll access that table from C program later.
Login to the database.

mysql -u root -p

Under MySQL, you can have multiple databases. Tables will be created under databases. You can create a new database by issuing this command.

CREATE DATABASE mydb;

Now you have to go inside the database by command.

USE mydb;

Create a table my_table under mydb database using this SQL statement.

CREATE TABLE my_table (table_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT);

Insert some entries to the table my_table.

INSERT INTO my_table(first_name, last_name) VALUES('Cristiano', 'Ronaldo');
INSERT INTO my_table(first_name, last_name) values('Lionel', 'Messi');

You can check the entries of the table by this SQL query.

SELECT * FROM my_table;

Access MySQL database from C Program

To access MySQL database, you need to have the mysql-devel package on you system. This package provides the share libraries and header files required to MySQL client program development. Header files are required for program compilation and shared libraries are used at run-time. To check whether the package is already installed by this command.

yum info mysql-devel

If not installed, issue this command to install mysql-devel.

yum install mysql-devel

Here is the MySQL client program to access database entities like table, rows of tables.

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

int main()
{
 MYSQL *conn;
 MYSQL_RES *res;
 MYSQL_ROW row;

 char *server = "localhost";
 char *user = "root";
 char *password = ""; /*password is not set in this example*/
 char *database = "mydb";

 conn = mysql_init(NULL);

 /* Connect to database */
 if (!mysql_real_connect(conn, server,
 user, password, database, 0, NULL, 0))
 {
   printf("Failed to connect MySQL Server %s. Error: %s\n", server, mysql_error(conn));
   return 0;
 }

 /* Execute SQL query to fetch all table names.*/
 if (mysql_query(conn, "show tables"))
 {
   printf("Failed to execute quesry. Error: %s\n", mysql_error(conn));
   return 0;
 }

 res = mysql_use_result(conn);

 /* Output table name */
 printf("MySQL Tables in mydb database:\n");
 while ((row = mysql_fetch_row(res)) != NULL)
 printf("%s \n", row[0]);

 /* free results */
 mysql_free_result(res);

 /* send SQL query */
 if (mysql_query(conn, "select * from my_table"))
 {
   printf("Failed to execute quesry. Error: %s\n", mysql_error(conn));
   return 0;
 }

 res = mysql_store_result(conn);
 if (res == NULL)
 {
   return 0;
 }

 int columns = mysql_num_fields(res);

 int i = 0;

 printf("Entries in the table my_table:\n");
 while(row = mysql_fetch_row(res))
 {
   for (i = 0; i < columns; i++)
   {
     printf("%s ", row[i] ? row[i] : "NULL");
   }
   printf("\n");
 }

 mysql_free_result(res);
 mysql_close(conn);

 return 1;
}

In our example program above, we display all tables in our mydb database and all rows from the table my_table.

Before doing any database operation, we need to connect to the database server. This portion of the code does that.

 conn = mysql_init(NULL);

 /* Connect to database */
 if (!mysql_real_connect(conn, server,
 user, password, database, 0, NULL, 0))
 {
   printf("Failed to connect MySQL Server %s. Error: %s\n", server, mysql_error(conn));
   return 0;
 }

The API mysql_init() initializes one MYSQL object and returns the address (pointer) of that object in case of successful allocation. If the function fails to allocate the MYSQL object, then it returns NULL. The returned pointer would be used as the handle for subsequent database operations. To access any database element like tables or any field of a table, we have to first connect to the database server. The mysql_real_connect() function is used to connect to the MySQL database server. It takes IP address or domain name of the system where the MySQL server is running. As our server is running on the same system where the program will run,  “localhost”
is used as the server domain name. If you want to access database running on a remote machine, you can specify the domain name or IP Address of the remote machine. Also we have to specify user id, password and database name. In our case user id is “root” and database name is “mydb”.

 

/* Execute SQL query to fetch all table names.*/
 if (mysql_query(conn, "show tables"))
 {
   printf("Failed to execute quesry. Error: %s\n", mysql_error(conn));
   return 0;
 }

 res = mysql_use_result(conn);

 /* Output table name */
 printf("MySQL Tables in mydb database:\n");
 while ((row = mysql_fetch_row(res)) != NULL)
 printf("%s \n", row[0]);

 /* free results */
 mysql_free_result(res);

The above piece of code gets all the table names from the connected MySQL database. To do that, we have to run a SQL query which we can do by using mysql_query() API. To get the result of the executed query we have to use either mysql_use_result() or mysql_store_result() API. Here we used mysql_use_result() which does not bring the whole result to the
client side. So we have to call mysql_fetch_row() to fetch one row at a time from the server. If mysql_fetch_row() returns NULL, then there is no more row to be fetched. The “show tables” query will return all table name in a table. So, this while loop will return all available tables and print them. The returned value from the mysql_use_result() needs to be freed by mysql_free_result() API.

Later part of the code fetches all row of the table my_table.

if (mysql_query(conn, "select * from my_table"))
 {
   printf("Failed to execute quesry. Error: %s\n", mysql_error(conn));
   return 0;
 }

 res = mysql_store_result(conn);
 if (res == NULL)
 {
   return 0;
 }

Here also we used mysql_query() API to execute SELECT SQL query. Here we use the mysql_store_result() to fetch all returned row to the client.
In this case mysql_fetch_row() will not bring the row from the server, rather it will return from the res which is available in the memory of the client (our program)

The code below will traverse through all rows and print them.

int columns = mysql_num_fields(res);

 int i = 0;

 printf("Entries in the table my_table:\n");
 while(row = mysql_fetch_row(res))
 {
   for (i = 0; i < columns; i++)
   {
     printf("%s ", row[i] ? row[i] : "NULL");
   }
   printf("\n");
 }

The mysql_num_fields() API returns the column number of the returned tables. The outer while loop iterated through the rows and the inner
for loop prints all columns of a row.

And finally the res if freed using mysql_free_result().

Compiling MySQL Client Code

Compiling the MySQL client code is tricky. First of all you need to have GCC installed on your system. You have to set proper compilation flag and libraries to link. To do that, we’ll use mysql_config utility to get compiler options. “mysql_config –cflags” returns C Compiler flags to find include files and critical compiler flags and defines used when compiling the libmysqlclient library. And “mysql_config –libs” Libraries and options required to link with the MySQL client library.
If your code is saved in test.c C file, then use this command to compile the code.

gcc -o mysqlc $(mysql_config --cflags) $(mysql_config --libs) test.c

The output binary will be stored as mysqlc.

If we run the program, the output will look like this.

./mysqlc
MySQL Tables in mysql database:
my_table
1 Cristiano Ronaldo
2 Lionel Messi

If you also want to contribute, click here.