Connect to OceanBase Cloud by using OceanBase Connector/ODBC

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

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

The files and directories are described as follows:

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

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

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

   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. %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 application if an error is present. Perform the following steps:

   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 application. When an error occurs in an ODBC function call, assert aborts the application.

   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 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() {
       // 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. Perform the following steps:

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

      • SQL_HANDLE_ENV: the type of handle to be allocated. 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 no parent handle is used.
      • &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 application.

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

   1. After the SQLSetEnvAttr function is executed, return a value of the SQLRETURN type to indicate the execution result. Store the 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 application.

   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 an ODBC connection handle, and call the ASSERT_CHECK function to check the allocation result. Perform the following steps:

   1. After the SQLAllocHandle function is executed, return a value of the SQLRETURN type to indicate the execution result. Store the 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. In this example, it 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 application.

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

   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 URL for connecting to the database.
      • Port=your_port: the port number for connecting to the database.
      • Database=your_schema: the name of the database to connect to.
      • 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: a buffer of the SQLCHAR type that 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 the 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 application.

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

   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.

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

    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, execute the CREATE TABLE statement 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 to insert 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. Perform the following steps:

    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, execute the INSERT INTO statement 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 to obtain 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. Perform the following steps:

    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, execute the SELECT statement that retrieves 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 the id and age variables of the SQLINTEGER type 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 id variable.

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

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

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

    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 application 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

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