使用C驱动和示例代码连接OceanBase - ApsaraDB for OceanBase

This topic describes how to connect to and use an OceanBase database by using OceanBase Connector/C.

Prerequisites

Before you install OceanBase Connector/C, 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.

Connect to an OceanBase database by using OceanBase Connector/C

Step 1: Obtain the database connection parameters

Obtain the database connection parameters. For more information, see Obtain the connection parameters. Example:

$ obclient -hxxx.xxx.xxx.xxx -P3306 -u a**** -p****** -Dtest

The database connection parameters specify the information required to access the database. You can verify the database connection parameters by logging on to the database before using them in the sample code.

Options:

-h: the domain name of the OceanBase database to be connected.
-P: the port for connecting to the OceanBase database. By default, the port is 3306 in MySQL mode.
-u: the tenant account.
-p: the account password.
-D: the database name.

Step 2: Install OceanBase Connector/C

Use RPM packages

Obtain the installation packages of OceanBase Connector/C (also known as LibOBClient) and OBClient. For more information about the installation packages of other versions, visit the open source image site of Alibaba Cloud. You can find the required installation packages in the image repository for your system version.

Note

OBClient depends on LibOBClient. Therefore, you must install LibOBClient first.

Install LibOBClient.

$ sudo rpm -ivh libobclient-xx.x86_64.rpm

Install OBClient.
```
$ sudo rpm -ivh obclient-xx.x86_64.rpm
```

Compile the source code

OceanBase Database provides the source code of the latest OBClient in its GitHub repository, which also includes sub-repositories.

Install the dependency tool.

$ sudo yum install -y git cmake gcc make openssl-devel ncurses-devel rpm-build  gcc-c++ bison bison-devel zlib-devel gnutls-devel libxml2-devel openssl-devel \libevent-devel libaio-devel

Compile and package the code
1. Download the source code of OBClient, which also includes sub-repositories.
```
$ git clone --recurse-submodules https://github.com/oceanbase/obclient 
```
2. Go to the obclient/libmariadb directory.
```
$ cd obclient/libmariadb
```
3. Compile LibOBClient.
```
$ sh build.sh
```
4. Package up the RPM package of LibOBClient.
```
$ sh rpm/libobclient-build.sh
```
5. Return to the OBClient directory.
```
$ cd ..
```
6. Compile OBClient.
```
$ sh build.sh
```
7. Package up OBClient into an RPM package.
```
$ sh rpm/obclient-build.sh
```
Install LibOBClient.
```
$ sudo rpm -ivh /libmariadb/rpmlibobclient-xx.x86_64.rpm
```
Install OBClient.
```
$ sudo rpm -ivh rpm/obclient-xx.x86_64.rpm
```
Note
OBClient depends on LibOBClient. Therefore, you must install LibOBClient first.

Step 3: Write the sample code

Perform the following steps to interact with an OBServer node by using OceanBase Connector/C:

Call mysql_library_init() to initialize the MySQL library.
```
mysql_library_init(0, NULL, NULL);
```
Call mysql_init() to initialize a connection handle.
```
MYSQL *mysql = mysql_init(NULL);
```

Call mysql_real_connect() to connect to an OBServer node.

mysql_real_connect (mysql, host_name, user_name, password,
db_name, port_num, socket_name, CLIENT_MULTI_STATEMENTS)

Call mysql_real_query() or mysql_query() to send an SQL statement to the OBServer node.
```
mysql_query(mysql,"sql_statement");
```
Call mysql_store_result() or mysql_use_result() to process the result.
```
result=mysql_store_result(mysql);
```
Call mysql_free_result() to release the memory space.
```
mysql_free_result(result);
```
Call mysql_close() to disconnect from the OBServer node.
```
mysql_close(mysql);
```
Call mysql_library_end() to stop using LibOBClient.
```
mysql_library_end();
```

Sample code

The following example shows the sample code for the mysql_test.c file:

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

