freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Re: [freetds] dbdatecrack producing erroneous results

From: crunsus <crunsus AT gmail.com>
To: freetds AT lists.ibiblio.org
Subject: Re: [freetds] dbdatecrack producing erroneous results
Date: Sun, 22 Apr 2012 22:07:06 -0400

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, freetds-request AT lists.ibiblio.org wrote:

Send FreeTDS mailing list submissions to
freetds AT lists.ibiblio.org

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
freetds-request AT lists.ibiblio.org

You can reach the person managing the list at
freetds-owner AT lists.ibiblio.org

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<crunsus AT gmail.com>
Subject: [freetds] dbdatecrack producing erroneous results
To: freetds AT lists.ibiblio.org
Message-ID:<4F9354BD.4070002 AT gmail.com>
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<ken AT metaskills.net>
Subject: Re: [freetds] dbdatecrack producing erroneous results
To: FreeTDS Development Group<freetds AT lists.ibiblio.org>
Message-ID:<58EB9F69-ABC9-4CAD-B540-37895A92213E AT metaskills.net>
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<freddy77 AT gmail.com>
Subject: Re: [freetds] dbdatecrack producing erroneous results
To: FreeTDS Development Group<freetds AT lists.ibiblio.org>
Message-ID:
<CAHt6W4dwShgaa6K+MLzkgAAnFFLZZ=FRtdor4ZFCJKOZg0qxHQ AT mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

Il 22 aprile 2012 01:58, Ken Collins<ken AT metaskills.net> 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<freddy77 AT gmail.com>
Subject: Re: [freetds] dbdatecrack producing erroneous results
To: FreeTDS Development Group<freetds AT lists.ibiblio.org>
Message-ID:
<CAHt6W4dv3vR_j-_cYGzytsdFGcbUyNvFmn-bdox4G=qgAwfezA AT mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

Il 22 aprile 2012 01:45, crunsus<crunsus AT gmail.com> 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
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

End of FreeTDS Digest, Vol 111, Issue 18
****************************************

[freetds] dbdatecrack producing erroneous results, crunsus, 04/21/2012
- Re: [freetds] dbdatecrack producing erroneous results, Ken Collins, 04/21/2012
  - Re: [freetds] dbdatecrack producing erroneous results, Frediano Ziglio, 04/22/2012
- Re: [freetds] dbdatecrack producing erroneous results, Frediano Ziglio, 04/22/2012
- <Possible follow-up(s)>
- Re: [freetds] dbdatecrack producing erroneous results, crunsus, 04/22/2012
  - Re: [freetds] dbdatecrack producing erroneous results, Frediano Ziglio, 04/23/2012
- Re: [freetds] dbdatecrack producing erroneous results, crunsus, 04/24/2012
  - Re: [freetds] dbdatecrack producing erroneous results, Frediano Ziglio, 04/24/2012
    - Re: [freetds] dbdatecrack producing erroneous results, Ken Collins, 04/24/2012
      - Re: [freetds] dbdatecrack producing erroneous results, James K. Lowden, 04/24/2012
        
        Re: [freetds] dbdatecrack producing erroneous results, Ken Collins, 04/24/2012
- Re: [freetds] dbdatecrack producing erroneous results, crunsus, 04/24/2012