Обсуждение: Access 97 DB to Postgres Migration Questions
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. 2) Is an ODBC type conversion table available for Access to Postgres? 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. 4) Is it possible to grant privileges on a Postgres function? 5) Any recommended backup software that can archive an on-line Postgres database similar to the big guys' commercial DB products and MySQL? 6) How does one capture a RAISE EXECPTION to a function return value? Lastly any caveats or recommendations from those with previous Access 97 / Postgres experience would be greatly appreciated. Thanks in advance Raymond
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
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. >
--- Richard Huxton <dev@archonet.com> wrote: > On Friday 18 Jul 2003 4:50 am, Raymond wrote: > > 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. > > Richard Huxton I'm dealing with this specific issue for the first time. How do you suggest dealing with the boolean incompatibility issue? I can't seem to make checkboxes work; but I want to keep the pgsql boolean datatype. Is there a way to change the value input by checkboxes? Or do I have to go with the text input of T, F, Y or N? Thanks, Andrew Gould
On Friday 18 July 2003 18:11, Andrew Gould wrote: > > 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. > > > > Richard Huxton > > I'm dealing with this specific issue for the first > time. How do you suggest dealing with the boolean > incompatibility issue? I can't seem to make > checkboxes work; but I want to keep the pgsql boolean > datatype. Is there a way to change the value input by > checkboxes? Or do I have to go with the text input of > T, F, Y or N? I could have sworn they did work in Access, but I'm not in Windows at the moment. The issue I was talking about is that if you pass raw SQL to the database you'll want to use "T" whereas in recordsets etc you can carry on using "-1" etc. If I get back into Windows before I knock off tonight I'll have a quick check. -- Richard Huxton Archonet Ltd
Afternoon All,
Try the PG Admin database migration utility. We've converted several Access MDB files to Postgres with it with little
orno problems.
Hope this helps,
Corey
-----Original Message-----
From: Andrew Gould [SMTP:andrewgould@yahoo.com]
Sent: Friday, July 18, 2003 10:12 AM
To: Richard Huxton; Raymond; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Access 97 DB to Postgres Migration Questions
--- Richard Huxton <dev@archonet.com> wrote:
> On Friday 18 Jul 2003 4:50 am, Raymond wrote:
> > 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.
>
> Richard Huxton
I'm dealing with this specific issue for the first
time. How do you suggest dealing with the boolean
incompatibility issue? I can't seem to make
checkboxes work; but I want to keep the pgsql boolean
datatype. Is there a way to change the value input by
checkboxes? Or do I have to go with the text input of
T, F, Y or N?
Thanks,
Andrew Gould
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
There is a switch in the ODBC configuration under OPTIONS | DATASOURCE | PAGE 2 to use -1 as true. I think that will
makeit work, although I have not tried it.
>>> Richard Huxton <dev@archonet.com> 07/18/03 10:41AM >>>
On Friday 18 July 2003 18:11, Andrew Gould wrote:
> > 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.
> >
> > Richard Huxton
>
> I'm dealing with this specific issue for the first
> time. How do you suggest dealing with the boolean
> incompatibility issue? I can't seem to make
> checkboxes work; but I want to keep the pgsql boolean
> datatype. Is there a way to change the value input by
> checkboxes? Or do I have to go with the text input of
> T, F, Y or N?
I could have sworn they did work in Access, but I'm not in Windows at the
moment.
The issue I was talking about is that if you pass raw SQL to the database
you'll want to use "T" whereas in recordsets etc you can carry on using "-1"
etc.
If I get back into Windows before I knock off tonight I'll have a quick check.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
I had the same problem with Access97 and checkboxes. My solution was: 1. Uncheck the "bools as char" driver option 2. Create a function in PostgreSQL to allow for "-1" to be accepted as "true". The code was posted by someone, called (I think) "booleqint4". If you want to try that, then search the archives for that name, or email me and I will send you the code. --- Ian Harding <ianh@tpchd.org> wrote: > There is a switch in the ODBC configuration under > OPTIONS | DATASOURCE | PAGE 2 to use -1 as true. I > think that will make it work, although I have not > tried it. > > >>> Richard Huxton <dev@archonet.com> 07/18/03 > 10:41AM >>> > On Friday 18 July 2003 18:11, Andrew Gould wrote: > > > 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. > > > > > > Richard Huxton > > > > I'm dealing with this specific issue for the first > > time. How do you suggest dealing with the boolean > > incompatibility issue? I can't seem to make > > checkboxes work; but I want to keep the pgsql > boolean > > datatype. Is there a way to change the value input > by > > checkboxes? Or do I have to go with the text input > of > > T, F, Y or N? > > I could have sworn they did work in Access, but I'm > not in Windows at the > moment. > > The issue I was talking about is that if you pass > raw SQL to the database > you'll want to use "T" whereas in recordsets etc you > can carry on using "-1" > etc. > > If I get back into Windows before I knock off > tonight I'll have a quick check. > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > > > ---------------------------(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
--- 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
--- Ian Harding <ianh@tpchd.org> wrote: > There is a switch in the ODBC configuration under > OPTIONS | DATASOURCE | PAGE 2 to use -1 as true. I > think that will make it work, although I have not > tried it. I unchecked "Bools as Char" and checked "True as -1" in psqlodbc 7.03.01.00. I've transferred the data from Access and added some dummy entries. Brief visual scans from both Access and psql indicate that it's working correctly. (Woohoo! HIPAA compliance, here we come!) Thanks much, Andrew > >>> Richard Huxton <dev@archonet.com> 07/18/03 > 10:41AM >>> > On Friday 18 July 2003 18:11, Andrew Gould wrote: > > > 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. > > > > > > Richard Huxton > > > > I'm dealing with this specific issue for the first > > time. How do you suggest dealing with the boolean > > incompatibility issue? I can't seem to make > > checkboxes work; but I want to keep the pgsql > boolean > > datatype. Is there a way to change the value input > by > > checkboxes? Or do I have to go with the text input > of > > T, F, Y or N? > > I could have sworn they did work in Access, but I'm > not in Windows at the > moment. > > The issue I was talking about is that if you pass > raw SQL to the database > you'll want to use "T" whereas in recordsets etc you > can carry on using "-1" > etc. > > If I get back into Windows before I knock off > tonight I'll have a quick check. > > -- > Richard Huxton > Archonet Ltd