Connect to MYSQL in C using CMake

C is one of the fastest language around. Many a times you might need to use it in your development activities to speed up some processes. For one of my requirement I need to interact with MYSQL in C. Although there are many different articles which explain different aspects, I didn't find all the details in one places. So here I have written this article to explain it. Although this research was done on Mac, many of the aspects can be copied to other OS as well. I will be using HomeBrew for

Tools and Techs

Here are the list of tools, frameworks and technologies which we are going to use for this research.

Mysql
C
VS Code
HomeBrew
pkg-config
MacOS

Installing MySQL

For this project I will be connecting to mysql remotely. But if you want you can setup a local mysql server. Irrespective of whether you are setting up remotely or locally, we will need to setup on our development machine. This is to access the libraries from our project.

Here is the brew command to install mysql.

brew install mysql

VSCode Plugins

I am not going into the detail of how to install VSCode. But there are some plugins which we will require for our development. Here are some of the plugins which will need to install in your VS Code.

Screenshot 2022-07-13 at 10.30.03 AM.png

Screenshot 2022-07-13 at 10.30.24 AM.png

Screenshot 2022-07-13 at 10.31.12 AM.png

Screenshot 2022-07-13 at 10.31.27 AM.png

Installing pkg-config

We will be using this package with CMake to find the some configuraations related to mysql libraries. Here is the brew command to install the package.

brew install pkg-config

Project Setup

Now that we have almost all the pre-requisites setup, lets get started with the project.

Create a folder for our project with name cmysqlcmake.
Open the folder in VSCode.
Open Command Palette.
Select CMake: Quick Start
It will show you popup as below. Click on Create.
Now it will ask you to select a kit for cmake. Choose unspecified for now.
Enter a project name.
Now you will be asked to whether the project is a library or executable. Choose executable.

Now the project has been generated as following.

Screenshot 2022-07-13 at 10.56.01 AM.png

Convert from C++ project to C project

As you can see the project which has been generated has cpp files rather than C. So let us make few changes to support C.

Replace main.cpp with main.c in CMakeLists.txt
Rename file main.cpp to main.c

Replace the C++ code with the following C code.

#include <stdio.h>
int main() {
// printf() displays the string inside quotation
printf("Hello, World!");
return 0;
}

Now can run the project by opening the command palette and selecting "CMake: Run without Debugging"

You will get output something similar to below in the terminal:

Hello, World!%

Including mysql in VS Code Include Path

Let us add the following code to main.c

#include <mysql.h>

You will see that as soon as you save the file, VS Code shows an error as seen in the following screenshot.

Screenshot 2022-07-13 at 11.33.31 AM.png

To resolve this issue we will need to add the folder containing the mysql header files to the includePath.

To do so goto .vscode folder in your project root directory.
Check if file c_cpp_properties.json is present or not.

If it is not present then create a file and add the following:

{
  "configurations": [
      {
          "name": "Mac",
          "includePath": [
              "${workspaceFolder}/**",
              "/usr/local/Cellar/mysql/8.0.29/include/mysql"
          ],
          "defines": [],
          "macFrameworkPath": [
              "/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/System/Library/Frameworks"
          ],
          "compilerPath": "/usr/bin/clang",
          "cStandard": "c17",
          "cppStandard": "c++17",
          "intelliSenseMode": "macos-clang-x64",
          "configurationProvider": "ms-vscode.cmake-tools"
      }
  ],
  "version": 4
}

If it is already present then just add the path to the mysql header files to the includePath parameter.
For me it is "/usr/local/Cellar/mysql/8.0.29/include/mysql". It might be different for you.
Once you have saved the changes VSCode will pick it up and the error will be gone. If it doesn't then you try restarting VSCode.

Adding C code for Mysql

Let us not start adding code to main.c

Add the variables to store your mysql server details.

char *server = "<your mysql server name>";
char *user = "<your mysql username>";
char *password = "<your mysql password>"; 
char *database = "<your mysql database name>";

