Обсуждение: 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=myuser;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
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 <mwtoews@gmail.com> 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
> (
> 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=myuser;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
>
On Tue, Jun 15, 2010 at 7:43 PM, Mike Toews <mwtoews@gmail.com> 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
On 15 June 2010 19:50, Richard Broersma <richard.broersma@gmail.com> 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