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);
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot