Thread: NUMERIC type makes trouble in MS Access

NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
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);



Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
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);



Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Hi Hiroshi,

thanks for your reply!

Am Sa, 26.05.2018, 14:23 schrieb Inoue, Hiroshi:
>> commercial ODBC driver:
>
> What is the commercial ODBC driver?

Devart ODBC Driver for PostgreSQL

> 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);

I don't insert things, it's a read only database. The problem is
about getting the data into Access. The insertions just were an
example for testing, which values or types actually make an issue ;)

Could you perhaps add a switchable function like "cast numeric to
float"?

Best regards,
Tobias



Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Some additional information about supported functions and mappings:

https://www.devart.com/odbc/postgresql/docs/supported_data_types.htm
https://www.devart.com/odbc/postgresql/docs/supported_odbc_api_functions.htm

Does PGSQL-ODBC map NUMERIC the same way (to SQL_NUMERIC)?



Re: NUMERIC type makes trouble in MS Access

From
Jan Wieck
Date:
Anything that is exact precision in the database should never be handled as "double" in any driver. That is a serious bug and a possible violation of accounting laws in many countries. 

That is assuming "double" in this case means "double precision floating point" as in C's double type.


Best Regards,
Jan


On Sat, May 26, 2018 at 10:58 PM, Tobias Wendorff <tobias.wendorff@tu-dortmund.de> wrote:
Some additional information about supported functions and mappings:

https://www.devart.com/odbc/postgresql/docs/supported_data_types.htm
https://www.devart.com/odbc/postgresql/docs/supported_odbc_api_functions.htm

Does PGSQL-ODBC map NUMERIC the same way (to SQL_NUMERIC)?





--
Jan Wieck
Senior Postgres Architect

Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Am So, 27.05.2018, 05:18 schrieb Jan Wieck:
> Anything that is exact precision in the database should never
> be handled as "double" in any driver. That is a serious bug and
> a possible violation of accounting laws in many countries.

So SQL_NUMERIC is DOUBLE? Sorry, I'm not an insider ;)

Access supports DECIMAL file type with a range from -9.999... x 10^27
to +9.999... x 10^27 while DOUBLE goes from -1.797 x 10^308 to
1.797 x 10^308.

Actually, older Access had problems with DECIMAL fields in the past,
maybe those got fixed already?

Best regards,
Tobias



RE: NUMERIC type makes trouble in MS Access

From
Danny Severns
Date:

Don't know if this helps, and I know almost nothing about Access, but I found this in the MS Docs, https://docs.microsoft.com/en-us/sql/odbc/microsoft/microsoft-access-data-types?view=sql-server-2017.

 

The following table shows the Microsoft Access data types (Col 1), data types used to create tables (Col 2), and ODBC SQL data types (Col 3).

NUMBER (FieldSize= SINGLE)

SINGLE

SQL_REAL

NUMBER (FieldSize= DOUBLE)

DOUBLE

SQL_DOUBLE

NUMBER (FieldSize= BYTE)

UNSIGNED BYTE

SQL_TINYINT

NUMBER (FieldSize= INTEGER)

SHORT

SQL_SMALLINT

NUMBER (FieldSize= LONG INTEGER)

LONG

SQL_INTEGER

NUMERIC

NUMERIC

SQL_NUMERIC

 

From this it appears the Access datatype NUMBER would have to have a qualifier when declared and used to CREATE a table, in order to know to which datatype it would need to be converted when pulled from PG and inserted into Access.  But I certainly may not have understood your problem correctly.

 

HTH

Danny

 

-----Original Message-----
From: Tobias Wendorff <tobias.wendorff@tu-dortmund.de>
Sent: Sunday, May 27, 2018 11:36 AM
To: Jan Wieck <jan@wi3ck.info>
Cc: Inoue, Hiroshi <h-inoue@dream.email.ne.jp>; pgsql-odbc@lists.postgresql.org
Subject: Re: NUMERIC type makes trouble in MS Access

 

Am So, 27.05.2018, 05:18 schrieb Jan Wieck:

> Anything that is exact precision in the database should never be

> handled as "double" in any driver. That is a serious bug and a

> possible violation of accounting laws in many countries.

 

