Background
We have a legacy windows application (developed in Clarion) that uses PostgreSQL 9.3 as the backend database. The application uses PostgreSQL ODBC 32-bit Driver(Unicode) 9.03.04 to interact with the database.
All the users of the application so far have used Windows 7 Professional 64-bit machines and have the same version of PostgreSQL ODBC 32-bit Driver installed on them. Everything worked well till then. Since last month we started giving new users Windows 7 Enterprise 64-bit machines. When we installed the same version 32-bit of psql-odbc driver on these new machines, it defaulted to install 64-bit version of the driver. Then we manually installed psql odbc 9.05.01 driver that had a 32-bit specific installer(after uninstalling the x64 driver) on all these machines to ensure all machines use a 32-bit driver.
Problem
On all the newer machines running Windows 7 Enterprise, when a fixed decimal value database field is inserted or updated it is being converted to longer floating point precision.
Below is a query that gets logged on the server when an update is made from these newer machines.
UPDATE schema.table SET VALUE = $1 WHERE ID = $2
parameters: $1 = '-57.192999999999998', $2 = '672718'
Data Types are VALUE:numeric and ID:integer
The problem is that the application is actually passing the value as '-57.193' and it goes through to the database exactly as '-57.193' from all the "Win7 Pro" machines, but the Win7 Enterprise send the data to postgres with 15 decimal places.
I have debugged the application to make sure the values are being passed to odbc with 3 decimal places in all cases. I can confirm that there is no issue on the application side. But, odbc is actually converting all the queries with fixed point decimals to 15 decimal places. This is happening for all queries that have fixed point decimal fields being inserted or updated. In all cases a 3 or 4 decimal value is converted to 15 decimal value after the period.
What is different on these Windows 7 Enterprise machines that could cause this problem? I made sure all the machines are using 32-bit PSQL ODBC drivers (albeit being different versions). The same problem occured even when the Enterprise machines had the same version(9.03.04) of the 64-bit driver installed on them. Am I looking at it the wrong way? May be the problem is somewhere outside the OS version and ODBC, any suggestions are welcome.
I tried to read the ODBC trace log to see exact in and out values of the SQL parameters, but they are not in plain text. They are in the below format.
HSTMT 0x0305F650
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 8 <SQL_C_DOUBLE>
SWORD 2 <SQL_NUMERIC>
SQLULEN 28
SWORD 6
PTR 0x0445F02E
SQLLEN 8
SQLLEN * 0x04818D08
Any help to even read the parameter values from the ODBC tracelog is much appreciated.
