Microsoft KB Archive/196384

= How To Perform Bulk Inserts Using SQLSetPos =

Article ID: 196384

Article Last Modified on 7/2/2004

-

APPLIES TO


 * Microsoft Open Database Connectivity 3.0
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q196384



SUMMARY
This article contains sample code that demonstrates how to perform bulk inserts using the SQLSetPos ODBC API call. The sample code illustrates a specific instance where bulk inserts are performed on the "Jobs" table in the SQL Server "Pubs" database.



MORE INFORMATION
The bulk insertion process is broken down into the following six steps:

 Connection: Create environment and connection handles. Initialization: Create a statement and set its attributes. Notable attributes set are as follows:  Currency Cursor-type Rowset size Row array size Row operation pointer</li></ul>

</li> Build and execute: Build the SQL statement and execute it.</li> Fetch the results and insert: Fetch the data and perform SQLSetPos to insert the data in bulk.</li> Commit the transaction.</li> Disconnect: Disconnect and free all handles.</li></ol>

The sample code illustrates how to insert 'ROW_SIZE' number of rows in a SQL Server table. The code uses an ODBC datasource named "LocalServer" that points to the SQL Server "pubs" database that contains the "jobs" table.

Note: The rows identified in the rowProceed array with the SQL_ROW_PROCEED flag set will be affected for update and delete operations only.

// START SAMPLE CODE // This code inserts 'ROW_SIZE' rows in a SQL server table named jobs. //   //  Field structure for jobs table: //    //    Field Name    SQL Type       SQL C Type        App Type //    //    *jobID        SQL_SMALLINT   SQL_C_SSHORT      SQLSMALLINT //   jobDesc       SQL_VARCHAR    SQL_C_VARCHAR     SQLVARCHAR //   minLvl        SQL_TINYINT    SQL_C_UTINYINT    BYTE //   maxLvl        SQL_TINYINT    SQL_C_UTINYINT    BYTE //   * JobID is an identity field with identity seed = 1 AND must be a primary key. //   If JobID is not a primary key, you cannot open a keyset cursor, and SQLSetPos fails.

// File Includes. #include <windows.h>  #include <odbcinst.h>   #include <sqlext.h>   #include <stdio.h>

// The MACRO definitions. #define RETURNX(x) ErrorMsg(x, rc) #define ROW_SIZE 10 #define MAX_JOBID_SIZE 2 #define MAX_JOBDESC_SIZE 100 #define MAX_MINLVL_SIZE 1 #define MAX_MAXLVL_SIZE 1

// Function declarations. void ErrorMsg(HSTMT hstmt,RETCODE retcode);

