Use MySQL Connector/C to connect to OceanBase Cloud

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 in their C/C++ applications.

This topic describes how to build an application by using the MySQL Connector/C (libmysqlclient) driver and OceanBase Cloud 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 GCC 4.8.5.
• The CMake version is 2.8.12 or later.
• You have registered an account for OceanBase Cloud and created an instance and a MySQL-compatible tenant. For more information, see Create an instance and Create a tenant.

Procedure

1. Obtain the connection information of the cloud database.
2. Install the MySQL Connector/C driver.
3. Write an application.
4. Run the application.

Step 1: Obtain the connection string

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the corresponding information in the URL below based on the created OceanBase Cloud database.

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

   Parameter description:

   • $host: the connection address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.

   • $port: the connection port of the OceanBase Cloud database, which is 3306 by default.

   • $database_name: the name of the database to be accessed.

     Notice

     The user of the tenant must have the CREATE, INSERT, DROP, and SELECT permissions on the database. For more information about account permissions, see Create and manage an account.

   • $user_name: the account for accessing the database.

   • $password: the password of the account.

   Here is an example:

   obclient -h t********.********.oceanbase.cloud -P3306 -u mysql001 -p****** -Dtest
   

Step 2: Install the MySQL Connector/C driver

## On Ubuntu, run the following command:
sudo apt-get install libmysqlclient-dev

## On CentOS, run the following command:
sudo yum install mysql-devel

## Confirm whether the installation was successful.
mysql_config --version

Step 3: Write an application

Open your text editor and edit the sample test.cc file. Save the file with the following code:

#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 connection information of OceanBase Cloud.

• 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 information obtained from the connection string.
if (mysql_real_connect(conn, "t********.********.oceanbase.cloud", "mysql001", "********", "test", 3306, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed\n");
        mysql_close(conn);
        return 1;
    }

Step 4: Compile the application

After editing the code, compile it using the following command:

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

If the compilation is successful, an executable file named test will be generated.

Step 5: Run the application

Run the application using the following command:

./test

If the output is as follows, it indicates that the database connection was successful and the sample statements were executed correctly.

1 Xiaoming xiaoming@example.com