NUMERIC type makes trouble in MS Access
От | Tobias Wendorff |
---|---|
Тема | NUMERIC type makes trouble in MS Access |
Дата | |
Msg-id | 6a37a7d4372deee3354e08983104ad81.squirrel@webmail.tu-dortmund.de обсуждение исходный текст |
Ответы |
Re: NUMERIC type makes trouble in MS Access
|
Список | pgsql-odbc |
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: - 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); INSERT INTO demo VALUES (1.0123456789); INSERT INTO demo VALUES (1.01234567890123456789); INSERT INTO demo VALUES (12345.012345678901234567890123456789); ALTER TABLE demo ALTER COLUMN v TYPE numeric(40,35); ALTER TABLE demo ALTER COLUMN v TYPE numeric(26,18);
В списке pgsql-odbc по дате отправления: