Export (0) Print
Expand All

bcp_moretext

Sends part of a long, variable-length data type value to SQL Server.


RETCODE bcp_moretext (
        HDBC hdbc,
        DBINT cbData,
        LPCBYTE pData);

hdbc

Is the bulk copy-enabled ODBC connection handle.

cbData

Is the number of bytes of data being copied to SQL Server from the data referenced by pData. A value of SQL_NULL_DATA indicates NULL.

pData

Is a pointer to the supported, long, variable-length data chunk to be sent to SQL Server.

SUCCEED or FAIL.

This function can be used in conjunction with bcp_bind and bcp_sendrow to copy long, variable-length data values to SQL Server in a number of smaller chunks. bcp_moretext can be used with columns that have SQL Server data types enumerated with SQLTEXT, SQLNTEXT, SQLIMAGE, varchar(max), nvarchar(max), varbinary(max), user-defined type (UDT), and XML. bcp_moretext does not support data conversions, the data supplied must match the data type of the target column.

If bcp_bind is called with a nonNULL pData parameter for data types that are supported by bcp_moretext, bcp_sendrow sends the entire data value, regardless of length. If, however, bcp_bind has a NULL pData parameter for supported data types, bcp_moretext can be used to copy data immediately after a successful return from bcp_sendrow indicating that any bound columns with data present have been processed.

If you use bcp_moretext to send one supported data type column in a row, you must also use it to send all other supported data type columns in the row. No columns may be skipped. Supported data types are SQLTEXT, SQLNTEXT, SQLIMAGE, SQLUDT and SQLXML. SQLCHARACTER, SQLVARCHAR, SQNCHAR, SQLBINARY and SQLVARBINARY also fall into this category if the column is a varchar(max), nvarchar(max) or varbinary(max), respectively.

Calling either bcp_bind or bcp_collen sets the total length of all data parts to be copied to the SQL Server column. An attempt to send SQL Server more bytes than specified in the call to bcp_bind or bcp_collen generates an error. This error would arise, for example, in an application which used bcp_collen to set the length of available data for an SQL Server text column to 4500, then called bcp_moretext five times while indicating on each call that the data buffer length was 1000 bytes long.

If a copied row contains more than one long, variable-length column, bcp_moretext first sends its data to the lowest ordinally numbered column, followed by the next lowest ordinally numbered column, and so on. Correct setting of the total length of expected data is important. There is no way to signal, outside of the length setting, that all data for a column has been received by bulk copy.

An application normally calls bcp_sendrow and bcp_moretext within loops to send a number of rows of data. Here's an outline of how to do this for a table containing two text columns:

while (there are still rows to send)
{
bcp_collen(..., total length of data for first text column, 
   first text column's ordinal position);
bcp_collen(..., total length of data for second text column, 
   second text column's ordinal position);

bcp_sendrow(...);

for (all the data in the first text column)
bcp_moretext(...);

for (all the data in the second text column)
bcp_moretext(...);
}

Example

This example shows how to use bcp_moretext with bcp_bind and bcp_sendrow:

// Variables like henv not specified.
HDBC      hdbc;
DBINT      idRow = 5;
char*      pPart1 = "This text value isn't very long,";
char*      pPart2 = " but it's broken into three parts";
char*      pPart3 = " anyhow.";
DBINT      cbAllParts;
DBINT      nRowsProcessed;
 
// 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 bulk copy. 
if (bcp_init(hdbc, "comdb..articles", NULL, NULL, DB_IN) == FAIL)
   {
   // Raise error and return.
   return;
   }

// Bind program variables to table columns. 
if (bcp_bind(hdbc, (LPCBYTE) &idRow, 0, SQL_VARLEN_DATA, NULL, 0,
   SQLINT4, 1)    == FAIL)
   {
   // Raise error and return.
   return;
   }

cbAllParts = (DBINT) (strlen(pPart1) + strlen(pPart2) + strlen(pPart3));
if (bcp_bind(hdbc, NULL, 0, cbAllParts, NULL, 0, SQLTEXT, 2) == FAIL)
   {
   // Raise error and return.
   return;
   }

// Send this row, with the text value broken into three chunks. 
if (bcp_sendrow(hdbc) == FAIL)
   {
   // Raise error and return.
   return;
   }

if (bcp_moretext(hdbc, (DBINT) strlen(pPart1), pPart1) == FAIL)
   {
   // Raise error and return.
   return;
   }
if (bcp_moretext(hdbc, (DBINT) strlen(pPart2), pPart2) == FAIL)
   {
   // Raise error and return.
   return;
   }
if (bcp_moretext(hdbc, (DBINT) strlen(pPart3), pPart3) == FAIL)
   {
   // Raise error and return.
   return;
   }

// All done. Get the number of rows processed (should be one).
nRowsProcessed = bcp_done(hdbc);

// Carry on.

Community Additions

ADD
Show:
© 2014 Microsoft