FreeTDS Development Group

[Peter Haight <peterh AT sapros.com>]

I'm using mxODBC on top of unixODBC using the freetds drive to access a
MSSQL 7.0 database from python on FreeBSD.

Integer fields were getting all screwed up and the reason is that according
to the ODBC API SQLBindCol takes a SQL C datatype, not a SQL datatype.
mxODBC was passing values like SQL_C_SLONG and that was just falling
through the _odbc_get_server_type function which would just return some
random value.

Also, the date conversion stuff doesn't convert to a valid ODBC date value.

I'm attaching a patch below, but it is just for illustrative purposes as I
did it quickly to get things working. I'd like to add some error checks and
verify that the date and time conversions work. Not to mention that some of
the types I just picked a some SYB type that looked good. I really don't
know much about TDS or ODBC so I just guessed.

This is such a big bug that I'm surprised no one else noticed. Am I missing
something obvious or is no one using the ODBC driver?


--- src/odbc/odbc.c.orig        Sun Dec  2 08:20:10 2001
+++ src/odbc/odbc.c     Thu May  2 23:08:20 2002
@@ -39,6 +39,9 @@
                                      no_unused_var_warn};
 
 static SQLSMALLINT _odbc_get_client_type(int srv_type);
+static int _odbc_sybdatetime_to_sqltime(unsigned char *sybdatetime_buf, 
unsigned char* sqltime_buf);
+static int _odbc_sybdatetime_to_sqldate(unsigned char *sybdatetime_buf, 
unsigned char* sqldate_buf);
+static int _odbc_sybdatetime_to_sqltimestamp(unsigned char *sybdatetime_buf, 
unsigned char* sqltimestamp_buf);
 static int _odbc_fix_literals(struct _hstmt *stmt);
 static int _odbc_get_server_type(int clt_type);
 static int _odbc_get_string_size(int size, char *str);
@@ -821,7 +824,6 @@
        stmt=(struct _hstmt *)hstmt;
 
        tds = stmt->hdbc->tds_socket;
-       
        ret = tds_process_row_tokens(stmt->hdbc->tds_socket);
        if (ret==TDS_NO_MORE_ROWS) {
                return SQL_NO_DATA_FOUND;
@@ -840,13 +842,31 @@
                                src = 
&resinfo->current_row[colinfo->column_offset];
                                srclen = -1;
                        }
