Re: Access 97 DB to Postgres Migration Questions

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.

--
Richard Huxton