Connect to OceanBase Database by using OceanBase Connector/C

This topic introduces how to build an application by using OceanBase Connector/C and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Download the c-oceanbase-capi sample project

Prerequisites

• You have installed OceanBase Database and created an Oracle tenant. For more information about how to install OceanBase Database, see Deployment overview.
• You have installed OceanBase Connector/C. Contact OceanBase Technical Support to obtain the installation package of OceanBase Connector/C for Windows.
• You have installed Visual Studio.

Procedure

1. Open the c-oceanbase-capi project.
2. Configure the properties for the c-oceanbase-capi project.
3. Obtain the connection information of OceanBase Database.
4. Modify the database connection information in the c-oceanbase-capi project.
5. Build the project.
6. Run the application.
7. Check the output.

Step 1: Open the c-oceanbase-capi project

1. Start Visual Studio Community 2019.

2. Open an existing project.

   1. In the start window of Visual Studio Community 2019, click Open a project or solution under Get started. Alternatively, click Continue without code in the lower right corner, and choose File > Open > Project/Solution in the menu bar of the page that appears.

   2. Browse to the c-oceanbase-capi folder, select the project file c-oceanbase-capi.sln or c-oceanbase-capi.vcxproj, and click Open.

Step 2: Configure properties for the c-oceanbase-capi project

1. Go to the property page of the project.

   Right-click the selected project in Solution Explorer and choose Properties from the menu, or choose Project > Properties from the top menu bar, or use the shortcut key Alt + Enter.

2. Set the configuration manager.

   1. Select Debug from the Configuration drop-down list.

   2. Select x64 from the Platform drop-down list.

3. Set the header file path for the compiler.

   Click the VC++ Directories tab, and edit the Include Directories field to add the header file path of the OceanBase Connector/C library.

4. Set the link library path for the compiler.

   Click the VC++ Directories tab, and edit the Library Directories field to add the link library path of the OceanBase Connector/C library.

5. Set the character set.

   Click the Advanced tab and find the Character Set field. Select Use Multi-Byte Character Set from the drop-down list.

Step 3: Obtain the connection information of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection string.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p******

where

• -h specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
• -P specifies 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.
• -u specifies 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.
• -p specifies the account password.

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

Step 4: Modify the database connection information in the c-oceanbase-capi project

Modify the database connection information in the test_tbl1.cpp file based on the information obtained in Step 3: Obtain the connection information of OceanBase Database.

Here is an example:

if (NULL == mysql_real_connect(&con_oboracle, "xxx.xxx.xxx.xxx", "sys@oracle001", "******", "sys", 2881, NULL, 0))

Step 5: Build the project

Choose Build > Build Solution. The output of the compiler and errors or warning messages if any are displayed during the build process.

Step 6: Run the application

Choose Debug > Start Debugging or choose Debug > Start Without Debugging to run the application.

Step 7: View the output

The output is displayed in the debug console. You can determine how to handle the output based on the design logic and code of the program.

Project code introduction

Click c-oceanbase-capi to download the project code, which is a compressed file named c-oceanbase-capi.zip.

After decompressing it, you will find a folder named c-oceanbase-capi. The directory structure is as follows:

c-oceanbase-capi
├─ c-oceanbase-capi.sln
├─ c-oceanbase-capi.vcxproj
├─ c-oceanbase-capi.vcxproj.filters
├─ c-oceanbase-capi.vcxproj.user
└─ test_tbl1.cpp

Here is a breakdown of the files and directories:

• c-oceanbase-capi.sln: the solution file in Visual Studio that manages one or more projects.
• c-oceanbase-capi.vcxproj: the project file in Visual Studio that describes the structure and configuration of a C/C++ project.
• c-oceanbase-capi.vcxproj.filters: the project filter file in Visual Studio that defines the directory structure and organization of files in the project.
• c-oceanbase-capi.vcxproj.user: the file that stores user-specific project settings.
• test_tbl1.cpp: the source code file that defines a data table and implements data table operations.

Code in test_tbl1.cpp

The test_tbl1.cpp file defines and creates a data table named test_tbl1 and implements the operations to insert data into and query data from the table.

To configure the test_tbl1.cpp file, perform the following steps:

