Connect to OceanBase Cloud using OceanBase Connector/C

This topic describes how to use OceanBase Connector/C to build an application that connects to OceanBase Cloud and performs basic operations such as creating tables, inserting data, and querying data.

Download the c-oceanbase-capi sample project Connect to OceanBase Cloud using OceanBase Connector/C (Oracle compatible mode)

Prerequisites

• You have registered for an OceanBase Cloud account, created an instance and an Oracle-compatible tenant. For more information, see Create an instance and Create a tenant.
• You have installed the OceanBase Connector/C driver. Contact Technical Support for the installation package of the OceanBase Connector/C driver for Windows.
• You have installed Visual Studio.

Procedure

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

Step 1: Open the c-oceanbase-capi project

1. Start Visual Studio Community 2019.

2. Open an existing project.

   1. On the start page of Visual Studio Community 2019, click Open Project or Solution (P) under Get Started. Alternatively, on the start page of Visual Studio Community 2019, click Continue without code (W) under Get Started, and then choose File > Open > Project/Solution (P).

   2. Navigate to the folder of the c-oceanbase-capi project, select the project file (c-oceanbase-capi.sln or c-oceanbase-capi.vcxproj), and then click Open.

Step 2: Configure the project properties

1. Open the project properties page.

   Right-click the selected project in Solution Explorer, and then choose Attribute from the shortcut menu. Alternatively, choose Projects > Attribute from the menu bar of Visual Studio. Alternatively, press the shortcut key Alt + Enter.

2. Configure the configuration manager.

   1. On the project properties page, select the Configuration (C) drop-down list at the top of the page and choose Debug from the drop-down list.

   2. On the project properties page, select the Platform (P) drop-down list at the top of the page and choose x64 from the drop-down list.

3. Set the header file path of the compiler.

   On the project properties page, select the VC++ Directories tab, and then add the header file path of the OceanBase Connector/C library in the Include Directories field.

4. Set the library path of the compiler.

   On the project properties page, select the VC++ Directories tab, and then add the library path of the OceanBase Connector/C library in the Library Directories field.

5. Set the character set.

   On the project properties page, select the Advanced tab, and then add the Character Set field. In the drop-down list, select Use Multi-Byte Character Set.

