Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
Merlin Moncure wrote: On Jan 4, 2008 11:51 AM, Afewtips.com <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
On Jan 4, 2008 11:51 AM, Afewtips.com <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
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) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
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" <[EMAIL PROTECTED]> 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.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Connect to SQL Server via ODBC from Postgresql
On Jan 4, 10:08 am, "Afewtips.com" <[EMAIL PROTECTED]> 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.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