So SQL_NUMERIC is DOUBLE? Sorry, I'm not an insider ;)

 

Access supports DECIMAL file type with a range from -9.999... x 10^27 to +9.999... x 10^27 while DOUBLE goes from -1.797 x 10^308 to

1.797 x 10^308.

 

Actually, older Access had problems with DECIMAL fields in the past, maybe those got fixed already?

 

Best regards,

Tobias

 

 

RE: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Am So, 27.05.2018, 21:35 schrieb Danny Severns:
> But I certainly may not have understood your problem correctly.

Access throws an error when trying to read columns with type NUMERIC,
which don't have a precision limit from linked tables.

I can't remember what happens to fields with a precision limit.
I'll try it soon again (lots of work to install / uninstall the drivers).



Re: NUMERIC type makes trouble in MS Access

From
Jan Wieck
Date:


On Sun, May 27, 2018 at 12:35 PM, Tobias Wendorff <tobias.wendorff@tu-dortmund.de> wrote:
Am So, 27.05.2018, 05:18 schrieb Jan Wieck:
> Anything that is exact precision in the database should never
> be handled as "double" in any driver. That is a serious bug and
> a possible violation of accounting laws in many countries.

So SQL_NUMERIC is DOUBLE? Sorry, I'm not an insider ;)

Nope, please read that again and then compare that statement to what you wrote about the "commercial" driver (using double) and your suggestion to have a switch to make the PostgreSQL ODBC driver do the same (nonsense).

 

Access supports DECIMAL file type with a range from -9.999... x 10^27
to +9.999... x 10^27 while DOUBLE goes from -1.797 x 10^308 to
1.797 x 10^308.

But what precision does Access claim to support?

All I understood so far is that PostgreSQL's NUMERIC without explicit precision sometimes overwhelms Access with too many digits after the decimal point. If that is accurate you may want to either complain to Microsoft about their insufficient implementation of DECIMAL or fix the schema of the source database.


Best Regards,
Jan

 



Actually, older Access had problems with DECIMAL fields in the past,
maybe those got fixed already?

Best regards,
Tobias




--
Jan Wieck
Senior Postgres Architect

Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Dear Jan,

Am So, 27.05.2018, 23:22 schrieb Jan Wieck:
>
> Nope, please read that again and then compare that statement to
> what you wrote about the "commercial" driver (using double) and
> your suggestion to have a switch to make the PostgreSQL ODBC
> driver do the same (nonsense).

I'm asking you not to be condescending with people who come to
this mailing list with a problem. Not all the members on this
lists senior postgres architects, some are just normal users.

My idea for this "nonsense" switch just was a workaround for bad
designed databases schemas or for compatibility reasons with rarely
used closed source applications, such as Microsoft Access.

> But what precision does Access claim to support?

Access 2010 at least can handle a precision of 28 digits.
Actually, that's what you can set on its GUI.

> All I understood so far is that PostgreSQL's NUMERIC without explicit
> precision sometimes overwhelms Access with too many digits after the
> decimal point. If that is accurate you may want to either complain to
> Microsoft about their insufficient implementation of DECIMAL or fix
> the schema of the source database.

I don't think that the database design is broken, since PostgreSQL
supports NUMERIC columns without a specific precision. So all I can
do is: create a VIEW, which rounds or casts my data on the fly to
a format, Access can access via the ODBC driver.

Best regards,
Tobias



Re: NUMERIC type makes trouble in MS Access

From
Craig Ringer
Date:
On 28 May 2018 at 05:43, Tobias Wendorff <tobias.wendorff@tu-dortmund.de> wrote:

> All I understood so far is that PostgreSQL's NUMERIC without explicit
> precision sometimes overwhelms Access with too many digits after the
> decimal point. If that is accurate you may want to either complain to
> Microsoft about their insufficient implementation of DECIMAL or fix
> the schema of the source database.

I don't think that the database design is broken, since PostgreSQL
supports NUMERIC columns without a specific precision. So all I can
do is: create a VIEW, which rounds or casts my data on the fly to
a format, Access can access via the ODBC driver.


Some quick research suggests that Access's Decimal data type is what you should be using. It's not floating point. This fits with what you're saying, as the docs say Decimal in Access is limited to 28 digits.

