Help Center/
GaussDB/
Developer Guide(Centralized_8.x)/
Application Development Guide/
Development Based on ODBC/
Example: Common Functions and Batch Binding
Updated on 2024-08-20 GMT+08:00
Example: Common Functions and Batch Binding
- The following is an example of the command for compiling ODBC application code in Windows:
gcc odbctest.c -o odbctest -lodbc32
Run the following command:
./odbctest.exe
- The following is an example of the command for compiling ODBC application code in Linux:
gcc odbctest.c -o odbctest -lodbc
Run the following command:
./odbctest
- If sql.h or API cannot be found during compilation, manually connect to the header file and dynamic library of unixODBC.
gcc -I /home/omm/unixodbc/include -L /home/omm/unixodbc/lib odbctest.c -o odbctest -lodbc
Code for Common Functions
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
// The example shows how to obtain data from GaussDB through ODBC. // DBtest.c (compile with: libodbc.so) #ifdef WIN32 #include <windows.h> #endif #include <stdio.h> #include <stdlib.h> #include <sql.h> #include <sqlext.h> SQLHENV V_OD_Env; // Handle ODBC environment SQLHSTMT V_OD_hstmt; // Handle statement SQLHDBC V_OD_hdbc; // Handle connection char typename[100]; SQLINTEGER value = 100; SQLINTEGER V_OD_erg,V_OD_buffer,V_OD_err,V_OD_id; int main(int argc,char *argv[]) { // 1. Allocate an environment handle. V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHandle\n"); exit(0); } // 2. Set environment attributes (version information). SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); // 3. Allocate a connection handle. V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation). char *userName; userName = getenv("EXAMPLE_USERNAME_ENV"); char *password; password = getenv("EXAMPLE_PASSWORD_ENV"); // 4. Set connection attributes. SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_AUTOCOMMIT,(SQLPOINTER *)SQL_AUTOCOMMIT_ON, 0); // 5. Connect to the data source. userName and password indicate the username and password for connecting to the database, respectively. // If the username and password have been set in the odbc.ini file, you can retain "". However, you are advised not to do so because the username and password will be disclosed if the permission for odbc.ini is abused. V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "gaussdb", SQL_NTS, (SQLCHAR*) userName, SQL_NTS, (SQLCHAR*) password, SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SQLConnect %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Connected !\n"); // 6. Set statement attributes. SQLSetStmtAttr(V_OD_hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER *)3,0); // 7. Allocate a statement handle. SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt); // 8. Run SQL statements. SQLExecDirect(V_OD_hstmt,"drop table IF EXISTS customer_t1",SQL_NTS); SQLExecDirect(V_OD_hstmt,"CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));",SQL_NTS); SQLExecDirect(V_OD_hstmt,"insert into customer_t1 values(25,'li')",SQL_NTS); // 9. Prepare for execution. SQLPrepare(V_OD_hstmt,"insert into customer_t1 values(?)",SQL_NTS); // 10. Bind parameters. SQLBindParameter(V_OD_hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0, &value,0,NULL); // 11. Run prepared statements. SQLExecute(V_OD_hstmt); SQLExecDirect(V_OD_hstmt,"select c_customer_sk from customer_t1",SQL_NTS); // 12. Obtain attributes of a specific column in the result set. SQLColAttribute(V_OD_hstmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL); printf("SQLColAtrribute %s\n",typename); // 13. Bind the result set. SQLBindCol(V_OD_hstmt,1,SQL_C_SLONG, (SQLPOINTER)&V_OD_buffer,150, (SQLLEN *)&V_OD_err); // 14. Obtain data in the result set by executing SQLFetch. V_OD_erg=SQLFetch(V_OD_hstmt); // 15. Obtain and return data by executing SQLGetData. while(V_OD_erg != SQL_NO_DATA) { SQLGetData(V_OD_hstmt,1,SQL_C_SLONG,(SQLPOINTER)&V_OD_id,0,NULL); printf("SQLGetData ----ID = %d\n",V_OD_id); V_OD_erg=SQLFetch(V_OD_hstmt); }; printf("Done !\n"); // 16. Disconnect data source connections and release handles. SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); return(0); } |
Code for Batch Processing
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 |
/********************************************************************** * Enable UseBatchProtocol in the data source and set the database parameter support_batch_bind to on. * The CHECK_ERROR command is used to check and print error information. * This example is used to interactively obtain the DSN, data volume to be processed, and volume of ignored data from users, and insert required data into the test_odbc_batch_insert table. ***********************************************************************/ #ifdef WIN32 #include <windows.h> #endif #include <stdio.h> #include <stdlib.h> #include <sql.h> #include <sqlext.h> #include <string.h> void Exec(SQLHDBC hdbc, SQLCHAR* sql) { SQLRETURN retcode; // Return status SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle SQLCHAR loginfo[2048]; // Allocate Statement Handle retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle(SQL_HANDLE_STMT) failed"); return; } // Prepare Statement 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 Statement retcode = SQLExecute(hstmt); sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute(hstmt) failed"); return; } // Free 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; long int batchCount = 1000; // Amount of data that is bound in batches. SQLLEN rowsCount = 0; int ignoreCount = 0; // Amount of data that is not imported to the database among the data that is bound in batches. int i = 0; SQLRETURN retcode; SQLCHAR dsn[1024] = {'\0'}; SQLCHAR loginfo[2048]; do { if (ignoreCount > batchCount) { printf("ignoreCount(%d) should be less than batchCount(%d)\n", ignoreCount, batchCount); } }while(ignoreCount > batchCount); retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle failed"); goto exit; } // Set ODBC version. retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetEnvAttr failed"); goto exit; } // Allocate Connection retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle failed"); goto exit; } // Set Login Timeout retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetConnectAttr failed"); goto exit; } // Set Auto Commit retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)(1), 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetConnectAttr failed"); goto exit; } // Connect to DSN // Replace GaussDB with the name of the data source used by the user. sprintf(loginfo, "SQLConnect(DSN:%s)", dsn); retcode = SQLConnect(hdbc, (SQLCHAR*) "gaussdb", SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLConnect failed"); goto exit; } // init table info. 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))"); // The code constructs the data to be inserted based on the data volume entered by users. { SQLRETURN retcode; SQLHSTMT hstmtinesrt = 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; // Data is constructed by column. Each column is stored continuously. ids = (SQLINTEGER*)malloc(sizeof(ids[0]) * batchCount); cols = (SQLCHAR*)malloc(sizeof(cols[0]) * batchCount * 50); // Data size in each row for a column. bufLenIds = (SQLLEN*)malloc(sizeof(bufLenIds[0]) * batchCount); bufLenCols = (SQLLEN*)malloc(sizeof(bufLenCols[0]) * batchCount); // The value SQL_PARAM_IGNORE or SQL_PARAM_PROCEED specifies whether a row needs to be processed. operptr = (SQLUSMALLINT*)malloc(sizeof(operptr[0]) * batchCount); memset(operptr, 0, sizeof(operptr[0]) * batchCount); // Processing result of the row. // Note: In the database, a statement belongs to one transaction. Therefore, data is processed as a unit. Either all data is inserted successfully or all data fails to be inserted. statusptr = (SQLUSMALLINT*)malloc(sizeof(statusptr[0]) * batchCount); memset(statusptr, 88, sizeof(statusptr[0]) * batchCount); if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds) { fprintf(stderr, "FAILED:\tmalloc data memory failed\n"); goto exit; } for (i = 0; i < batchCount; i++) { ids[i] = i; sprintf(cols + 50 * i, "column test value %d", i); bufLenIds[i] = sizeof(ids[i]); bufLenCols[i] = strlen(cols + 50 * i); operptr[i] = (i < ignoreCount) ? SQL_PARAM_IGNORE : SQL_PARAM_PROCEED; } // Allocate Statement Handle retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle failed"); goto exit; } // Prepare Statement sql = (SQLCHAR*)"insert into test_odbc_batch_insert values(?, ?)"; retcode = SQLPrepare(hstmtinesrt, (SQLCHAR*) sql, SQL_NTS); sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql); if (!SQL_SUCCEEDED(retcode)) { printf("SQLPrepare failed"); goto exit; } retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)batchCount, sizeof(batchCount)); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetStmtAttr failed"); goto exit; } retcode = SQLBindParameter(hstmtinesrt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0,&(ids[0]), 0, bufLenIds); if (!SQL_SUCCEEDED(retcode)) { printf("SQLBindParameter failed"); goto exit; } retcode = SQLBindParameter(hstmtinesrt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols); if (!SQL_SUCCEEDED(retcode)) { printf("SQLBindParameter failed"); goto exit; } retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process)); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetStmtAttr failed"); goto exit; } retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_STATUS_PTR, (SQLPOINTER)statusptr, sizeof(statusptr[0]) * batchCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetStmtAttr failed"); goto exit; } retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_OPERATION_PTR, (SQLPOINTER)operptr, sizeof(operptr[0]) * batchCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetStmtAttr failed"); goto exit; } retcode = SQLExecute(hstmtinesrt); sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute(hstmtinesrt) failed"); goto exit; retcode = SQLRowCount(hstmtinesrt, &rowsCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLRowCount failed"); goto exit; } if (rowsCount != (batchCount - ignoreCount)) { sprintf(loginfo, "(batchCount - ignoreCount)(%d) != rowsCount(%d)", (batchCount - ignoreCount), rowsCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute failed"); goto exit; } } else { sprintf(loginfo, "(batchCount - ignoreCount)(%d) == rowsCount(%d)", (batchCount - ignoreCount), rowsCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute failed"); goto exit; } } // check row number returned if (rowsCount != process) { sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute failed"); goto exit; } } else { sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute failed"); goto exit; } } for (i = 0; i < batchCount; i++) { if (i < ignoreCount) { if (statusptr[i] != SQL_PARAM_UNUSED) { sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_UNUSED", i, statusptr[i]); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute failed"); goto exit; } } } else if (statusptr[i] != SQL_PARAM_SUCCESS) { sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_SUCCESS", i, statusptr[i]); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute failed"); goto exit; } } } retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmtinesrt); sprintf((char*)loginfo, "SQLFreeHandle hstmtinesrt"); if (!SQL_SUCCEEDED(retcode)) { printf("SQLFreeHandle failed"); goto exit; } } } exit: (void) printf ("\nComplete.\n"); // Connection if (hdbc != SQL_NULL_HDBC) { SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); } // Environment if (henv != SQL_NULL_HENV) SQLFreeHandle(SQL_HANDLE_ENV, henv); return 0; } |
Parent topic: Development Based on ODBC
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.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
