dblink postgresql

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
lao
Posts: 71
Joined: Wed 10 Dec 2008 10:56

dblink postgresql

Post by lao » Thu 13 Jun 2013 09:59

Hi,
i have problem to execute this commande with dblink and delphi 2007 (i try with unidac 4.6.12 and 5.0.1)
this request work in pgadmin3.

insert into tablea
select * from
dblink('host=hostname user=username dbname=dbnamepassword=mypassword',
'select * from tablea') As servertablea
(
a_id integer,
a_name varchar(25)
)
where not exists(select a_id from tablea where tablea.a_id=a_id)


regards

lao
Posts: 71
Joined: Wed 10 Dec 2008 10:56

Re: dblink postgresql

Post by lao » Thu 13 Jun 2013 10:30

Hi,
the request is executed without problem but no data inserted.
Regards

DemetrionQ
Devart Team
Posts: 271
Joined: Wed 23 Jan 2013 11:21

Re: dblink postgresql

Post by DemetrionQ » Thu 13 Jun 2013 12:22

Hello.

  UniDAC just sends the SQL query to server without any changes. All data processing operations will be performed on the server side. We couldn't reproduce the problem. In our tests UniDAC sent a query similar to yours to the server - and data was inserted to the table successfully.

Please check the following:
 - the source table is not empty;
 - conditions for selection from the source table are set correctly;
 - you have rights to edit the table.

lao
Posts: 71
Joined: Wed 10 Dec 2008 10:56

Re: dblink postgresql

Post by lao » Thu 13 Jun 2013 12:40

Hi,
thank you for your reply.
Yes everything is ok, and when i execute this command in pgadmin my local table is populated this records from server.
regards,

lao
Posts: 71
Joined: Wed 10 Dec 2008 10:56

Re: dblink postgresql

Post by lao » Thu 13 Jun 2013 12:47

Hi, sorry, everything is working now.
reagrds

DemetrionQ
Devart Team
Posts: 271
Joined: Wed 23 Jan 2013 11:21

Re: dblink postgresql

Post by DemetrionQ » Thu 13 Jun 2013 14:19

If any other questions come up, please contact us.

wusir01
Posts: 6
Joined: Tue 10 Dec 2013 10:29

Re: dblink postgresql

Post by wusir01 » Tue 10 Dec 2013 10:44

Hi,
I've a problem with postgresql dblink. I'm working on database synchronization. I have one master database1 hosted on host1 and a slave database2 hosted on host2. the 2 databases have not the same tables columns (differences between tables data model). on each changes operated on database1 (hoste1) I want to replicate it on a specific column and specific table on database2 (host2) using dblink. can someone help me please ??

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: dblink postgresql

Post by AlexP » Tue 10 Dec 2013 11:34

Hello,

For such synchronization, you can use triggers on the required tables in the master database and insert data in these triggers via dblink into the required tables and fields in the slave database.

wusir01
Posts: 6
Joined: Tue 10 Dec 2013 10:29

Re: dblink postgresql

Post by wusir01 » Tue 10 Dec 2013 11:44

yes how can i make the requet

i try this but not work

select dblink_connect_u('conn104', 'hostaddr=192.168.1.104 port=5432 dbname=db104 user=postgres password=postgres');

select dblink_connect_u('conn105', 'hostaddr=192.168.1.105 port=5432 dbname=db105 user=postgres password=postgres');


select dblink_exec('conn105',
'insert into db105.table1(col1, col2) select * from dblink('conn104', 'select * from db104.table2')
return (id int, colx varchar);');

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: dblink postgresql

Post by AlexP » Tue 10 Dec 2013 14:53

Hello,

Below is a simple sample of a trigger with dblink:

Code: Select all

CREATE or replace FUNCTION dept_ins() RETURNS trigger AS $dept_ins$
    BEGIN
 perform dblink_connect('hostaddr=xxx.xxx.xxx.xxx port=5432 dbname=alexp user=postgres password=postgres');
 perform dblink_exec('insert into dept(deptno, dname, loc) values ('||NEW.deptno||','''||New.dname||''','''||New.loc||''');'); 
 perform dblink_disconnect();
        RETURN NEW;
    END;
$dept_ins$ LANGUAGE plpgsql;

CREATE TRIGGER dept_ins BEFORE INSERT ON dept
    FOR EACH ROW EXECUTE PROCEDURE dept_ins();

wusir01
Posts: 6
Joined: Tue 10 Dec 2013 10:29

Re: dblink postgresql

Post by wusir01 » Tue 10 Dec 2013 15:27

thanks for your reply

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: dblink postgresql

Post by AlexP » Wed 11 Dec 2013 07:50

Hello,

If you have any other questions, feel free to contact us.

wusir01
Posts: 6
Joined: Tue 10 Dec 2013 10:29

Re: dblink postgresql

Post by wusir01 » Wed 11 Dec 2013 09:54

thanks very much, i adapt it to my configuration and it works fine.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: dblink postgresql

Post by AlexP » Wed 11 Dec 2013 10:19

Hello,

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.

sham78
Posts: 1
Joined: Tue 30 Apr 2019 09:33

Re: dblink postgresql

Post by sham78 » Thu 09 May 2019 03:27

Hello..
I have a problem to access ad procedure/function from postgresql. I have 2 databases postgresql. To make both databases communicate each other, i have created a dblink. When i access table using dblink, success. For example access table. I use like this - select * from hr_staffs@pghrdev..But when i want to access a function/procedure using same method, failed.

Post Reply