Connect to OceanBase Database by using OceanBase Connector/ODBC

This topic introduces how to build an application by using OceanBase Connector/ODBC 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-odbc 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 Visual Studio.

• You have installed the OceanBase Connector/ODBC driver.

  Note

  You can download the installation package of OceanBase Connector/ODBC for Windows from OceanBase Download Center. To install OceanBase Connector/ODBC for Windows, follow the default instructions.

Procedure

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

Step 1: Open the c-oceanbase-odbc 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-odbc folder, select the project file c-oceanbase-odbc.sln or c-oceanbase-odbc.vcxproj, and click Open.

Step 2: Configure properties for the c-oceanbase-odbc 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 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 tenant by using OBClient.

Step 4: Modify the database connection information in the c-oceanbase-odbc 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:

char* mydriver = (char*)"Driver={OceanBase ODBC 2.0 Driver};Server=xxx.xxx.xxx.xxx;Port=2881;Database=sys;User=sys@oracle001;Password=******;Option=3;";

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-odbc to download the project code, which is a compressed file named c-oceanbase-odbc.zip.

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

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

Here is a breakdown of the files and directories:

• c-oceanbase-odbc.sln: the solution file in Visual Studio that manages one or more projects.
• c-oceanbase-odbc.vcxproj: the project file in Visual Studio that describes the structure and configuration of a C/C++ project.
• c-oceanbase-odbc.vcxproj.filters: the project filter file in Visual Studio that defines the directory structure and organization of files in the project.
• c-oceanbase-odbc.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.

   Reference header files stdio.h, assert.h, windows.h, sql.h, and sqlext.h.

   The sample code is as follows:

   #include <stdio.h>
   #include <assert.h>
   #include <windows.h>
   #include <sql.h>
   #include <sqlext.h>
   

2. Define the odbc_print_error function.

   This function is called to print error messages for ODBC-related errors that occur during the running process of the program. The steps are as follows:

   1. Set the function name to odbc_print_error. Specify the parameters SQLSMALLINT HandleType and SQLHANDLE Handle to indicate the handle type and handle corresponding to an error message.
   2. Define the variables that store error messages obtained from ODBC. The types and names of these variables are defined in the ODBC API.
   3. Call SQLGetDiagRec() to get the last generated error message in ODBC. The SQLState, NativeError, and SQLMessage arrays then store the relevant content of the error message.
   4. Call the fprintf function to print the error message to the standard output stream. [%s] (%d) %s\n is the print format, which uses a formatted string similar to that in the printf function. Here, %s is used to print data of the string type, %d is used to print integer data, and \n represents a line break. SQLState, NativeError, and SQLMessage correspond to the three parameters in the formatted string, and provide the content of the error message to print.

   The sample code is as follows:

   static void odbc_print_error(SQLSMALLINT HandleType, SQLHANDLE Handle)
   {
       SQLCHAR SQLState[6];
       SQLINTEGER NativeError;
       SQLCHAR SQLMessage[SQL_MAX_MESSAGE_LENGTH] = { 0 };
       SQLSMALLINT TextLengthPtr;
       SQLGetDiagRec(HandleType, Handle, 1, SQLState, &NativeError, SQLMessage, SQL_MAX_MESSAGE_LENGTH, &TextLengthPtr);
       fprintf(stdout, "[%s] (%d) %s\n", SQLState, NativeError, SQLMessage);
   }
   

3. Define the ASSERT_CHECK function.

   This function is used to check and process the return value of an ODBC function call. It checks whether the return value of the ODBC API function has an error, and prints the error message and exits the program if there is an error. The steps are as follows:

   1. Set the macro name to ASSERT_CHECK. Specify the parameters SQLSMALLINT HandleType, SQLHANDLE Handle, and SQLRETURN rcode to indicate the ODBC handle type, the ODBC handle, and the return value of an ODBC API function call, respectively.
   2. Use the if statement to judge the return value of an ODBC API function. If the return value is not equal to SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, an error occurs in the function call.
   3. Call the odbc_print_error function to print the ODBC error message.
   4. Call the assert function during the running of the program. When an error occurs in an ODBC function call, assert aborts the program.

   The sample code is as follows:

   static void ASSERT_CHECK(SQLSMALLINT HandleType, SQLHANDLE Handle, SQLRETURN rcode)
   {
       if (rcode != SQL_SUCCESS && rcode != SQL_SUCCESS_WITH_INFO) {
           odbc_print_error(HandleType, Handle);
           assert(0);
       }
   }
   

