Connect to OceanBase Cloud by using OceanBase Connector/C

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

Decompress the package to obtain 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

The files and directories are described as follows:

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

Perform the following steps to configure the test_tbl1.cpp file:

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 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 application and return an integer value. In the main function, write the code related to database connections 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 initial 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. Perform the following steps:

   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 application 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 application, 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. Perform the following steps:

   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 if 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");
   

   The following parameters are required to connect to OceanBase Cloud:

   • your_ip: the access address of OceanBase Cloud. The value is sourced from the -h parameter in the connection string.
   • your_user: the account name. The value is sourced from the -u parameter in the connection string.
   • your_password: the account password. The value is sourced from the -p parameter in the connection string.
   • your_schema: the name of the database to be accessed. The value is sourced from the -D parameter in the connection string.
   • your_port: the access port of OceanBase Cloud. The value is sourced from the -P parameter in the connection string.

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. Perform the following steps:

   1. Call the sprintf_s() function to format and store an SQL statement in an 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 application 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 the table. If the data is inserted successfully, print a success message. If the insertion fails, print an error message and exit the application. Perform the following steps:

   1. Call the sprintf_s() function to format and store an SQL statement in an sql_str array. This SQL statement inserts id, name, and age column values into 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 the data insertion fails, indicating that the application 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 application. Perform the following steps:

   1. Call the sprintf_s() function to format and store an SQL statement in an 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 the query fails, indicating that the application 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 operation to store the query result set fails, indicating that the application is abnormally terminated.

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

   6. Define the row variable of the 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. Exit the application.

   Release resources and close the connection to the database. Perform the following steps:

   1. Use mysql_free_result(result) to release the result set resources stored by the mysql_store_result() function. result is a pointer to the MYSQL_RES structure. It 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. &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 application, and return 0 as the exit status of the application.

   The sample code is as follows:

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

Complete code

#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;
}