This topic describes how to build an application using OceanBase Connector/C and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a tenant.
- You have installed the OceanBase Connector/C driver. Contact technical support to obtain the installation package for the Windows version of the OceanBase Connector/C driver.
- You have installed Visual Studio.
Procedure
Note
The procedure described in this topic is based on using Visual Studio Community 2019 in a Windows environment to compile and run the project. If you are using a different operating system or compiler, the steps may vary slightly.
- Open the
c-oceanbase-capiproject. - Configure the properties of the
c-oceanbase-capiproject. - Obtain the connection information for the OceanBase Cloud database.
- Modify the database connection information in the
c-oceanbase-capiproject. - Build the project.
- Run the application.
- View the output results.
Step 1: Open the c-oceanbase-capi project
Start Visual Studio Community 2019.
Open an existing project.
On the start page of Visual Studio Community 2019, click Open Project or Solution (P) below Get Started. Alternatively, click Continue without code (W) below Get Started; then select File > Open > Project/Solution (P) from the top menu bar.
Navigate to the folder containing the c-oceanbase-capi project, select the project file (
c-oceanbase-capi.slnorc-oceanbase-capi.vcxproj), and click Open.
Step 2: Configure the properties of the c-oceanbase-capi project
Open the project's property page.
Right-click the selected project in Solution Explorer, then select Attribute from the context menu. Alternatively, select Project > Attribute from the top menu bar of Visual Studio, or use the shortcut key Alt + Enter.
Configure the management settings.
On the property page, find the Configuration (C) drop-down menu at the top. In the drop-down menu, select Debug.
On the property page, find the Platform (P) drop-down menu at the top. In the drop-down menu, select x64.
Set the compiler's header file path.
On the property page, select the VC++ Directory tab. Find the Include Directories section and add the header file path for the OceanBase Connector/C library.
Set the compiler's link library path.
On the property page, select the VC++ Directory tab. Find the Libraries section and add the link library path for the OceanBase Connector/C library.
Set the character set.
On the property page, select the Advanced tab. Find the Character Set section. In the drop-down menu, select Use multibyte character set.
Step 3: Obtain the connection information for the OceanBase Cloud database
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get connection string.
Fill in the corresponding information in the URL below based on the created OceanBase Cloud database.
Example:
obclient -h t********.********.oceanbase.cloud` -P1521 -u oracle001 -p******Parameter description:
-h: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.-P: The connection port of the OceanBase Cloud database. Default is 1521.-u: The account for accessing the database.-p: 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 OceanBase Cloud database connection information.
Example:
if (NULL == mysql_real_connect(&con_oboracle, "t********.********.oceanbase.cloud", "oracle001", "******", "sys", 1521, NULL, 0))
Step 5: Build the project
Select the Build menu and then choose Build Solution. The compiler output and any error or warning messages are displayed during the build process.
Step 6: Run the application
Select the Debug menu and then choose Start Debugging or Run Without Debugging to run the application.
Step 7: View the output results
The output results are displayed in the debug console. You can determine how to process the output based on the program's design logic and code.
Sample project overview
Click c-oceanbase-capi to download the project code, which is a compressed file named c-oceanbase-capi.zip.
After decompressing it, you will get 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 a data table and implements operations on the data table.
Introduction to the code in the test_tbl1.cpp file
The test_tbl1.cpp file defines a data table named test_tbl1 and implements operations such as creating the data table, inserting data into it, and querying data from it.
The code in the test_tbl1.cpp file mainly consists of the following parts:
Include header files and library files.
- Include the header files
stdio.h,stdlib.h, andmysql.h. - Specify that the linker should link to the specific library files
Shlwapi.lib,ws2_32.lib,Crypt32.lib,Secur32.lib, andobclnt.lib. These library files are the Windows API library, the WinSock library, the encryption library, the security library, and the OceanBase Cloud database client library, respectively.
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")- Include the header files
Define the
mainfunction.Define the program's entry function
main, which returns an integer value. In themainfunction, write code related to database connection and data manipulation.Code:
int main() { // Create a connection instance object. // Connect to the database // Create a table // Insert data // Query data // ... }Declare variables and arrays.
- Declare a variable named
con_oboracleof type MYSQL. By declaring this variable, we can create a connection instance. - Declare a character array named
sql_strwith a length of1024and initialize it with{ 0 }. This array is used to store SQL query statements.
Code:
MYSQL con_oboracle; char sql_str[1024] = { 0 };- Declare a variable named
Initialize the connection instance object.
Initialize the connection object
con_oboracleand perform corresponding processing based on the initialization result. If initialization fails, print an error message and return a non-zero value; if initialization succeeds, print a success message and continue to execute subsequent operations. The specific steps are as follows:Call the
mysql_init()function to initialize the connection objectcon_oboracle. This function returns a pointer to the initialized connection object; if initialization fails, it returnsNULL.NULLis a macro that represents a null pointer. CompareNULLwith the return value ofmysql_init()to determine whether initialization succeeded.Use the
fprintf()function to print error messages to the standard error streamstderr. The first parameter of thefprintf()function is the output stream,"Error: %s\n"is the format string, andmysql_error(&con_oboracle)is the actual argument used to replace the%sin the format string.mysql_error(&con_oboracle)retrieves the error information for the connection objectcon_oboracle.Use
return 1to exit themainfunction and return a non-zero value when initialization fails. This value can indicate that the program terminated abnormally or initialization failed.
Print a success message if initialization succeeds. This allows you to see whether the connection object was successfully initialized in the console.
Code:
if (NULL == mysql_init(&con_oboracle)) { fprintf(stderr, "Error: %s\n", mysql_error(&con_oboracle)); return 1; } printf("mysql_init() success\n");Connect to the database.
In the program, attempt to connect to the database and perform corresponding processing 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 proceed to the subsequent operations. The specific steps are as follows:
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 returnsNULL.Use the
fprintf()function to print error messages to the standard error streamstderr. The first parameter offprintf()is the output stream,"Error: %s\n"is the format string, andmysql_error(&con_oboracle)is the actual argument that replaces the%sin the format string.mysql_error(&con_oboracle)retrieves the error information for the connection objectcon_oboracle.Use
return 1to exit themainfunction and return a non-zero value when the connection fails. This value can indicate that the program terminated abnormally or the connection failed.
When the connection succeeds, print a success message. This allows you to see from the console whether the database was successfully connected.
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");Parameter description for connecting to OceanBase Cloud:
your_ip: The connection address for OceanBase Cloud, for example,t********.********.oceanbase.cloud.your_user: The account used to access the database.your_password: The password for the account.your_schema: The name of the schema to be accessed.your_port: The port for connecting to OceanBase Cloud, default is 1521.
Create a table.
Execute an SQL query statement to create a table. If the table is created successfully, print a success message; if creation fails, print an error message and exit the program. The specific steps are as follows:
Use the
sprintf_s()function to format an SQL query string and store it in thesql_strcharacter array. This SQL query statement creates a table namedtest_tbl1, which contains columnsid(primary key),name, andage.Use the
mysql_query()function to execute the SQL query statement.&con_oboracleis a pointer to the database connection object, specifying the object to be queried.sql_stris the query statement to be executed. Then check whether the query execution was successful. If the query fails (the return value is not 0), execute the following code block.Use the
fprintf()function to print error messages to the standard error streamstderr. The first parameter offprintf()is the output stream,"Error: %s\n"is the format string, andmysql_error(&con_oboracle)is the actual argument that replaces the%sin the format string.mysql_error(&con_oboracle)retrieves the error information for the connection objectcon_oboracle.Close the connection to the database and release the resources of the connection object.
Use
return 1to exit themainfunction and return a non-zero value1when table creation fails, indicating that the program terminated abnormally.
If the table is created successfully, print a success message indicating the table was created successfully.
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");Insert data.
Execute an SQL query statement to insert data into the table. If the data is inserted successfully, print a success message; if insertion fails, print an error message and exit the program. The specific steps are as follows:
Use the
sprintf_s()function to format an SQL query string and store it in thesql_strcharacter array. This SQL query statement is used to insert data into thetest_tbl1table, including values for theid,name, andagecolumns.Use the
mysql_query()function to execute the SQL query statement.&con_oboracleis a pointer to a database connection object, specifying the connection object to be queried.sql_stris the query statement to be executed. Then, check whether the query execution was successful. If the query fails (the return value is not 0), execute the following code block.Use the
fprintf()function to print error messages to the standard error streamstderr. The first parameter offprintf()is the output stream,"Error: %s\n"is the format string, andmysql_error(&con_oboracle)is the actual parameter that replaces%sin the format string.mysql_error(&con_oboracle)retrieves the error information from the connection objectcon_oboracle.Close the connection to the database and release the resources of the connection object.
Use
return 1to exit themainfunction when data insertion fails, returning a non-zero value1to indicate an abnormal program termination status.
If data insertion succeeds, print a success message indicating that the data has been successfully inserted.
Sample 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");Query data.
Execute an SQL query to retrieve data from the table and print each row of retrieved data. If data retrieval succeeds, print a success message; if retrieval fails, print an error message and exit the program. The specific steps are as follows:
Use the
sprintf_s()function to format an SQL query string and store it in thesql_strcharacter array. This SQL query statement is used to retrieve data from all columns in thetest_tbl1table.Use the
mysql_query()function to execute the SQL query statement.&con_oboracleis a pointer to a database connection object, specifying the connection object to be queried.sql_stris the query statement to be executed. Then, check whether the query execution was successful. If the query fails (the return value is not 0), execute the following code block.Use the
fprintf()function to print error messages to the standard error streamstderr. The first parameter offprintf()is the output stream,"Error: %s\n"is the format string, andmysql_error(&con_oboracle)is the actual parameter that replaces%sin the format string.mysql_error(&con_oboracle)retrieves the error information from the connection objectcon_oboracle.Close the connection to the database and release the resources of the connection object.
Use
return 1to exit themainfunction when the query fails, returning a non-zero value1to indicate an abnormal program termination status.
Use the
mysql_store_result()function to store the query result set in aMYSQL_RESstructure.&con_oboracleis a pointer to a database connection object, specifying the connection object from which to retrieve the result set.Check whether
mysql_store_result()returnedNULL. For this SELECT query,NULLindicates a failure to retrieve the result set, rather than a result set with zero rows.Use the
fprintf()function to print error messages to the standard error streamstderr. The first parameter offprintf()is the output stream,"Error: %s\n"is the format string, andmysql_error(&con_oboracle)is the actual parameter that replaces%sin the format string.mysql_error(&con_oboracle)retrieves the error information from the connection objectcon_oboracle.Close the connection to the database and release the resources of the connection object.
Use
return 1to exit themainfunction when storing the query result set fails, returning a non-zero value1to indicate an abnormal program termination status.
If data retrieval succeeds, print a success message indicating that the data has been successfully retrieved.
Define a variable
rowof typeMYSQL_ROWto store each row of data.Use the
mysql_fetch_row()function to retrieve data row by row from the result set and store each row's data in therowvariable. Thewhileloop continues until all rows in the result set have been retrieved. Inside the loop, print the data of each row.row[0],row[1], androw[2]represent the values of the first, second, and third columns in the current row, respectively.
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]); }The program ends.
Release resources and close the database connection. The specific steps are as follows:
Use
mysql_free_result(result)to release the result set resources stored by themysql_store_result()function.resultis a pointer to aMYSQL_RESstructure that stores information and data about the query result set.Use
mysql_close(&con_oboracle)to close the database connection and release the resources occupied by the connection object.&con_oboracleis a pointer to the database connection object. By calling themysql_close()function, you can close the database connection and release the resources related to the connection object.Use
return 0;to indicate that the program has ended normally and return0as the program's exit status.
Code:
mysql_free_result(result); mysql_close(&con_oboracle); printf("finish\n"); return 0; }
Complete 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")
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.