Jaa

bcp_bind

  • Artikkeli

Binds data from a program variable to a table column for bulk copy into SQL Server 2005.

Syntax

RETCODE bcp_bind (
        HDBC hdbc, 
        LPCBYTE pData,
        INT cbIndicator,
        DBINT cbData,
        LPCBYTE pTerm,
        INT cbTerm,
        INT eDataType,
        INT idxServerCol);

Arguments

  • hdbc
    Is the bulk copy-enabled ODBC connection handle.

  • pData
    Is a pointer to the data copied. If eDataType is SQLTEXT, SQLNTEXT, SQLXML, SQLUDT, SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, SQLNCHAR or SQLIMAGE, pData can be NULL. A NULL pData indicates that long data values will be sent to SQL Server in chunks using bcp_moretext. The user should only set pData to NULL if the column corresponding to the user bound field is a BLOB column otherwise bcp_bind will fail.

    If indicators are present in the data, they appear in memory directly before the data. The pData parameter points to the indicator variable in this case, and the width of the indicator, the cbIndicator parameter, is used by bulk copy to address user data correctly.

  • cbIndicator
    Is the length, in bytes, of a length or null indicator for the column's data. Valid indicator length values are 0 (when using no indicator), 1, 2, 4, or 8. Indicators appear in memory directly before any data. For example, the following structure type definition could be used to insert integer values into an SQL Server table using bulk copy:

    typedef struct tagBCPBOUNDINT
    {
    int iIndicator;
    int Value;
    } BCPBOUNDINT;

    In the example case, the pData parameter would be set to the address of a declared instance of the structure, the address of the BCPBOUNDINT iIndicator structure member. The cbIndicator parameter would be set to the size of an integer (sizeof(int)), and the cbData parameter would again be set to the size of an integer (sizeof(int)). To bulk copy a row to the server containing a NULL value for the bound column, the value of the instance's iIndicator member should be set to SQL_NULL_DATA.

  • cbData
    Is the count of bytes of data in the program variable, not including the length of any length or null indicator or terminator.

    Setting cbData to SQL_NULL_DATA signifies that all rows copied to the server contain a NULL value for the column.

    Setting cbData to SQL_VARLEN_DATA indicates that the system will use a string terminator, or other method, to determine the length of data copied.

    For fixed-length data types, such as integers, the data type indicates the length of the data to the system. Therefore, for fixed-length data types, cbData can safely be SQL_VARLEN_DATA or the length of the data.

    For SQL Server character and binary data types, cbData can be SQL_VARLEN_DATA, SQL_NULL_DATA, some positive value, or 0. If cbData is SQL_VARLEN_DATA, the system uses either a length/null indicator (if present) or a terminator sequence to determine the length of the data. If both are supplied, the system uses the one that results in the least amount of data being copied. If cbData is SQL_VARLEN_DATA, the data type of the column is a SQL Server character or binary type, and neither a length indicator nor a terminator sequence is specified, the system returns an error message.

    If cbData is 0 or a positive value, the system uses cbData as the data length. However, if, in addition to a positive cbData value, a length indicator or terminator sequence is provided, the system determines the data length by using the method that results in the least amount of data being copied.

    The cbData parameter value represents the count of bytes of data. If character data is represented by Unicode wide characters, then a positive cbData parameter value represents the number of characters multiplied by the size in bytes of each character.

  • pTerm
    Is a pointer to the byte pattern, if any, that marks the end of this program variable. For example, ANSI and MBCS C strings usually have a 1-byte terminator (\0).

    If there is no terminator for the variable, set pTerm to NULL.

    You can use an empty string ("") to designate the C null terminator as the program-variable terminator. Because the null-terminated empty string constitutes a single byte (the terminator byte itself), set cbTerm to 1. For example, to indicate that the string in szName is null-terminated and that the terminator should be used to indicate the length:

    bcp_bind(hdbc, szName, 0,
   SQL_VARLEN_DATA, "", 1,
   SQLCHARACTER, 2)

    A nonterminated form of this example could indicate that 15 characters be copied from the szName variable to the second column of the bound table:

    bcp_bind(hdbc, szName, 0, 15, 
   NULL, 0, SQLCHARACTER, 2)

    The bulk copy API performs Unicode-to-MBCS character conversion as required. Make sure that both the terminator byte string and the length of the byte string are set correctly. For example, to indicate that the string in szName is a Unicode wide character string, terminated by the Unicode null terminator value:

    bcp_bind(hdbc, szName, 0, 
   SQL_VARLEN_DATA, L"",
   sizeof(WCHAR), SQLNCHAR, 2)

    If the bound SQL Server column is wide character, no conversion is performed on bcp_sendrow. If the SQL Server column is an MBCS character type, wide character to multibyte character conversion is performed as the data is sent to SQL Server.

  • cbTerm
    Is the count of bytes present in the terminator for the program variable, if any. If there is no terminator for the variable, set cbTerm to 0.

  • eDataType
    Is the C data type of the program variable. The data in the program variable is converted to the type of the database column. If this parameter is 0, no conversion is performed.

    The eDataType parameter is enumerated by the SQL Server data type tokens in sqlncli.h, not the ODBC C data type enumerators. For example, you can specify a two-byte integer, ODBC type SQL_C_SHORT, using the SQL Server-specific type SQLINT2.

    SQL Server 2005 now adds support for SQLXML and SQLUDT data type tokens in the eDataType paramenter.

  • idxServerCol
    Is the ordinal position of the column in the database table to which the data is copied. The first column in a table is column 1. The ordinal position of a column is reported by SQLColumns.