1. Reference header files and library files.

   • Reference header files stdio.h, stdlib.h, and mysql.h.
   • Specify the specific library files Shlwapi.lib, ws2_32.lib, Crypt32.lib, Secur32.lib, and obclnt.lib to be linked by the linker. These library files are the Windows API library, WinSock library, encryption library, security library, and OceanBase Database client library.

   The sample code is as follows:

   #include <stdio.h>
   #include <stdlib.h>
   #include <mysql.h>
   #pragma comment(lib, "Shlwapi.lib")
   #pragma comment( lib, "ws2_32.lib" )
   #pragma comment(lib, "Crypt32.lib")
   #pragma comment(lib, "Secur32.lib")
   #pragma comment(lib, "obclnt.lib")
   

2. Define the main function.

   Define the entry function main for the program and return an integer value. In the main function, write the code related to database connection and data operations.

   The sample code is as follows:

   int main() {
       // Create a connection instance object.
       // Connect to the database.
       // Create a table.
       // Insert data.
       // Query data.
       // ...
   }
   

3. Declare variables and arrays.

   • Declare the con_oboracle variable of the MYSQL type. With this variable, you can create a connection instance.
   • Declare the character array sql_str whose length is 1024 and initialized value is { 0 }. This array stores SQL statements.

   The sample code is as follows:

       MYSQL con_oboracle;
       char sql_str[1024] = { 0 };
   

4. Initialize the connection object.

   Initialize the connection object con_oboracle and perform follow-up operations based on the initialization result. If the initialization fails, print an error message and return a non-zero value. If the initialization succeeds, print a success message and continue with subsequent operations. The steps are as follows:

   1. Call the mysql_init() function to initialize the connection object con_oboracle. The function returns a pointer to the initialized connection object, or NULL if the initialization fails. NULL is a macro that represents a null pointer. The NULL macro is compared with the return value of the mysql_init() function to determine whether the initialization is successful.

      1. Call the fprintf() function to print an error message to the standard error stream stderr. The first argument to the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual argument that replaces %s in the formatted string. mysql_error(&con_oboracle) is the error message returned for getting the con_oboracle connection object.

      2. Use return 1 to exit the main function and return a non-zero value if initialization fails. This value can be used to indicate the status where the program is abnormally terminated or the initialization fails.

   2. Print a success message when the initialization succeeds. This way, you can check in the console whether the initialization of the connection object is successful.

   The sample code is as follows:

       if (NULL == mysql_init(&con_oboracle)) {
           fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
           return 1;
       }
       printf("mysql_init() success\n");
   

5. Connect to the database.

   Connect to the database from the program, and perform follow-up operations based on the connection result. If the connection fails, print an error message and return a non-zero value. If the connection succeeds, print a success message and continue with subsequent operations. The steps are as follows:

   1. Call the mysql_real_connect() function to connect to the database. The function returns a pointer to the connected object, or NULL if the connection fails.

      1. Call the fprintf() function to print an error message to the standard error stream stderr. The first argument to the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual argument that replaces %s in the formatted string. mysql_error(&con_oboracle) is the error message returned for getting the con_oboracle connection object.

      2. Use return 1 to exit the main function and return a non-zero value when the connection fails. This value can be used to indicate the status where the program is abnormally terminated or the connection fails.

   2. Print a success message when the connection succeeds. This way, you can check in the console whether the connection to the database is successful.

   The sample code is as follows:

       if (NULL == mysql_real_connect(&con_oboracle, "your_ip", "your_user", "your_password", "your_schema", your_port, NULL, 0)) {
           fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
           return 1;
       }
       printf("mysql_real_connect() success\n");
   

   Parameters required to connect to OceanBase Database are described as follows:

   • your_ip: the IP address for connecting to OceanBase Database. For connection through ODP, this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.

   • your_user: 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.

   • your_password: the account password.

   • your_schema: the name of the schema to access.

   • your_port: 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.

