Updated on 2025-09-04 GMT+08:00

Complete Example

/**********************************************************************
 * Enable UseBatchProtocol in the data source and set the database parameter support_batch_bind to on.
 * CHECK_ERROR and CHECK_ERROR_VOID are used to check for and print error information.
 * This example interactively obtains the DSN and the data size for batch binding, and inserts the final data into test_odbc_batch_insert.
 ***********************************************************************/
#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#define CHECK_ERROR(e, s, t, h)                               \
    ({                                                        \
        if (e != SQL_SUCCESS && e != SQL_SUCCESS_WITH_INFO) { \
            fprintf(stderr, "FAILED:\t");                     \
            print_diag(s, h, t);                              \
            goto exit;                                        \
        }                                                     \
    })
#define CHECK_ERROR_VOID(e, s, t, h)                          \
    ({                                                        \
        if (e != SQL_SUCCESS && e != SQL_SUCCESS_WITH_INFO) { \
            fprintf(stderr, "FAILED:\t");                     \
            print_diag(s, h, t);                              \
        }                                                     \
    })
#define BATCH_SIZE 100  // Data size to be bound in batches.
// Print error information.
void print_diag(char *msg, SQLSMALLINT htype, SQLHANDLE handle);
// Execute SQL statements.
void Exec(SQLHDBC hdbc, SQLCHAR *sql)
{
    SQLRETURN retcode;                // Returned error code.
    SQLHSTMT hstmt = SQL_NULL_HSTMT;  // Statement handle.
    SQLCHAR loginfo[2048];
    // Allocate a statement handle.
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (!SQL_SUCCEEDED(retcode)) {
        printf("SQLAllocHandle(SQL_HANDLE_STMT) failed");
        return;
    }
    // Prepare statements.
    retcode = SQLPrepare(hstmt, (SQLCHAR *)sql, SQL_NTS);
    sprintf((char *)loginfo, "SQLPrepare log: %s", (char *)sql);
    if (!SQL_SUCCEEDED(retcode)) {
        printf("SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS) failed");
        return;
    }
    // Execute statements.
    retcode = SQLExecute(hstmt);
    sprintf((char *)loginfo, "SQLExecute stmt log: %s", (char *)sql);
    if (!SQL_SUCCEEDED(retcode)) {
        printf("SQLExecute(hstmt) failed");
        return;
    }
    // Release the handle.
    retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    sprintf((char *)loginfo, "SQLFreeHandle stmt log: %s", (char *)sql);
    if (!SQL_SUCCEEDED(retcode)) {
        printf("SQLFreeHandle(SQL_HANDLE_STMT, hstmt) failed");
        return;
    }
}
int main()
{
    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLLEN rowsCount = 0;
    int i = 0;
    SQLRETURN retcode;
    SQLCHAR dsn[1024] = {'\0'};
    SQLCHAR loginfo[2048];
    // Allocate an environment handle.
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if (!SQL_SUCCEEDED(retcode)) {
        printf("SQLAllocHandle failed");
        goto exit;
    }
    CHECK_ERROR(retcode, "SQLAllocHandle henv", henv, SQL_HANDLE_ENV);
    // Set the ODBC version.
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER *)SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr", henv, SQL_HANDLE_ENV);
    // Allocate connections.
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle hdbc", hdbc, SQL_HANDLE_DBC);
    // Set the login timeout.
    retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr SQL_LOGIN_TIMEOUT", hdbc, SQL_HANDLE_DBC);
    // Disable the autocommit option in order to use transactions for commit.
    retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr SQL_ATTR_AUTOCOMMIT", hdbc, SQL_HANDLE_DBC);
    // Establish a database connection.
    retcode = SQLConnect(hdbc, (SQLCHAR *)"gaussdb", SQL_NTS, (SQLCHAR *)"", 0, (SQLCHAR *)"", 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr SQL_ATTR_AUTOCOMMIT", hdbc, SQL_HANDLE_DBC);
    printf("SQLConnect success\n");
    // Initialize table information.
    Exec(hdbc, "DROP TABLE IF EXISTS test_odbc_batch_insert");
    Exec(hdbc, "CREATE TABLE test_odbc_batch_insert (id INT PRIMARY KEY, col VARCHAR2(50))");
    // Commit the transaction in segments for other SQL operations.
    retcode = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    CHECK_ERROR(retcode, "SQLEndTran", hdbc, SQL_HANDLE_DBC);
    // Construct the data to be inserted in batches based on the user-specified data size.
    {
        SQLRETURN retcode;
        SQLHSTMT hstmt = SQL_NULL_HSTMT;
        SQLCHAR *sql = NULL;
        SQLINTEGER *ids = NULL;
        SQLCHAR *cols = NULL;
        SQLLEN *bufLenIds = NULL;
        SQLLEN *bufLenCols = NULL;
        SQLUSMALLINT *operptr = NULL;
        SQLUSMALLINT *statusptr = NULL;
        SQLULEN process = 0;
        // Construct fields by column.
        ids = (SQLINTEGER *)malloc(sizeof(ids[0]) * BATCH_SIZE);
        cols = (SQLCHAR *)malloc(sizeof(cols[0]) * BATCH_SIZE * 50);
        // Memory length for each row of data with each field.
        bufLenIds = (SQLLEN *)malloc(sizeof(bufLenIds[0]) * BATCH_SIZE);
        bufLenCols = (SQLLEN *)malloc(sizeof(bufLenCols[0]) * BATCH_SIZE);
        if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds) {
            fprintf(stderr, "FAILED:\tmalloc data memory failed\n");
            goto exit;
        }
        // Assign values to data.
        for (i = 0; i < BATCH_SIZE; i++) {
            ids[i] = i;
            sprintf(cols + 50 * i, "column test value %d", i);
            bufLenIds[i] = sizeof(ids[i]);
            bufLenCols[i] = strlen(cols + 50 * i);
        }
        // Allocate a statement handle.
        retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
        CHECK_ERROR(retcode, "SQLSetConnectAttr SQL_ATTR_AUTOCOMMIT", hstmt, SQL_HANDLE_STMT);
        // Prepare statements.
        sql = (SQLCHAR *)"INSERT INTO test_odbc_batch_insert VALUES(?, ?)";
        retcode = SQLPrepare(hstmt, (SQLCHAR *)sql, SQL_NTS);
        CHECK_ERROR(retcode, "SQLPrepare", hstmt, SQL_HANDLE_STMT);
        // Bind parameters.
        retcode = SQLBindParameter(
            hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0, &(ids[0]), 0, bufLenIds);
        CHECK_ERROR(retcode, "SQLBindParameter 1", hstmt, SQL_HANDLE_STMT);
        retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols);
        CHECK_ERROR(retcode, "SQLBindParameter 2", hstmt, SQL_HANDLE_STMT);
        // Set the total number of rows in the parameter array.
        retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)BATCH_SIZE, sizeof(BATCH_SIZE));
        CHECK_ERROR(retcode, "SQLSetStmtAttr", hstmt, SQL_HANDLE_STMT);
        // Set the number of processed rows.
        retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process));
        CHECK_ERROR(retcode, "SQLSetStmtAttr SQL_ATTR_PARAMS_PROCESSED_PTR", hstmt, SQL_HANDLE_STMT);
        // Execute batch insertion.
        retcode = SQLExecute(hstmt);
        // Manually commit the transaction.
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            retcode = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
            CHECK_ERROR(retcode, "SQLEndTran", hdbc, SQL_HANDLE_DBC);
        }
        // On failure, roll back the transaction.
        else {
            CHECK_ERROR_VOID(retcode, "SQLExecute", hstmt, SQL_HANDLE_STMT);
            retcode = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
            printf("Transaction rollback\n");
            CHECK_ERROR(retcode, "SQLEndTran", hdbc, SQL_HANDLE_DBC);
        }
        // Obtain the number of rows processed in batches.
        SQLRowCount(hstmt, &rowsCount);
        sprintf((char *)loginfo, "SQLRowCount : %ld", rowsCount);
        puts(loginfo);
        // Check whether the number of inserted rows matches the number of processed rows.
        if (rowsCount != process) {
            sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount);
            puts(loginfo);
        } else {
            sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount);
        }
        retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        CHECK_ERROR(retcode, "SQLFreeHandle", hstmt, SQL_HANDLE_STMT);
    }
