Re: Using PostgreSQL and Access?

Here is the routine that is needed in the backend for xid to int4
comparisons (for row versionig):
create function int4eq(xid,int4)
returns bool
as ''
language 'internal';

create operator = (
leftarg=xid,
rightarg=int4,
procedure=int4eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

Also here's a little snippet which might help with bools.
For what its worth MS Access bools are int2's and I tend just to use int2's
instead of bools (0 false, anything else true. Use <>False rather than =True
if you do this as true has a definite value of -1).

DROP OPERATOR = (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
CREATE FUNCTION MsAccessBool (bool, int4) RETURNS BOOL AS '
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;

IF $1 IS TRUE THEN
IF $2 <> 0 THEN
RETURN TRUE;
END IF;
ELSE
IF $2 = 0 THEN
RETURN TRUE;
END IF;
END IF;
RETURN FALSE;
END;
' LANGUAGE 'plpgsql';

CREATE OPERATOR = (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBool,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);

Also if you are using Access 97 it can be funny about accepting that things
it can't index can be indexed by the backend. Another possible problem is
that text translates the memo as default (unless changed in the ODBC driver)
and access is a bit funny about what it'll do with memo's (eg IIRC it won't
join on them).

FYI: What happens when Access tries to update is that it uses a where clause
specfying everything in the row if you don't use row versioning. This can
cause problems with float precision, etc.....
Hope something in this lot helps.
- Stuart

> -----Original Message-----
> From: prinsarian(at)zonnet(dot)nl [SMTP:prinsarian(at)zonnet(dot)nl]
> Sent: Thursday, October 25, 2001 9:41 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Using PostgreSQL and Access?
>
> If Access changes / inserts a record it tries to refetch the thing
> after that operation has finished. If it hasn't got a unique ID to try
> to find the record it will give errors like "row deleted" and maybe
> your index-errors.
>
> Try switching on Datasource...OID-options....Show Column and Fake
> index.
>
> arian.
>
>
> On Thu, 25 Oct 2001 00:59:22 +0000 (UTC), meyer(at)mesw(dot)de ("Markus
> Meyer") wrote:
>
> >Since the PostgreSQL (CygWin) mailing list seems to be quite dead, I'm
> >posting this one here.
> >
> >My question is: Has any one successfully used PostgreSQL with Access? I
> have
> >read the Accces-FAQ, but I still have major problems: When I create a
> form
> >and change / add some data in there, I always get errors, f.e. "Cannot
> add
> >duplicate index" (although I did just a minor change), but also other
> error
> >messages, or data conversion errors ("Cannot convert XID to int4"). The
> >error messages change, if I try other settings in ODBC driver, but it
> >doesn't work either. Also I get the error messages about the unique index
> >when I add a new record as described in the ODBC FAQ, but I don't agree
> with
> >the "workaround" by using a timestamp. That one is really weird, and it
> >really should work without.
> >
> >Maybe PostgreSQL (or the ODBC driver) should have a "compatibility" mode
> to
> >work with more forgiving frontends. I don't see, why f.e. a boolean
> column
> >gives an error, if you do something like "boolean_col = 0".
> >
> >Access does work well with other databases over ODBC (okay, SQL Server
> >works, but also MySQL), so why doesn't PostgreSQL?
> >
> >Regards
> >
> >
> >Markus
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
> _______________________
> Arian Prins
> _______________________
> prinsarian
> apetale
> zonnet
> dot
> nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly