Обсуждение: Beginner's question about ODBC and/or foreign data sources
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.
groberge@andforthelamb.org 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
Very good, thank you for the help! George R. On 10/15/2012 02:42 PM, Tom Lane wrote: > groberge@andforthelamb.org 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....
On 16 October 2012 06:52, <groberge@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
On 10/17/2012 06:52 PM, Andrej wrote: > On 16 October 2012 06:52, <groberge@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 > > 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.
On 18 October 2012 13:57, George Roberge <groberge@andforthelamb.org> 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
On 10/17/2012 09:42 PM, Andrej wrote: > On 18 October 2012 13:57, George Roberge <groberge@andforthelamb.org> 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....