decimal/numeric field sometimes shows too many digits

[JannemanDev]

I have a table with a field resultaat of type decimal(3,1). If I insert a value into this field for example 9.3 or 9.2 and then I do a select query it shows respectively 9.300000000000001 and 9.199999999999999 Other values like 7.9 or 6.3 or 4.7 or 3.3 or whole numbers like 8 all work. Changing decimal to numeric suffers the same problem since they are equivalent (see https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql )

I connect to a MSSQL server...: Microsoft SQL Server Enterprise (64-bit) Microsoft Windows NT 6.3 (9600) NT x64 12.0.5557.0

...from a Linux machine with version: root@ubuntu:~# uname -r 4.4.0-62-generic

root@ubuntu:~# lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.2 LTS Release: 16.04 Codename: xenial

I have attached the table definition and used packages versions. tabledefinition.txt

installed_packages_versions.txt

all_installed_packages.txt

[JannemanDev]

I did some more research and tried a different MSSQL driver for my Ubuntu (v16): Microsoft Drivers for PHP for SQL Server

apt install curl  
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-tools.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install mssql-tools
sudo apt-get install unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

sqlcmd -S <ip>\\SQLEXPRESS,1433 -U username -P password
use elo_beoordeling
go
select * from beoordeling
go

And now it works... all numbers are correctly displayed. So it's clearly a bug in FreeTDS.

Online resources I used: https://github.com/Microsoft/msphpsql If you have trouble connecting with the SQL Server using the sqlcmd commandline tool see: https://github.com/Microsoft/msphpsql/issues/470

[freddy77]

Are you using the right protocol version? Which FreeTDS version are you using?

[JannemanDev]

@freddy77 I used protocol version 4.2 in my /etc/freetds/freetds.conf. I then tried to use a higher version with my MSSQL Server 2014 (v12)... by specifying it in the command line like: TDSVER=7.0 tsql -H hostname -p portnumber -U username -P password

Here are my findings (see also http://www.freetds.org/userguide/choosingtdsprotocol.htm ): 5.0 didn't work 7.0 works !! 7.1 works !! 7.2 error "unrecognized msgno" while connecting, but decimals were displayed correctly! 7.3 no errors on connecting, but still failed to display the decimals correctly 7.4 no errors on connecting, but still failed to display the decimals correctly

So now it works from command line. But it still doesn't work when I specify a protocol version higher than 4.2 in freetds.confand connecting from PHP with the following connection string:

$dsn="odbc:DRIVER=FreeTDS;SERVERNAME=mssql;DATABASE=elo_beoordeling;ClientCharset=WE8ISO8859P1";
$dbDB = new PDO($dsn,$username, $password,array(PDO::ATTR_TIMEOUT => "1",PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

I get an error: "SQLSTATE[08001] SQLDriverConnect: 0 [unixODBC][FreeTDS][SQL Server]Unable to connect to data source"

My freetds.conf looks like:

[mssql]
host = mssql.fhict.local
Port = 1433
tds version = 7.0

Update: after some more testing I found out it's failing because the value of the extra ClientCharset parameter I give in the DSN is incorrect. If I leave the complete parameter and value out and use the connection string below it works!

$dsn="odbc:DRIVER=FreeTDS;SERVERNAME=mssql;DATABASE=elo_beoordeling";

If you want to specify charset you can do this inside freetds.conf by adding a line to the section:

[mssql]
host = mssql.fhict.local
Port = 1433
tds version = 7.0
client charset = UTF-8

or in DSN :
$dsn="odbc:DRIVER=FreeTDS;SERVERNAME=mssql;DATABASE=elo_beoordeling";ClientCharset=UTF-8

But mind you the value is case sensitive! So utf-8 doesn't work but only UTF-8 !

[freddy77]

Sorry for late reply, seems I'm not receiving notifications for new posts. Protocol 4.2 is quite old and does not support numeric/decimal. For this reason the server has to convert to float/real which have the precision issue. I don't know why 7.3 and 7.4 are not working but I suspect, given the old freetds version installed that are not supported and fall back to some other version. I won't even be surprised if the warning with 7.2 would disappear with an update too. About utf-8/UTF-8 I'll test it, they both should work. I really encourage you to update, 0.91 is from 3 to 5 years old.

[JannemanDev]

How/where do I get the newest version of freetds-bin ? How do I update?

[freddy77]

There are 2 bugs open for Debian (Ubuntu usually get packages from Debian ones):

• https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=803258
• https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=866071

I just sent some emails trying to understand why these packages has not been updated to newer versions. FreeTDS project is not so big to be able to maintain multiple release for years and years, old versions get not updated and contains potentially security bugs and surely not all the support for newer server versions. I know having binary packages from the distro is very helpful but I would suggest the sources if Debian/Ubuntu cannot provide correct updates.

[freddy77]

Closing this issue, opened another more specific to Debian packaging