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

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

Richard Huxton wrote:

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

Afternoon All,

Try the PG Admin database migration utility. We've converted several Access MDB files to Postgres with it with little or no 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)

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

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

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