returning time value from SQL Server through ODBC api

[tpucke]

So I am using the ODBC api with Visual Studio 2010, and having trouble getting a time value returned into a TIME_STRUCT, which is the type declared in sqltypes.h seemingly for just such use. I have been able to return every other type back, including simple date, but time only wants to return as a string (works ok for char and wchar_t bindings). The relevant code is:

#include <windows.h>
#include <string.h>
#include <TCHAR.H>

#include <sql.h>
#include <sqlext.h>

// some code to allocate environment and statement handles
SQLExecDirect( m_hStmt, _T("select * from TypesTable"), SQL_NTS ) ;
TIME_STRUCT sTimeStruct ;
SQLBindCol(m_hStmt, 17, SQL_TYPE_TIME, &sTimeStruct, 6, pReturnedBufSize ) ;
SQLFetchScroll( m_hStmt, SQL_FETCH_FIRST, 0 ) ;

So at this point sTimeStruct has been reporting junk, but when I looked at the memory at that location it contains the string data as if I had bound the column with type SQL_WCHAR (the default for a time field). I tried binding with every type value from -150 to 150 and nothing fills in the time structure properly.

Has anyone successfully had a time value returned to a TIME_STRUCT?

[freddie1]

I have for a long time used the TIMESTAMP_STRUCT to retrieve time/date data from SQL databases. Maybe my use of it in the procedure below will assist you...

UINT blnDumpData(SQL& sql, TCHAR** ptrLines, unsigned int& iLine, HWND hWnd, LPCRITICAL_SECTION cs)
{
 TCHAR szQuery[100],szBuffer[128],szDate[12];
 SQLTCHAR szString[20];
 TIMESTAMP_STRUCT ts;
 SQLINTEGER iJnk;
 SQLHSTMT hStmt;
 double dblNum;
 UINT iId;

 #if defined(MY_DEBUG)
     _ftprintf(fp,_T("Entering blnDumpData()\n"));
 #endif
 if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    _tcscpy(szQuery,_T("SELECT Table1.Id,Table1.Float_Point,Table1.Date_Field,Table1.Text_Field FROM Table1;"));
    SQLBindCol(hStmt,1,SQL_C_ULONG,&iId,0,&iJnk);
    SQLBindCol(hStmt,2,SQL_C_DOUBLE,&dblNum,0,&iJnk);
    SQLBindCol(hStmt,3,SQL_C_TYPE_DATE,&ts,0,&iJnk);
    SQLBindCol(hStmt,4,SQL_C_TCHAR,szString,20,&iJnk);
    if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("iId      Double           Date           String           0=SQL_SUCCESS")))
          return FALSE;
       if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("========================================================================")))
          return FALSE;
       do
       {
          EnterCriticalSection(cs);
          if(SQLFetch(hStmt)==SQL_NO_DATA)
             break;
          memset(szBuffer,0,128);
          MkDate(ts,szDate);
          _stprintf(szBuffer,_T("%-6u%8.2f           %-12.10s  %-20s%6u"),iId,dblNum,szDate,szString,SQL_SUCCESS);
          LeaveCriticalSection(cs);
          if(blnLineFailed(hWnd,sql,ptrLines,iLine,szBuffer))
             return FALSE;
       }  while(TRUE);
    }
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    #if defined(MY_DEBUG)
        _ftprintf(fp,_T("Leaving blnDumpData()\n\n"));
    #endif
    return TRUE;
 }
 #if defined(MY_DEBUG)
     _ftprintf(fp,_T("Leaving blnDumpData()\n\n"));
 #endif

 return FALSE;
}


void MkDate(TIMESTAMP_STRUCT& ts, TCHAR* szBuffer)
{
 TCHAR szMonth[4],szDay[4],szYear[8];

 _stprintf(szMonth,_T("%u"),ts.month);
 _stprintf(szDay,_T("%u"),ts.day);
 _stprintf(szYear,_T("%u"),ts.year);
 _tcscpy(szBuffer,szMonth);
 _tcscat(szBuffer,_T("/"));
 _tcscat(szBuffer,szDay);
 _tcscat(szBuffer,_T("/"));
 _tcscat(szBuffer,szYear);

 return;
}

[tpucke]

Thanks. I have been afraid that I will need to just bind a string buffer and then convert, similar to the reverse of using your MkDate function.

I see that you are binding to a date column, which I have had no trouble with, but the time field simply does not work. I wonder whether it is a bug in the Microsoft driver.

Actually I am surprised to see that you can successfully get the TIMESTAMP_STRUCT filled correctly when you bind using the SQL_C_TYPE_DATE type. Does this fill in only the date fields of the timestamp? Perhaps it only works because the year, month, and date are the first 3 fields in the structure, like the DATE_STRUCT type.

[freddie1]

I guess I'm guilty of not reading your original post close enough tpucke. Sorry. I see you were able to get dates.

My guess is that the underlying SQL Server database needs to have the field typed as just a time field - if that's possible. I'm not sure. My understanding is that the typical storage of date/time data is an eight byte double; the whole number part being the number of days since late 1899 till now; the decimal part being the time since midnight. So I suppose if all that was being stored is time - that would only require four bytes, i.e., a 32 bit C float. I don't know if that is supported by DBMS at all. Otherwise, likely eight bytes would be used and the whole number part ignored. I'm just guessing here. As you can probably tell, I've never worked with times - only dates. And you may know more about this than I. If I was having your problem though what I would check first is how SQL Server has the underlying field typed and I'd dig into the details of that type; then try to match it up with the various time/date related structs in Sqltypes.h.

[tpucke]

The db field I am querying is of type time. This is a valid field type and there is a corresponding C struct called TIME_STRUCT that is documented to be able to receive time. The struct has 3 UHSORT fields, for hour, minute, and seconds. So it should be possible to bind this struct to the column and fetch values to it, but as I said before it doesn't. Instead, it starts putting the time in there as a string until the 6 bytes of space is used up and then it's done. No truncation message, nothing. The corresponding date works fine.

And I am not using combined date/time field. That's the whole point.

So I ask again, has anyone out there been able to populate a TIME_STRUCT with a value from a time field in a db table?