Creating SQLite DB in code

Databases
1

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?

Thanks,

  • karen
2

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

3

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

4

Hi Karen,

what you think about this?
https://forum.xojo.com/40210-valentina-studio-7-1-new-generation-of-xojo-code-from-db-schema

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?