Wrong scale for numeric data types in MS Access and ADODB

Hi,

I'm mostly convinced this is a bug, but I just need to verify (and
maybe find a workaround??). I am trying to link a postgres table with
a numeric column into Microsoft Access 2003 using the PostgreSQL
Unicode ODBC driver, version 8.04.02.00 (12/27/2009). Here is the
table and data in PostgreSQL 8.4.

CREATE TABLE chem
(
id serial NOT NULL,
"name" text NOT NULL,
atomic_weight numeric,
CONSTRAINT chem_pkey PRIMARY KEY (id),
CONSTRAINT chem_name_key UNIQUE (name)
);

INSERT INTO chem("name", atomic_weight) VALUES
('Arsenic',74.92159),
('Sodium', 22.98976928),
('Tritium',3.016049);


The data looks normal in pgAdmin, so there is no problem on the server end.

On my Microsoft Windows test laptop, I've set up a System DSN using
the PostgreSQL Unicode ODBC driver to the database using all defaults,
setting only the required fields. In MS Access 2003, I can link
public.chem as public_chem, but viewing the linked table shows me
this:

id name atomic_weight
1 Arsenic 74.92159
#Error #Error #Error
3 Tritium 3.016049

There are three rows, but the second shows #Error across all lines,
and if I click on that row I see an error dialog "Scaline of decimal
value resulted in data truncation" eight times. If I insert another
row (using psql):
INSERT INTO chem("name", atomic_weight) VALUES ('Cesium',132.9054519);

this also has an error, since there are 7 decimal places used.
However, I can insert more lines with 6 or fewer decimal places
without error.

Viewing the MS Access linked table in [read-only] "Design mode" of
reveals that the numeric field "atomic_weight" has a precision of 28
and a scale of 6. This is not correct (or modifiable), since there are
more than 6 decimal places used in the database for my example data.
This is why the rows with Sodium and Cesium have errors, while Arsenic
and Tritium are good, since there are 6 or less decimal places used.

Investigating further using VBA/ADODB shows a similar underling scale issue:

Sub test()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

conn.ConnectionString = "Driver={PostgreSQL
Unicode};Server=192.168.123.1;Port=5432;Database=mydb;Uid=my user;Pwd=mypass;"
conn.Open

Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM chem WHERE name='Sodium';"

Set rs = cmd.Execute()
Set cmd = Nothing

Debug.Print rs("name"), rs("atomic_weight"),
rs("atomic_weight").NumericScale

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing
End Sub

The console debug output shows:
Sodium 22.98976928 6

meaning that the NumericScale of the field is also set at 6. Again,
this scale of 6 is incorrect, but the output is correct and no errors
are thrown by this sub.

How can I modify the scale for this column? Is this a bug, and with
what component? Are there any known workarounds besides crippling my
data to a smaller scale?

Thanks for taking a look!

-Mike

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Wrong scale for numeric data types in MS Access and ADODB

Oh, I did find a simple workaround by modifying my database:

ALTER TABLE chem ALTER atomic_weight TYPE numeric(28,14);

I guess my case for a bug is if the numeric data type has an
unspecified scale, then it defaults to 6 for some unknown reason.

-Mike

