Dear all:
I am stumped (again) with what appears to be the precision of the datetime field in an SQLServer table, where it is defined as 'datetime' length 8. I can enter date/time to the second there via the SQL Server Enterprise Manager. I use VC++ .NET 7.0 (just the ANSI C part of it though) doing ODBC. I got char strings and integers to work, but the datetime only works if full minutes are entered. As soon as a second is non-zero, it fails to update the table. I use SQLBindParameter for update and the SQLBindCol for the result set of the selection, and I tried two versions of which I always list one first and the other second:
SQL_TIMESTAMP_STRUCT Datum; or
DATE Datum; // Declaration
RetCode = SQLBindParameter( lpStmt, pos, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, sizeof( TIMESTAMP_STRUCT ), 0, field, 0, length ); or
RetCode = SQLBindParameter( lpStmt, pos, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, sizeof( DATE ), 0, field, 0, length ); // Binding for update part
RetCode = SQLBindCol( lpStmt, pos, SQL_C_TYPE_TIMESTAMP, field, sizeof( TIMESTAMP_STRUCT ), length ); or
RetCode = SQLBindCol( lpStmt, pos, SQL_C_TIMESTAMP, field, sizeof( DATE ), length ); // Binding for Select/Fetch part
I suspect that the variable has 8 bytes of which 4 are filled, like a shortdatetime. Why else do the seconds matter so much Also any attempt to use the fraction part other than zero fails. The variable pos correlates to the parameter position - like I said it works when the data contains full minutes.
I searched this forum, read the ODBC manual from the MDAC SDK and tried the C++ help. While fragments are helpful there, I have not seen a slightly more complex example. What I found was always the simplest case (e.g., insert with type char). I will need to be able to read and update a date field with the precision of at least 1/10 of a second - at the very, very least one second. I also tried the decimal digits parameter without success. However, there are so many possible combinations among C type, SQL Type and the sequence of events that I may have overlooked one or two.
Could anyone enlighten me whether it is the type (which one) or the syntax I am on OBDC version 3 which I set upon connection like this:
iRet = SQLSetEnvAttr( *lpEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0 );
Better yet, if anyone has a working binding source code snippet (including the variable declaration) I would appreciate that. Once the SQL communication works, I will have to focus on datetime operations within C, but that is not necessarily part of my problem (yet). BTW, I put values into a small table manually, read the table entries and added a day to the day part, then updated the record. My point is that I am sure the internal date format was correct, because the select and update worked, if no second was used.
Thanks in advance for your help. Let me know, if I forgot to provide important information.
Regards, Wolfgang
Problem solved?
David Funez
I searched the internet more and after a total of five days I found two snippets that point to 'sizeof( TIMESTAMP_STRUCT )' in the binding call as the culprit. One post I found mentioned using SQL_TIMESTAMP_LEN (a define that translates to 19 on my machine) and another site claimed a hardcoded 22 was the magic number. The sizeof I used appears to render 16, resulting in a precision truncation.
I am still not 100% clear on what happens here, but SQL_TIMESTAMP_LEN works for me. So if anyone else has this problem, try this instead of the sizeof for a DATETIME datatype. My above example appears to be missing some '&' because I do the binding in a subroutine to which I pass pointers and pointerpointers, so that was not the issue.
Case closed, but if anyone has an interesting comment or explanation, please reply to this so other programmers don't struggle as much. And if anyone from Microsoft reads this, I suggest pointing out this 'oddity' in the SQLBindParameter help. Better yet, post a short example for each SQL type in that section - if we have hard disk space for the Office Assistant animation, I am sure we have space for a few lines of text, too. :-)
Regards,
Wolfgang