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.
In that embedded section you have firebird embedded and SQLite, where SQlite is less limited than firebird.
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.