Re: dbdatecrack producing erroneous results
crunsus <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
Absolutely, here is the code I am using to read datetime field from SQL
Server 2008 R2
This I found to produce wrong results with Visual Studio 2005 and
linking statically with freetds 0.82
I checked the newer releases but it looks like tds_datecrack is still
the same and didn't want to recompile the whole thing as the current
version that I have matches what I have on the production server which
is a CentOS with freetds 0.82 deployed and used by PHP as well, and it
cannot be touched.
So I do my development on Windows with VS2005 but deploy with a
GCC/Makefile on Linux the same code as it is not Windows specific (just
ANSI C++), on Windows being much easier to debug and that is why I
prefer this method of working.
Anyways this is the code that deals with reading and parsing datetime
fields which doesn't work in all cases (on Windows):
//this overloaded method for datetime types is currently not working
well because
//it uses dbdatecrack from dblib which appears to be broken in
//FreeTDS (MS implementation of dblib works OK though, but don't want
that dependency)
void dbconn::get_data(int colid, DBDATEREC& val)
{
DBINT dl;
char m[BUFSIZ];
int type;
BYTE *tmp;
if (colid < 0 || colid > dbnumcols(dbproc)) {
sprintf(m, "column %d: out of range", colid);
throw dbexception(m);
}
type = dbcoltype(dbproc, colid);
if ((type != SYBDATETIME )&&
type != SYBDATETIME4)){
sprintf(m, "column %d: datatype does not match", colid);
throw dbexception(m);
}
dl = dbdatlen(dbproc, colid);
if (dl) {
tmp = const_cast<BYTE*>(dbdata(dbproc, colid));
dbdatecrack(dbproc, &val, (DBDATETIME *)tmp); // <------
here is the problem !!!!!
} else memset(&val, 0, sizeof(DBDATEREC));
}
The temporary replacement for this above function that I had to come up
with and which is absolutely ridiculous I know but it works 100%, is
this monstrosity:
//works very well for both datetime and smalldate types but is slow ,as
opposed to dbcrackdate implementation from freeTDS which is fast but
inaccurate
void dbconn::get_data(int colid, DBDATEREC& val){
DBINT dl;
char m[BUFSIZ];
int type;
DBDATETIME *tmp;
DBDATETIME4 *tmp1;
int dt_days;
unsigned int dt_time;
int years, months, days, ydays, wday, hours, mins, secs, ms;
int l, n, i, j;
if (colid < 0 || colid > dbnumcols(dbproc)) {
sprintf(m, "column %d: out of range", colid);
throw dbexception(m);
}
type = dbcoltype(dbproc, colid);
if (type != SYBDATETIME &&
type != SYBDATETIME4) {
sprintf(m, "column %d: datatype does not match", colid);
throw dbexception(m);
}
dl = dbdatlen(dbproc, colid);
if (dl) {
if (dl > 4){
tmp = (DBDATETIME*)(dbdata(dbproc, colid));
dt_days = tmp->dtdays;
dt_time = tmp->dttime;
ms = ((dt_time % 300) * 1000 + 150) / 300;
dt_time = dt_time / 300;
secs = dt_time % 60;
dt_time = dt_time / 60; // get seconds
hours = dt_time / 60; //get hours
mins = dt_time % 60; //get mins
} else
{
//4 byte date time
tmp1 = (DBDATETIME4*)(dbdata(dbproc, colid));
dt_days = tmp1->days;
dt_time = tmp1->minutes;
secs = 0;
ms = 0;
hours = dt_time / 60;
mins = dt_time % 60;
}
val.datehour = hours;
val.dateminute = mins;
val.datesecond = secs;
val.datemsecond = ms;
dt_days += 53690; //starting from 1753/1/1 the beginning of
Gregorian calendar
int fyears = dt_days / 365;
bool is_leap_year = false;
int sup_days = 0;
int elim_year=0;
int counted_years=1753;
int day_counter = 1;
for(int j=1;j<=dt_days;j++){
if(counted_years % 4 != 0 ) {
is_leap_year = false;
//normal year
if (day_counter % 365 == 0){
//increment normal year
counted_years++;
day_counter=1;
continue;
} else{
day_counter++;
continue;
}
} else {
//divisible by four check to see if it is also
divisible by 100
if (counted_years % 100 == 0){
//see if it is also divisible by 400
if (counted_years % 400 == 0){
//special leap year
is_leap_year = true;
if (day_counter % 366 == 0){
//increment normal year
counted_years++;
day_counter=1;
continue;
} else{
day_counter++;
continue;
}
} else {
//end of the century normal year
is_leap_year = false;
if (day_counter % 365 == 0){
//increment normal year
counted_years++;
day_counter=1;
continue;
} else{
day_counter++;
continue;
}
}
} else {
//no this is a "normal" leap year
//end of the century normal year
is_leap_year = true;
if (day_counter % 366 == 0){
//increment normal year
counted_years++;
day_counter=1;
continue;
} else{
day_counter++;
continue;
}
}
}
}
//here day_counter should contain the number of days left in
current year while counted_years should contain the current year
int current_year_days = day_counter;
if((current_year_days >=1) && (current_year_days <= 31)){
//jan
val.datemonth = 1;
val.datedmonth = current_year_days;
} else
{
//feb
if ((current_year_days >=32) && (current_year_days <= 59 +
(is_leap_year ? 1 : 0))){
val.datemonth = 2;
val.datedmonth = current_year_days - 31;
} else {
if ((current_year_days >= 60 + (is_leap_year ? 1 : 0))
&& (current_year_days <= 90 + (is_leap_year ? 1 : 0))){
//march
val.datemonth = 3;
val.datedmonth = current_year_days - (59 +
(is_leap_year ? 1 : 0));
}else {
if ((current_year_days >= 91 + (is_leap_year ? 1 :
0)) && (current_year_days <= 120 + (is_leap_year ? 1 : 0))){
//april
val.datemonth = 4;
val.datedmonth = current_year_days - (90 +
(is_leap_year ? 1 : 0));
} else {
if ((current_year_days >= 121 + (is_leap_year ?
1 : 0)) && (current_year_days <= 151 + (is_leap_year ? 1 : 0))){
//may
val.datemonth = 5;
val.datedmonth = current_year_days - (120
+ (is_leap_year ? 1 : 0));
} else {
if ((current_year_days >= 152 +
(is_leap_year ? 1 : 0)) && (current_year_days <= 181 + (is_leap_year ? 1
: 0))){
//june
val.datemonth = 6;
val.datedmonth = current_year_days -
(151 + (is_leap_year ? 1 : 0));
} else {
if ((current_year_days >= 182 +
(is_leap_year ? 1 : 0)) && (current_year_days <= 212 + (is_leap_year ? 1
: 0))){
//july
val.datemonth = 7;
val.datedmonth = current_year_days
- (181 + (is_leap_year ? 1 : 0));
} else {
if ((current_year_days >= 213 +
(is_leap_year ? 1 : 0)) && (current_year_days <= 243 + (is_leap_year ? 1
: 0))){
//august
val.datemonth = 8;
val.datedmonth =
current_year_days - (212 + (is_leap_year ? 1 : 0));
} else {
if ((current_year_days >= 244 +
(is_leap_year ? 1 : 0)) && (current_year_days <= 273 + (is_leap_year ? 1
: 0))){
//september
val.datemonth = 9;
val.datedmonth =
current_year_days - (243 + (is_leap_year ? 1 : 0));
} else {
if ((current_year_days >=
274 + (is_leap_year ? 1 : 0)) && (current_year_days <= 304 +
(is_leap_year ? 1 : 0))){
//october
val.datemonth = 10;
val.datedmonth =
current_year_days - (273 + (is_leap_year ? 1 : 0));
} else {
if ((current_year_days
>= 305 + (is_leap_year ? 1 : 0)) && (current_year_days <= 334 +
(is_leap_year ? 1 : 0))){
//november
val.datemonth = 11;
val.datedmonth =
current_year_days - (304 + (is_leap_year ? 1 : 0));
} else {
if
((current_year_days >= 335 + (is_leap_year ? 1 : 0)) &&
(current_year_days <= 365 + (is_leap_year ? 1 : 0))){
//dember
val.datemonth = 12;
val.datedmonth
= current_year_days - (334 + (is_leap_year ? 1 : 0));
} else {
//handle error,
should never get here
}
}
}
}
}
}
}
}
}
}
}
}
val.dateyear = counted_years;
}
else
memset(&val, 0, sizeof(DBDATEREC));
}
Best Regards,
Crunsus
On 4/22/2012 12:00 PM, [email protected] wrote:
> Send FreeTDS mailing list submissions to
> [email protected]
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.ibiblio.org/mailman/listinfo/freetds
> or, via email, send a message with subject or body 'help' to
> [email protected]
>
> You can reach the person managing the list at
> [email protected]
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of FreeTDS digest..."
>
>
> Today's Topics:
>
> 1. dbdatecrack producing erroneous results (crunsus)
> 2. Re: dbdatecrack producing erroneous results (Ken Collins)
> 3. Re: dbdatecrack producing erroneous results (Frediano Ziglio)
> 4. Re: dbdatecrack producing erroneous results (Frediano Ziglio)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Sat, 21 Apr 2012 20:45:49 -0400
> From: crunsus<[email protected]>
> Subject: [freetds] dbdatecrack producing erroneous results
> To: [email protected]
> Message-ID:<[email protected]>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi,
>
> I am trying to put together a C++ utility class that uses FreeTDS to
> connect to SQL 2008 R2 from Linux and
> it is so for working excellent with this small exception.
>
> When reading datetime fields of 8 byte precision from SQL server and
> parsing them using dbdatecrack the date is passed incorrectly for values
> that are outside of what normally one would use (i.e years in the
> distant future like 2300 A.D)
> I noticed that it works quite well for dates that are closer to our
> present time but it could be a day or so off for days that are in the
> distant future. I also noticed that when examining the results parsed by
> this function and cross checked with what SQL Manager would display,
> FreeTDS's results would be off by a day sometime, not all the times,
> depends on the praticular date.
>
> Was wondering if anyone is aware of this variance, or I am the only one
> experiencing this.
> Funny think is that when the same C++ code is compiled on win32 using
> borland's c++ compiler and dblib from Microsoft, it works perfectly so
> this is why I think Microsoft's implementation must be somehow different
> from the one used in FreeTDS. To me this is a make it or brake it kind
> of functionality that I got to have working perfectly as I rely heavily
> on datetime manipulations in my code. Using MS's dblib is not an option
> as it has to run on Linux but for Win32 I could get around by using
> their libs, this is how I came to notice this. While I am no C++ guru,
> I traced the problem to be in convert.c file in the tds_datecrack
> function. I tried to fix it but I have to admit that I don't understand
> how that works, so instead I rolled out my own naive implementation
> which is very slow compared to that one but it has the property that it
> always works for me,so I use it just to get me going, unlike dbdatecrack
> which in my opinion is not fully functiona as it stands now.
>
> I would very much like to help get this fixed professionally so
> that it works in all cases and with good performance. My approach on
> doing the date cracking is very naive (lots of looping and counting of
> leap years etc, but it always matches MS's result to the milisecond even
> for year 4000 AD say, but admittedly it is slow and sub-optimal, still
> better for me now than fast and erroneous :).
> Just trying to get a feel of what everybody else thinks about it,
> this should be huge if true and I was wondering how come nobody else
> reported it, but maybe it's just me and somehow I compiled this
> wrongfully or who knows. But the rest of the FreeTDS works great for me
> so how could that be? Anyways thanks a lot for this astounding piece of
> software and hopefully it can be solved in the future.
>
> Best Regards,
> Crunsus
>
>
>
> ------------------------------
>
> Message: 2
> Date: Sat, 21 Apr 2012 20:58:16 -0400
> From: Ken Collins<[email protected]>
> Subject: Re: [freetds] dbdatecrack producing erroneous results
> To: FreeTDS Development Group<[email protected]>
> Message-ID:<[email protected]>
> Content-Type: text/plain; charset=us-ascii
>
>
>> When reading datetime fields of 8 byte precision from SQL server and
>> parsing them using dbdatecrack the date is passed incorrectly for values
>> that are outside of what normally one would use (i.e years in the
>> distant future like 2300 A.D)
> Do you mean datetime2? I did not think the datetime type had a user defined precision. If you did mean datetime2, I am not sure FreeTDS supports that. FWIW, I have datetime tested to the hilt in my Ruby C extension of FreeTDS and it works like a champ with dbdatecrack() in all scenarios.
>
> But moot if your talking about datetime2. Again if that is the case, you may want to frame your questions and research around that.
>
>
> - Ken
>
>
>
> ------------------------------
>
> Message: 3
> Date: Sun, 22 Apr 2012 08:25:47 +0100
> From: Frediano Ziglio<[email protected]>
> Subject: Re: [freetds] dbdatecrack producing erroneous results
> To: FreeTDS Development Group<[email protected]>
> Message-ID:
> <CAHt6W4dwShgaa6K+MLzkgAAnFFLZZ=FRtdor4ZFCJKOZg0qxHQ@mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Il 22 aprile 2012 01:58, Ken Collins<[email protected]> ha scritto:
>>> When reading datetime fields of 8 byte precision from SQL server and
>>> parsing them using dbdatecrack the date is passed incorrectly for values
>>> that are outside of what normally one would use (i.e years in the
>>> distant future like 2300 A.D)
>> Do you mean datetime2? I did not think the datetime type had a user defined precision. If you did mean datetime2, I am not sure FreeTDS supports that. FWIW, I have datetime tested to the hilt in my Ruby C extension of FreeTDS and it works like a champ with dbdatecrack() in all scenarios.
>>
>> But moot if your talking about datetime2. Again if that is the case, you may want to frame your questions and research around that.
>>
>>
>> ?- Ken
>>
> No, ms dblib does not support protocols which allow datetime2 on wire.
>
> Frediano
>
>
> ------------------------------
>
> Message: 4
> Date: Sun, 22 Apr 2012 08:28:51 +0100
> From: Frediano Ziglio<[email protected]>
> Subject: Re: [freetds] dbdatecrack producing erroneous results
> To: FreeTDS Development Group<[email protected]>
> Message-ID:
> <CAHt6W4dv3vR_j-_cYGzytsdFGcbUyNvFmn-bdox4G=qgAwfezA@mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Il 22 aprile 2012 01:45, crunsus<[email protected]> ha scritto:
>> Hi,
>>
>> I am trying to put together a C++ utility ?class that uses FreeTDS to
>> connect to SQL 2008 R2 from Linux and
>> it is so for working excellent with this small exception.
>>
>> When reading datetime fields of 8 byte precision from SQL server and
>> parsing them using dbdatecrack the date is passed incorrectly for values
>> that are outside of what normally one would use (i.e years in the
>> distant future like 2300 A.D)
>> I noticed that it works quite well for dates that are closer to our
>> present time but it could be a day or so off for days that are in the
>> distant future. I also noticed that when examining the results parsed by
>> this function and cross checked with what SQL Manager would display,
>> FreeTDS's results would be off by a day sometime, not all the times,
>> depends on the praticular date.
>>
>> Was wondering if anyone is aware of this variance, or I am the only one
>> experiencing this.
>> Funny think is that when the same C++ code is compiled on win32 using
>> borland's c++ compiler and dblib from Microsoft, it works perfectly so
>> this is why I think Microsoft's implementation must be somehow different
>> from the one used in FreeTDS. To me this is a make it or brake it kind
>> of functionality that I got to have working perfectly ?as I rely heavily
>> on datetime manipulations in my code. Using MS's dblib is not an option
>> as it has to run on Linux but for Win32 I could get around by using
>> their libs, this is how I came to notice this. ?While I am no C++ guru,
>> I traced the problem to be in convert.c file in the tds_datecrack
>> function. ?I tried to fix it but I have to admit that I don't understand
>> how that works, so instead I rolled out my own naive implementation
>> which is very slow compared to that one but it has the property that it
>> always works for me,so I use it just to get me going, unlike dbdatecrack
>> which in my opinion is not fully functiona as it stands now.
>>
>> ? ? I would very much like to help get this fixed professionally so
>> that it works in all cases and with good performance. My approach on
>> doing the date cracking is very naive (lots of looping and counting of
>> leap years etc, but it always matches MS's result to the milisecond even
>> for year 4000 AD say, but admittedly it is slow and sub-optimal, still
>> better for me now than fast and erroneous :).
>> ? ? Just trying to get a feel of what everybody else thinks about it,
>> this should be huge if true and I was wondering how come nobody else
>> reported it, but maybe it's just me and somehow I compiled this
>> wrongfully or who knows. But the rest of the FreeTDS ?works great for me
>> so how could that be? Anyways thanks a lot for this astounding piece of
>> software and hopefully it can be solved in the future.
>>
>> Best Regards,
>> Crunsus
>>
> Quite strange... I extended odbc data test in order to test data in a
> far future and using 3803 as year works correctly. Are you sure you
> are using correct MSDBLIB definition ??
> Could you post some code?
>
> Frediano Ziglio
>
>
> ------------------------------
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
>
> End of FreeTDS Digest, Vol 111, Issue 18
> ****************************************