From: | Mike Toews <mwtoews(at)gmail(dot)com> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Wrong scale for numeric data types in MS Access and ADODB |
Date: | 2010-06-16 02:37:51 |
Message-ID: | AANLkTin13wLqVkcji92D5KOPWVtpJ_MwKrzBjVwrKZCa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Toews | 2010-06-16 02:43:08 | Re: Wrong scale for numeric data types in MS Access and ADODB |
Previous Message | Alexandre - Aldeia Digital | 2010-06-14 21:49:59 | Unicode x ANSI speed |