Initialise Mysql connection.
```
MYSQL *conn = mysql_init(NULL);
```

Connect to database.

if (!mysql_real_connect(conn, server,
                       user, password, database, 0, NULL, 0))
{
 fprintf(stderr, "%s\n", mysql_error(conn));
 return(1);
}

Send an SQL query. Here we are just retrieving the tables in the database.

/* send SQL query */
if (mysql_query(conn, "show tables"))
{
 fprintf(stderr, "%s\n", mysql_error(conn));
 return(1);
}

Retrieve the result.

MYSQL_RES *res = mysql_use_result(conn);

Print the tables.

printf("Tables in mysql database:\n");
MYSQL_ROW row;
while ((row = mysql_fetch_row(res)) != NULL){
printf("%s \n", row[0]);
}

Free resources and close connection.

mysql_free_result(res);
mysql_close(conn);
return(0);

Here is how your main.c should look like:

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

int main() {

    char *server = "<your mysql server name>";
    char *user = "<your mysql username>";
    char *password = "<your mysql password>"; 
    char *database = "<your mysql database name>";
    MYSQL *conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
    {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return(1);
    }

    /* send SQL query */
    if (mysql_query(conn, "show tables"))
    {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return(1);
    }

    MYSQL_RES *res = mysql_use_result(conn);

    printf("Tables in mysql database:\n");
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res)) != NULL){
        printf("%s \n", row[0]);
    }

    mysql_free_result(res);
    mysql_close(conn);
    return 0;
}

CMake Changes

Although we have added all the codes and it doesnt show any error, to make a cmake build we will need to update our CMakeLists.txt file. Here are the changes.

Set the minimum required version for cmake. We are setting to 3.0.
```
cmake_minimum_required(VERSION 3.0)
```
Set project.
```
project(cmysqlcmake C)
```

Set CFlags.

set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -std=c99")

Include pkg-config.
```
include(FindPkgConfig)
```

Check for mysql client modules.

pkg_check_modules(LIBMYSQLCLIENT REQUIRED mysqlclient)

Iterate and set the mysql C flags.

foreach(FLAG ${LIBMYSQLCLIENT_CFLAGS_OTHER})
set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} ${FLAG}")
endforeach()

Link the mysql client libraries dir.

foreach(FLAG ${LIBMYSQLCLIENT_CFLAGS_OTHER})
set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} ${FLAG}")
endforeach()

Add the source files.
```
add_executable(cmysqlcmake main.c)
```

Set the include directories.

target_include_directories(cmysqlcmake PRIVATE ${LIBMYSQLCLIENT_INCLUDE_DIRS})

Link the libraries.

target_link_libraries(cmysqlcmake PRIVATE ${LIBMYSQLCLIENT_LIBRARIES})

Here is how your final CMakeLists.txt file should look like:

cmake_minimum_required(VERSION 3.0)
project(cmysqlcmake C)
set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -std=c99")
include(FindPkgConfig)
pkg_check_modules(LIBMYSQLCLIENT REQUIRED mysqlclient)

foreach(FLAG ${LIBMYSQLCLIENT_CFLAGS_OTHER})
  set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} ${FLAG}")
endforeach()

link_directories(${LIBMYSQLCLIENT_LIBRARY_DIRS})

add_executable(cmysqlcmake main.c)
target_include_directories(cmysqlcmake PRIVATE ${LIBMYSQLCLIENT_INCLUDE_DIRS})
target_link_libraries(cmysqlcmake PRIVATE ${LIBMYSQLCLIENT_LIBRARIES})

Test Run

Now we are ready to test our changes. To do so open the command palette and select "CMake: Run Without Debugging"

If everything goes fine then you should get the list of tables. Here is the output for me, where I had a table by name jobs int the database.

Tables in mysql database:
jobs

What's Next

Now that we have successfully connected to mysql with c, we can move ahead and try out the CRUD operations. Soon I will write an article on the CRUD operations in C. If you have any query feel free to ask in the comments section below.