Re: Smallmoney and money rounded to 2 decimals?

On 04/16/2018 11:39 AM, Frediano Ziglio wrote:
> 2018-04-12 9:37 GMT+01:00 Sebastien FLAESCH <[email protected]>:
>> On 04/12/2018 09:41 AM, Frediano Ziglio wrote:
>>>
>>> 2018-04-06 12:59 GMT+01:00 Sebastien FLAESCH <[email protected]>:
>>>>
>>>> Hi all,
>>>>
>>>> Using FreeTDS 1.00.9:
>>>>
>>>> When fetching a smallmoney or money data, SQLDescribeCol() returns
>>>> the sqltype SQL_DECIMAL, precision 10 for smallmoney and or 19 for
>>>> money and a scale of 4 for both types, which is fine, since smallmoney
>>>> and money can store 4 decimal digits.
>>>>
>>>> Using an SQL_C_CHAR buffer to hold the values, we see that the decimal
>>>> part is rounded to 2 digits...
>>>>
>>>> Is this expected?
>>>>
>>>> using other ODBC drivers for SQL Server we get 4 decimal digits.
>>>>
>>>> Is there some configuration settings to control this?
>>>>
>>>> Thanks!
>>>> Seb
>>>
>>>
>>> Just checked. Yes, apparently Microsoft ODBC drivers format money with
>>> 4 decimal digit.
>>> I think in the past tools used 2 digits but they moved to 4 along the way.
>>> There's actually no way to change that behavior but probably there should
>>> be.
>>> The 2 digits code is currently quite hard coded in src/tds/convert.c
>>> and src/tds/numeric.c.
>>> I suppose a good way would be to add a flag to TDSCONTEXT (which is
>>> used during conversion to pass locale information).
>>>
>>> Frediano
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> https://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>
>> Hello Frediano, and thanks for considering this!
>>
>> I am not familiar with FreeTDS code internals and TDSCONTEXT.
>>
>> I you want to keep backward compatibility, I assume that there will be a new
>> ODBC connection attribute to control that.
>>
>> http://www.freetds.org/userguide/odbcconnattr.htm
>>
>> And if we consider the current behavior as a bug, the default should be 4
>> decimal
>> digits, and people expecting 2 will have to define the parameter.
>>
>> MoneyFormatDecDigits=2
>>
>> You may also consider rounding issues:
>> Currently FreeTDS rounds to 2 digits...
>> What happens with a db value like 0.1160 ?
>> To be fetched as 0.12, or as 0.11 ?
>> When 0.11, is this data loss or ok?
>> It's monetary data!
>>
>> I vote for a bug => return always 4 digits.
>>
>>
>> Seb
> 
> Got some patches in both Branch-1_00 and master.
> On master ODBC uses always 4 digits. I'm considering if this should be
> backported too.
> Maybe I should just use 4 digits by default, I don't see much
> breakage, also considering
> Fredy comment on BCP exporting/importing MONEY with bcp surrently is loosing
> precision, which is not something we want.
> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> https://lists.ibiblio.org/mailman/listinfo/freetds
> 

Hi Frediano!

Sounds good... thanks a lot.

Seb