void main(void) {     SQLHSTMT hStmt;   // Statement handle. SQLHDBC hDbc;    // Connection Handle. SQLHENV hEnv;    // Environment Handle. SQLSMALLINT jobId[ROW_SIZE]; // Job ID Field Buffer BYTE minLvl[ROW_SIZE], maxLvl[ROW_SIZE]; // minLvl, maxLvl Buffer SQLVARCHAR jobDesc[ROW_SIZE][MAX_JOBDESC_SIZE]; // jobDesc Buffer SQLINTEGER IDvalue[ROW_SIZE]; // jobID Length Array SQLINTEGER Descvalue[ROW_SIZE]; // jobDesc Length Array SQLINTEGER minvalue[ROW_SIZE]; //minLvl Length Array SQLINTEGER maxvalue[ROW_SIZE]; // maxLvl Length Array SQLUSMALLINT rowProceed[ROW_SIZE]; // SQL_ATTR_ROW_OPERATION_PTR array SQLRETURN rc; // Return code SQLINTEGER value, i;

// 1.Connection // Allocate Environment Handle. rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,&hEnv); // Set Environment Attributes. value = SQL_OV_ODBC3; // Set the ODBC version rc = SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,          (SQLPOINTER) value,SQL_IS_INTEGER); // Allocate Connection Handle. rc = SQLAllocHandle(SQL_HANDLE_DBC,hEnv,&hDbc); // Perform Connection. rc = SQLConnect(hDbc,(SQLCHAR*)"LocalServer", SQL_NTS,(SQLCHAR*)"sa",          SQL_NTS,(SQLCHAR*)"",SQL_NTS); // Set Connection Attributes. value = SQL_MODE_READ_WRITE; rc = SQLSetConnectAttr(hDbc,SQL_ATTR_ACCESS_MODE,          (SQLPOINTER)value,SQL_IS_INTEGER);

// 2.Initialization // Allocate Statement Handle. rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); RETURNX(hStmt); // Set the statement attributes. rc = SQLSetStmtAttr(hStmt,SQL_ATTR_APP_ROW_DESC,          SQL_NULL_HANDLE,SQL_IS_POINTER); RETURNX(hStmt); // Set the Concurrency. rc = SQLSetStmtOption(hStmt, SQL_CONCURRENCY, SQL_CONCUR_LOCK); RETURNX(hStmt); // Set the cursor type. rc = SQLSetStmtOption(hStmt,SQL_ATTR_CURSOR_TYPE,          SQL_CURSOR_KEYSET_DRIVEN); RETURNX(hStmt); // Bookmarks are optional. rc = SQLSetStmtOption(hStmt,SQL_ATTR_USE_BOOKMARKS,SQL_UB_FIXED); RETURNX(hStmt); // Set the Rowset size. rc = SQLSetStmtOption(hStmt,SQL_ROWSET_SIZE,ROW_SIZE); RETURNX(hStmt); // Set the Row Array size. rc = SQLSetStmtOption(hStmt,SQL_ATTR_ROW_ARRAY_SIZE,ROW_SIZE); RETURNX(hStmt); rc = SQLSetStmtAttr(hStmt,SQL_ATTR_ROW_OPERATION_PTR,rowProceed,          SQL_IS_POINTER); RETURNX(hStmt);

// 3. Build and Execute // Performing the query. rc = SQLExecDirect(hStmt,(SQLCHAR*)"SELECT * FROM jobs",SQL_NTS); RETURNX(hStmt);

// 4. Fetch the results // Binding the buffers and length arrays to the columns. // 1. statement handle, // 2. Column number, // 3. type, // 4. ptr to buffer, // 5. max_size_of_one_element // 6. returns the # retrieved rc = SQLBindCol(hStmt,1,SQL_C_SSHORT,jobId,MAX_JOBID_SIZE,IDvalue); RETURNX(hStmt); rc = SQLBindCol(hStmt,2,SQL_C_CHAR,jobDesc,                     MAX_JOBDESC_SIZE,Descvalue); RETURNX(hStmt); rc = SQLBindCol(hStmt,3,SQL_C_TINYINT,minLvl,                     MAX_MINLVL_SIZE,minvalue); RETURNX(hStmt); rc = SQLBindCol(hStmt,4,SQL_C_TINYINT,maxLvl,                     MAX_MAXLVL_SIZE,maxvalue); RETURNX(hStmt); if (SQLFetch(hStmt)!= SQL_NO_DATA) // Set Position API. {        // Set the appropriate data values for the 10 rows. for(i=0 ;i<ROW_SIZE;i++) {           // Note: JobID is an identity field so skip it. strcpy( (char*)jobDesc[i], "SQLSetPos Test" ); Descvalue[i] = strlen((char*)jobDesc[i]); minLvl[i] = (BYTE) i + 15; minvalue[i] = (SQLINTEGER) 1; maxLvl[i] = (BYTE) i+30; maxvalue[i] = (SQLINTEGER) 1; rowProceed[i] = SQL_ROW_PROCEED; }        rc = SQLSetPos(hStmt,0,SQL_ADD,SQL_LOCK_NO_CHANGE); RETURNX(hStmt); }

// 5. Commit Transaction (not needed if SQL_ATTR_AUTOCOMMIT is ON) rc = SQLEndTran(SQL_HANDLE_DBC,hDbc,SQL_COMMIT); RETURNX(hStmt);

// 6. Disconnect // Free Statement Handle. rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt); RETURNX(hStmt); // Disconnect. rc = SQLDisconnect(hDbc); RETURNX(hStmt); // Free Database Connection Handle. rc = SQLFreeHandle(SQL_HANDLE_DBC,hDbc); RETURNX(hStmt); // Free Environment Handle. rc = SQLFreeHandle(SQL_HANDLE_ENV,hEnv); RETURNX(hStmt); }

// The Error function. void ErrorMsg( HSTMT hstmt, RETCODE retcode ) {     RETCODE rc; UCHAR szSqlState[6]; SDWORD sdwNativeError; UCHAR szErrorMsg[SQL_MAX_MESSAGE_LENGTH]; char  errMsg[4096]; SWORD cbErrorMsg;

if (retcode != SQL_SUCCESS) {        rc = SQLError(SQL_NULL_HENV,SQL_NULL_HDBC,hstmt,              szSqlState,&sdwNativeError,szErrorMsg,              sizeof(szErrorMsg),&cbErrorMsg); if (rc == SQL_INVALID_HANDLE) return; while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {           sprintf(errMsg,"Error Message:%s\n\n",(LPCSTR)szErrorMsg); MessageBox(NULL,errMsg,"Error",MB_OK); rc = SQLError(NULL,NULL,hstmt,szSqlState,&sdwNativeError,                szErrorMsg,sizeof(szErrorMsg),NULL); }     }   }   // END SAMPLE CODE.

Keywords: kbhowto kbdatabase kbcode KB196384

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.