iOS and SQLite

I am new to Xojo so I am trying to figure out if its the right tool for what I want to do.

My goal is to build a small iOS utility which is effectively a database - users can add items in a hierarchy and then search for these items based on various criteria.

So an iOS app using an SQLite database.

Do I declare and build the database using SQL within Xojo, or do I build the database using something like Valentina Studio and copy it across. I can see RealBasic used to have a built in tool but that seems to be missing in the current version of Xojo.

I have also looked at Alpha Anywhere which seems to have more tools and hints available, but it won’t build an iOS app.

Appreciate your assistance in getting me going on my Xojo journey - I have been enjoying Paul’s webinars while doing my ironing on the weekend.
thanks
Hamish

You can create a SQLite database with Xojo IDE

There are a bunch of Free and Inexpensive tools to help you work with SQLite. I’m a fan of Base but also use Navicat on a regular basis. I don’t recommend using the built-in database editor - it just does too many things wrong.

As far as iOS goes you can do a couple of things. You could either put a copy in the resources directory and then copy it to the ApplicationData directory at startup (if it’s not already there), or you could have a script file and do an SQLExecute to run the script after creating the database in the proper location.

I agree Bob, personally I use to create the database by code.
But he is wondering if he can create the database with Xojo and the answer is YES

Thanks gents. @Antonio Rinaldi when you say you can create the database with Xojo, is that using SQL or is there a GUI or other tool I can use.

Apologies; my background is as an accountant so this is new to me. And while I am pretty good at financial modelling in Excel with various formulae, I always shied away from macros. So learning Xojo is a big learning curve.

@Bob Keeney I have in mind to subscribe to some of your videos - looks like a comprehensive resource.

OK I see that there is a command “Insert > Database > New SQLite database” for a desktop app.
My idea is for an iOS app and alas, that command is not available for that project type.

Thoughts?

You define it in code and simply use it that way
See the examples included under Examples > IOS > Database

Thanks! We added about 10 hours of iOS specific stuff to it late last year that you might find useful.

[quote=171964:@Hamish Blair]OK I see that there is a command “Insert > Database > New SQLite database” for a desktop app.
My idea is for an iOS app and alas, that command is not available for that project type.

Thoughts?[/quote]

Even for desktop I do not to use the built-in database. I use an external editor and get the script (this is usually a dump or export command) and just add that as a constant in the project somewhere. Then at startup I use that script to create the database.

FWIW, I like doing it this way because later on, as you make changes to your schema, you’ll have to use a script to update the database anyway. When new run the script. When existing check the schema version and see if you need to run any of the update scripts.

Thanks guys. I think I understand the process - use an external editor to “create” a version of the SQLite database, and then copy and paste the SQL commands from the editor to Xojo to replicate the creation process.

But I would also need to test whether the app has already been run and previously created a database; then I just need to load the database in the first instance. If the database is out of date I then need to update it (but not overwrite it).

All you need to do is Set the FolderItem of the final location and see if it exists. If not, create a new one which will create it.

If it does exist you then query the database for it’s schema version. If your current model is at 3 but the one on disk is still at one you usually run Update2 and upon success run Update3 and so on. Always a good idea to run these in transactions in case something goes wrong and you have to back out.

@Bob just reviewing this process. You said
“Then at startup I use that script to create the database.”

I get that. However how do you load any previously saved data? I presume the “database” gets created each time the app starts, and then it can separately “load” data from a file. Any changes would be written to the file.

Then when the app is launched next time, it re-creates a blank database and reloads the data from the file.

Is this understanding on the right track?

you can also make the database with an external editor ( navicat, sqlitemanager, valentina (can’t make this one works!), mesasqlite)
then import datas with these external editors, and finally drop the file created inside a xojo project
the database will be available for the items in the project.
I think you don’t even have to open the file it will be opened for you.

[quote=174330:@Hamish Blair]@Bob just reviewing this process. You said
“Then at startup I use that script to create the database.”

I get that. However how do you load any previously saved data? I presume the “database” gets created each time the app starts, and then it can separately “load” data from a file. Any changes would be written to the file.

Then when the app is launched next time, it re-creates a blank database and reloads the data from the file.

Is this understanding on the right track?[/quote]
No. When your app runs, check to see if a database file exists in the application data folder for your app. If so, open it. If not, it must be the first time the app is being run, so you would create the database into the application data folder (or copy a template from the Resources folder). See http://developer.xojo.com/specialfolder for a definition of what the folders are for.

Just to clarify for the beginner, I assume you are referring to

dim rs as recordset
dim version as integer
rs = db.SQLSelect("pragma Schema_Version")
version = rs.IdxField(1).IntegerValue