Επεξεργασία

Κοινή χρήση μέσω

bcp_bind

  • Άρθρο

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

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 (9.x) introduced support for SQLXML and SQLUDT data type tokens in the eDataType parameter.

The following table lists valid enumerated data types and the corresponding ODBC C data types.

eDataType C type
SQLTEXT char *
SQLNTEXT wchar_t *
SQLCHARACTER char *
SQLBIGCHAR char *
SQLVARCHAR char *
SQLBIGVARCHAR char *
SQLNCHAR wchar_t *
SQLNVARCHAR wchar_t *
SQLBINARY unsigned char *
SQLBIGBINARY unsigned char *
SQLVARBINARY unsigned char *
SQLBIGVARBINARY unsigned char *
SQLBIT char
SQLBITN char
SQLINT1 char
SQLINT2 short int
SQLINT4 int
SQLINT8 _int64
SQLINTN cbIndicator
1: SQLINT1
2: SQLINT2
4: SQLINT4
8: SQLINT8
SQLFLT4 float
SQLFLT8 float
SQLFLTN cbIndicator
4: SQLFLT4
8: SQLFLT8
SQLDECIMALN SQL_NUMERIC_STRUCT
SQLNUMERICN SQL_NUMERIC_STRUCT
SQLMONEY DBMONEY
SQLMONEY4 DBMONEY4
SQLMONEYN cbIndicator
4: SQLMONEY4
8: SQLMONEY
SQLTIMEN SQL_SS_TIME2_STRUCT
SQLDATEN SQL_DATE_STRUCT
SQLDATETIM4 DBDATETIM4
SQLDATETIME DBDATETIME
SQLDATETIMN cbIndicator
4: SQLDATETIM4
8: SQLDATETIME
SQLDATETIME2N SQL_TIMESTAMP_STRUCT
SQLDATETIMEOFFSETN SQL_SS_TIMESTAMPOFFSET_STRUCT
SQLIMAGE unsigned char *
SQLUDT unsigned char *
SQLUNIQUEID SQLGUID
SQLVARIANT Any data type except:
- text
- ntext
- image
- varchar(max)
- varbinary(max)
- nvarchar(max)
- xml
- timestamp
SQLXML Supported C data types:
- char*
- wchar_t *
- unsigned char *

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_init szDataFile 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. Rebinding a column is not supported.

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.

If pData for a column is set to NULL because its value will be supplied by calls to bcp_moretext, any subsequent columns with eDataType set to SQLTEXT, SQLNTEXT, SQLXML, SQLUDT, SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, SQLNCHAR, or SQLIMAGE must also be bound with pData set to NULL, and their values must also be supplied by calls to bcp_moretext.

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.

If cbTerm is not 0, any value (1, 2, 4, or 8) is valid for the prefix (cbIndicator). In this situation, SQL Server Native Client will search for the terminator, calculate data length with respect to the terminator (i), and set the cbData to the smaller value of i and the value of prefix.

If cbTerm is 0 and cbIndicator (the prefix) is not 0, cbIndicator must be 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.

bcp_bind Support for Enhanced Date and Time Features

For information about the types used with the eDataType parameter for date/time types, see Bulk Copy Changes for Enhanced Date and Time Types (OLE DB and ODBC).

For more information, see Date and Time Improvements (ODBC).

Example

#include sql.h  
#include sqlext.h  
#include odbcss.h  
// 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, strnlen(pTerm, sizeof(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.  
      break;  
      }  
   }  
  
// Terminate the bulk copy operation.  
if ((nRowsProcessed = bcp_done(hdbc)) == -1)  
   {  
   printf_s("Bulk-copy unsuccessful.\n");  
   return;  
   }  
  
printf_s("%ld rows copied.\n", nRowsProcessed);

See Also

Bulk Copy Functions