=== modified file 'ChangeLog' --- ChangeLog 2011-08-03 13:00:51 +0000 +++ ChangeLog 2011-08-05 08:56:25 +0000 @@ -5,6 +5,7 @@ timeout. (Bug #39878) * MS Access with VARCHAR NOT NULL columns. (Bug #31067) * sqlwcharchr might read one SQLWCHAR after end of string. (Bug #61586) + * Fractional parts of second is ignored in SQLGetData. (Bug #60646) ---- === modified file 'driver/myutil.h' --- driver/myutil.h 2010-10-09 10:12:54 +0000 +++ driver/myutil.h 2011-08-04 20:18:10 +0000 @@ -64,6 +64,10 @@ #define SQLNUM_TRUNC_FRAC 1 #define SQLNUM_TRUNC_WHOLE 2 +/* Conversion to SQL_TIMESTAMP_STRUCT errors(str_to_ts) */ +#define SQLTS_NULL_DATE -1 +#define SQLTS_BAD_DATE -2 + /* Wrappers to hide differences in client library versions. */ #if MYSQL_VERSION_ID >= 40100 # define my_int2str(val, dst, radix, upcase) \ @@ -314,6 +318,7 @@ SQLTypeMap *proc_get_param_map_by_index(int index); char *proc_param_next_token(char *str, char *str_end); void set_row_count(STMT * stmt, my_ulonglong rows); +char *get_fractional_part(const char * str, SQLUINTEGER * fraction); #ifdef __WIN__ #define cmp_database(A,B) myodbc_strcasecmp((const char *)(A),(const char *)(B)) === modified file 'driver/results.c' --- driver/results.c 2011-02-04 17:57:07 +0000 +++ driver/results.c 2011-08-05 08:46:24 +0000 @@ -139,6 +139,24 @@ return TRUE; } } + case MYSQL_TYPE_STRING: + { + switch (cType) + { + /* Date string is(often) identified as binary data by the driver. + Have to either add exception here, or to change the way we detect if + field is binary. + */ + case SQL_C_TIMESTAMP: + case SQL_C_DATE: + case SQL_C_TIME: + case SQL_C_TYPE_TIMESTAMP: + case SQL_C_TYPE_DATE: + case SQL_C_TYPE_TIME: + + return TRUE; + } + } } return FALSE; @@ -169,6 +187,7 @@ SQLLEN tmp; long long numericValue; my_bool convert= 1; + SQLRETURN result= SQL_SUCCESS; /* get the exact type if we don't already have it */ if (fCType == SQL_C_DEFAULT) @@ -214,7 +233,10 @@ if (!odbc_supported_conversion(get_sql_data_type(stmt, field, 0), fCType) && !driver_supported_conversion(field,fCType)) { - return set_stmt_error(stmt, "07009", "Conversion is not possible", 0); + /*The state 07009 was incorrect + (http://msdn.microsoft.com/en-us/library/ms715441%28v=VS.85%29.aspx) + */ + return set_stmt_error(stmt, "07006", "Conversion is not possible", 0); } if (!pcbValue) @@ -424,19 +446,32 @@ field->type == MYSQL_TYPE_DATETIME) { SQL_TIMESTAMP_STRUCT ts; - if (str_to_ts(&ts, value, stmt->dbc->ds->zero_date_to_min)) + + switch (str_to_ts(&ts, value, stmt->dbc->ds->zero_date_to_min)) + { + case SQLTS_BAD_DATE: + return set_stmt_error(stmt, "22018", "Data value is not a valid time(stamp) value", 0); + case SQLTS_NULL_DATE: *pcbValue= SQL_NULL_DATA; - else - { - SQL_TIME_STRUCT *time_info= (SQL_TIME_STRUCT *)rgbValue; - - if (time_info) + break; + default: { - time_info->hour= ts.hour; - time_info->minute= ts.minute; - time_info->second= ts.second; + SQL_TIME_STRUCT *time_info= (SQL_TIME_STRUCT *)rgbValue; + + if (time_info) + { + time_info->hour= ts.hour; + time_info->minute= ts.minute; + time_info->second= ts.second; + + if (ts.fraction > 0) + { + set_stmt_error(stmt, "01S07", NULL, 0); + result= SQL_SUCCESS_WITH_INFO; + } + } + *pcbValue= sizeof(TIME_STRUCT); } - *pcbValue= sizeof(TIME_STRUCT); } } else if (field->type == MYSQL_TYPE_DATE) @@ -455,10 +490,13 @@ { SQL_TIME_STRUCT ts; if (str_to_time_st(&ts, value)) + { *pcbValue= SQL_NULL_DATA; + } else { SQL_TIME_STRUCT *time_info= (SQL_TIME_STRUCT *)rgbValue; + SQLUINTEGER fraction; if (time_info) { @@ -466,7 +504,19 @@ time_info->minute= ts.minute; time_info->second= ts.second; } + *pcbValue= sizeof(TIME_STRUCT); + + get_fractional_part(value, &fraction); + + if (fraction) + { + /* http://msdn.microsoft.com/en-us/library/ms713346%28v=VS.85%29.aspx + We are loosing fractional part - thus we have to set correct sqlstate + and return SQL_SUCCESS_WITH_INFO */ + set_stmt_error(stmt, "01S07", NULL, 0); + result= SQL_SUCCESS_WITH_INFO; + } } } break; @@ -478,7 +528,9 @@ SQL_TIME_STRUCT ts; if (str_to_time_st(&ts, value)) + { *pcbValue= SQL_NULL_DATA; + } else { SQL_TIMESTAMP_STRUCT *timestamp_info= @@ -487,23 +539,30 @@ struct tm cur_tm; localtime_r(&sec_time, &cur_tm); + /* I wornder if that hasn't to be server current date*/ timestamp_info->year= 1900 + cur_tm.tm_year; timestamp_info->month= 1 + cur_tm.tm_mon; /* January is 0 in tm */ timestamp_info->day= cur_tm.tm_mday; timestamp_info->hour= ts.hour; timestamp_info->minute= ts.minute; timestamp_info->second= ts.second; - timestamp_info->fraction= 0; + get_fractional_part(value, ×tamp_info->fraction); *pcbValue= sizeof(SQL_TIMESTAMP_STRUCT); } } else { - if (str_to_ts((SQL_TIMESTAMP_STRUCT *)rgbValue, value, + switch (str_to_ts((SQL_TIMESTAMP_STRUCT *)rgbValue, value, stmt->dbc->ds->zero_date_to_min)) + { + case SQLTS_BAD_DATE: + return set_stmt_error(stmt, "22018", "Data value is not a valid date/time(stamp) value", 0); + case SQLTS_NULL_DATE: *pcbValue= SQL_NULL_DATA; - else + break; + default: *pcbValue= sizeof(SQL_TIMESTAMP_STRUCT); + } } break; @@ -561,9 +620,7 @@ if (stmt->getdata.source) /* Second call to getdata */ return SQL_NO_DATA_FOUND; - stmt->getdata.source= NULL; /* All data is retrieved */ - - return SQL_SUCCESS; + return result; } === modified file 'driver/utility.c' --- driver/utility.c 2011-05-31 10:42:22 +0000 +++ driver/utility.c 2011-08-05 09:01:11 +0000 @@ -32,6 +32,7 @@ #include const SQLULEN sql_select_unlimited= (SQLULEN)-1; +const uint datetime_digits= 14; /** Execute a SQL statement. @@ -1901,48 +1902,72 @@ my_bool str_to_ts(SQL_TIMESTAMP_STRUCT *ts, const char *str, int zeroToMin) { uint year, length; - char buff[15],*to; + char buff[15], *to, *decptr; SQL_TIMESTAMP_STRUCT tmp_timestamp; + SQLUINTEGER fraction; if ( !ts ) - ts= (SQL_TIMESTAMP_STRUCT *) &tmp_timestamp; - - for ( to= buff ; *str && to < buff+sizeof(buff)-1 ; ++str ) - { - if ( isdigit(*str) ) - *to++= *str; - } - + { + ts= (SQL_TIMESTAMP_STRUCT *) &tmp_timestamp; + } + + /* We don't wan to change value in the out parameter directly + before we know that string is a good datetime */ + decptr= get_fractional_part(str, &fraction); + + for ( to= buff ; (decptr && str < decptr || !decptr && *str); ++str ) + { + if ( isdigit(*str) ) + { + if (to < buff+sizeof(buff)-1) + { + *to++= *str; + } + else + { + /* We have too many numbers in the string and we not gonna tolerate it */ + return SQLTS_BAD_DATE; + } + } + } + + /* If there was fractional part length would be set */ length= (uint) (to-buff); if ( length == 6 || length == 12 ) /* YYMMDD or YYMMDDHHMMSS */ { - memmove(to+2, to, length); - if ( buff[0] <= '6' ) - { - buff[0]='2'; - buff[1]='0'; - } - else - { - buff[0]='1'; - buff[1]='9'; - } - length+= 2; - to+= 2; - } - - if ( length < 14 ) - strfill(to,14 - length,'0'); + memmove(to+2, to, length); + + if ( buff[0] <= '6' ) + { + buff[0]='2'; + buff[1]='0'; + } + else + { + buff[0]='1'; + buff[1]='9'; + } + + length+= 2; + to+= 2; + } + + if (length < datetime_digits) + { + strfill(buff + length, datetime_digits - length, '0'); + } else - *to= 0; - + { + *to= 0; + } + year= (digit(buff[0])*1000+digit(buff[1])*100+digit(buff[2])*10+digit(buff[3])); if (!strncmp(&buff[4], "00", 2) || !strncmp(&buff[6], "00", 2)) { if (!zeroToMin) /* Don't convert invalid */ - return 1; + return SQLTS_NULL_DATE; /* convert invalid to min allowed */ if (!strncmp(&buff[4], "00", 2)) @@ -1951,13 +1976,14 @@ buff[7]= '1'; } - ts->year= year; - ts->month= digit(buff[4])*10+digit(buff[5]); - ts->day= digit(buff[6])*10+digit(buff[7]); - ts->hour= digit(buff[8])*10+digit(buff[9]); - ts->minute= digit(buff[10])*10+digit(buff[11]); - ts->second= digit(buff[12])*10+digit(buff[13]); - ts->fraction= 0; + ts->year= year; + ts->month= digit(buff[4])*10+digit(buff[5]); + ts->day= digit(buff[6])*10+digit(buff[7]); + ts->hour= digit(buff[8])*10+digit(buff[9]); + ts->minute= digit(buff[10])*10+digit(buff[11]); + ts->second= digit(buff[12])*10+digit(buff[13]); + ts->fraction= fraction; + return 0; } @@ -1976,7 +2002,7 @@ for ( to= buff ; *str && to < buff+sizeof(buff)-1 ; ++str ) { - if ( isdigit(*str) ) + if (isdigit(*str)) *to++= *str; } @@ -3431,3 +3457,41 @@ stmt->dbc->mysql.affected_rows= rows; } } + +/** + Gets fractional time of a second from datetime or time string. + + @param[in] value (date)time string + @param[out] fraction buffer where to put fractional part in nanoseconds + + Returns pointer to decimal point in the string +*/ +char * +get_fractional_part(const char * str, SQLUINTEGER * fraction) +{ + char *decptr= strchr(str, '.'); + + if (decptr) + { + char *ptr, buff[10]; + strfill(buff, sizeof(buff)-1, '0'); + + for (ptr= buff, str= decptr+1; *str && ptr < buff + sizeof(buff); ++ptr) + { + /* there actually should not be anything that is not a digit... */ + if (isdigit(*str)) + { + *ptr= *str++; + } + } + + buff[9]= 0; + *fraction= atoi(buff); + } + else + { + *fraction= 0; + } + + return decptr; +} === modified file 'test/my_datetime.c' --- test/my_datetime.c 2010-12-01 09:22:31 +0000 +++ test/my_datetime.c 2011-08-05 08:49:52 +0000 @@ -1,5 +1,5 @@ /* - Copyright (c) 2007, 2010, Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2007, 2011, Oracle and/or its affiliates. All rights reserved. The MySQL Connector/ODBC is licensed under the terms of the GPLv2 , like most @@ -22,6 +22,7 @@ 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ +#include #include "odbctap.h" @@ -965,8 +966,104 @@ } +/** + Bug#60646 - Fractions of seconds ignored in results +*/ +DECLARE_TEST(t_bug60646) +{ + SQLCHAR buff[128]; + TIMESTAMP_STRUCT ts; + SQLLEN len; + const char *expected= "2012-01-01 01:01:01.000001"; + + ok_sql(hstmt, + "SELECT timestamp('2012-01-01 01:01:01.000001')" /*1*/ + " ,timestamp('2012-01-01 01:01:01.100002')" /*2*/ + " ,'2011-07-29 17:52:15.0000000009'" /*3*/ + " ,'1000-01-01 12:00:00.000000001'" /*4*/ + " ,time('2011-12-31 23:59:59.999999')" /*5*/ + " ,ADDTIME('9999-12-31 23:59:59.999999', '1 1:1:1.000002')" /*6*/ + ); + ok_stmt(hstmt, SQLFetch(hstmt)); + + /* Fields 1-4 checking conversions from date as a string + /* 1) just to be sure that everything is fine with string */ + is_str(my_fetch_str(hstmt, buff, 1), expected, sizeof(expected)); + + /* 2) testing if fractional part is converted to nanoseconds correctly */ + ok_stmt(hstmt, SQLGetData(hstmt, 2, SQL_C_TYPE_TIMESTAMP, &ts, sizeof(ts), + &len)); + + is_num(ts.fraction, 100002000); + + /* 3) fractional part is less than we care (less than nanosecond). + Test using string as MySQL does not support units less than a microsecond */ + ok_stmt(hstmt, SQLGetData(hstmt, 3, SQL_C_TYPE_TIMESTAMP, &ts, sizeof(ts), + &len)); + is_num(ts.fraction, 0); + + /* 4) testing if min fraction detected + Again - mysql supports microseconds only. thus using string + */ + ok_stmt(hstmt, SQLGetData(hstmt, 4, SQL_C_TYPE_TIMESTAMP, &ts, sizeof(ts), + &len)); + is_num(ts.fraction, 1); + + /* 5) if time is converted to timestamp - checking if current date is set + and if fractional part in place. former can actually fail if day is + being changed */ + + { + time_t sec_time= time(NULL); + struct tm * cur_tm; + + ok_stmt(hstmt, SQLGetData(hstmt, 5, SQL_C_TYPE_TIMESTAMP, &ts, sizeof(ts), + &len)); + cur_tm= localtime(&sec_time); + + is_num(ts.year, 1900 + cur_tm->tm_year); + is_num(ts.month, 1 + cur_tm->tm_mon); + is_num(ts.day, cur_tm->tm_mday); + } + + is_num(ts.fraction, 999999000); + + /* 6) Expecting an error because of longer date + At the moment ADDTIME('9999-12-31 23:59:59.999999', '1 1:1:1.000002') + will give you 10000-01-02 01:01:01.000001 + */ + + expect_stmt(hstmt, SQLGetData(hstmt, 6, SQL_C_TYPE_TIMESTAMP, &ts, sizeof(ts), + &len), SQL_ERROR); + + if (check_sqlstate(hstmt, "22018") != OK) + { + return FAIL; + } + + /* 5th col once again This time we get it in time struct. Thus we are + loosing fractioanl part. Thus the state has to be 01S07 and + SQL_SUCCESS_WITH_INFO returned */ + { + SQL_TIME_STRUCT timestruct; + + expect_stmt(hstmt, SQLGetData(hstmt, 5, SQL_C_TYPE_TIME, ×truct, + sizeof(timestruct), &len), SQL_SUCCESS_WITH_INFO); + + if (check_sqlstate(hstmt, "01S07") != OK) + { + return FAIL; + } + } + + expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA_FOUND); + + return OK; +} + + BEGIN_TESTS - ADD_TEST(my_ts) + /*ADD_TEST(my_ts) ADD_TEST(t_tstotime) ADD_TEST(t_tstotime1) ADD_TEST(t_bug25846) @@ -980,7 +1077,8 @@ ADD_TEST(t_bug14414) ADD_TEST(t_bug30939) ADD_TEST(t_bug31009) - ADD_TEST(t_bug37342) + ADD_TEST(t_bug37342)*/ + ADD_TEST(t_bug60646) END_TESTS === modified file 'test/odbctap.h' --- test/odbctap.h 2011-08-02 12:29:22 +0000 +++ test/odbctap.h 2011-08-04 10:11:39 +0000 @@ -1,5 +1,5 @@ /* - Copyright (c) 2007, 2010, Oracle and/or its affiliates. All rights reserved. + Copyright (c) 2007, 2011, Oracle and/or its affiliates. All rights reserved. The MySQL Connector/ODBC is licensed under the terms of the GPLv2 , like most