Seems like it probably has a more limited implementation using fixed-point, not PostgreSQL's BCD representation, as they use a fixed 17 bytes in the native storage. 

It should map as SQL_DECIMAL, NOT double or float.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Hi Craig,

Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>
> Some quick research suggests that Access's Decimal data type is
> what you should be using. It's not floating point. This fits with
> what you're saying, as the docs say Decimal in Access is limited
> to 28 digits.

But there's a problem: you can't change the field type of a linked
table in MS Access. Even if the driver delivers the correct type,
I cannot influence the decimal width (or anything else) and the
problem inevitably arises.

So, as Jan and I suggested, the only way is to apply a change
either in the column itself or do it in realtime using a VIEW.

> Seems like it probably has a more limited implementation using
> fixed-point, not PostgreSQL's BCD representation, as they use
> a fixed 17 bytes in the native storage.
>
> It should map as SQL_DECIMAL, NOT double or float.

Sure, I didn't want to force fundamental changes here. I just
wanted to suggest something like a "compatibility switch" for
Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.

Best,
Tobias



RE: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Here an update:

by default, MS Access sets linked tables with "unlimited" NUMERIC
to DECIMAL with "precision: 28" and "decimals: 6" (MS Access names).
Trying to read a value [INSERT INTO public.demo VALUES (1.0 / 3)]
breaks with an error.

When creating the column using NUMERIC(28, 24), MS Access shows this:
"precision: 28" and "decimals: 24". And there's no error.

Funny, when creating the column using NUMERIC(32, 28), MS Access
interprets it as TEXT with field length 32.

NUMERIC(28,27) is the last column type, MS Access can read as DECIMAL.

A view on an "unlimited" NUMERIC with casting ::NUMERIC(28, 24)
is accepted by Access. So it seems like I need to create a VIEW as a
work-around.



Re: NUMERIC type makes trouble in MS Access

From
Craig Ringer
Date:
On 28 May 2018 at 11:50, Tobias Wendorff <tobias.wendorff@tu-dortmund.de> wrote:
Here an update:

by default, MS Access sets linked tables with "unlimited" NUMERIC
to DECIMAL with "precision: 28" and "decimals: 6" (MS Access names).
Trying to read a value [INSERT INTO public.demo VALUES (1.0 / 3)]
breaks with an error.

When creating the column using NUMERIC(28, 24), MS Access shows this:
"precision: 28" and "decimals: 24". And there's no error.

Funny, when creating the column using NUMERIC(32, 28), MS Access
interprets it as TEXT with field length 32.

NUMERIC(28,27) is the last column type, MS Access can read as DECIMAL.

A view on an "unlimited" NUMERIC with casting ::NUMERIC(28, 24)
is accepted by Access. So it seems like I need to create a VIEW as a
work-around.

Sounds like this is something the ODBC driver could work around, by exposing the option to clamp the reported NUMERIC scale and precision.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Hi Tobias,

Could you please try the test drivers 10.03.0100 on Windows at
    https://winpg.jp/~inoue/psqlodbc/index.html
?
The test drivers have an option
    Numeric(without precision) as
.

regards,
Hiroshi Inoue

On 2018/05/28 11:13, Tobias Wendorff wrote:
Hi Craig,

Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
Some quick research suggests that Access's Decimal data type is
what you should be using. It's not floating point. This fits with
what you're saying, as the docs say Decimal in Access is limited
to 28 digits.
But there's a problem: you can't change the field type of a linked
table in MS Access. Even if the driver delivers the correct type,
I cannot influence the decimal width (or anything else) and the
problem inevitably arises.

So, as Jan and I suggested, the only way is to apply a change
either in the column itself or do it in realtime using a VIEW.

Seems like it probably has a more limited implementation using
fixed-point, not PostgreSQL's BCD representation, as they use
a fixed 17 bytes in the native storage.

It should map as SQL_DECIMAL, NOT double or float.
Sure, I didn't want to force fundamental changes here. I just
wanted to suggest something like a "compatibility switch" for
Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.

Best,
Tobias

Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Hi Hiroshi,

works nice, well done!

Could you perhaps extend the field length of varchar to "max varchar"
from "Miscellaneous" on page 1? Right now it's limited to 28.