-                       len = tds_convert(
-                       tds_get_conversion_type(colinfo->column_type, 
colinfo->column_size),
-                       src,
-                       srclen, 
-                       _odbc_get_server_type(colinfo->column_bindtype), 
-                       colinfo->varaddr, 
-                       colinfo->column_bindlen);
+                       if (colinfo->column_bindtype == SQL_C_TIMESTAMP)
+                       {
+                               len = _odbc_sybdatetime_to_sqltimestamp(
+                                               src, colinfo->varaddr);
+                       }
+                       else if (colinfo->column_bindtype == SQL_C_DATE)
+                       {
+                               len = _odbc_sybdatetime_to_sqldate(src,
+                                               colinfo->varaddr);
+                       }
+                       else if (colinfo->column_bindtype == SQL_C_TIME)
+                       {
+                               len = _odbc_sybdatetime_to_sqltime(src,
+                                               colinfo->varaddr);
+                       }
+                       else
+                       {
+                               len = tds_convert(
+                               tds_get_conversion_type(colinfo->column_type, 
colinfo->column_size),
+                               src,
+                               srclen, 
+                               
_odbc_get_server_type(colinfo->column_bindtype), 
+                               colinfo->varaddr, 
+                               colinfo->column_bindlen);
+                       }
 /*
                        strcpy(colinfo->varaddr, 
                        &resinfo->current_row[colinfo->column_offset]);
@@ -1490,16 +1510,120 @@
                return size;
        }
 }
+
+static int _odbc_sybdatetime_to_sqltime(unsigned char *sybdatetime_buf, 
unsigned char* sqltime_buf)
+{
+       TDS_INT days;
+       TDS_INT time_intervals;
+       time_t secs_from_epoch;
+       struct tm* local_time;
+       SQL_TIME_STRUCT sql_time;
+       
+       memcpy(&days, sybdatetime_buf, 4);
+       memcpy(&time_intervals, sybdatetime_buf + 4, 4);
+       
+       secs_from_epoch = ((days - 25567) * 24 * 60 * 60) + (time_intervals / 
300);
+       local_time = gmtime(&secs_from_epoch);
+       sql_time.hour = local_time->tm_hour;
+       sql_time.minute = local_time->tm_min;
+       sql_time.second = local_time->tm_sec;
+
+       memcpy(sqltime_buf, &sql_time, sizeof(SQL_TIME_STRUCT));
+
+       return sizeof(SQL_TIME_STRUCT);
+}
+
+static int _odbc_sybdatetime_to_sqldate(unsigned char *sybdatetime_buf, 
unsigned char* sqldate_buf)
+{
+       TDS_INT days;
+       TDS_INT time_intervals;
+       time_t secs_from_epoch;
+       struct tm* local_time;
+       SQL_DATE_STRUCT sql_date;
+       
+       memcpy(&days, sybdatetime_buf, 4);
+       memcpy(&time_intervals, sybdatetime_buf + 4, 4);
+       
+       secs_from_epoch = ((days - 25567) * 24 * 60 * 60) + (time_intervals / 
300);
+       local_time = gmtime(&secs_from_epoch);
+       sql_date.year = local_time->tm_year + 1900;
+       sql_date.month = local_time->tm_mon + 1;
+       sql_date.day = local_time->tm_mday;
+
+       memcpy(sqldate_buf, &sql_date, sizeof(SQL_DATE_STRUCT));
+
+       return sizeof(SQL_DATE_STRUCT);
+}
+
+
+static int _odbc_sybdatetime_to_sqltimestamp(unsigned char *sybdatetime_buf, 
unsigned char* sqltimestamp_buf)
+{
+       TDS_INT days;
+       TDS_INT time_intervals;
+       time_t secs_from_epoch;
+       struct tm* local_time;
+       SQL_TIMESTAMP_STRUCT sql_timestamp;
+       
+       memcpy(&days, sybdatetime_buf, 4);
+       memcpy(&time_intervals, sybdatetime_buf + 4, 4);
+       
+       secs_from_epoch = ((days - 25567) * 24 * 60 * 60) + (time_intervals / 
300);
+       local_time = gmtime(&secs_from_epoch);
+       sql_timestamp.year = local_time->tm_year + 1900;
+       sql_timestamp.month = local_time->tm_mon + 1;
+       sql_timestamp.day = local_time->tm_mday;
+       sql_timestamp.hour = local_time->tm_hour;
+       sql_timestamp.minute = local_time->tm_min;
+       sql_timestamp.second = local_time->tm_sec;
+       sql_timestamp.fraction = 0;
+
+       memcpy(sqltimestamp_buf, &sql_timestamp, 
sizeof(SQL_TIMESTAMP_STRUCT));
+
+       return sizeof(SQL_TIMESTAMP_STRUCT);
+}
+
 static int _odbc_get_server_type(int clt_type)
 {
        switch (clt_type) {
+       case SQL_C_DATE:
+       case SQL_C_TIME:
+       case SQL_C_TIMESTAMP:
+       case SQL_C_TYPE_DATE:
+       case SQL_C_TYPE_TIMESTAMP:
+       case SQL_C_INTERVAL_YEAR:
+       case SQL_C_INTERVAL_MONTH:
+       case SQL_C_INTERVAL_DAY:
+       case SQL_C_INTERVAL_HOUR:
+       case SQL_C_INTERVAL_MINUTE:
+       case SQL_C_INTERVAL_SECOND:
+       case SQL_C_INTERVAL_YEAR_TO_MONTH:
+       case SQL_C_INTERVAL_DAY_TO_HOUR:
+       case SQL_C_INTERVAL_DAY_TO_MINUTE:
+       case SQL_C_INTERVAL_DAY_TO_SECOND:
+       case SQL_C_INTERVAL_HOUR_TO_MINUTE:
+       case SQL_C_INTERVAL_HOUR_TO_SECOND:
+       case SQL_C_INTERVAL_MINUTE_TO_SECOND:
+               return SYBDATETIME;
+       case SQL_C_BINARY:
+       case SQL_C_BIT:
+               return SYBBIT;
+       case SQL_C_TINYINT:
+               return SYBINT1;
+       case SQL_C_SLONG:
+               return SYBINT4;
+       case SQL_C_SSHORT:
+               return SYBINT2;
+       case SQL_C_STINYINT:
+               return SYBINT1;
+       case SQL_C_ULONG:
+               return SYBINT4;
+       case SQL_C_USHORT:
+               return SYBINT2;
+       case SQL_C_UTINYINT:
+               return SYBINT1;
        case SQL_CHAR:
        case SQL_VARCHAR:
                return SYBCHAR;
-       case SQL_BIT:
-               return SYBBIT;
-       case SQL_TINYINT:
-               return SYBINT1;
        case SQL_SMALLINT:
                return SYBINT2;
        case SQL_INTEGER:
@@ -1513,6 +1637,7 @@
        case SQL_FLOAT:
                return SYBREAL;
        }
+       return 0;
 }
 static SQLSMALLINT _odbc_get_client_type(int srv_type)
 {