FIX: dbconvert() May Return String With More Than 15 Digits
  
PSS ID Number: Q108159
Article last modified on 02-21-1995
 
4.20   | 4.20    | 4.20
 
MS-DOS | WINDOWS | OS/2
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server Programmer's Toolkit, version 4.2
----------------------------------------------------------------------
 
BUG# 9660 (4.20.50)
 
SYMPTOMS
========
 
The dbconvert() function of DB-Library may convert a floating point number
to a string containing more than the 15 digits of precision as specified in
the documentation.
 
Even though the SQL Server documentation states that float datatype
variables may have up to 15 digits of precision, it is possible to get a
string containing 16 digits from the dbconvert function. The unexpected
16th digit is a result of the inherent loss of precision incurred when
representing floating point numbers, and it is necessary in order to
guarantee that the remaining 15 digits provide a better approximation of
the original value.
 
For example, 0.0006 will be converted to 6.000000000000001e-004. Note that
the 1 is appended as digit 16 and appears extraneous. If this extraneous
digit is dropped and the remaining 15 digits in the string are rounded, we
get the a better result. You may verify this using the following code
segment:
 
  dbcmd (dbproc, "SELECT CONVERT(float,0.0006) ");
 
  dbsqlexec (dbproc);
 
  while (dbresults(dbproc) != NO_MORE_RESULTS)
   {¬
    dbbind(dbproc,1,NTBSTRINGBIND,  (DBINT) 0,       (BYTE *) char_result);
    while (dbnextrow(dbproc) != NO_MORE_ROWS)
     {¬
      printf("dbconvert   %s\n",char_result);
 
WORKAROUND
==========
 
Use the TRANSACT SQL STR function when retrieving data of type FLOAT from
SQL Server.
 
Alternatively allocate a character buffer wide enough to store the 16
digits and then round the number to the desired precision ignoring the 16th
digit.
 
Unless information which can only be represented as float needs to be
stored, use the other available datatypes.
 
STATUS
======
 
Microsoft has confirmed this to be a problem in DB-Library version 4.20.50.
This problem was corrected in version 4.21.02. For more information, please
contact your primary support provider.
 
Additional reference words: 4.20 dblib dbroutines Windows NT
KBCategory: kbprg
KBSubCategory: SSrvDB_Lib SSrvProg SSrvWinNT
 
=============================================================================
 
Copyright Microsoft Corporation 1995.