That would allow exporting the highest possible precision in Access
(f.e. export to CSV).

Thanks again... really good work!
Tobias



Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
> Hi Tobias,
>
> Could you please try the test drivers 10.03.0100 on Windows at
> https://winpg.jp/~inoue/psqlodbc/index.html
> ?
> The test drivers have an option
>      Numeric(without precision) as
> .
>
> regards,
> Hiroshi Inoue
>
> On 2018/05/28 11:13, Tobias Wendorff wrote:
>> Hi Craig,
>>
>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>> Some quick research suggests that Access's Decimal data type is
>>> what you should be using. It's not floating point. This fits with
>>> what you're saying, as the docs say Decimal in Access is limited
>>> to 28 digits.
>> But there's a problem: you can't change the field type of a linked
>> table in MS Access. Even if the driver delivers the correct type,
>> I cannot influence the decimal width (or anything else) and the
>> problem inevitably arises.
>>
>> So, as Jan and I suggested, the only way is to apply a change
>> either in the column itself or do it in realtime using a VIEW.
>>
>>> Seems like it probably has a more limited implementation using
>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>> a fixed 17 bytes in the native storage.
>>>
>>> It should map as SQL_DECIMAL, NOT double or float.
>> Sure, I didn't want to force fundamental changes here. I just
>> wanted to suggest something like a "compatibility switch" for
>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>
>> Best,
>> Tobias
>




Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Hi Tobias,

Could you please try the test drivers 10.03.0110 on Windows at
   https://winpg.jp/~inoue/psqlodbc/index.html
?

regards,
Hiroshi Inoue

On 2018/05/28 16:32, Tobias Wendorff wrote:
> Hi Hiroshi,
>
> works nice, well done!
>
> Could you perhaps extend the field length of varchar to "max varchar"
> from "Miscellaneous" on page 1? Right now it's limited to 28.
>
> That would allow exporting the highest possible precision in Access
> (f.e. export to CSV).
>
> Thanks again... really good work!
> Tobias
>
>
>
> Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
>> Hi Tobias,
>>
>> Could you please try the test drivers 10.03.0100 on Windows at
>> https://winpg.jp/~inoue/psqlodbc/index.html
>> ?
>> The test drivers have an option
>>       Numeric(without precision) as
>> .
>>
>> regards,
>> Hiroshi Inoue
>>
>> On 2018/05/28 11:13, Tobias Wendorff wrote:
>>> Hi Craig,
>>>
>>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>>> Some quick research suggests that Access's Decimal data type is
>>>> what you should be using. It's not floating point. This fits with
>>>> what you're saying, as the docs say Decimal in Access is limited
>>>> to 28 digits.
>>> But there's a problem: you can't change the field type of a linked
>>> table in MS Access. Even if the driver delivers the correct type,
>>> I cannot influence the decimal width (or anything else) and the
>>> problem inevitably arises.
>>>
>>> So, as Jan and I suggested, the only way is to apply a change
>>> either in the column itself or do it in realtime using a VIEW.
>>>
>>>> Seems like it probably has a more limited implementation using
>>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>>> a fixed 17 bytes in the native storage.
>>>>
>>>> It should map as SQL_DECIMAL, NOT double or float.
>>> Sure, I didn't want to force fundamental changes here. I just
>>> wanted to suggest something like a "compatibility switch" for
>>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>>
>>> Best,
>>> Tobias


Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Dear Hiroshi,

in case you missed my E-Mail from 13 hours ago:

works nice, well done!

Could you perhaps extend the field length of varchar to "max varchar"
from "Miscellaneous" on page 1? Right now it's limited to 28.

That would allow exporting the highest possible precision in Access
(f.e. export to CSV).

Thanks again... really good work!
Tobias



