MySQL Connector/C sample program for connecting to seekdb

MySQL Connector/C, also known as the MySQL C API, provides a set of C language functions and data structures that allow developers to connect, query, and manage MySQL databases using C/C++. This API enables developers to directly use MySQL databases within their C/C++ applications.

This topic describes how to build an application using the MySQL Connector/C (libmysqlclient) driver and seekdb to perform basic operations such as creating tables, inserting data, and querying data.

Prerequisites

Before you install and use MySQL Connector/C (libmysqlclient), make sure that the basic database development environment is set up. The requirements are as follows:

• The GCC version is 3.4.6 or later. We recommend that you use version 4.8.5.
• The CMake version is 2.8.12 or later.
• You have installed seekdb.

Procedure

1. Obtain the connection information for seekdb.
2. Install the MySQL Connector/C driver.
3. Write an application.
4. Run the application.

Step 1: Obtain the database connection string

Contact the deployment personnel or administrator of seekdb to obtain the corresponding database connection string, for example:

mysql -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connecting to seekdb. Replace it with the actual IP address. You can also use the local IP address, such as 127.0.0.1.
• $port: the port for connecting to seekdb. Replace it with the actual port. The default port is 2881. You can customize the port during the deployment of seekdb.
• $database_name: the name of the database to be accessed.
• $user_name: the username for connecting to seekdb. Format: username.
• $password: the password for connecting to seekdb.

For more information about the connection string, see Connect to seekdb by using a MySQL client.

Here is an example:

mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -Dtest

Step 2: Install the MySQL Connector/C driver

## Run the following command to install the driver on an Ubuntu system:
sudo apt-get install libmysqlclient-dev

## Run the following command to install the driver on a CentOS system:
sudo yum install mysql-devel

## Run the following command to confirm whether the driver is installed:
mysql_config --version

Step 3: Write an application

Open your text editor and edit the sample test.cc file, and then save the file. The code is as follows:

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

int main() {
    MYSQL *conn = mysql_init(NULL); // Initialize the MySQL connection

    if (conn == NULL) {
        fprintf(stderr, "mysql_init() failed\n");
        return 1;
    }

    // Connect to the MySQL database server
    if (mysql_real_connect(conn, "host", "user", "passwd", "db", port, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed\n");
        mysql_close(conn);
        return 1;
    }

    // Create a users table
    if (mysql_query(conn, "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255), PRIMARY KEY(id))") != 0) {
        fprintf(stderr, "Error in creating table: %s\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Insert data
    if (mysql_query(conn, "INSERT INTO users (name, email) VALUES ('Xiaoming', 'xiaoming@example.com')") != 0) {
        fprintf(stderr, "Error in inserting data: %s\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }

    // Query data
    if (mysql_query(conn, "SELECT * FROM users") == 0) {
        MYSQL_RES *result = mysql_store_result(conn);
        if (result != NULL) {
            int num_fields = mysql_num_fields(result);
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(result))) {
                for (int i = 0; i < num_fields; i++) {
                    printf("%s ", row[i] ? row[i] : "NULL");
                }
                printf("\n");
            }
            mysql_free_result(result);
        } else {
            fprintf(stderr, "Error in retrieving result: %s\n", mysql_error(conn));
        }
    } else {
        fprintf(stderr, "Error in selecting data: %s\n", mysql_error(conn));
    }

    // Drop the table
    if (mysql_query(conn, "DROP TABLE IF EXISTS users") != 0) {
    fprintf(stderr, "Error in dropping table: %s\n", mysql_error(conn));
    mysql_close(conn);
    return 1;
}
    mysql_close(conn); // Close the connection
    return 0;
}

Modify the database connection information in the test.cc file based on the information obtained in Step 1: Obtain the seekdb connection information.

• In a Linux environment, you can use the vi test.cc or vim test.cc command to edit the test.cc file and modify the database connection information to match the actual situation.

Here is an example of the database connection information in the test.cc file:

# Modify the following connection information to match the actual connection string obtained.
if (mysql_real_connect(conn, "10.10.10.1", "root", "test", "db", 2881, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed\n");
        mysql_close(conn);
        return 1;
    }

Step 4: Compile the application

After editing the code, run the following command to compile the application.

gcc -o test test.cc `mysql_config --cflags --libs`

After the compilation is successful, an executable file named test is generated.

Step 5: Run the application

Run the following command to run the application.

./test

If the following result is returned, the database connection is successful and the sample statements are correctly executed.

1 Xiaoming xiaoming@example.com