delphi - Opening a Firebird database file on a network share

Question

I thought converting a mapped drive letter to a UNC path would be enough to be able to open a .GDB file, but alas:

function ConvertToUNCPath(AMappedDrive: string) : string;
var
   lRemoteString : array[0..255] of char;
   lpRemote      : PChar;
   lStringLen    : Cardinal;
begin
   lpRemote := @lRemoteString;
   lStringLen := 255;
   If WNetGetConnection(Pchar(ExtractFileDrive(AMappedDrive)) ,
                        lpRemote,
                        lStringLen) = NO_ERROR Then
      Result := lRemoteString
   else
      Result := ''; // No mapping found
end;

function TDataModuleData.OpenGDBDatabase(AGDBName: string) : Boolean;
var
   lDlgLogin: TFrmLogin;
   p        : Integer;
   lUNC,
   lErrMsg  : String;
begin
   Result := False;

   with FDConnection do   // TFDConnection
   begin
      Close;
      TxOptions.Isolation := xiDirtyRead;

      p := Pos(':',AGDBName);
      if p = 2 then
      begin
         lUNC := ConvertToUNCPath(Copy(AGDBName,1,2));
         if lUNC <> '' then
         begin
            lUNC := Copy(lUNC,3);
            p := pos('',lUNC);
            AGDBName := Copy(lUNC,p) + Copy(AGDBName,3);
            lUNC := copy(lUNC,1,p-1);
         end;
      end;

      DriverName := S_FD_IBId;
      Params.Database := AGDBName;
      if lUNC <> '' then
         Params.Add('Server=' + lUNC)
      else   
         Params.Add('Server=localhost');  // Not strictly necessary

      Params.UserName := 'SYSDBA';
      Params.Password := 'masterkey';

      try
         Open;
         Result := Connected;
      except
         on E:Exception do
         begin
            lErrMsg := LowerCase(E.Message);
         end;
      end;
   end;
end;

Depending on how I parse the ConvertToUNCPath result I get different error messages:

[firedac][phys][ib]unavailable database
[firedac][phys][ib]i/o error during "createfile (open)" operation for file "persoonlijkjanklanten.gdb"'#$D#$A'error while trying to open file'#$D#$A'the system cannot find the path specified.

The part of the code using ConvertToUNCPath succesfully converts e.g. P:JanKLANTEN.GDB to \tt2012serverpersoonlijkJanKLANTEN.GDB.

How can I open a GDB file when the path points to a mapped drive letter?

Added: I tried these hardcoded variations, they all fail:

// lUNC := '\2012server';  // Unable to complete network request to host
lUNC := 'tt2012server';
//AGDBName := '\tt2012serverpersoonlijkjanklanten.gdb';
//AGDBName := 'tt2012serverpersoonlijkjanklanten.gdb';
//AGDBName := 'persoonlijkjanklanten.gdb';
//AGDBName := 'persoonlijkjanklanten.gdb';
//AGDBName := 'janklanten.gdb';
//AGDBName := 'janklanten.gdb';
//AGDBName := 'p:janklanten.gdb'; (original input)

(P: maps to \tt2012serverpersoonlijk)

Added:

Sorry, I was not clear in my initial text: this is not about connecting to a database on a remote server per se. I just want my local 'DB inspection' tool to be able to open a GDB file if someone places it in my network share for inspection (instead of having to copy it to local disk first).
To only intention of using WNetGetConnection was to resolve drive letter to UNC path (some I code I found on the web).

Answer 1

1. Firebird explicitly denies attempts to open database files on non-local disks

Firebird is database server, and as such it focuses on performance and reliability.

http://www.firebirdfaq.org/faq46/

Performance means lots of data is cached, both cached for reading and cached for writing.

Reliability means Firebird has to gain worthy warrants from OS that:

a. no other process would tinker with the database file while the server has some data from it cached for reading.

b. at any moment in time the server might wish to write any data to the file from its cache and it is warranted that that data - at any moment in time - ends persistently written to the persistent media.

Network-connected disks nullify both warranties and consequently Firebird Server refuses to trust them.

You may hack Firebird configuration or source files on your own discretion to remove this safety check and open network-shared files, if you really need this more than safety and speed.

But proper solution would be installing Firebird server on the machine whose disks do carry the database file.

2. Connection String is not a database file name

AGDBName := '\tt2012serverpersoonlijkjanklanten.gdb'

This does NOT mean "local Firebird server should connect to tt2012server server using LOCAL_SYSTEM credentials and read the database file from persoonlijk shared resource", as you probably intended it to mean.

http://www.firebirdfaq.org/faq260/

If anything, Windows LOCAL_SYSTEM user is explicitly barred from most network operations to contain intruders and viruses. Even if you hack Firebird into opening network files, most probably Windows would prohibit this access anyway, unless you would setup your Windows to run Firebird Server service with some user account other than the default LOCAL_SYSTEM.

Anyway, what \tt2012serverpersoonlijkjanklanten.gdb Connection String actually means is that you request your application to connect to tt2012server using WNET (aka Microsoft Named Pipes) protocol and find Firebird server running on that server and communicating by WNET protocol, as opposed to TCP/IP protocol.

Judging by the error you quote - lUNC := '\2012server'; // Unable to complete network request to host - the said tt2012server computer perhaps does not have a Firebird Server running and accepting Named Pipes connections.

The WNET protocol is considered obsoleted and would most probably be removed from the future Firebird Server versions. As of now it is working, but few people use it, thus little up to date experience exists in that area. It is suggested you would use TCP/IP protocol by default to connect your application to the Firebird Server running on the tt2012server machine, not WNET protocol.

PS. This question has duplicates:

• Connecting to Firebird database from Windows local network
• ibase_connect: remote computer host and shared db file from windows

PPS. Firebird is a multi-generation database engine.

Consequently, there is no "dirty read" transactions possible in Interbase/Yaffil/Firebird family.

TxOptions.Isolation := xiDirtyRead; - this line would not work. Most probably it would silently change the transaction class to "READ COMMITTED", less probably it would give an explicit error.