6. Create a table.

   Execute an SQL statement to create a table. Print a success message if the table is created successfully. Print an error message and exit the program if the table creation fails. The steps are as follows:

   1. Call the sprintf_s() function to format and store an SQL string in a sql_str array. This SQL statement creates the test_tbl1 table that contains the id (primary key), name, and age columns.

   2. Call the mysql_query() function to execute the SQL statement. &con_oboracle is a pointer to the database connection object on which the SQL statement is executed. sql_str is the SQL statement to execute. Check whether the execution is successful. If the execution fails (with a non-zero return value), the following code block is executed.

      1. Call the fprintf() function to print an error message to the standard error stream stderr. The first argument to the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual argument that replaces %s in the formatted string. mysql_error(&con_oboracle) is the error message returned for getting the con_oboracle connection object.

      2. Close the connection to the database and release the resources of the connection object.

      3. Use return 1 to exit the main function and return the value 1 if the table creation fails, indicating that the program is abnormally terminated.

   3. Print a success message if the table is created successfully.

   The sample code is as follows:

       sprintf_s(sql_str, "CREATE TABLE test_tbl1(id NUMBER PRIMARY KEY, name VARCHAR2(50),age NUMBER NOT NULL)");
       if (mysql_query(&con_oboracle, sql_str) != 0) {
           fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
           mysql_close(&con_oboracle);
           return 1;
       }
       printf("Table created successfully\n");
   

7. Insert data.

   Execute an SQL statement to insert data into a table. If the data is inserted successfully, print a success message. If the insertion fails, print an error message and exit the program. The steps are as follows:

   1. Call the sprintf_s() function to format and store an SQL string in a sql_str array. This SQL statement inserts data into the test_tbl1 table, including id, name, and age values.

   2. Call the mysql_query() function to execute the SQL statement. &con_oboracle is a pointer to the database connection object on which the SQL statement is executed. sql_str is the SQL statement to execute. Check whether the execution is successful. If the execution fails (with a non-zero return value), the following code block is executed.

      1. Call the fprintf() function to print an error message to the standard error stream stderr. The first argument to the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual argument that replaces %s in the formatted string. mysql_error(&con_oboracle) is the error message returned for getting the con_oboracle connection object.

      2. Close the connection to the database and release the resources of the connection object.

      3. Use return 1 to exit the main function and return the value 1 if data insertion fails, indicating that the program is abnormally terminated.

   3. Print a success message if the data is successfully inserted.

   The sample code is as follows:

       sprintf_s(sql_str, "INSERT INTO test_tbl1 (id,name,age) VALUES  (1,'Tom', 18),(2,'Jerry', 20),(3,'Bob', 22)");
       if (mysql_query(&con_oboracle, sql_str) != 0) {
           fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
           mysql_close(&con_oboracle);
           return 1;
       }
       printf("Data inserted successfully\n");
   

8. Query data.

   Execute an SQL statement to retrieve data from the table and print the retrieved data row by row. If the data is retrieved successfully, print a success message. If the retrieval fails, print an error message and exit the program. The steps are as follows:

   1. Call the sprintf_s() function to format and store an SQL string in a sql_str array. This SQL statement retrieves data of all columns in the test_tbl1 table.

   2. Call the mysql_query() function to execute the SQL statement. &con_oboracle is a pointer to the database connection object on which the SQL statement is executed. sql_str is the SQL statement to execute. Check whether the execution is successful. If the execution fails (with a non-zero return value), the following code block is executed.

      1. Call the fprintf() function to print an error message to the standard error stream stderr. The first argument to the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual argument that replaces %s in the formatted string. mysql_error(&con_oboracle) is the error message returned for getting the con_oboracle connection object.

      2. Close the connection to the database and release the resources of the connection object.

      3. Use return 1 to exit the main function and return the value 1 if data retrieval fails, indicating that the program is abnormally terminated.

   3. Use the mysql_store_result() function to store the query result set in a MYSQL_RES structure. &con_oboracle is a pointer to a database connection object from which the result set is obtained.

   4. Check whether the result set is empty. If the result set is empty, the query result set fails to be stored. In this case, execute the following code block.

      1. Call the fprintf() function to print an error message to the standard error stream stderr. The first argument to the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual argument that replaces %s in the formatted string. mysql_error(&con_oboracle) is the error message returned for getting the con_oboracle connection object.

      2. Close the connection to the database and release the resources of the connection object.

      3. Use return 1 to exit the main function and return the value 1 if the query result set fails to be stored, indicating that the program is abnormally terminated.

   5. Print a success message if the data is successfully retrieved.

   6. Define the row variable of MYSQL_ROW type to store data in each row.

   7. Call the mysql_fetch_row() function to retrieve the data in the result set row by row and store the data in each row in the row variable. The while loop iterates until all rows in the result set are retrieved. In the loop, print the data of each row. row[0], row[1], and row[2] represent the values of the first, second, and third columns in the current row, respectively.

   The sample code is as follows:

       sprintf_s(sql_str, "SELECT * FROM test_tbl1");
       if (mysql_query(&con_oboracle, sql_str) != 0) {
           fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
           mysql_close(&con_oboracle);
           return 1;
       }
   
       MYSQL_RES* result = mysql_store_result(&con_oboracle);
       if (result == NULL) {
           fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
           mysql_close(&con_oboracle);
           return 1;
       }
   
       printf("Data retrieved successfully\n");
   
       MYSQL_ROW row;
       while ((row = mysql_fetch_row(result)) != NULL) {
           printf("id: %s, name: %s, age: %s\n", row[0], row[1], row[2]);
       }
   

