Embedded Databases

Jim_Smith · February 27, 2014, 3:23pm

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?

Kem_Tekinay · February 27, 2014, 3:49pm

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?

Paul_Lefebvre · February 27, 2014, 3:59pm

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.

Peter_Rodwell · February 27, 2014, 4:08pm

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.

Jim_Smith · February 27, 2014, 4:25pm

Paul
What is the advantage of using an “artificial” primary key?

Paul_Lefebvre · February 27, 2014, 4:33pm

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.

Jim_Smith · February 27, 2014, 6:03pm

Paul
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?

Norman_P · February 27, 2014, 6:26pm

you could just make them auto increment integer columns

Frederick_Roller · February 27, 2014, 6:35pm

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.

Julian_Mussi · February 27, 2014, 6:55pm

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.

Paul_Lefebvre · February 28, 2014, 2:39am

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.

Jim_Smith · February 28, 2014, 3:09am

Thanks Pall.

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.

Thanks again.