[freetds] FreeTDS and numeric fields in MSSQL2005 database

Frediano Ziglio freddy77 at gmail.com
Fri Feb 19 04:21:26 EST 2010

2010/2/19 James K. Lowden <jklowden at freetds.org>:
> Thomas Stover wrote:
>> When you say "work with them as text", does that mean the column type is
>> text or a numerical type? A numerical type is going to be converted
>> somewhere along the line for sure if you go from text and back.
> Exactly so.  If you look at a TDSDUMP log you'll see metadata describing
> the column's native format and calls to tds_convert() to provide a text
> representation.  The library performs the conversion and chooses the
> format.  For an analogous question, cf.
> http://www.freetds.org/faq.html#dateformat.  To ensure identical string
> representations, have the server convert them for you.
> For db-lib and ct-lib, the usual metric for compatibility is, "Do as
> Sybase does".  If the Sybase implementation behaves differently from
> FreeTDS, that's normally considered a bug.  If the Microsoft
> implementation differs, that's normally filed under Known Behavior until
> and unless someone decides to create a way to choose the behavior.  The
> standard for ODBC is Microsoft's driver.
> But it's hard to guaranty perfect fidelity.  It's not even clear we should
> try.  Allow me to explain.
> In the specific case of *numeric*, yes, it's possible to create a string
> that exactly represents the decimal value stored in the database. But for
> float/real, it's not.  To take one example I know very well: how should
> single-precision be converted ot double-precision.  You might think:
>        float f = 1/3; double d;
>        ...
>        d = f;
> would produce the same thing as:
>        dbconvert( ... &f ... &d ... );
> but it won't, not with Microsoft's db-lib.  The C assignment yields a
> standard expansion of the binary representation.  It assumes that f is
> doing its level best to hold 1/3.  Microsoft's db-library assumes
> something else: that f can be trusted to hold only 6 decimal places
> accurately, and therefore anything beyond that must be assumed to be zero.
>  The upshot is that printf("%1.15f", d) produces:
> C:      0.333333333333333
> db-lib: 0.333333000000000
> Which answer is "right" depends on information not available to the
> library: how did the value fetched from the database come to be there?  If
> it was an exact number, exactly 333,333 parts in a million, Microsoft's
> answer is exactly right.  If, on the other hand, it's a ratio, the
> compiler's outcome is more accurate.
> FreeTDS does not follow Microsoft's lead here, if lead it be.  To do so
> would be difficult and arguably wrong.  Instead, it allows the compiler to
> determine the expansion.  Any application relying on db-lib for that
> expansion will get different answers, depending on the implementation wo
> which it links.  I know; mine was one.

I remember when I started with FreeTDS for the first time... types not
supported, cores and so on... some years ago we was speaking about
conversions, locales and rouding problems. Now we are speaking about
differencies between '.0000', '0.0000' and '0.00' !!! Mathematically
the same number (not in physics) !!! I think that any client should
handle 0.0000 as well as .0000 or 0 or .00. I thinkg that our library
does the same. Honestly I prefer 0.xxx form, I don't understand why
anybody should format a 0 FLOAT as 0.0 and a 0 NUMERIC to .0, they are
two numbers. Here we have no precision problems (NUMERICs store
EXACTLY decimal positions you specify, there is no rounding problems)
like FLOAT; NUMERIC store decimal digits (even if in binary format)
while FLOAT store binary digits (that is bits). Why don't we catch
these conversions in our tests (like src/odbc/data.c and
src/odbc/genparams.c) ??


More information about the FreeTDS mailing list