Connect C or C++ applications by using the MySQL C API - Lindorm

This topic describes how to use the MySQL C API to connect to LindormTable and run SQL operations from a C or C++ application.

Prerequisites

Before you begin, make sure you have:

MySQL compatibility enabled on your Lindorm instance. For more information, see Enable the MySQL compatibility feature.
Your client IP address added to the Lindorm instance whitelist. For more information, see Configure a whitelist.

Connect to LindormTable using the MySQL C API

Step 1: Install the MySQL C API library

Install the mysql-devel package, which provides the MySQL C API headers (including mysql/mysql.h) and client libraries. The following example uses CentOS:

yum install mysql-devel

After installation, the header file is located at /usr/include/mysql/mysql.h and the client library is libmysqlclient. Use #include "mysql/mysql.h" in your source file to include it.

Step 2: Configure connection parameters

Define the connection parameters in your C source file:

char lindorm_addr[]     = "ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com";
char lindorm_user[]     = "user";
char lindorm_password[] = "test";
char database[]         = "default";
int  lindorm_mysql_port = 33060;

The following table describes each parameter:

Parameter	Description
`lindorm_addr`	The LindormTable endpoint for MySQL. For more information about how to get it, see View endpoints. Use the VPC endpoint when your application runs on an ECS instance, or the Internet endpoint when connecting over a public network.
`lindorm_user`	The LindormTable username. If you forget the password, reset it in the cluster management system. For more information, see Manage users.
`lindorm_password`	The LindormTable password.
`database`	The database to connect to. Defaults to `default`.
`lindorm_mysql_port`	The MySQL protocol port for LindormTable. Fixed at `33060`.

Important

Choose the right endpoint based on your deployment:

ECS instance (recommended): Use a virtual private cloud (VPC) connection for lower latency and higher security. Set lindorm_addr to the MySQL-compatible VPC address.
On-premises application: Enable public endpoint access first. In the console, go to Database Connections > Wide Table Engine, then click Enable Public Endpoint. Set lindorm_addr to the MySQL-compatible Internet address.

Step 3: Create a connection and run SQL

Call mysql_real_connect() to establish a connection, then use wide table SQL syntax to run operations. The example below connects to LindormTable and creates a table:

MYSQL      conn;
int        res;

mysql_init(&conn);

// Create a connection
if (!mysql_real_connect(
        &conn,               // Connection handle
        lindorm_addr,        // Host: LindormTable endpoint
        lindorm_user,        // Username
        lindorm_password,    // Password
        database,            // Default database
        lindorm_mysql_port,  // Port: 33060
        NULL,                // Unix socket (not used)
        0                    // Client flags (none)
)) {
    fprintf(stderr, "Failed to connect: %s\n", mysql_error(&conn));
    mysql_close(&conn);
    exit(1);
} else {
    printf("conect lindorm successfully\n");
}

// Create a table
char create_table[] = "create table if not exists user_test(id int, name varchar,age int, primary key(id))";
res = mysql_query(&conn, create_table);
if (!res) {
    printf("Table created successfully\n");
} else {
    fprintf(stderr, "Create table error: %s\n", mysql_error(&conn));
    mysql_close(&conn);
    exit(1);
}

// ... run additional queries, then release resources
mysql_free_result(result);
mysql_close(&conn);

Step 4: Compile and run

Use mysql_config to supply the correct compiler and linker flags:

gcc -o a.out $(mysql_config --cflags) demo.c $(mysql_config --libs)
./a.out

Flag	Effect
`--cflags`	Adds the include path so the compiler can locate `mysql/mysql.h`.
`--libs`	Links against `libmysqlclient`.

Tip: If you get undefined reference to 'mysql_init' or similar linker errors, make sure $(mysql_config --libs) appears after your source file in the command. GCC resolves symbols left to right.

Complete example

The following code demonstrates all four operations: connect, create a table, insert data, and query data.

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

int main() {
    MYSQL      conn;
    int        res;
    MYSQL_RES *result;
    MYSQL_ROW  row;

    mysql_init(&conn);

    // Connection configuration
    char lindorm_addr[]     = "ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com"; // LindormTable endpoint for MySQL
    char lindorm_user[]     = "user";    // LindormTable username
    char lindorm_password[] = "test";    // LindormTable password
    char database[]         = "default"; // Database to connect to
    int  lindorm_mysql_port = 33060;     // MySQL protocol port (fixed at 33060)

    // Create a connection
    if (!mysql_real_connect(
            &conn,               // Connection handle
            lindorm_addr,        // Host
            lindorm_user,        // Username
            lindorm_password,    // Password
            database,            // Default database
            lindorm_mysql_port,  // Port
            NULL,                // Unix socket (not used)
            0                    // Client flags (none)
    )) {
        fprintf(stderr, "Failed to connect: %s\n", mysql_error(&conn));
        mysql_close(&conn);
        exit(1);
    } else {
        printf("conect lindorm successfully\n");
    }

    // Create a table
    char create_table[] = "create table if not exists user_test(id int, name varchar,age int, primary key(id))";
    res = mysql_query(&conn, create_table);
    if (!res) {
        printf("create table successfully\n");
    } else {
        fprintf(stderr, "create table Error: %s\n", mysql_error(&conn));
        mysql_close(&conn);
        exit(1);
    }

    // Insert data using upsert syntax
    char insert_data[] = "upsert into user_test(id,name,age) values(3,'wangwu',23)";
    res = mysql_query(&conn, insert_data);
    if (!res) {
        printf("insert data successfully\n");
    } else {
        fprintf(stderr, "insert data Error: %s\n", mysql_error(&conn));
        mysql_close(&conn);
        exit(1);
    }

    // Query data
    char select_query[] = "select * from user_test";
    if (mysql_query(&conn, select_query) != 0) {
        fprintf(stderr, "select Error: %s\n", mysql_error(&conn));
        mysql_close(&conn);
        exit(1);
    }

    result = mysql_store_result(&conn);
    if (result == NULL) {
        fprintf(stderr, "store result Error: %s\n", mysql_error(&conn));
        mysql_close(&conn);
        exit(1);
    }

    while ((row = mysql_fetch_row(result)) != NULL) {
        printf("name is %s , ", row[0]);
        printf("age is %s\n", row[1]);
    }

    mysql_free_result(result);
    mysql_close(&conn);
    return 0;
}

Expected output:

conect lindorm successfully
create table successfully
insert data successfully
name is 3 , age is wangwu

LindormTable uses upsert into instead of the standard INSERT INTO to write rows. This is the wide table SQL syntax specific to LindormTable.

What's next

View endpoints — get the VPC or Internet endpoint for your instance
Manage users — reset passwords or manage access