int main(int argc, char** argv)
{
  mysql_library_init(0, NULL, NULL);
  MYSQL *mysql = mysql_init(NULL);
  char* host_name = "xxx.xxx.xxx.xxx";//set your mysql host
  char* user_name = "a****"; //set your user_name
  char* password = "******"; //set your password
  char* db_name = "test"; // Set your database name.
  int port_num = 3306; //set your mysql port
  char* socket_name = NULL;
  MYSQL_RES* result;
  MYSQL_FIELD* fields;
  MYSQL_ROW row;
  int status = 0;
  /* connect to server with the CLIENT_MULTI_STATEMENTS option */
  if (mysql_real_connect (mysql, host_name, user_name, password,
    db_name, port_num, socket_name, CLIENT_MULTI_STATEMENTS) == NULL)
  {
    printf("mysql_real_connect() failed\n");
    mysql_close(mysql);
    exit(1);
  }

  /* execute multiple statements */
  status = mysql_query(mysql, "DROP TABLE IF EXISTS test_table;");
                      
  if (status)
  {
    printf("Could not execute statement(s)");
    mysql_close(mysql);
    exit(0);
  }

  status = mysql_query(mysql, "CREATE TABLE test_table(id INT,name varchar(24));");
  status = mysql_query(mysql, "INSERT INTO test_table VALUES(10,'hangzhou'),(20,'shanghai');");
  status = mysql_query(mysql, "UPDATE test_table SET id=20 WHERE id=10;");
  status = mysql_query(mysql, "SELECT * FROM test_table;");

  /* did current statement return data? */
  result = mysql_store_result(mysql);

  if (result)
  {
    /* yes; process rows and free the result set */
    //process_result_set(mysql, result);

    int num_fields = mysql_num_fields(result);
    int num_rows = mysql_num_rows(result);

    printf("result: %d rows %d fields\n", num_rows, num_fields);
    printf("---------------------\n");

    fields = mysql_fetch_fields(rs_metadata);

    for (int i = 0; i < num_fields; ++i)
    {
      printf("%s\t", fields[i].name);
    }

    printf("\n---------------------\n");

    while ((row = mysql_fetch_row(result)))
    {
      for (int i = 0; i < num_fields; ++i)
      {
        printf("%s\t", row[i] ? row[i] : "NULL");
      }

      printf("\n");
    }

    printf("---------------------\n");

    mysql_free_result(result);
  }
  else          /* no result set or error */
  {
    if (mysql_field_count(mysql) == 0)
     {
       printf("%lld rows affected\n",
            mysql_affected_rows(mysql));
     }
     else  /* some error occurred */
     {
       printf("Could not retrieve result set\n");
     }
  }
  
  status = mysql_query(mysql, "DROP TABLE test_table;");

  mysql_close(mysql);
  return 0;
}

Modify the database connection parameters in the code. Refer to the following parameters and format. The parameter values are obtained in Step 1.

host_name: the domain name of the OceanBase database to be connected, which corresponds to the -h option.
user_name: the tenant account, which corresponds to the -u option.
password: the tenant account password, which corresponds to the -p option.
db_name: the database name, which corresponds to the -D option.
port_num: the port for connecting to the OceanBase database, which corresponds to the -P option. By default, the port is 3306 in MySQL mode.

Step 4: Run the sample code

After you edit the code, run the following command for compilation:

Note

The default installation path of OBClient is /u01/obclient.

$ g++ -I/u01/obclient/include/ -L/u01/obclient/lib -lobclnt mysql_test.c -o mysql_test

After you edit the code, run the following command to execute the sample statement:

Specify the execution path.

$ export LD_LIBRARY_PATH=/u01/obclient/lib

Run mysql_test.
```
$ ./mysql_test
```

If the following result is returned, the database is connected and the sample statement is executed correctly.

---------------------
id      name
---------------------
20      hangzhou
20      shanghai
---------------------