Thread: Connect to SQL Server via ODBC from Postgresql

Connect to SQL Server via ODBC from Postgresql

From
"Afewtips.com"
Date:
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

Re: Connect to SQL Server via ODBC from Postgresql

From
"Afewtips.com"
Date:
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.

Re: Connect to SQL Server via ODBC from Postgresql

From
Sim Zacks
Date:
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.

Re: Connect to SQL Server via ODBC from Postgresql

From
Ow Mun Heng
Date:
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.


Re: Connect to SQL Server via ODBC from Postgresql

From
"Joshua D. Drake"
Date:
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
>


Re: Connect to SQL Server via ODBC from Postgresql

From
Ow Mun Heng
Date:
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)

Re: Connect to SQL Server via ODBC from Postgresql

From
"Joshua D. Drake"
Date:
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


Re: Connect to SQL Server via ODBC from Postgresql

From
"Merlin Moncure"
Date:
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

Re: Connect to SQL Server via ODBC from Postgresql

From
"Joshua D. Drake"
Date:
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