Am Mo, 28.05.2018, 14:59 schrieb Inoue, Hiroshi:
> Hi Tobias,
>
> Could you please try the test drivers 10.03.0110 on Windows at
>    https://winpg.jp/~inoue/psqlodbc/index.html
> ?
>
> regards,
> Hiroshi Inoue
>
> On 2018/05/28 16:32, Tobias Wendorff wrote:
>> Hi Hiroshi,
>>
>> works nice, well done!
>>
>> Could you perhaps extend the field length of varchar to "max varchar"
>> from "Miscellaneous" on page 1? Right now it's limited to 28.
>>
>> That would allow exporting the highest possible precision in Access
>> (f.e. export to CSV).
>>
>> Thanks again... really good work!
>> Tobias
>>
>>
>>
>> Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
>>> Hi Tobias,
>>>
>>> Could you please try the test drivers 10.03.0100 on Windows at
>>> https://winpg.jp/~inoue/psqlodbc/index.html
>>> ?
>>> The test drivers have an option
>>>       Numeric(without precision) as
>>> .
>>>
>>> regards,
>>> Hiroshi Inoue
>>>
>>> On 2018/05/28 11:13, Tobias Wendorff wrote:
>>>> Hi Craig,
>>>>
>>>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>>>> Some quick research suggests that Access's Decimal data type is
>>>>> what you should be using. It's not floating point. This fits with
>>>>> what you're saying, as the docs say Decimal in Access is limited
>>>>> to 28 digits.
>>>> But there's a problem: you can't change the field type of a linked
>>>> table in MS Access. Even if the driver delivers the correct type,
>>>> I cannot influence the decimal width (or anything else) and the
>>>> problem inevitably arises.
>>>>
>>>> So, as Jan and I suggested, the only way is to apply a change
>>>> either in the column itself or do it in realtime using a VIEW.
>>>>
>>>>> Seems like it probably has a more limited implementation using
>>>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>>>> a fixed 17 bytes in the native storage.
>>>>>
>>>>> It should map as SQL_DECIMAL, NOT double or float.
>>>> Sure, I didn't want to force fundamental changes here. I just
>>>> wanted to suggest something like a "compatibility switch" for
>>>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>>>
>>>> Best,
>>>> Tobias
>




Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Hi Tobias,

On 2018/05/29 6:19, Tobias Wendorff wrote:
> Dear Hiroshi,
>
> in case you missed my E-Mail from 13 hours ago:

No.
The modified drivers should extend the field length to "max varchar" size.
Please test the driver and tell me the result.

regards,
Hiroshi Inoue

>
> works nice, well done!
>
> Could you perhaps extend the field length of varchar to "max varchar"
> from "Miscellaneous" on page 1? Right now it's limited to 28.
>
> That would allow exporting the highest possible precision in Access
> (f.e. export to CSV).
>
> Thanks again... really good work!
> Tobias
>
>
>
> Am Mo, 28.05.2018, 14:59 schrieb Inoue, Hiroshi:
>> Hi Tobias,
>>
>> Could you please try the test drivers 10.03.0110 on Windows at
>>     https://winpg.jp/~inoue/psqlodbc/index.html
>> ?
>>
>> regards,
>> Hiroshi Inoue
>>
>> On 2018/05/28 16:32, Tobias Wendorff wrote:
>>> Hi Hiroshi,
>>>
>>> works nice, well done!
>>>
>>> Could you perhaps extend the field length of varchar to "max varchar"
>>> from "Miscellaneous" on page 1? Right now it's limited to 28.
>>>
>>> That would allow exporting the highest possible precision in Access
>>> (f.e. export to CSV).
>>>
>>> Thanks again... really good work!
>>> Tobias
>>>
>>>
>>>
>>> Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
>>>> Hi Tobias,
>>>>
>>>> Could you please try the test drivers 10.03.0100 on Windows at
>>>> https://winpg.jp/~inoue/psqlodbc/index.html
>>>> ?
>>>> The test drivers have an option
>>>>        Numeric(without precision) as
>>>> .
>>>>
>>>> regards,
>>>> Hiroshi Inoue
>>>>
>>>> On 2018/05/28 11:13, Tobias Wendorff wrote:
>>>>> Hi Craig,
>>>>>
>>>>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>>>>> Some quick research suggests that Access's Decimal data type is
>>>>>> what you should be using. It's not floating point. This fits with
>>>>>> what you're saying, as the docs say Decimal in Access is limited
>>>>>> to 28 digits.
>>>>> But there's a problem: you can't change the field type of a linked
>>>>> table in MS Access. Even if the driver delivers the correct type,
>>>>> I cannot influence the decimal width (or anything else) and the
>>>>> problem inevitably arises.
>>>>>
>>>>> So, as Jan and I suggested, the only way is to apply a change
>>>>> either in the column itself or do it in realtime using a VIEW.
>>>>>
>>>>>> Seems like it probably has a more limited implementation using
>>>>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>>>>> a fixed 17 bytes in the native storage.
>>>>>>
>>>>>> It should map as SQL_DECIMAL, NOT double or float.
>>>>> Sure, I didn't want to force fundamental changes here. I just
>>>>> wanted to suggest something like a "compatibility switch" for
>>>>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>>>>
>>>>> Best,
>>>>> Tobias


Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Hi Hiroshi,

