[freetds] FreeTDS and numeric fields in MSSQL2005 database
James K. Lowden
jklowden at freetds.org
Thu Feb 18 19:42:46 EST 2010
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:
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.
More information about the FreeTDS