4. 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() {
       // Apply for an environment handle.
       // Set the version (an environment attribute) of ODBC.
       // Allocate a connection handle.
       // Connect to the data source.
       // Create a table.
       // Insert data.
       // Query data.
       // Release resource handles.
   }
   

5. Define variables.

   Define the henv variable, which is an ODBC environment handle object that manages ODBC connections and resource allocation. OutConnStr and OutConnStrLen store the connection string and the string length respectively. These parameters are passed to the SQLDriverConnect function when the application connects to the database to obtain the return value and some other connection information.

   The sample code is as follows:

       HENV henv;
       SQLCHAR OutConnStr[255];
       SQLSMALLINT OutConnStrLen;
   

6. Apply for an environment handle.

   Call the SQLAllocHandle function to allocate an ODBC environment handle, and call the ASSERT_CHECK function to check the allocation result. The steps are as follows:

   1. Return a value of the SQLRETURN type after the SQLAllocHandle function is executed to indicate the execution result. Store this return value in the rcode variable. The parameters of the SQLAllocHandle function are as follows:

      • SQL_HANDLE_ENV: the type of the handle to allocate. In this example, it is set to SQL_HANDLE_ENV, indicating that an environment handle is to be allocated.
      • SQL_NULL_HANDLE: the parent handle. In this example, it is set to SQL_NULL_HANDLE, indicating that there is no parent handle.
      • &henv: a pointer to the variable that stores the allocated handle. In this example, it is the pointer to the henv variable that stores the environment handle.

   2. Call the ASSERT_CHECK function to check the rcode value returned by the SQLAllocHandle function. If the rcode value is not SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, handle allocation fails, and the ASSERT_CHECK function calls the odbc_print_error function to print the error message and uses the assert macro to terminate the program.

   The sample code is as follows:

       SQLRETURN rcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
       ASSERT_CHECK(SQL_HANDLE_ENV, henv, rcode);
   

7. Set the version (an environment attribute) of ODBC.

   Call the SQLSetEnvAttr function to set the environment attributes of ODBC to influence the behavior of the database connection and the driver. Call the ASSERT_CHECK function to check the attribute setting result. The steps are as follows:

   1. Return a value of the SQLRETURN type after the SQLSetEnvAttr function is executed to indicate the execution result. Store this return value in the rcode variable. The parameters of the SQLSetEnvAttr function are as follows:

      • henv: the ODBC environment handle that specifies the environment object for which the attribute is set.
      • SQL_ATTR_ODBC_VERSION: the type of the environment attribute to be set. In this example, it is set to SQL_ATTR_ODBC_VERSION, indicating that the ODBC version is to be set.
      • (void*)SQL_OV_ODBC3: a pointer to the attribute value. In this example, it is set to SQL_OV_ODBC3, indicating that the new ODBC version is ODBC V3.x.
      • 0: the length of the attribute value. In this example, the attribute value is an enumerated constant, and therefore the length is set to 0.

   2. Call the ASSERT_CHECK function to check the rcode value returned by the SQLSetEnvAttr function. If the rcode value is not SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, attribute setting fails, and the ASSERT_CHECK function calls the odbc_print_error function to print the error message and uses the assert macro to terminate the program.

   The sample code is as follows:

       rcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
       ASSERT_CHECK(SQL_HANDLE_ENV, henv, rcode);
   