Am Di, 29.05.2018, 00:39 schrieb Inoue, Hiroshi:
> The modified drivers should extend the field length to
> "max varchar" size. Please test the driver and tell me the result.

Oops, I didn't see a change in version number and thought it was the
same build.

Sorry, it does NOT work. I've set "max varchar" to 250, but the column
in Access still has a width of 28. I've uninstalled and re-installed
the driver and I've set up a fresh DSN for testing.

Also, there's another bug: When relinking the table from Access'
tablemanager, the column type suddenly gets changed to double
(without having anything changed in the ODBC driver). It's exactly
the same table. There's no way to revert this :(

Best regards,
Tobias



Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Am Di, 29.05.2018, 00:39 schrieb Inoue, Hiroshi:
>>> Could you please try the test drivers 10.03.0110 on Windows at
>>>     https://winpg.jp/~inoue/psqlodbc/index.html

I think, I might have found the problem:
The driver version after installing the latest version is 10.03.01.00,
which could be 10.03.0100 only...

I've downloaded again, manually uninstalled and reinstalled it.
Still 10.03.01.00 without new varchar setting :(



Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Interesting detail: It's not an Access problem.

I've tested it with Python and Libre Office Base. NUMERIC without
precision gets set to 28,6 => like in Access.

Would it perhaps better to use MEMO instead of VARCHAR?
MEMO allows more than 8,000 chars in "Base" and some gigabytes in
Access.

But there's not enough space for an additional toggle switch :(



Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Oops sorry,

Please download it again and retry.

regards,
Hiroshi Inoue

On 2018/05/29 9:43, Tobias Wendorff wrote:
> Am Di, 29.05.2018, 00:39 schrieb Inoue, Hiroshi:
>>>> Could you please try the test drivers 10.03.0110 on Windows at
>>>>      https://winpg.jp/~inoue/psqlodbc/index.html
> I think, I might have found the problem:
> The driver version after installing the latest version is 10.03.01.00,
> which could be 10.03.0100 only...
>
> I've downloaded again, manually uninstalled and reinstalled it.
> Still 10.03.01.00 without new varchar setting :(



Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Am Di, 29.05.2018, 03:39 schrieb Inoue, Hiroshi:
> Oops sorry,
>
> Please download it again and retry.

It works.

There are some little layout issues with the label. They're
intersecting with the radiobuttons.

What do you think about my MEMO approach?



Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Hi Tobias,

On 2018/05/29 18:00, Tobias Wendorff wrote:
Am Di, 29.05.2018, 03:39 schrieb Inoue, Hiroshi:
Oops sorry,

Please download it again and retry.
It works.

There are some little layout issues with the label. They're
intersecting with the radiobuttons.

What do you think about my MEMO approach?

Could you please try the test drivers 10.03.0120 on Windows at
     https://winpg.jp/~inoue/psqlodbc/index.html
?
I added *memo* and removed *numeric* because default means
*numeric*.
 
regards,
Hiroshi Inoue

Re: NUMERIC type makes trouble in MS Access

From
Jan Wieck
Date:
Tobias,

On Sun, May 27, 2018 at 5:43 PM, Tobias Wendorff <tobias.wendorff@tu-dortmund.de> wrote:
Dear Jan,

Am So, 27.05.2018, 23:22 schrieb Jan Wieck:
>
> Nope, please read that again and then compare that statement to
> what you wrote about the "commercial" driver (using double) and
> your suggestion to have a switch to make the PostgreSQL ODBC
> driver do the same (nonsense).

I'm asking you not to be condescending with people who come to
this mailing list with a problem. Not all the members on this
lists senior postgres architects, some are just normal users.

sorry if that came across as condescending. It wasn't meant as such.

 

My idea for this "nonsense" switch just was a workaround for bad
designed databases schemas or for compatibility reasons with rarely
used closed source applications, such as Microsoft Access.

In my opinion such broad "switches", that then silently mangle over all data, are dangerous at best. 

Where is the difference between truncating a number and truncating a date to stop PostgreSQL from complaining about February 31st when importing data from MySQL? Or a switch for the Oracle FDW to replace NUL bytes in VARCHAR2 columns with \x00 sequences?

The only difference I can see is that one is out of PostgreSQL, the other two are into. 


Regards, Jan



 

> But what precision does Access claim to support?

Access 2010 at least can handle a precision of 28 digits.
Actually, that's what you can set on its GUI.

> All I understood so far is that PostgreSQL's NUMERIC without explicit
> precision sometimes overwhelms Access with too many digits after the
> decimal point. If that is accurate you may want to either complain to
> Microsoft about their insufficient implementation of DECIMAL or fix
> the schema of the source database.

I don't think that the database design is broken, since PostgreSQL
supports NUMERIC columns without a specific precision. So all I can
do is: create a VIEW, which rounds or casts my data on the fly to
a format, Access can access via the ODBC driver.

Best regards,
Tobias




--
Jan Wieck
Senior Postgres Architect

Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Hi Hiroshi,

Am Di, 29.05.2018, 14:20 schrieb Inoue, Hiroshi:
>
> I added *memo* and removed *numeric* because default means
> *numeric*.

Thanks it works nice. What do you think about extending this feature
to always apply it to NUMERIC - even when it has a scale and precision?

When having a column with NUMERIC(60,40), it doesn't fit into DECIMAL,
but it fits into MEMO and VARCHAR.

Using the current switch, the user could either keep it as default
or cast it to anything else. That feature would be VERY complete then.

Best regards,
Tobias



Re: NUMERIC type makes trouble in MS Access

From
"Tobias Wendorff"
Date:
Hi there,

your website seems to have some cipher hick-ups.

Am Di, 29.05.2018, 13:20 schrieb Inoue, Hiroshi:
>
> Could you please try the test drivers 10.03.0120 on Windows at
> https://winpg.jp/~inoue/psqlodbc/index.html

Do you have a mirror of the test driver or even a never version?

Best regards,
Tobias



Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Hi Tobias,

Thanks for the report.
I would contact the site manager.

On 2018/11/02 23:01, Tobias Wendorff wrote:
> Hi there,
>
> your website seems to have some cipher hick-ups.
>
> Am Di, 29.05.2018, 13:20 schrieb Inoue, Hiroshi:
>> Could you please try the test drivers 10.03.0120 on Windows at
>> https://winpg.jp/~inoue/psqlodbc/index.html
> Do you have a mirror of the test driver or even a never version?

We are planning to make a new release in a few weeks.
We have some troubles with missing 32-bit libpq.

regards,
Hiroshi Inoue

> Best regards,
> Tobias

---
このメールは、AVG によってウイルス チェックされています。
http://www.avg.com



Re: NUMERIC type makes trouble in MS Access

From
"Inoue, Hiroshi"
Date:
Hi Tobias,

Connection to the website has been restored.
Psqlodbc version of the site is 10.03.0200.

regards,
Hiroshi Inoue

On 2018/11/03 9:30, Inoue, Hiroshi wrote:
> Hi Tobias,
>
> Thanks for the report.
> I would contact the site manager.
>
> On 2018/11/02 23:01, Tobias Wendorff wrote:
>> Hi there,
>>
>> your website seems to have some cipher hick-ups.
>>
>> Am Di, 29.05.2018, 13:20 schrieb Inoue, Hiroshi:
>>> Could you please try the test drivers 10.03.0120 on Windows at
>>> https://winpg.jp/~inoue/psqlodbc/index.html
>> Do you have a mirror of the test driver or even a never version?
>
> We are planning to make a new release in a few weeks.
> We have some troubles with missing 32-bit libpq.
>
> regards,
> Hiroshi Inoue
>
>> Best regards,
>> Tobias


---
このメールは、AVG によってウイルス チェックされています。
http://www.avg.com