Step 3: Obtain the connection information of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. In the instance list, find the target instance and expand its information. In the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the corresponding information in the URL based on the OceanBase Cloud database that you have created.

   Here is an example:

   obclient -h t********.********.oceanbase.cloud` -P1521 -u oracle001 -p******
   

   Parameter description:

   • -h: specifies the connection address of the OceanBase Cloud database. For example, t********.********.oceanbase.cloud.
   • -P: specifies the connection port of the OceanBase Cloud database. The default value is 1521.
   • -u: specifies the account for accessing the database.
   • -p: specifies the password of the account.

For more information about the connection string, see Connect to an OceanBase 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 the OceanBase Cloud database.

Here is an example:

if (NULL == mysql_real_connect(&con_oboracle, "t********.********.oceanbase.cloud", "oracle001", "******", "sys", 1521, NULL, 0))

Step 5: Build the project

Choose Build > Build Solution. The compiler output and any error or warning messages are displayed during the build.

Step 6: Run the application

Choose Debug > Start Debugging or Start Execution (Without Debugging) to run the application.

Step 7: View the output

The output is displayed in the debug console. You can process the output based on the program logic and code.

Project code

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

After decompressing the file, 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

File description:

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

Introduction to the test_tbl1.cpp file

The test_tbl1.cpp file defines a data table named test_tbl1 and implements the creation, insertion, and query operations of the data table.

The test_tbl1.cpp file contains the following code:

1. Include header files and libraries.

   • Include the stdio.h, stdlib.h, and mysql.h header files.
   • Specify the libraries that the linker needs to link to: Shlwapi.lib, ws2_32.lib, Crypt32.lib, Secur32.lib, and obclnt.lib. These libraries are the Windows API library, WinSock library, encryption library, security library, and the OceanBase Cloud client library for cloud databases.

   Sample 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")
   

2. Define the main function.

   Define the entry function main that returns an integer value. In the main function, write code for connecting to the database and performing data operations.

   Sample code:

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

3. Declare variables and arrays.

   • Declare a variable named con_oboracle of type MYSQL. By declaring this variable, you can create a connection instance.
   • Declare a character array named sql_str with a length of 1024 and initialize it to { 0 }. This array is used to store SQL query statements.

   Sample code:

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

4. Initialize the connection instance object.

   Initialize the connection object con_oboracle and process it 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 the subsequent operations. The specific steps are as follows:

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

      1. Use the fprintf() function to print the error message to the standard error stream stderr. The first parameter of the fprintf() function is the output stream, "Error: %s\n" is a formatted string, and mysql_error(&con_oboracle) is the actual parameter that replaces %s in the formatted string. mysql_error(&con_oboracle) is used to obtain the error information of the connection object con_oboracle.

      2. Use return 1 to exit the main function and return a non-zero value when the initialization fails. This value can be used to indicate the abnormal termination of the program or the failure of the initialization.

   2. Print a success message when the initialization is successful. This way, you can see whether the connection object is initialized successfully in the console.

   Sample code:

       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.

   Try to connect to the database in the program and process it 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 the subsequent operations. The specific steps are as follows:

   1. Call the mysql_real_connect() function to connect to the database. This function returns a pointer to the connected object. If the connection fails, it returns NULL.

      1. Use the fprintf() function to print the error message to the standard error stream stderr. The first parameter of the fprintf() function is the output stream, "Error: %s\n" is a formatted string, and mysql_error(&con_oboracle) is the actual parameter that replaces %s in the formatted string. mysql_error(&con_oboracle) is used to obtain the error information of the connection object con_oboracle.

      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 abnormal termination of the program or the failure of the connection.

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

   Sample code:

       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 for connecting to the OceanBase Cloud database:

   • your_ip: the IP address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.

   • your_user: the username for accessing the database.

   • your_password: the password for the username.

   • your_schema: the name of the schema to be accessed.

   • your_port: the port of the OceanBase Cloud database. The default value is 1521.

6. Create a table.

   Execute an SQL query statement to create a table. If the table is created successfully, print a success message. If the table creation fails, print an error message and exit the program. The specific steps are as follows:

   1. Use the sprintf_s() function to format an SQL query string and store it in the sql_str character array. This SQL query statement is used to create a table named test_tbl1, which contains the id (primary key), name, and age columns.

   2. Use the mysql_query() function to execute the SQL query statement. &con_oboracle is a pointer to the database connection object, which specifies the connection object to be queried. sql_str is the query statement to be executed. Then, check whether the query execution is successful. If the query execution fails (the return value is not 0), execute the following code block.

      1. Use the fprintf() function to print the error message to the standard error stream stderr. The first parameter of the fprintf() function is the output stream, "Error: %s\n" is a formatted string, and mysql_error(&con_oboracle) is the actual parameter that replaces %s in the formatted string. mysql_error(&con_oboracle) is used to obtain the error information of the connection object con_oboracle.

      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 a non-zero value 1 when the table creation fails. This value is used to indicate the abnormal termination of the program.

   3. If the table is created successfully, print a success message to indicate that the table is created.

   Sample code:

       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 query statement to insert data into the table. If the data is inserted successfully, print a success message. If the data insertion fails, print an error message and exit the program. The specific steps are as follows:

   1. Use the sprintf_s() function to format an SQL query string and store it in the sql_str character array. This SQL query statement is used to insert data into the test_tbl1 table. The inserted data includes the values of the id, name, and age columns.

   2. Use the mysql_query() function to execute the SQL query statement. &con_oboracle is a pointer to the database connection object, which specifies the connection object to be queried. sql_str is the query statement to be executed. Then, check whether the query execution is successful. If the query execution fails (the return value is not 0), execute the following code block.

      1. The fprintf() function prints the error message to the standard error stream stderr. The first parameter of the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual parameter used to replace %s in the formatted string. mysql_error(&con_oboracle) is used to obtain the error message of the connection object con_oboracle.

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

      3. Use return 1 to exit the main function when data insertion fails and return a non-zero value 1 to indicate an abnormal program termination.

   3. If the data is successfully inserted, print a success message to indicate that the data has been successfully inserted.

   Code:

       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 query to retrieve data from the table and print the retrieved data line by line. If the data retrieval is successful, print a success message; if the retrieval fails, print an error message and exit the program. The specific steps are as follows:

   1. Use the sprintf_s() function to format and store an SQL query string in the sql_str character array. This SQL query statement is used to retrieve all column data from the test_tbl1 table.

   2. Use the mysql_query() function to execute the SQL query statement. &con_oboracle is a pointer to the database connection object, specifying the connection object to be queried. sql_str is the query statement to be executed. Then, check whether the query execution is successful. If the query execution fails (the return value is not 0), execute the following code block.

      1. The fprintf() function prints the error message to the standard error stream stderr. The first parameter of the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual parameter used to replace %s in the formatted string. mysql_error(&con_oboracle) is used to obtain the error message of the connection object con_oboracle.

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

      3. Use return 1 to exit the main function when the query fails and return a non-zero value 1 to indicate an abnormal program termination.

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

   4. Check whether the result set is empty. If the result set is empty, it indicates that the query result set storage failed, and execute the following code block.

      1. The fprintf() function prints the error message to the standard error stream stderr. The first parameter of the fprintf() function is the output stream, "Error: %s\n" is the formatted string, and mysql_error(&con_oboracle) is the actual parameter used to replace %s in the formatted string. mysql_error(&con_oboracle) is used to obtain the error message of the connection object con_oboracle.

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

      3. Use return 1 to exit the main function when storing the query result set fails and return a non-zero value 1 to indicate an abnormal program termination.

   5. If the data retrieval is successful, print a success message to indicate that the data has been successfully retrieved.

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

   7. Use the mysql_fetch_row() function to retrieve data from the result set row by row and store the data of each row in the row variable. The while loop continues to iterate 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] respectively represent the values of the first, second, and third columns in the current row.

   Code:

       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. Program ends.

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

   1. Use mysql_free_result(result) to release the result set resources stored by the mysql_store_result() function. result is a pointer to a MYSQL_RES structure, storing the 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. By calling the mysql_close() function, you can close the connection to the database and release the resources related to the connection object.

   3. Use return 0; to indicate that the program has normally ended and return 0 as the program's exit status.

   Code:

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

References

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

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