Firebird Embeded versus SQLite considerations?

Hi Jon. What do you mean with limitations in firebird?

Yeah, I would say the other way around. Although there is a bit of fiddling with firebird to get it to go, vs just choosing the driver with SQLite. Not a lot of fiddling, but it’s not as easy.
Firebird also has the option to expand to true DBMS with little effort, which is worth something.

Hi Dirk,

Talking specifically about the embedded version (not client-server)…the help says:

Client access can be only via the local (XNET) protocol, i.e. NOT a TCP/IP local loopback connection string that includes the server name “localhost” or the IP address 127.0.0.1. The embedded server supports only the local connect to an absolute database file path without a server name.

The client program gets exclusive access to the database file after successful connect. If another Firebird server already has a client attached to the database, the client program will be denied access. This is intentional.

Compare that to SQLite, where the client program does not get exclusive access to the file, so you can have multiple clients happily retrieving information from the same SQLite database concurrently. Changing data is another story.

Jon

1 Like

Hi Jon,

By default, Firebird 3 embedded will indeed require exclusive access to the database. This can be changed by making sure there is a firebird.conf in the same location as your fbclient.dll used by your application, and setting the ServerMode setting to SuperClassic or ThreadedShared

So what i have is:

 if ~exists(Clip(glo:uncDatapath)&'\fbclient.dll') 
    stop(' Firebird file fbclient.dll is not found')
    HALT(0,'fbclient.dll is not found')
elsif  ~exists(Clip( glo:uncDatapath)&'\firebird.conf') 
...........

This also prevents a problem if a firebird server is installed for another program on the same machine because first it will look for gds32.dll in c:\windows\system32

Hi Dirk - Can you show what the conf file looks like?

Thank you

Hi Jeff,

I only commented the last sentence in the conf file
#ServerMode = Super
and added
ServerMode = SuperClassic

Using fbclient.dll version 3.0x

1 Like

Two more settings i have are

a: to prevent looking for gds32.dll in c:\windows\system32
LOC:ODBCDriver=clip(LOC:ODBCDriver)&‘;CLIENT=fbclient.dll’

b: if firebird odbc is not found on the machine

 loc:longpath=makeunc(getreg(reg_local_machine,'SOFTWARE\ODBC\ODBCINST.INI\Firebird/InterBase(r) driver','Driver'))     
  if ~exists(clip(loc:longpath)) 
    CASE MESSAGE('The Firebird ODBC driver is not found do you want to install it now?','Error',ICON:Question,BUTTON:Yes+BUTTON:No,BUTTON:No,1)
    OF BUTTON:YES
        if exists(clip(glo:uncInstallatPath)&'\Firebird_ODBC_2.0.5.156_Win32.exe')
           run(clip(glo:uncInstallatiepad)&'\Firebird_ODBC_2.0.5.156_Win32.exe /VERYSILENT',1) 
        else
          message('Copy: https://firebirdsql.org/en/odbc-driver and surf to|Download 32 bits Windows ODBC version 2.05|(Firebird_ODBC_2.0.5.156_Win32.exe) and install it','Message',,,,MSGMODE:CANCOPY) 
          HALT(0,'Firebird ODBC not correct installed')
          ! HALT should not be used in multi-DLL application?
       end        
    END !case
  end !exists

We are in the process of migrating away from TPS to SQLite3.
To keep it short:

  • Clarion SQLite3 driver had a bunch of issues that made the migration a no-go in a multi-user environment
  • SQLite3 is extremely capable and fast
  • SQLite3 adds some extra complexity if you need to use it over LAN due to how file locking works
  • Full text search out of the box

I made the necessary plumbing that keeps the SQLite3 databases in sync with the TPS files, allowing us to leverage full SQL reads on TPS databases. That’s been a gamechanger so far, as the replica both acts as a live backup if a TPSfix (TPS file repair tool) fails, and as a massive accelerator when it comes to data retrieval, for the low cost of some overhead in write operations.

Sounds interesting. In the first thing I tried with SQLite I ran into the Clarion driver causing locking issues even before I got to multi-user. For that project I opted to have data stored in TPS first, and transferred to SQLite for reporting. It didn’t have to be immediate, they were only doing monthly reports. But immediate would be cool.

Did you notice any file size differences between the SQLite and TPS files?

I made a simple ABC app (almost out of the wizard box) with embedded Firebird. If you start the app 2 times and change the same form you will be informed with the message changed by another station.
FB_Clarion.clw (4,4 MB)
Change clw to 7z and unpack

1 Like

that’s what you would expect isn’t it? I tried it and it only gave that message if I did in fact change the record in the other instance.

Maybe I am misunderstanding you, but I could not get it to misbehave.

Hi Geoff,

It’s not a problem it’s a solution. Others where questioning if it was possible with embedded Firebird

Ok thanks. I definitely misunderstood you!

Cheers

Thanks Dirk, that’s a nice example. Do you enable tracing somewhere in the app, if so where? It’s generating a trace log and I couldn’t see why?

Hi Geoff,

Maybe because trace.exe is found?

You can disable it with:

SYSTEM{PROP:DriverTracing} = ‘’

‘’= off. Trace saves passwords and login information in plain text so it’s not save