8. Allocate a connection handle.

   Call the SQLAllocHandle function to allocate a ODBC connection handle, and call the ASSERT_CHECK function to check the allocation result for successful handle allocation. The steps are as follows:

   1. Return a value of the SQLRETURN type after the SQLAllocHandle function is executed to indicate the execution result. Store this return value in the rcode variable. The parameters of the SQLAllocHandle function are as follows:

      • SQL_HANDLE_DBC: the type of handle to be allocated, which is set to SQL_HANDLE_DBC, indicating a connection handle.
      • henv: the allocated ODBC environment handle that acts as a parent handle to associate with the connection handle.
      • &hdbc: the pointer to the variable that stores the allocated connection handle.

   2. Call the ASSERT_CHECK function to check the rcode value returned by the SQLAllocHandle function. If the rcode value is not SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, handle allocation fails, and the ASSERT_CHECK function calls the odbc_print_error function to print the error message and uses the assert macro to terminate the program.

   The sample code is as follows:

       SQLHDBC hdbc;
       rcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
       ASSERT_CHECK(SQL_HANDLE_DBC, hdbc, rcode);
   

9. Connect to the data source.

   Call the SQLDriverConnect function to connect to the specified ODBC data source. Call the ASSERT_CHECK function to check the connection result. The steps are as follows:

   1. Define the connection string mydriver that describes the information required to connect to the database. The string contains the following parts:

      • Driver={OceanBase ODBC 2.0 Driver}: specifies that OceanBase ODBC 2.0 Driver is used as the driver.
      • Server=your_ip: the IP address of the database server.
      • Port=your_port: the port used to connect to the database.
      • Database=your_schema: the name of the database.
      • User=your_use: the username used to connect to the database.
      • Password=your_password: the password used to connect to the database.
      • Option=3: the connection option, which is set to 3, indicating a TCP/IP connection.

   2. Call the SQLDriverConnect function to connect to the specified ODBC data source. Return a value of the SQLRETURN type after the function is executed to indicate the execution result. Store this return value in the rcode variable. The parameters of the function are as follows:

      • hdbc: the ODBC connection handle for establishing a connection with the data source.
      • NULL: a reserved parameter, which is not used.
      • mydriver: the connection string that contains all the information needed to connect to the database, such as the driver name, database IP address, port number, database name, username, and password.
      • strlen((char*)mydriver) +1: the length of the connection string.
      • OutConnStr: the buffer of the SQLCHAR type, which stores the connection string.
      • 255: the length of the buffer.
      • &OutConnStrLen: the pointer to the variable that stores the actual length of the connection string.
      • SQL_DRIVER_NOPROMPT: a connection flag that specifies not to display any prompt for a connection.

   3. Call the ASSERT_CHECK function to check the rcode value returned by the SQLDriverConnect function. If the rcode value is not SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the connection fails, and the ASSERT_CHECK function calls the odbc_print_error function to print the error message and uses the assert macro to terminate the program.

   The sample code is as follows:

       char* mydriver = (char*)"Driver={OceanBase ODBC 2.0 Driver};Server=your_ip;Port=your_port;Database=your_schema;User=your_use;Password=your_password;Option=3;";
       rcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)mydriver, strlen((char*)mydriver) + 1, OutConnStr, 255, &OutConnStrLen, SQL_DRIVER_NOPROMPT);
       ASSERT_CHECK(SQL_HANDLE_DBC, hdbc, rcode);
   

   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_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.
   • your_schema: the name of the schema to access.
   • 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.

10. Create a table.

    Call the SQLAllocHandle function to allocate an ODBC statement handle, and then call the SQLExecDirect function to execute an SQL statement to create a table. Use the ASSERT_CHECK function to check the results of handle allocation and statement execution. Call the SQLFreeHandle function to release the statement handle to free resources. The steps are as follows:

    1. Declare an ODBC statement handle stmt.

    2. Call the SQLAllocHandle function to allocate an ODBC statement handle.

    3. Call the ASSERT_CHECK function to check the rcode value returned by the SQLAllocHandle function.

    4. Call the SQLExecDirect function to execute an SQL statement. Here, the CREATE TABLE statement is executed to create a table named test_tbl1.

    5. Call the ASSERT_CHECK function to check the rcode value returned by the SQLExecDirect function.

    6. Call the SQLFreeHandle function to release the ODBC statement handle.

    The sample code is as follows:

        SQLHSTMT stmt;
        rcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
        ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);
    
        rcode = SQLExecDirect(stmt, (SQLCHAR*)"CREATE TABLE test_tbl1(id NUMBER PRIMARY KEY, name VARCHAR2(50),age NUMBER NOT NULL)", SQL_NTS);
        ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);
    
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    

