Are there any embedded databases other that SQLite that Xojo support?
I’m having a problem with the way SQLite handles single quote (as in Joe’s). I have a data base created with and updated with RealSQLDatabase and it will allow you to add and update records using these as keys with these without problem.
SQLite will let you add but gives an error on trying to update.
I can not release an update to my application with this problem. Or do I continue using RealSQL?
If you are ready to consider another database, I think you should first consider a table redesign for your SQLite database so you use an artificial primary key ((INTEGER ID) ) rather than a text-based one like this.
The advantage is that you have a primary key that is only a primary key. It does not also contain information about the data or derive from the data. It eliminates issues if you needed to change the value that was being used as the primary key, because an artificial one never needs to be changed. It’s likely faster in small ways as well.
If you still need to enforce uniqueness on your text column, you can do that by adding a UNIQUE index for the column.
I have a class that generates GUID’s for all the table ids. Works well, is easy to implement and avoids some of the issues with using auto increment. It makes debugging a bit more challenging but solves a lot of other issues.