unable to bind C program variable to a datetime type column in SQL Server

开发者_如何转开发Right now the function call used is:

bcp_bind(hdbc1, (LPCBYTE)&dtime, 0, -1, (LPCBYTE) "", 1, SQLDATETIME, 5)

where, dttime is a character array containing datetime value. Also tried using a TIMESTAMP_STRUCT type.

The datetime formats tried (datetime values as specified in the data file):

mm/dd/yyyy hh:mm:ss.nnn
mm/dd/yyyy
yyyy-dd-mm hh:mm:ss.nnn
yyyy-dd-mm hh:mm:ss

For all the cases, NULL values are getting inserted in the table.

Related questions: What should be the program variable type while binding the datetime datatype using bcp API? Can it be a character array? Is odbc api/ SQL Server implicitly capable of converting the character type to datetime? If not, please suggest a C type that can be used.

---

I think the problem is your fourth argument cbData being -1. That is (at least on my system) SQL_NULL_DATA, signifying that all rows contain a NULL for this column.

What you probably want is sizeof(TIMESTAMP_STRUCT), assuming that dtime points to a variable of that type of data.

Also, I don't see SQLDATETIME defined anywhere. I do have a SQL_DATETIME, but I'm not sure if that is a problem or a difference between platforms.

I think as long as you set your eDataType, the ODBC driver is pretty good at handling conversions. I've only really looked into the bulk copy stuff briefly, so I'm not sure if there is any difference from the standard ODBC stuff here, but for reference, I normally use SQL_C_TYPE_TIMESTAMP, and a TIMESTAMP_STRUCT. This same C-type appears to work well for various database column types.

---

In case someone else still has this issue, this is what works for me:

bcp_bind(m_hdbc, (BYTE *)buffer, 4, sizeof(TIMESTAMP_STRUCT), 0, 0, SQLDATETIME2N, index);

And buffer filling looks like this:

*(int*)buffer = sizeof(TIMESTAMP_STRUCT);

TIMESTAMP_STRUCT* datePtr = (TIMESTAMP_STRUCT*)((int*)buffer+1);

datePtr->year = value.Year();
datePtr->month = value.Month();
datePtr->day = value.Day();
datePtr->hour = value.Hour();
datePtr->minute = value.Minute();
datePtr->second = value.Second();
datePtr->fraction = value.Millisecond() * 1000000;