Re: Access 97 DB to Postgres Migration Questions

Another thing to watch out for is Memo fields. Do a search of the archives.

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.
>