Creating SQLite DB in code

I usually create a database in another tool and then use the empty database in my App (on first run copy it out of resources and use the copy) so i typically don’t create the DB Structure from scratch in xojo code is SQLExecute… But that should work…

This time I want to use an in-memory DB… So I created the DB structure in another tool and saved the SQL it produced as a string. If I put that string into another tool besides the one that created it, it does produce the desired table structure… so I know the SQL is correct.

But when I try it is Xojo I wind up with a DB with no tables… So I tried having it create a file took… Still no tables. The Xojo relevant code:

theDB = New SQLiteDB ' ---- Delete Later ---- theDB.DatabaseFile = New FolderItem("ExportDB.sqlite") If NOT theDB.DatabaseFile.Exists Then CALL theDB.CreateDatabaseFile ' this does always create the file '--------------------- CALL theDB.Connect theDB.SQLExecute(DB_Structure) theDB.Close End If

DB_Structure is a string constant with the SQL ti create teh structure that as I said I know is correct.

SQLiteDB is an SQLiteDatabase subclass where I override:
Connect - raises and exception if it fails… If it succeeds turns on Foreign Keys
CreateDatabaseFile, SQLExecute and InsertRecord - these raise exceptions if they fail

No tables get created but the code does not raise any exceptions… The first one In get is on a DB.Insert saying the table is non existent

Any idea what I am missing?


  • karen

That you should break the structure variable/constant into multiple 1 liners and execute each one instead of as one giant blob

Thanks Norm. That works… As I said i don’t usually do it this way but I could swear that putting it all in a single string constant did work in some previous version.

Anyway for the record, here is the modified code that works:

theDB = New SQLiteDB CALL theDB.Connect Dim StructureCommands() as String = DB_Structure.Split(";") For each Statement as String In StructureCommands theDB.SQLExecute(Statement) Next theDB.Close

Hi Karen,

what you think about this?

Do you have your SQLite DB?
Do you want send it to me, so I will make sure our next script Generate code ready for use?