9. End the program.

   Release resources and close the connection to the database. The steps are as follows:

   1. Use mysql_free_result(result) to release the result set resources stored by the mysql_store_result() function. Here, result is a pointer to the MYSQL_RES structure and stores information and data of the query result set.

   2. Use mysql_close(&con_oboracle) to close the connection to the database and release the resources occupied by the connection object. Here, &con_oboracle is a pointer to the database connection object. You can call the mysql_close() function to close the connection to the database and release the resources related to the connection object.

   3. Use return 0; to indicate the end of the normal execution of the program, and return 0 as the exit status of the program.

   The sample code is as follows:

       mysql_free_result(result);
   
       mysql_close(&con_oboracle);
       printf("finish\n");
   
       return 0;
   }
   

Complete code examples

#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
#pragma comment(lib, "Shlwapi.lib")
#pragma comment( lib, "ws2_32.lib" )
#pragma comment(lib, "Crypt32.lib")
#pragma comment(lib, "Secur32.lib")
#pragma comment(lib, "obclnt.lib")

int main() {
    MYSQL con_oboracle;
    char sql_str[1024] = { 0 };

    if (NULL == mysql_init(&con_oboracle)) {
        fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
        return 1;
    }
    printf("mysql_init() success\n");

    if (NULL == mysql_real_connect(&con_oboracle, "your_ip", "your_user", "your_password", "your_schema", your_port, NULL, 0)) {
        fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
        return 1;
    }
    printf("mysql_real_connect() success\n");

    sprintf_s(sql_str, "CREATE TABLE test_tbl1(id NUMBER PRIMARY KEY, name VARCHAR2(50),age NUMBER NOT NULL)");
    if (mysql_query(&con_oboracle, sql_str) != 0) {
        fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
        mysql_close(&con_oboracle);
        return 1;
    }
    printf("Table created successfully\n");

    sprintf_s(sql_str, "INSERT INTO test_tbl1 (id,name,age) VALUES  (1,'Tom', 18),(2,'Jerry', 20),(3,'Bob', 22)");
    if (mysql_query(&con_oboracle, sql_str) != 0) {
        fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
        mysql_close(&con_oboracle);
        return 1;
    }
    printf("Data inserted successfully\n");

    sprintf_s(sql_str, "SELECT * FROM test_tbl1");
    if (mysql_query(&con_oboracle, sql_str) != 0) {
        fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
        mysql_close(&con_oboracle);
        return 1;
    }

    MYSQL_RES* result = mysql_store_result(&con_oboracle);
    if (result == NULL) {
        fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle));
        mysql_close(&con_oboracle);
        return 1;
    }

    printf("Data retrieved successfully\n");
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result)) != NULL) {
        printf("id: %s, name: %s, age: %s\n", row[0], row[1], row[2]);
    }

    mysql_free_result(result);

    mysql_close(&con_oboracle);
    printf("finish\n");

    return 0;
}

References

• For more information about how to connect to OceanBase Database, see Overview of connection methods.

• For more information about OceanBase Connector/C, see OceanBase Connector/C.