I'm writing a .net application that connect to a remote database server hosted on internet to make a sort of replication.
The database source is on my local firebird server, the destination database is on the Firebird server hosted on the internet
This is the query
execute block as DECLARE panum INT; DECLARE nom varchar(50); DECLARE prenom
varchar(50); DECLARE dna date; DECLARE datemodif date; DECLARE VARIABLE VSQL
VARCHAR(500); begin VSQL = 'select panum, nom, prenom, dna, DATEMODIF from
patient where (datemodif is not null) and (datediff (day from current_date
to cast(DATEMODIF as date)) >= 0) ';
FOR execute statement VSQL ON EXTERNAL DATA SOURCE
'127.0.0.1:C:\BDD\D2018.gdb'
AS USER 'sysdba' PASSWORD 'masterkey' INTO :panum, :nom, :prenom, :dna,
:DATEMODIF DO
merge into patient bb using (select :panum panum, :nom nom, :prenom prenom,
:dna dna, :DATEMODIF datemodif from RDB$DATABASE ) n on bb.panum = n.panum
when matched
then update set bb.nom = :nom, bb.prenom = :prenom, bb.dna = :DNA,
bb.DATEMODIF = :DATEMODIF when not matched
then insert(panum, nom, prenom, dna, datemodif) values(:panum, :nom,
:prenom, :dna, :datemodif);
suspend; end;
Both servers run Firebird 3.0.3 on Windows 64 machines.
It fails with this error message :
Error while trying to open file 12515659 : The system cannot find the
path specified. Data source : Firebird::127.0.0.1:C:\BDD\D2018.gdb
I have also tried localhost and my local ip adress as well as server name .
If I flip the direction so my source database is the remote server and the destination is my local server it works.
I know that's a little weird, but I have some constraints for doing it like this.
Best Answer
If I understand correctly from your comments, the execute block is executed on the external server, and it needs to query data from the local server.
The problem with your code - as far as I can tell - is that you seem to think the IP addresses are resolved from the perspective of the process initiating the execution (so 127.0.0.1 would be your local database). It is not, address resolution is from the perspective of the process executing the
execute block(in this case: the Firebird process on the external server, so 127.0.0.1 is that external server).You will need to replace 127.0.0.1 with the IP address of the local server for this to work. This IP address should be public, or at least accessible from the external server.
Be aware though, that if you expose both the local and external server to the public internet, that is a security risk. Consider using a VPN to connect between the local and external server(s).