Geting local sytem path when executing Postgres queries on remote system in delphi

Question

im working on a delphi 7 application with postgres 9 as the database. the application works in two ways, it can connect to the

1. Postgres database on local system
2. Postgres on the remote system

On both local and remote postgres the database will have the same number of tables.

we have module where we create backup of the datbase tables using COPY and write the result to a file on the system. example

query1:='COPY (Select * from mytable where mycolumn in (2011406)) TO 'C:/Documents and Settings/All Users/Documents/drDir/myfile.bck' WITH BINARY'

     Query_dmp_res.SQL.Clear;
     Query_dmp_res.SQL.Add(query1) ;
     Query_dmp_res.ExecSQL;

The path C:/Documents and Settings/All Users/Documents/drDir is created if not existing on the local system.

As the above query is executed on the local system and the C:/Documents and Settings/All Users/Documents/drDir/ exists also This works fine when working with a local.

But the problem is when we have connected to the remote system.The query 'COPY (Select * from mytable where mycolumn in (2011406)) TO 'C:/Documents and Settings/All Users/Documents/drDir/myfile.bck' WITH BINARY' Fails as the path /drDir/ is created on the local system and not on the remote system.

But even if the path 'C:/Documents and Settings/All Users/Documents/drDir/ exists on the remote system, the File /myfile.bck is created on the remote system.

can any one tell me how to

1. Give the path of my system, so the file /myfile.bck is created on my system after the query is executed. This is How to specify my local system path in the query 'COPY (Select * from mytable where mycolumn in (2011406)) TO 'myLocalSystemPath/myfile.bck' WITH BINARY'

2. How to create the File a directory on the remote system for the query execution.

i tried changing the path as 'C:\Documents and Settings\All Users\Documents\drDir\ but i get this error

EDIT i tried the

COPY (Select * from mytable where mycolumn in (2011406)) TO STDOUT WITH BINARY but how to get the result of the query?

Answer 1

1) not possible when a filename is specified (excerpt from the docs):

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server.

The next sentence though gives you some ideas (and that depends on the drivers also):

When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

2) that's a matter of access to the server, check with your administrator... but bear in mind that when you create a folder on the server the user that Postgres runs under should have write access to that folder.