exit:
    (void)printf("Complete.\n");
    // Close the connection.
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    // Release the environment handle.
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
    return 0;
}
void print_diag(char *msg, SQLSMALLINT htype, SQLHANDLE handle)
{
    char sqlstate[32];
    char message[1000];
    SQLINTEGER nativeerror;
    SQLSMALLINT textlen;
    SQLRETURN ret;
    SQLSMALLINT recno = 0;
    if (msg)
        printf("%s\n", msg);
    do {
        recno++;
        // Obtain diagnostic information.
        ret = SQLGetDiagRec(
            htype, handle, recno, (SQLCHAR *)sqlstate, &nativeerror, (SQLCHAR *)message, sizeof(message), &textlen);
        if (ret == SQL_INVALID_HANDLE)
            printf("Invalid handle\n");
        else if (SQL_SUCCEEDED(ret))
            printf("%s=%s\n", sqlstate, message);
    } while (ret == SQL_SUCCESS);
    if (ret == SQL_NO_DATA && recno == 1)
        printf("No error information\n");
}

Result Verification

After successfully connecting to the database, ODBC inserts 100 data records in batches. Below are the expected results for Complete Example:

SQLConnect success
SQLRowCount : 100
Complete.

Rollback Method

If any abnormal operations occur during the transaction, call the SQLEndTran API to roll them back, as follows:

SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);