Thread: Connect to SQL Server via ODBC from Postgresql
Hi, I am new to this product and don't quite know what can and cannot be done. I need to submit queries to a SQL Server database and create a table in Postgresql with the results from SQL Server. Normally I would do this with an ODBC driver into SQL Server, but I don't see any references to doing that with Postgresql, only connect to the Postgresql table from another database with ODBC. I do read mentions about dblink connections, but how to install it looks unclear and complicated. (I didn't install the PostgreSQL source and it seems that it is needed to install dblink. Does dblink have a standard application install?) I am running 8.2 - any help would be very appreciated! Thanks
On Jan 4, 10:08 am, "Afewtips.com" <afewt...@gmail.com> wrote:
> Hi,
> I am new to this product and don't quite know what can and cannot be
> done.
>
> I need to submit queries to a SQL Server database and create a table
> in Postgresql with the results from SQL Server.
>
> Normally I would do this with an ODBC driver into SQL Server, but I
> don't see any references to doing that with Postgresql, only connect
> to the Postgresql table from another database with ODBC.
>
> I do read mentions about dblink connections, but how to install it
> looks unclear and complicated.
>
> (I didn't install the PostgreSQL source and it seems that it is needed
> to install dblink. Does dblink have a standard application install?)
>
> I am running 8.2 - any help would be very appreciated!
>
> Thanks
I think I may have figured it out - so this may be useful to someone
as well.
I installed the enterprise server and it had everything installed and
the syntax that I am using and works is:
select * from dblink('hostaddr=000.00.00.00 port=1433 dbname=db
user=username password=password',
'select fieldname from ext_database')
as t1(fieldname text) ;
The objective is to connect and submit a query from Postgresql into a
SQL Server database and return the results.
And from what I read, I can create a view from the above string and
connect more easily with the view.
Another way of doing this, without dblink, is using an unsecured language
(plpython, for example) is to connect to the sql server using odbc and then
putting the data into your postgresql.
Sim
Afewtips.com wrote:
> On Jan 4, 10:08 am, "Afewtips.com" <afewt...@gmail.com> wrote:
>> Hi,
>> I am new to this product and don't quite know what can and cannot be
>> done.
>>
>> I need to submit queries to a SQL Server database and create a table
>> in Postgresql with the results from SQL Server.
>>
>> Normally I would do this with an ODBC driver into SQL Server, but I
>> don't see any references to doing that with Postgresql, only connect
>> to the Postgresql table from another database with ODBC.
>>
>> I do read mentions about dblink connections, but how to install it
>> looks unclear and complicated.
>>
>> (I didn't install the PostgreSQL source and it seems that it is needed
>> to install dblink. Does dblink have a standard application install?)
>>
>> I am running 8.2 - any help would be very appreciated!
>>
>> Thanks
>
> I think I may have figured it out - so this may be useful to someone
> as well.
>
> I installed the enterprise server and it had everything installed and
> the syntax that I am using and works is:
>
> select * from dblink('hostaddr=000.00.00.00 port=1433 dbname=db
> user=username password=password',
> 'select fieldname from ext_database')
> as t1(fieldname text) ;
>
> The objective is to connect and submit a query from Postgresql into a
> SQL Server database and return the results.
>
> And from what I read, I can create a view from the above string and
> connect more easily with the view.
On Wed, 2008-01-09 at 08:41 +0200, Sim Zacks wrote: > Another way of doing this, without dblink, is using an unsecured language > (plpython, for example) is to connect to the sql server using odbc and then > putting the data into your postgresql. I use perl DBI to connect to both PG and MSSQL.
Ow Mun Heng wrote: > On Wed, 2008-01-09 at 08:41 +0200, Sim Zacks wrote: >> Another way of doing this, without dblink, is using an unsecured language >> (plpython, for example) is to connect to the sql server using odbc and then >> putting the data into your postgresql. > > I use perl DBI to connect to both PG and MSSQL. > Perl or Python both would service this just fine or PHP (egads). Sincerely, Joshua D. Drake > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
On Tue, 2008-01-08 at 23:16 -0800, Joshua D. Drake wrote: > Ow Mun Heng wrote: > > On Wed, 2008-01-09 at 08:41 +0200, Sim Zacks wrote: > >> Another way of doing this, without dblink, is using an unsecured language > >> (plpython, for example) is to connect to the sql server using odbc and then > >> putting the data into your postgresql. > > > > I use perl DBI to connect to both PG and MSSQL. > > > > Perl or Python both would service this just fine or PHP (egads). hehe.. I read a presentation from someone (I forgot who) who detailed how to use PHP as a procedural language for PG. That was fun (to read only)
Ow Mun Heng wrote: > On Tue, 2008-01-08 at 23:16 -0800, Joshua D. Drake wrote: >> Ow Mun Heng wrote: >>> On Wed, 2008-01-09 at 08:41 +0200, Sim Zacks wrote: >>>> Another way of doing this, without dblink, is using an unsecured language >>>> (plpython, for example) is to connect to the sql server using odbc and then >>>> putting the data into your postgresql. >>> I use perl DBI to connect to both PG and MSSQL. >>> >> Perl or Python both would service this just fine or PHP (egads). > > > hehe.. I read a presentation from someone (I forgot who) who detailed > how to use PHP as a procedural language for PG. Likely Robert Treat. > > That was fun (to read only) > Hey... it has come along way :) http://projects.commandprompt.com/public/plphp Joshua D. Drake
On Jan 4, 2008 11:51 AM, Afewtips.com <afewtips@gmail.com> wrote: > > I do read mentions about dblink connections, but how to install it > > looks unclear and complicated. > > small clarification here. dblink is for connecting two postgresql databases together. It is, IMO, neither unclear nor complicated :-). dbilink, (note the 'I'), which aiui uses perl dbi in a dblink style interface...never used it personally. for moving data in and out of sql server and postgresql, you have a number of options, odbc, dbi, php, etc etc. in the end, you have to query a, process the result and send the answer to b. csv format is great for data transfers in and out of postgres. for example, you can pull data out of something in csv, load it into a postgresql scratch table with the copy statement, and run queries there to get it into the final destination table. merlin
Merlin Moncure wrote: > On Jan 4, 2008 11:51 AM, Afewtips.com <afewtips@gmail.com> wrote: >>> I do read mentions about dblink connections, but how to install it >>> looks unclear and complicated. >>> > > small clarification here. dblink is for connecting two postgresql > databases together. It is, IMO, neither unclear nor complicated :-). > dbilink, (note the 'I'), which aiui uses perl dbi in a dblink style > interface...never used it personally. > > for moving data in and out of sql server and postgresql, you have a > number of options, odbc, dbi, php, etc etc. in the end, you have to > query a, process the result and send the answer to b. There is a PostgreSQL project explicitly for this type of thing... dbi-link: http://pgfoundry.org/projects/dbi-link/ Sincerely, Joshua D. Drake