On 15 June 2010 19:37, Mike Toews wrote:
> Hi,
>
> I'm mostly convinced this is a bug, but I just need to verify (and
> maybe find a workaround??). I am trying to link a postgres table with
> a numeric column into Microsoft Access 2003 using the PostgreSQL
> Unicode ODBC driver, version 8.04.02.00 (12/27/2009). Here is the
> table and data in PostgreSQL 8.4.
>
> CREATE TABLE chem
> (
> =A0id serial NOT NULL,
> =A0"name" text NOT NULL,
> =A0atomic_weight numeric,
> =A0CONSTRAINT chem_pkey PRIMARY KEY (id),
> =A0CONSTRAINT chem_name_key UNIQUE (name)
> );
>
> INSERT INTO chem("name", atomic_weight) VALUES
> ('Arsenic',74.92159),
> ('Sodium', 22.98976928),
> ('Tritium',3.016049);
>
>
> The data looks normal in pgAdmin, so there is no problem on the server en=
d.
>
> On my Microsoft Windows test laptop, I've set up a System DSN using
> the PostgreSQL Unicode ODBC driver to the database using all defaults,
> setting only the required fields. In MS Access 2003, I can link
> public.chem as public_chem, but viewing the linked table shows me
> this:
>
> id =A0 =A0 =A0name =A0 =A0atomic_weight
> 1 =A0 =A0 =A0 Arsenic 74.92159
> #Error =A0#Error =A0#Error
> 3 =A0 =A0 =A0 Tritium 3.016049
>
> There are three rows, but the second shows #Error across all lines,
> and if I click on that row I see an error dialog "Scaline of decimal
> value resulted in data truncation" eight times. If I insert another
> row (using psql):
> INSERT INTO chem("name", atomic_weight) VALUES ('Cesium',132.9054519);
>
> this also has an error, since there are 7 decimal places used.
> However, I can insert more lines with 6 or fewer decimal places
> without error.
>
> Viewing the MS Access linked table in [read-only] "Design mode" of
> reveals that the numeric field "atomic_weight" has a precision of 28
> and a scale of 6. This is not correct (or modifiable), since there are
> more than 6 decimal places used in the database for my example data.
> This is why the rows with Sodium and Cesium have errors, while Arsenic
> and Tritium are good, since there are 6 or less decimal places used.
>
> Investigating further using VBA/ADODB shows a similar underling scale iss=
ue:
>
> Sub test()
> =A0 =A0Dim conn As New ADODB.Connection
> =A0 =A0Dim cmd As New ADODB.Command
> =A0 =A0Dim rs As ADODB.Recordset
>
> =A0 =A0conn.ConnectionString =3D "Driver=3D{PostgreSQL
> Unicode};Server=3D192.168.123.1;Port=3D5432;Database=3Dmydb; Uid=3Dmyuser;=
Pwd=3Dmypass;"
> =A0 =A0conn.Open
>
> =A0 =A0Set cmd.ActiveConnection =3D conn
> =A0 =A0cmd.CommandText =3D "SELECT * FROM chem WHERE name=3D'Sodium';"
>
> =A0 =A0Set rs =3D cmd.Execute()
> =A0 =A0Set cmd =3D Nothing
>
> =A0 =A0Debug.Print rs("name"), rs("atomic_weight"),
> rs("atomic_weight").NumericScale
>
> =A0 =A0rs.Close
> =A0 =A0Set rs =3D Nothing
>
> =A0 =A0conn.Close
> =A0 =A0Set conn =3D Nothing
> End Sub
>
> The console debug output shows:
> Sodium =A0 =A0 =A0 =A0 22.98976928 =A0 6
>
> meaning that the NumericScale of the field is also set at 6. Again,
> this scale of 6 is incorrect, but the output is correct and no errors
> are thrown by this sub.
>
> How can I modify the scale for this column? Is this a bug, and with
> what component? Are there any known workarounds besides crippling my
> data to a smaller scale?
>
> Thanks for taking a look!
>
> -Mike
>

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Wrong scale for numeric data types in MS Access and ADODB

On Tue, Jun 15, 2010 at 7:43 PM, Mike Toews wrote:
> Oh, I did find a simple workaround by modifying my database:
>
> ALTER TABLE chem ALTER atomic_weight TYPE numeric(28,14);
>
> I guess my case for a bug is if the numeric data type has an
> unspecified scale, then it defaults to 6 for some unknown reason.

I did a quick google search and came up with the following document:

http://support.microsoft.com/kb/214854

I think that it helps to explain part of the issue your seeing.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Wrong scale for numeric data types in MS Access and ADODB

On 15 June 2010 19:50, Richard Broersma wrote:
> I did a quick google search and came up with the following document:
>
> http://support.microsoft.com/kb/214854
>
> I think that it helps to explain part of the issue your seeing.

Thanks, the document helps in understand how JET interprets database
types. But it does not mention anything about defaulting to scale of 6
if scale is not specified for numeric types, so I'm still unsure who
is to blame for this behaviour.

-Mike

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc