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?
There was already another thread on this where I suggested using a SQL update statement with a PreparedSQLStatement. That should work just fine for your app until their bug is fixed.
Had you tried that?
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.
Before sending a string to a database I just replace each instance of a single quote with two instances, as per SQL practice:
newstring = ReplaceAll(oldstring,"'","''")
and do the reverse when reading from the database:
newstring = ReplaceAll(oldstring,"''","'")
Thus “Peter’'s” is written to the database instead of “Peter’s” and is converted back to “Peter’s” when retrieved.
What is the advantage of using an “artificial” primary key?
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.
When using ID as primary key where does the value for ID come from?
I notice that is the Examples project “PreparedSQLStatements” the ID is part of the input file. Is that something I need to keep track of so that I can assign it when a record is inserted?
you could just make them auto increment integer columns
Visiting http://sqlite.org/lang_createtable.html and reading the sections titled “SQL Data Constraints” and “ROWIDs and the INTEGER PRIMARY KEY” may be helpful.
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.
With SQLite, an INTEGER PRIMARY KEY column applies an auto-generated value for you when you INSERT a row and omit the primary key column.
This is all covered in User Guide Book 3: Framework, Chapter 4: Databases, Section 3: SQLite.
I have copies that table and used: ID integer NOT NULL PRIMARY KEY
I can now update the record(s) with an ’ in the name (no longer primary key) and not get an error.