How to bulk copy a SELECT result set (ODBC)

To bulk copy out the result set of a SELECT statement

1. Allocate an environment handle and a connection handle.
2. Set SQL_COPT_SS_BCP and SQL_BCP_ON to enable bulk copy operations.
3. Connect to Microsoft® SQL Server™.
4. Call bcp_init to set the following information:
   • Specify NULL for the szTable parameter.
   • The name of the data file that receives result set data.
   • The name of a data file to receive any bulk copy error messages (specify NULL if you do not want a message file).
   • The direction of the copy: DB_OUT.
5. Call bcp_control, set eOption to BCPHINTS and place in iValue a pointer to a SQLTCHAR array containing the SELECT statement.
6. Call bcp_exec to execute the bulk copy operation.

When using these steps the file is created in native format. You can convert the data values to other data types by using bcp_colfmt, for more information see How to create a bulk copy format file (ODBC).

Examples

The following example shows using bulk copy functions to bulk copy out the result set of a SELECT statement. Most error-checking code was removed to simplify this example.

// Sample showing ODBC BCP_OUT of a SELECT result set

// using native mode format.

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>

SQLHENV        henv = SQL_NULL_HENV;

HDBC            hdbc1 = SQL_NULL_HDBC;

int main() {

    RETCODE retcode;

    // Bulk copy variables.

    SDWORD    cRows;

    SQLTCHAR    szBCPQuery[] =

        "SELECT LastName, FirstName FROM Northwind.dbo.Employees";

     // Allocate the ODBC environment and save handle.

    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

    // Let ODBC know this is an ODBC 3.0 app.

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

                            (SQLPOINTER) SQL_OV_ODBC3,

                             SQL_IS_INTEGER);

    // Allocate ODBC connection handle, set bulk copy mode, and

    // then connect.

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

    retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP,

                                (void *)SQL_BCP_ON,

                                 SQL_IS_INTEGER);

    retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,

                "sa", SQL_NTS, "MyPassWord", SQL_NTS);

    // Initialize the bulk copy.

    retcode = bcp_init(hdbc1, NULL, "c:\\BCPODBC.bcp",

                        "c:\\BCPERROR.out, DB_OUT);

    // Note that the test is for the bulk copy return of SUCCEED,

    // not the ODBC return of SQL_SUCCESS.

    if ( (retcode != SUCCEED) )

    {

           ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,

                            "bcp_init(hdbc1) Failed\n\n");

           return(9);

    }

    // Specify the query to use.

    retcode = bcp_control(hdbc1, BCPHINTS, (void *)szBCPQuery);

    if ( (retcode != SUCCEED) )

    {

           ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,

                            "bcp_control(hdbc1) Failed\n\n");

           return(9);

    }

    // Execute the bulk copy.

    retcode = bcp_exec(hdbc1, &cRows);

    if ( (retcode != SUCCEED) )

    {

           ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,

                            "bcp_exec(hdbc1) Failed\n\n");

           return(9);

    }

    printf("Number of rows bulk copied out = %d.\n", cRows);

    /* Clean up. */

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}

See Also