Connect to OceanBase Database by using MySQL Connector/C

MySQL Connector/C is generally referred to as the MySQL C API. It provides a set of C functions and data structures and allows developers to use C or C++ to connect to and manage MySQL databases. In other words, it allows developers to use a MySQL database directly in a C or C++ application.

This topic describes how to build an application based on MySQL Connector/C (libmysqlclient) and OceanBase Database to perform basic operations such as table creation, data insertion, and data query.

Prerequisites

Before you install MySQL Connector/C (libmysqlclient), make sure that you have set up the basic database development environment that meets the following requirements:

• The GNU Compiler Collection (GCC) version is 3.4.6 or later. Version 4.8.5 is recommended.
• The CMake version is 2.8.12 or later.
• You have installed OceanBase Database and created a MySQL tenant.

Procedure

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

Step 1: Obtain a database connection string

Obtain a database connection string from a database deployment engineer or administrator. Here is an example:

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

where

• $host indicates the IP address for connecting to OceanBase Database, which is the IP address of an OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.

• $port indicates the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

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

  Notice

  The user for connecting to a tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name indicates the tenant account. For connection through ODP, the tenant account can be in the Username@Tenant name#Cluster name or Cluster name:Tenant name:Username format. For direct connection, the tenant account is in the Username@Tenant name format.

• $password indicates the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Install the MySQL Connector/C (libmysqlclient) driver

## To install the driver in Ubuntu, run the following command:
sudo apt-get install libmysqlclient-dev

## To install the driver in CentOS, run the following command:
sudo yum install mysql-devel

## Check whether the driver is installed.
mysql_config --version

Step 3: Write an application

Open your text editor, write a sample test.cc file, and save it. The sample 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 user 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 a 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 connection string obtained in Step 1.

• In Linux, you can run the vi test.cc or vim test.cc command to edit the test.cc file and modify the database connection information in the file based on the actual situation.

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

# Modify the connection information based on the obtained connection string.
if (mysql_real_connect(conn, "10.10.10.1", "test_user001@mysql001", "test", "db", 2881, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed\n");
        mysql_close(conn);
        return 1;
    }

Step 4: Compile the application

After you edit the code, run the following command:

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

After the compilation, 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 is connected and the sample application is executed correctly.

1 Xiaoming xiaoming@example.com