I have an app that uses a local SQLite database with no issues. I just use the built-in (i.e., non-plugin) SQLite facilities for that. But I want to be able to host this SQLite database on my server here, such that I or my wife can choose to have the app use that database instead of the local one.
But I can’t quite grasp if this is supposed to be possible or not. If not, then what is the point of using ODBC to access a local SQLite database, when Xojo provides it directly?
So, on the assumption that with ODBC I should be able to access a remote SQLite database, I downloaded the Actual Technologies macOS driver package as suggested in the Notes for the ODCBDatabase class in the Xojo doc.
But it was immediately unclear whether to install the driver locally or on my server. I tried locally, but then the DSN is just a name with no possibility to add a remote IP address or port. If I do install it on my server, do I have to pretend to be another database type so that I can access the file on my server?
SQLite is inherently a file-based (“local”) database. If you’re hosting it on a server, I think it will have to be on a network-shared folder (e.g. smbfs, nfs). Caveat: This would free your app from having to manage the network connectivity, but could cause exceptions if connectivity is lost.
As you said, since Xojo already supports SQLite, using ODBC is probably overcomplicating things.
If you’re needing a truly remote SQL server, you’ll probably need to switch to something like MySQL/MariaDB (i.e. a bonafide TCP/IP server as opposed to a file-based database). Just my opinion, of course.
I could set up mySQL/MariaDB/CuseSQL, but ISTM that 's all far too heavy-duty for what is needed. My plan orignally was to develop the app I already have to use Auto-Dicovery to find other instances of itself on the LAN here. That’s going to be a max of three, one on the server and our two Minis. Then I could create my own protocol to send data back and forth between the instances. This would have the downside that much of the SQL access to the database would have to be duplicated in another form. I had hoped that I could avoid this via ODBC and then just the source data choice would remain, which would be easy enough to do.
Clearly an SQLite database is not meant to be accessed over a network file system; I have tried accessing the database from the server just by mounting the remote drive locally, but some vagary or other of Apple’s SMB means that my app (or even a test app using ODBC) is unable to write to the database. I’m not pursuing that any further.
Well, I suppose I should clarify: I agree that you probably shouldn’t use a network share with SQLite, hence the “Caveat” I mentioned: "could cause exceptions”. But my real point was: “ … you’ll probably need to switch to … MySQL/MariaDB”.
there are many database solutions which are more or less easy to setup,
cheap or expensive. works well with xojo or not.
every kind of database have a similar sql instruction set.
ODBC could be limited in features for your database. i prefer direct connect in network.
I do have such at present but I wish to throw it away and replace with an extended version of another app I already have, which uses the same database but only locally.
I do have such at present but I wish to throw it away and replace with an extended version of another app I already have, which uses the same database but only locally.