DOCUMENT:Q196384  23-AUG-2001  [odbc]
TITLE   :HOWTO: Perform Bulk Inserts Using SQLSetPos
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER::2.5,2.6,2.7,3.0
OPER/SYS:
KEYWORDS:kbcode kbDatabase kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbmdac270

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Open Database Connectivity, version 3.0 
 - Microsoft Data Access Components versions 2.5, 2.6, 2.7 
-------------------------------------------------------------------------------

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:

1. Connection: Create environment and connection handles.

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

3. Build and execute: Build the SQL statement and execute it.

4. Fetch the results and insert: Fetch the data and perform SQLSetPos to insert
   the data in bulk.

5. Commit the transaction.

6. Disconnect: Disconnect and free all handles.

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.

Additional query words:

======================================================================
Keywords          : kbcode kbDatabase kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbmdac270 
Technology        : kbAudDeveloper kbODBCSearch kbMDACSearch kbMDAC250 kbMDAC260 kbODBC300 kbMDAC270
Version           : :2.5,2.6,2.7,3.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.