Beginner's question about ODBC and/or foreign data sources

[g***@andforthelamb.org]

Greetings.

I have installed and used PostgreSQL at a beginner's level (simple
database creation, simple PHP website querying) and would love to use it
at work to complement and/or (hopefully) replace Access as our software
of choice.

Anyhow, I'm trying to find information on how to connect PostgreSQL to
an ODBC data source. I've seen a lot of information on connecting TO
PostgreSQL from other (ODBC) sources, but I can't locate a clear
description of how to access data FROM ODBC sources to query within
PostgreSQL. I have software which offers Windows ODBC drivers, but need
to get at it from PostgreSQL.

I may be entirely misunderstanding the ODBC issue, but it seems that
the PostgreSQL ODBC drivers are for connecting TO PostgreSQL, and not
what I am trying to accomplish.

Do I need to be messing with/foreign data wrappers here?

Thank you for your patience with this silly question!
George R.

[Tom Lane]

Post by g***@andforthelamb.org
Anyhow, I'm trying to find information on how to connect PostgreSQL to
an ODBC data source. I've seen a lot of information on connecting TO
PostgreSQL from other (ODBC) sources, but I can't locate a clear
description of how to access data FROM ODBC sources to query within
PostgreSQL. I have software which offers Windows ODBC drivers, but need
to get at it from PostgreSQL.
I may be entirely misunderstanding the ODBC issue, but it seems that
the PostgreSQL ODBC drivers are for connecting TO PostgreSQL, and not
what I am trying to accomplish.

Yup.

Post by g***@andforthelamb.org
Do I need to be messing with/foreign data wrappers here?

Yup, what you need is odbc_fdw:
http://www.postgresonline.com/journal/archives/246-ODBC-Foreign-Data-wrapper-odbc_fdw-on-windows.html

Be warned that FDWs currently support only reads not writes, and in
general are more development-stage than hardened production code.
I'm not sure what state odbc_fdw in particular is in. You may well
find that this doesn't work reliably enough to be worth your trouble.

regards, tom lane

[George Roberge]

Very good, thank you for the help!

George R.

Post by Tom Lane

Post by g***@andforthelamb.org
Anyhow, I'm trying to find information on how to connect PostgreSQL to
an ODBC data source. I've seen a lot of information on connecting TO
PostgreSQL from other (ODBC) sources, but I can't locate a clear
description of how to access data FROM ODBC sources to query within
PostgreSQL. I have software which offers Windows ODBC drivers, but need
to get at it from PostgreSQL.
I may be entirely misunderstanding the ODBC issue, but it seems that
the PostgreSQL ODBC drivers are for connecting TO PostgreSQL, and not
what I am trying to accomplish.

Yup.

Post by g***@andforthelamb.org
Do I need to be messing with/foreign data wrappers here?

http://www.postgresonline.com/journal/archives/246-ODBC-Foreign-Data-wrapper-odbc_fdw-on-windows.html
Be warned that FDWs currently support only reads not writes, and in
general are more development-stage than hardened production code.
I'm not sure what state odbc_fdw in particular is in. You may well
find that this doesn't work reliably enough to be worth your trouble.
regards, tom lane

--
George Roberge
www.andforthelamb.org

-- If Bill Gates had a nickel for every time Windows crashed... oh, wait....

[Andrej]

On 16 October 2012 06:52, <***@andforthelamb.org> wrote:
Hi George,

I have installed and used PostgreSQL at a beginner's level (simple database
creation, simple PHP website querying) and would love to use it at work to
complement and/or (hopefully) replace Access as our software of choice.

Maybe I completely misunderstood this initial paragraph of your mail,
but how does
using postgres to look at other windows data sources comprise a replacement
for access?

May I ask what those other odbc data sources are, and how you utilise
them with access?


Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml

[George Roberge]

Post by Andrej
Hi George,

I have installed and used PostgreSQL at a beginner's level (simple database
creation, simple PHP website querying) and would love to use it at work to
complement and/or (hopefully) replace Access as our software of choice.

Maybe I completely misunderstood this initial paragraph of your mail,
but how does
using postgres to look at other windows data sources comprise a replacement
for access?
May I ask what those other odbc data sources are, and how you utilise
them with access?
Cheers,
Andrej

Oh sure.

That was just a very broad (and useless as far as my actual questions
go) statement legitimizing my question's existence on the planet!

It is a long-term goal to break PostgreSQL into the workplace, and
eventually use it enough that perhaps Access will disappear. It's a
battle against "The Machine" of course, but hey....

The data source is a Syspro ERP ODBC source. Access naturally readily
connects to this source, and we supplement the ERP with a few Access
databases when we need to use its data in ways that the ERP doesn't handle.

George R.

[Andrej]

Post by George Roberge

Post by Andrej
Maybe I completely misunderstood this initial paragraph of your mail,
but how does using postgres to look at other windows data sources comprise a
replacement for access?

Oh sure.
That was just a very broad (and useless as far as my actual questions go)
statement legitimizing my question's existence on the planet!

LOL ...

Post by George Roberge
It is a long-term goal to break PostgreSQL into the workplace, and
eventually use it enough that perhaps Access will disappear. It's a battle
against "The Machine" of course, but hey....
The data source is a Syspro ERP ODBC source. Access naturally readily
connects to this source, and we supplement the ERP with a few Access
databases when we need to use its data in ways that the ERP doesn't handle.

So you're primarily using access as a front-end/GUI to your ERP system, with
a little extra ooomph? If that's the case postgres probably isn't the cure for
your access ailments :}

Most likely you'll need a custom made GUI app, and possibly use postgres
for the "and then some" bits from the same app.

Post by George Roberge
George R.

Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml

[George Roberge]

Post by Andrej

Post by George Roberge

Post by Andrej
Maybe I completely misunderstood this initial paragraph of your mail,
but how does using postgres to look at other windows data sources comprise a
replacement for access?

Oh sure.
That was just a very broad (and useless as far as my actual questions go)
statement legitimizing my question's existence on the planet!

LOL ...

Post by George Roberge
It is a long-term goal to break PostgreSQL into the workplace, and
eventually use it enough that perhaps Access will disappear. It's a battle
against "The Machine" of course, but hey....
The data source is a Syspro ERP ODBC source. Access naturally readily
connects to this source, and we supplement the ERP with a few Access
databases when we need to use its data in ways that the ERP doesn't handle.

So you're primarily using access as a front-end/GUI to your ERP system, with
a little extra ooomph? If that's the case postgres probably isn't the cure for
your access ailments :}
Most likely you'll need a custom made GUI app, and possibly use postgres
for the "and then some" bits from the same app.

Post by George Roberge
George R.

Cheers,
Andrej

We're using the data in the ODBC linked tables, and writing queries,
forms, labels, and reports from the raw data, in Access databases. The
ERP itself is a monolithic package with plenty of screens, but some
people need only certain information (labels, for example) on the shop
floor. Other uses are more easily-designed reports and various sales
and customer service figures.

Access has served the company well as far as a RAD tool, but has been
used a little sloppily. It's time to overhaul some of the systems, and
I'm an Open Source kind of guy, so I lean more towards those tools.

I've been thinking about the possibility of using a simple web browser
as a front end, but that's a story for another day.

Thank you for your posts!
George R.
--
George Roberge
www.andforthelamb.org

-- If Bill Gates had a nickel for every time Windows crashed... oh, wait....