Thread: Extra Opts = Ox2 (Fake MS SQL Server so that MS Access recognizes PostgresSQL's serial type as AutoNumber type)

Hey Gang,

We've been working hard here trying to get our rather large MS Access
application to work with PG as its backend. At a gross level, that means
keeping our existing forms and reports while instead pointing them to
our new PG backend using the psqlODBC driver. We have learned a
tremendous amount in the process -- gaining a better understanding of
architecture and idiosyncracy alike.

Right now, we're snagged on what seems to be a bug lying somewhere in
the ether where DAO, ODBC and psqlODBC meet. It centers around the
ability to insert into a table with a numeric ID field of type serial.
There is an Extra Opts parameter, 0x2, that one can set in the DSN to
get Access to map fields of type serial to its own native type of
"Autonumber". With this in place, one is able to append a new row to an
open record set that is bound to that table. This can be done in Access
with either the DAO Recordset AddNew method or by appending the record
in the Datasheet view of the linked table in Access. The main trick here
is ODBC's ability to ascertain the last sequence value generated by the
insert. However, we're finding this functionality is failing when the
table contains a field of PG type text(mapped to Access type memo).

To demonstrate, we have two tables, note_text and note_varchar. The note
field in the note_text table is mapped in Access as type memo. Snippets
from the psqlODBC COMM log are provided below in correct chronological
order. They were generated by three user actions in the Access frontend:

Insert #1) Creating a new record in the note_text table in the Datasheet
view.
Insert #2) Creating a new record in the note_varchar table in the
Datasheet view.
Insert #3) Creating a new record in the note_text table in the Datasheet
view.

If you look carefully at the log snippets, you will see the ODBC layer
fail to identify the last sequence value for insert #1. In fact, it
simply issues "SELECT 0" and, subsequently, you will see a bunch of
"#DELETED" fields in the Datasheet view after the insert. Sometimes it
shoves the values that were just entered and creates a compound where
clause. The insert into the note_varchar table(#2) works well(as we have
found for all our tables that do not have a field of type text).
Interestingly, you will find that in attempt #3(2nd for the note_text
table), the ODBC layer will attempt to get the currval from the last
sequence referenced -- which is incorrect.

If any of you can provide any insight, advice or solutions, we'd be
incredibly grateful! Thanks in advance for your mental energy. We hope
we haven't been too verbose, but it's a complicated one to explain.
Details follow...

Garner Chung

PS. We have tried various combinations of DSN/connection settings
including playing with the Unknown Sizes, Max Varchar and Text as
LongVarChar options. However, I will leave those details out as we'd
appreciate a fresh perspective.

--------- TABLES ---------

CREATE TABLE note_text
(
  noteid serial NOT NULL,
  note text,
  dateentered timestamp DEFAULT now(),
  CONSTRAINT note_text_pkey PRIMARY KEY (noteid)
)
WITHOUT OIDS;

CREATE TABLE note_varchar
(
  noteid serial NOT NULL,
  note varchar(254),
  dateentered timestamp DEFAULT now(),
  CONSTRAINT note_varchar_pkey PRIMARY KEY (noteid)
)
WITHOUT OIDS;

--------- COMMLOG ---------

Insert #1)
conn=9553bc8, query='INSERT INTO  "public"."note_text"  ("note") VALUES
(E'asdasd')'
conn=9553bc8, query='SELECT 0'
    [ fetched 1 rows ]
conn=9553bc8, query='SELECT "noteid","note","dateentered","xmin"  FROM
"public"."note_text"  WHERE "noteid" IS NULL'
    [ fetched 0 rows ]
conn=9553bc8, query='COMMIT'
conn=9553bc8, query='SELECT "noteid","note","dateentered","xmin"  FROM
"public"."note_text"  WHERE "noteid" = 0'
    [ fetched 0 rows ]

Insert #2)
conn=9553bc8, query='INSERT INTO  "public"."note_varchar"  ("note")
VALUES (E'asdasdasd')'
conn=9553bc8, query='SELECT
currval('note_varchar_noteid_seq'::regclass)'
    [ fetched 1 rows ]
conn=9553bc8, query='SELECT "noteid","note","dateentered","xmin"  FROM
"public"."note_varchar"  WHERE "noteid" IS NULL'
    [ fetched 0 rows ]
conn=9553bc8, query='COMMIT'
conn=9553bc8, query='SELECT "noteid","note","dateentered","xmin"  FROM
"public"."note_varchar"  WHERE "noteid" = 5'
    [ fetched 1 rows ]

Insert #3)
conn=9553bc8, query='INSERT INTO  "public"."note_text"  ("note") VALUES
(E'asdasdasd')'
conn=9553bc8, query='SELECT
currval('note_varchar_noteid_seq'::regclass)'
    [ fetched 1 rows ]
conn=9553bc8, query='SELECT "noteid","note","dateentered","xmin"  FROM
"public"."note_text"  WHERE "noteid" IS NULL'
    [ fetched 0 rows ]
conn=9553bc8, query='COMMIT'
conn=9553bc8, query='SELECT "noteid","note","dateentered","xmin"  FROM
"public"."note_text"  WHERE "noteid" = 5'
    [ fetched 0 rows ]

garner

Garner Chung wrote:
> Hey Gang,
>
> We've been working hard here trying to get our rather large MS Access
> application to work with PG as its backend. At a gross level, that means
> keeping our existing forms and reports while instead pointing them to
> our new PG backend using the psqlODBC driver. We have learned a
> tremendous amount in the process -- gaining a better understanding of
> architecture and idiosyncracy alike.
>
> Right now, we're snagged on what seems to be a bug lying somewhere in
> the ether where DAO, ODBC and psqlODBC meet. It centers around the
> ability to insert into a table with a numeric ID field of type serial.
> There is an Extra Opts parameter, 0x2, that one can set in the DSN to
> get Access to map fields of type serial to its own native type of
> "Autonumber". With this in place, one is able to append a new row to an
> open record set that is bound to that table. This can be done in Access
> with either the DAO Recordset AddNew method or by appending the record
> in the Datasheet view of the linked table in Access. The main trick here
> is ODBC's ability to ascertain the last sequence value generated by the
> insert. However, we're finding this functionality is failing when the
> table contains a field of PG type text(mapped to Access type memo).

Please try the snapshot dll at
   http://www.geocities.jp/inocchichichi/psqlodbc/index.html
.

regards,
Hiroshi Inoue