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.

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

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

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

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.

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

Now you have to go inside the database by command.

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

Insert some entries to the table my_table.

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

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.

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

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

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.

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”.

 

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.

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.

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.

The output binary will be stored as mysqlc.

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

Leave a Reply

Your email address will not be published. Required fields are marked *