Build a C application

This topic describes how to build a C application with OceanBase Database and MySQL Connector/C (libmysqlclient).

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.

Procedure

Step 1: Obtain a database connection string

Obtain a database connection string from a database deployment engineer or administrator. For example:

obclient -h100.88.xx.xx -uroot@test -p****** -P2881 -Doceanbase

The database connection string contains parameters required for accessing OceanBase Database. Before you create an application, you can log on to OceanBase Database by using the database connection string to verify that the parameters are correct.

The parameters are described as follows:

• -h: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an OceanBase Database Proxy (ODP).
• -u: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The default tenant of a cluster is sys, and the default administrator of a tenant is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.
• -p: the user password.
• -P: the port for connecting to OceanBase Database, which is also the listening port of the ODP.
• -D: the name of the database to be accessed.

Step 2: Install the MySQL Connector/C driver

Install the MariaDB client by using YUM:

sudo yum install mariadb-devel

Step 3: Write an application

An application interacts with an OBServer node by using MySQL Connector/C in the following process:

1. Call mysql_library_init() to initialize the MySQL library.

   mysql_library_init(0, NULL, NULL);
   

2. Call mysql_init() to initialize a connection handle.

   MYSQL *mysql = mysql_init(NULL);
   

3. Call mysql_real_connect() to connect to the OBServer node.

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

4. Call mysql_real_query() or mysql_query() to send an SQL statement to the OBServer node.

   mysql_query(mysql,"sql_statement");
   

5. Call mysql_store_result() or mysql_use_result() to process the result.

   result=mysql_store_result(mysql);
   

6. Call mysql_free_result() to release the memory.

   mysql_free_result(result);
   

7. Call mysql_close() to disconnect from the OBServer node.

   mysql_close(mysql);
   

8. Call mysql_library_end() to stop using the MariaDB client.

   mysql_library_end();
   

Sample code

The following example shows the content of 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 = "******"; //set your user_name
  char* password = "******"; //set your password
  char* db_name = "test"; //set your databasename
  int port_num = 2883; //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(result);

    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. Values of the parameters are from the database connection string obtained in Step 1.

• host_name: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an ODP. The value of this parameter is obtained from the -h parameter.

• user_name: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The value of this parameter is obtained from the -u parameter. The default tenant of a cluster is sys, and the default administrator of a tenant is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.

• password: the user password. The value of this parameter is obtained from the -p parameter.

• db_name: the name of the database to be accessed. The value of this parameter is obtained from the -D parameter.

• port_num: the port for connecting to OceanBase Database, which is also the listening port of the ODP. The value of this parameter is obtained from the -P parameter.

Step 4: Run the application

1. After you edit the code, run the following command:

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

   The options are described as follows:

   • -I: specifies the search path for the compiler search path so that it can find the header files. For example, if you want the compiler to find mysql.h in /usr/include/mysql, you would add that directory to the compiler's search path. You can use the command find / -name mysql.h 2>/dev/null to locate the mysql.h file path.

   • -L: specifies the search path for dynamic-linked libraries.

   • -l: specifies the name of the library to be linked. If you use the -l option, remove the lib prefix and .so suffix from the library name. For example, in the above command, the library file name is libmysqlclient.so, but if you use the -l option, you only need to specify mysqlclient.

2. Specify the running path.

   export LD_LIBRARY_PATH=/usr/lib64/mysql
   

3. Run the following command to run the application:

   ./mysql_test
   

   If the following results are returned, you have connected to OceanBase Database successfully, and the sample script is correctly executed.

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