Sending null values to an integer column in an SQL table from a C++ application

Question

I am doing a bulk copy of program variables to a SQL Server 2005 table and I use bcp_bind and bcp_sendrow in a C++ application

However, some of the integer columns in the table allow null values. I'm not quite sure how I should be sending null values at runtime using bcp_sendrow when the need arises to send a null value for one of the columns.

Documentation on bcp_bind says:

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

However this still doesn't make things quite clear. Am I supposed to have 2 different calls to bcp_sendrow: one for the not null case and another for the null case?

Answer 1

The SQL server bulk copy interface has two distinct methods for declaring null values:

1. You can specify SQL_NULL_DATA as the cbData parameter to bcp_bind. This indicates that all rows sent to the server will be NULLs

2. You can use an indicator variable to say when the column contains an integer and when it contains NULL. To do this, you need to set aside space immediately before your int for a 64-bit indicator variable. The documentation shows this in a small struct, like this:

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

   (I've changed the iIndicator to an explicit 64-bit integer here; it's important that it not be shorter.)

   When you call bcp_bind, you point to the struct, rather than the value, and you specify that you are using an 8-byte indicator, and that the data length is variable.

   bcp_bind(hdbc, &local_struct, 8, SQL_VARLEN_DATA, NULL, 0, SQLINT4, colIndex);
   

   To send a row with data in this column, set iIndicator to the size of the integer field (4), and put the value in Value. If the column should be null, then set iIndicator to SQL_NULL_DATA and don't worry about the value:

   if (my_int_is_null) {
       local_struct.iIndicator = SQL_NULL_DATA;
   } else {
       local_struct.iIndicator = sizeof(int);
       local_struct.Value = my_int_value;
   }
   /* Fill the rest of the columns in a similar way */
   /* ... */
   /* finally, queue the row */
   bcp_sendrow(hdbc);