11. Insert data.

    Call the SQLAllocHandle function to allocate an ODBC statement handle, and then call the SQLExecDirect function to execute an SQL statement that inserts data into a database table. Use the ASSERT_CHECK function to check the results of handle allocation and statement execution. Call the SQLFreeHandle function to release the statement handle to free resources. The steps are as follows:

    1. Call the SQLAllocHandle function to allocate an ODBC statement handle.

    2. Call the ASSERT_CHECK function to check the rcode value returned by the SQLAllocHandle function.

    3. Call the SQLExecDirect function to execute an SQL statement. Here, the INSERT INTO statement is executed to insert three records into the test_tbl1 table.

    4. Call the ASSERT_CHECK function to check the rcode value returned by the SQLExecDirect function.

    5. Call the SQLFreeHandle function to release the ODBC statement handle.

    The sample code is as follows:

        rcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
        ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);
    
        rcode = SQLExecDirect(stmt, (SQLCHAR*)"INSERT INTO test_tbl1 (id,name,age) VALUES  (1,'Tom', 18),(2,'Jerry', 20),(3,'Bob', 22)", SQL_NTS);
        ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);
    
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    

12. Query data.

    Call the SQLAllocHandle function to allocate an ODBC statement handle, and then call the SQLExecDirect function to execute an SQL statement that obtains the result set. Call the SQLBindCol function to bind the columns of the result set to variables, and then call the SQLFetch function to get the result set row by row. In the while loop, print the data in the result set based on the value of rcode. Call the SQLFreeHandle function to release the statement handle to free resources. The steps are as follows:

    1. Call the SQLAllocHandle function to allocate an ODBC statement handle.

    2. Call the ASSERT_CHECK function to check the rcode value returned by the SQLAllocHandle function.

    3. Call the SQLExecDirect function to execute an SQL statement. Here, the SELECT statement is executed to retrieve all rows from the test_tbl1 table.

    4. Call the ASSERT_CHECK function to check the rcode value returned by the SQLExecDirect function.

    5. Declare the res variable of the SQLLEN type to store the result of the column binding operation.

    6. Declare two variables of the SQLINTEGER type: id and age, to store the values of the corresponding columns in the result set.

    7. Declare the name array of the SQLCHAR type to store the values of the corresponding column in the result set.

    8. Call the SQLBindCol function to bind the first column in the result set to the variable id.

    9. Call the SQLBindCol function to bind the second column in the result set to the array name.

    10. Call the SQLBindCol function to bind the third column in the result set to the variable age.

    11. Call the SQLFetch function to get the rows in the result set and save data in the rows to the bound variables. The while loop iterates until all rows are fetched.

    12. In the while loop, print the data of the corresponding row based on the value of rcode. If the rcode value is SQL_ERROR, print an error message.

    13. Call the SQLFreeHandle function to release the ODBC statement handle.

    The sample code is as follows:

        rcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
        ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);
    
        rcode = SQLExecDirect(stmt, (SQLCHAR*)"SELECT * FROM test_tbl1", SQL_NTS);
        ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);
    
        SQLLEN res = SQL_NTS;
        SQLINTEGER id, age;
        SQLCHAR name[255];
        SQLBindCol(stmt, 1, SQL_C_SLONG, &id, sizeof(id), &res);
        SQLBindCol(stmt, 2, SQL_C_CHAR, name, sizeof(name), &res);
        SQLBindCol(stmt, 3, SQL_C_SLONG, &age, sizeof(age), &res);
    
        while ((rcode = SQLFetch(stmt)) != SQL_NO_DATA_FOUND)
        {
            if (rcode == SQL_ERROR) {
                printf("sql error!\n");
            }
            else {
                printf("id:%d, name:%s, age:%ld\n", id, name, age);
            }
        }
    
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    

