Firebird Embeded versus SQLite considerations?

[Dirk1]

@JonW

I used SQLite because I cannot really conceive of not being able to run ad hoc queries against the data that is being collected, and it is “embedded”, you don’t actually have a “server” that takes requests to add/change/serve up data that you have to install. But the whole-database locking thing with SQLite and Clarion making that worse meant that in one application I use TPS for actual data entry, and then periodically dump the data to SQLite for reporting and analysis.

@JonW

In that embedded section you have firebird embedded and SQLite, where SQlite is less limited than firebird.

@JonW

Then you have free client/server solutions including Postgres and MariaDB (a fork of MySQL). Personally, I would just go for Postgres in that realm. Client/server has a lot to recommend it, but in my environment (government) there is a whole bunch of hassle in making a use case because you require administrative privileges to install, and I have shied away from that.

And then there are commercial databases that are often free up to a certain size (MSSQL, Oracle, Mimer, MySQL) which you might choose based on footprint or particular capabilities. And yes, you’ll be using ODBC to access them; MSSQL also has the optimized ODBC driver, and Oracle has an OCI-based driver. For the larger things I have built here at work I have an Oracle server I control, and then all I have to do is get people access through the firewall. That’s easier than getting Postgres installed someplace, which I guess is why I don’t go that middle route

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

[seanh]

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.

[JonW]

@Dirk1

What do you mean with limitations in Firebird?

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

[Dirk1]

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

[jslarve]

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

Thank you

[Dirk1]

Hi Jeff,

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

Using fbclient.dll version 3.0x

[Dirk1]

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

[Tom_H]

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.

[JonW]

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.

[jslarve]

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

[Dirk1]

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

[vitesse]

@Dirk1

and change the same form you will be informed with the message changed by another station.

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.

[Dirk1]

Hi Geoff,

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

[vitesse]

Ok thanks. I definitely misunderstood you!

Cheers

[PurpleEdge2214]

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?

[Dirk1]

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

[CarlBarnes]

Sorry just realized I never clicked the button to finish this reply…

@Dirk1

b: if firebird odbc is not found on the machine

 If Exists(...
    Run(clip(glo:uncInstallatiepad)&'\Firebird_ODBC_2.0.5.156_Win32.exe /VERYSILENT',1) 
 Else 

RUN() can fail and sets an ERRORCODE(). Its good to check and display a Message so there is no mystery for Tech Support or yourself why the install did not run. You could have bad code where your EXISTS(xxx) is different than your RUN(xxx). Or it could fail to run due to rights, elevation, corrupt EXE, etc. E.g. add IF Error and Halt

  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) 
!--- Check if Run ok
     if ErrorCode() then
        HALT(0,'Run Install Firebird ODBC failed error '& ErrorCode() &'  '& Error() )  !should be more
     end  

---

Having worked where there were 10+ people in Tech Support I realized I needed problem/fail Messages to make obvious many details of the problem.

Best bet put that Run EXE in a variable so its just once in code and so the Halt() can inform Tech support what failed to run:

InstallFireBirdExe STRING(260)
FireBirdODBCEXE EQUATE('Firebird_ODBC_2.0.5.156_Win32.exe')
...
  InstallFireBirdODBCExe = clip(glo:uncInstallAtPath) &'\'& FireBirdODBCEXE
  if exists(InstallFireBirdODBCExe)
     run(clip(InstallFireBirdODBCExe)&' /VERYSILENT',1) 
!--- Check if Run ok
     if ErrorCode() then
        HALT(0,'Run Install Firebird ODBC failed Error: '& ErrorCode() &'  '& Error() &' |
               '<13,10,13,10>ODBC Installer: ' & InstallFireBirdODBCExe )
     end  

You could also check RUNCODE() for the return code from the Installer.

---

I think I would also open a simple Window otherwise the program appears to hang with no idea what is happening.

RunWindow WINDOW('Running Install'),AT(,,250,60),CENTER,GRAY,FONT('Microsoft Sans Serif',10)
        STRING('Running FireBird Installer ... Please Wait'),AT(66,15)
        STRING(@s260),AT(4,35),USE(InstallFireBirdODBCExe)
    END
...
   OPEN(RunWindow) ; DISPLAY()
   RUN( ... )
   IF ERRORCODE() THEN ...
   END 
   CLOSE(RunWindow)  

[Dirk1]

Thanks Carl,
Noted and i use this in my code now.