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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
[hidden email] writes:
> 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.

> 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


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

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

groberge
Very good, thank you for the help!

George R.

On 10/15/2012 02:42 PM, Tom Lane wrote:

> [hidden email] writes:
>> 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.
>
>> 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
www.andforthelamb.org

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



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

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

Andrej Ricnik-Bay
In reply to this post by groberge
On 16 October 2012 06:52,  <[hidden email]> 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


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

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

groberge
On 10/17/2012 06:52 PM, Andrej wrote:

> On 16 October 2012 06:52,  <[hidden email]> 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
>
>
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.



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

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

Andrej Ricnik-Bay
On 18 October 2012 13:57, George Roberge <[hidden email]> wrote:
>> 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 ...

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




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


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

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

groberge
On 10/17/2012 09:42 PM, Andrej wrote:

> On 18 October 2012 13:57, George Roberge <[hidden email]> wrote:
>>> 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 ...
>
>> 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.
>
>
>
>
>> 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....



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice