Re: Access 97 DB to Postgres Migration Questions - Mailing list pgsql-general

From Jeff Eckermann
Subject Re: Access 97 DB to Postgres Migration Questions
Date
Msg-id 20030718193717.73773.qmail@web20808.mail.yahoo.com
Whole thread Raw
In response to Re: Access 97 DB to Postgres Migration Questions  (Dennis Gearon <gearond@cvc.net>)
List pgsql-general
--- Dennis Gearon <gearond@cvc.net> wrote:
> Another thing to watch out for is Memo fields. Do a
> search of the archives.

Good point.  If you have the driver option "text as
longvarchar" checked (the default), any fields created
in PostgreSQL using the "text" datatype will be
recognized by Access as Memo fields.  Access will
recognize the PostgreSQL "varchar" type as its own
text type.

> Richard Huxton wrote:
>
> > On Friday 18 Jul 2003 4:50 am, Raymond wrote:
> >
> >>Have an Access 97 database being migrated to
> Postgres 7.3.3. Access 97
> >>clients will query and write to the Postgres
> database across a WAN via
> >>ODBC.
> >>
> >>A few questions:
> >>
> >>1)  How does one pass a record from Access 97 to a
> Postgres function with a
> >>record parameter. Postgres side seems easy enough;
> a table parameter.
> >>Intention is to create functions for insert,
> update and delete operations
> >>that Access 97 would call, simply passing the
> record as an argument. The
> >>function would perform the necessary validation
> and DML.
> >
> >
> > Hmm - think you'll want to unpack the record
> values and pass them in normally.
> >
> >
> >>2)  Is an ODBC type conversion table available for
> Access to Postgres?
> >
> >
> > They match up pretty much as you would expect
> int4=>long integer etc. One
> > thing to watch out for is booleans - come through
> as 0/-1 in Access.
> >
> >
> >>3)  Is an SQL error table available for Postgres;
> would like to return SQL
> >>error-codes and error-text from aforementioned
> functions and some triggers.
> >>No need to reinvent the wheel.
> >
> >
> > I don't believe we have a separate table, but I
> know Tom Lane has been doing a
> > lot of work on the error messages for 7.4
> >
> >
> >>4)  Is it possible to grant privileges on a
> Postgres function?
> >
> >
> > A function can run with the privileges of the user
> calling it (the default) or
> > the user who created it.
> >
> >
> >>5)  Any recommended backup software that can
> archive an on-line Postgres
> >>database similar to the big guys' commercial DB
> products and MySQL?
> >
> >
> > Ahem - pg_dump provides a guaranteed consistent
> backup of a live database.
> > Better than some, I think you'll find.
> >
> >
> >>6)  How does one capture a RAISE EXECPTION to  a
> function return value?
> >
> >
> > Ah - you don't. You're going to have to wait a
> while for this I'm afraid,
> > although you're not alone in wanting the feature.
> >
> >
> >>Lastly any caveats or recommendations from those
> with previous Access 97 /
> >>Postgres experience would be greatly appreciated.
> >
> >
> > 1. Access "passthrough queries" can be
> significantly faster than just querying
> > linked tables to PG.
> > 2. Check your ODBC driver settings if you have
> problems.
> > 3. Make sure you have the latest ODBC driver if
> you have problems.
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

pgsql-general by date:

Previous
From: Jeff Eckermann
Date:
Subject: Re: FATAL 2: open of /var/lib/pgsql/data/pg_clog/0EE3
Next
From: "scott.marlowe"
Date:
Subject: Re: Urgent: 10K or more connections