Skip site navigation (1) Skip section navigation (2)

Wrong scale for numeric data types in MS Access and ADODB

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: (view raw or flat)
Lists: pgsql-odbc

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 (12/27/2009). Here is the
table and data in PostgreSQL 8.4.

  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
('Sodium', 22.98976928),

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

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

    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"),

    Set rs = Nothing

    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!



pgsql-odbc by date

Next:From: Mike ToewsDate: 2010-06-16 02:43:08
Subject: Re: Wrong scale for numeric data types in MS Access and ADODB
Previous:From: Alexandre - Aldeia DigitalDate: 2010-06-14 21:49:59
Subject: Unicode x ANSI speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group