Re: NUMERIC type makes trouble in MS Access

Hi Tobias,

On 2018/02/13 2:44, Tobias Wendorff wrote:
> Hi there,
>
> I'm experiencing problems with the ODBC driver on Windows 7 (and 10)
> on 32 and 64 bit in Microsoft Access 2003, 2010 and 2016 (32, 64 bit).
>
> My PostgreSQL server runs on Debian (64-bit): 9.6.7-1.pgdg80+1
> Tested ODBC drivers: psqlodbc_09_06_0500 & psqlodbc_10_01_0000
>
> Summary of the problem:
> The current behavior of the ODBC driver creates an error in Access,
> since the incoming values are longer than the field type. This leads
> into a truncation warning (with many messageboxes) and no values
> are shown at all. The expected behavior is either TEXT (bad) or
> DOUBLE (without any precision details).
>
> Documentation:
> As stated in the docs, I've played with the setting "Unknown Sizes";
> they don't help, but give me a different result:
> - "Maximum" & "Longest" behave equal (see blow)
> - "Don't Know" creates an error in Access (types can't be identified)
>
> Deeper analyis:
> 1. column type NUMERIC (without further specification)
> PostgreSQL's ODBC driver:
> - field type: decimal
> - precision: 28
> - decimal places: 6
> - decimal places display: automatic
> => I'm getting the truncation warning, no values shown (only errors).
>
> commercial ODBC driver:

What is the commercial ODBC driver?

> - field type: double
> - decimal places display: automatic
> => Display works as expected.
>
> 2. column type NUMERIC (40, 32)
> PostgreSQL's ODBC driver:
> - field type: text
> - length: 40
> => All values shown with trailing zeros and it's a string.
>
> commercial ODBC driver:
> => same behaviour
>
> 3. column type NUMERIC (26, 18)
> PostgreSQL's ODBC driver:
> - field type: decimal
> - precision: 26
> - decimal places: 18
> - decimal places display: automatic
> => As expected, truncation to '12345.012345678901234568'.
>
> Lossy workaround:
> A view, which casts NUMERIC to FLOAT.
>
> Could anyone have a look please?
>
> Best regards,
> Tobias
>
> Tested with this syntax:
> DROP TABLE IF EXISTS demo;

> CREATE TABLE DEMO (v numeric);
> INSERT INTO demo VALUES (1.0);

The column v is numeric item without precision.
Are the following insertions possible using the commercial DBMS?

> INSERT INTO demo VALUES (1.0123456789);
> INSERT INTO demo VALUES (1.01234567890123456789);
> INSERT INTO demo VALUES (12345.012345678901234567890123456789);