Returns

SUCCEED or FAIL.

Remarks

Use bcp_bind for a fast, efficient way to copy data from a program variable into a table in SQL Server.

Call bcp_init before calling this or any other bulk-copy function. Calling bcp_init sets the SQL Server target table for bulk copy. When calling bcp_init for use with bcp_bind and bcp_sendrow, the bcp_initszDataFile parameter, indicating the data file, is set to NULL; the bcp_initeDirection parameter is set to DB_IN.

Make a separate bcp_bind call for every column in the SQL Server table into which you want to copy. After the necessary bcp_bind calls have been made, then call bcp_sendrow to send a row of data from your program variables to SQL Server.

Whenever you want SQL Server to commit the rows already received, call bcp_batch. For example, call bcp_batch once for every 1000 rows inserted or at any other interval.

When there are no more rows to be inserted, call bcp_done. Failure to do so results in an error.

Control parameter settings, specified with bcp_control, have no effect on bcp_bind row transfers.

For new large value types, such as varchar(max), varbinary(max), or nvarchar(max), you can use SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY and SQLNCHAR as type indicators in the eDataType parameter, along with prefix of size 8 in the cbIndicator parameter. The bcp_bind function validates that if a prefix is specified for a field corresponding to a max type column then it can not be any value other than 8. The 8 byte prefix can take the following values:

  • 0xFFFFFFFFFFFFFFFF means a null value for the field
  • 0xFFFFFFFFFFFFFFFE is treated as a special prefix value which is used for efficiently sending data in chunks to the server. The format of data with this special prefix is:
  • <SPECIAL_PREFIX> <0 or more DATA CHUNKS> <ZERO_CHUNK> where:
  • SPECIAL_PREFIX is 0xFFFFFFFFFFFFFFFE
  • DATA_CHUNK is a 4 byte prefix containing length of the chunk,followed by the actual data whose length is specified in the 4 byte prefix.
  • ZERO_CHUNK is a 4 byte value containing all zeros (00000000) indicating the end of data.
  • Any other valid 8 byte length is treated as a regular data length.

Calling bcp_columns when using bcp_bind results in an error.

Example

// Variables like henv not specified.
HDBC      hdbc;
char         szCompanyName[MAXNAME];
DBINT      idCompany;
DBINT      nRowsProcessed;
DBBOOL      bMoreData;
char*      pTerm = "\t\t";

// Application initiation, get an ODBC environment handle, allocate the
// hdbc, and so on.
... 

// Enable bulk copy prior to connecting on allocated hdbc.
SQLSetConnectAttr(hdbc, SQL_COPT_SS_BCP, (SQLPOINTER) SQL_BCP_ON,
   SQL_IS_INTEGER);

// Connect to the data source; return on error.
if (!SQL_SUCCEEDED(SQLConnect(hdbc, _T("myDSN"), SQL_NTS,
   _T("myUser"), SQL_NTS, _T("myPwd"), SQL_NTS)))
   {
   // Raise error and return.
   return;
   }

// Initialize bcp. 
if (bcp_init(hdbc, "comdb..accounts_info", NULL, NULL
   DB_IN) == FAIL)
   {
   // Raise error and return.
   return;
   }

// Bind program variables to table columns. 
if (bcp_bind(hdbc, (LPCBYTE) &idCompany, 0, sizeof(DBINT), NULL, 0,
   SQLINT4, 1)    == FAIL)
   {
   // Raise error and return.
   return;
   }
if (bcp_bind(hdbc, (LPCBYTE) szCompanyName, 0, SQL_VARLEN_DATA,
   (LPCBYTE) pTerm, strlen(pTerm), SQLCHARACTER, 2) == FAIL)
   {
   // Raise error and return.
   return;
   }

while (TRUE)
   {
   // Retrieve and process program data. 
   if ((bMoreData = getdata(&idCompany, szCompanyName)) == TRUE)
      {
      // Send the data. 
      if (bcp_sendrow(hdbc) == FAIL)
         {
         // Raise error and return.
         return;
         }
      }
   else
      {
      // Break out of loop and carry on.
      break;
      }
   }

// Terminate the bulk copy operation.
if ((nRowsProcessed = bcp_done(hdbc)) == -1)
   {
   printf("Bulk-copy unsuccessful.\n");
   return;
   }

printf("%ld rows copied.\n", nRowsProcessed);

// Carry on.

See Also

Reference

Bulk Copy Functions

Help and Information

Getting SQL Server 2005 Assistance