13. Disconnect from the database and release resource handles.

    Close the database connection, and release the ODBC connection handle and environment handle to free resources. Call the SQLDisconnect and SQLFreeHandle functions to correctly close the connection to the database and release relevant handles. Return 0 to indicate that the program execution is successful. The steps are as follows:

    1. Call the SQLDisconnect function to disconnect from the database.

    2. Call the SQLFreeHandle function to release the ODBC connection handle.

    3. Call the SQLFreeHandle function to release the ODBC environment handle.

    4. Return 0 to indicate that the program execution is successful.

    The sample code is as follows:

        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
    
        return 0;
    

Complete code examples

#include <stdio.h>
#include <assert.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

static void odbc_print_error(SQLSMALLINT HandleType, SQLHANDLE Handle)
{
    SQLCHAR SQLState[6];
    SQLINTEGER NativeError;
    SQLCHAR SQLMessage[SQL_MAX_MESSAGE_LENGTH] = { 0 };
    SQLSMALLINT TextLengthPtr;
    SQLGetDiagRec(HandleType, Handle, 1, SQLState, &NativeError, SQLMessage, SQL_MAX_MESSAGE_LENGTH, &TextLengthPtr);
    fprintf(stdout, "[%s] (%d) %s\n", SQLState, NativeError, SQLMessage);
}

static void ASSERT_CHECK(SQLSMALLINT HandleType, SQLHANDLE Handle, SQLRETURN rcode)
{
    if (rcode != SQL_SUCCESS && rcode != SQL_SUCCESS_WITH_INFO) {
        odbc_print_error(HandleType, Handle);
        assert(0);
    }
}

int main()
{
    HENV henv;
    SQLCHAR OutConnStr[255];
    SQLSMALLINT OutConnStrLen;

    SQLRETURN rcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    ASSERT_CHECK(SQL_HANDLE_ENV, henv, rcode);

    rcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    ASSERT_CHECK(SQL_HANDLE_ENV, henv, rcode);

    SQLHDBC hdbc;
    rcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    ASSERT_CHECK(SQL_HANDLE_DBC, hdbc, rcode);

    char* mydriver = (char*)"Driver={OceanBase ODBC 2.0 Driver};Server=your_ip;Port=your_port;Database=your_schema;User=your_use;Password=your_password;Option=3;";
    rcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)mydriver, strlen((char*)mydriver) + 1, OutConnStr, 255, &OutConnStrLen, SQL_DRIVER_NOPROMPT);
    ASSERT_CHECK(SQL_HANDLE_DBC, hdbc, rcode);

    SQLHSTMT stmt;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
    ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);

    rcode = SQLExecDirect(stmt, (SQLCHAR*)"CREATE TABLE test_tbl1(id NUMBER PRIMARY KEY, name VARCHAR2(50),age NUMBER NOT NULL)", SQL_NTS);
    ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    rcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
    ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);

    rcode = SQLExecDirect(stmt, (SQLCHAR*)"INSERT INTO test_tbl1 (id,name,age) VALUES  (1,'Tom', 18),(2,'Jerry', 20),(3,'Bob', 22)", SQL_NTS);
    ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    rcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
    ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);

    rcode = SQLExecDirect(stmt, (SQLCHAR*)"SELECT * FROM test_tbl1", SQL_NTS);
    ASSERT_CHECK(SQL_HANDLE_STMT, stmt, rcode);

    SQLLEN res = SQL_NTS;
    SQLINTEGER id, age;
    SQLCHAR name[255];
    SQLBindCol(stmt, 1, SQL_C_SLONG, &id, sizeof(id), &res);
    SQLBindCol(stmt, 2, SQL_C_CHAR, name, sizeof(name), &res);
    SQLBindCol(stmt, 3, SQL_C_SLONG, &age, sizeof(age), &res);

    while ((rcode = SQLFetch(stmt)) != SQL_NO_DATA_FOUND)
    {
        if (rcode == SQL_ERROR) {
            printf("sql error!\n");
        }
        else {
            printf("id:%d, name:%s, age:%ld\n", id, name, age);
        }
    }

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    SQLDisconnect(hdbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

References

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

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