I am trying to decide how I should save and open the data in my project that I am working on. I was at this point some years ago and when it was time to create the save/open part of my project I got so stuck that I stopped using Xojo. The problem at that time that I could not grasp the database side.
However I believe now that I also could use Json or xml format to save and open my project’s data, is this correct? My project is basically a word processor that uses lots of text fields and a couple of popup menus and maybe is using a database as saving system too much? I have a program that does the same kind of projects and saw that it is saving the information as XML, maybe this could work for me as well?
that is what I started with some years ago and turned out to be so complicated that I complete walked away from Xojo out of frustration and back to Filemaker. I don’t want to make that mistake again. That’s why I hope using xml would be an option as well. I believe it’s less complicated?
lots of text fields, images, radio buttons, popup menus.
What confused me before was how to connect the database to my project file, saving the information from the text fields, images, radio buttons, popup menus, en opening and saving everything in the project. It was simple too complicated compared with Filemaker where I came from which has the database side automatically added. Last time I tried to work with this was in 2019, maybe this has been simplified in the later versions?
When you say “radio buttons, menus” do you means whether a radio button is pressed or not, or which menu item is showing? In the database, you can basically save integers, floats, and text strings. You can also save blobs such as images.
Your first step is to map the things you want to save onto these types, which allows you to define your database schema. Then create the database using this schema, then when you want to save program values, write them to the database.
Are you trying to save the preferences or a project?
The problem with Filemaker is it insulated the user from needing to understand how a database works and learning SQL syntax. It’s not hard and you can get lots of help here in the forum, but you need to describe better what you want to do and perhaps provide a screen shot of what all the text fields, images, radio buttons, popup menus, etc. look like. What of those are app preferences and what is project data.
Here is a screenshot of the app I am working on. It has a listbox, radio buttons, 2 popup menus and 4 text fields. So the user enters information and then must be able to save it and again open the file another time which then loads all this information again.
OK so lets just take the character description and gender as examples. First is a text string, second is a number. Then the schema might look like this:
Var f as folderitem, dbh as sqlitedatabase
dbh = new sqlitedatabase
f = new folderitem ("/path/to/database/prefs.db", FolderItem.PathModes.Native)
if (f.exists=false) then
dbh.databasefile = f
dbh.connect ()
end if
Now we can create the database table and write an empty row of data there:
Var sql as string
sql = "create table prefs (descr text, gender integer default 0)"
dbh.executeSQL (sql)
dbh.executeSQL ("insert into prefs (descr, gender) values ('', 0)")
Now get the actual values from your app and save them:
Var descr as string, gender as integer
descr = descriptionfield.Text // Your field with the description string
gender = genders.Index // Your set of radio buttons for gender
sql = "update prefs set descr=?1, gender=?2"
dbh.executeSQL (sql, descr, gender)
This works very well with SQLite. What exactly do you want to achieve? Step by step.
If you get stuck, ask here, be as specific as you can be and post the code block you have issues with.
P.S. this is one of the reasons I moved from FM to Xojo. Here I can store all the internal/user prefs stuff in SQLite, which sits in the appropriate place → lookup SpecialFolder.
OK so I see what you are wanting to do at t a high level. For your initial purpose you may want to not try to create your database in code at first. I expect you will find that somewhat frustrating so I recommend downloading a tool called (link → DB Browser for SQLite ← link)
With this tool you can create an empty SQLite database file that contains the tables you want. You can then open that database in your app and focus on inserting, updating and querying the database to get your app functional.
Once you have all the database functionality coded you can then circle back and code to be able to create an empty database in case the app needs it, or if you don’t want to ship the app with a pre-made database file. One thing you will notice when you create a table in DB Browser is it provides you with syntax for table creation, which can be modified by removing the ; and used to create a table in Xojo code.
Once you have completed you design of all the tables you need the code that @TimStreater provided will be helpful for creating the database and the tables you need, but if you use this DB Browser* tool at first you can focus on the Xojo code needed for your app to save your data to the DB.
Edit: This DB Browser tool is available in both Mac and Windows flavors. The screen captures above are the Windows version. I have found it invaluable for making sure I can create databases with proper indexes and experiment with views and triggers for greater db sophistication and functionality.
Thanks for all the info, Tom. I will have a look at it. Maybe using a database will not be that daunting any longer after having worked with some Swift/Xcode for a while. The programming language is not as complex for me any longer as if was before.
in the archive zip, you will find a project in the “The Data Base” folder:
at run it creates a folder in Application Folder, and create a Data Base with a TABLE there.
There are windows (simple) and one who let you add two other TABLEs.
You can Import / Export as Text your data.
Free; the only cost is to download the zip and look the data (you will also find the Xojo source code (of the projects) in a PDF…
Nothing fancy (in the code), everything you can read and understand (and explore !)
Thanks, I created 2 tables and their rows for the Story and for the Characters. Things are coming back from my Filemaker days… I now need to see how I connect this with Xojo and where to write the code in Xojo to interact with the fields.
Now I need to add the information from the Characters Listbox to the database/Character table/Name Field. I assume that I need to write this code in the Add Character button?
A Character is being added to the Listbox in another place and the other Listbox gets those same values:
You’re welcome. For simplicity, I left out any pretense at error handling. In my app, I have an extended initialisation sequence, part of which goes through and checks for the existance of each operating database. If it does not exist, then it is created and the appropriate create table ... is run to initialise it. That way the user just runs the app without having to worry about separately doing any setup. I could probably have put all those tables in one database - the choice of which tables to put in each is to some extent arbitrary. But it is harder to change that later, so I tried to think a bit about it beforehand.
For reloading the prefs from the database, it would be something like:
Var f as folderitem, sql, descr as string, gender as integer
Var reg as rowset, dbh as sqlitedatabase
dbh = new sqlitedatabase
f = new folderitem ("/path/to/database/prefs.db", FolderItem.PathModes.Native)
if (f.exists=true) then
dbh.databasefile = f
dbh.connect ()
end if
sql = "select descr, gender from prefs"
reg = dbh.SelectSQL (sql)
descr = reg.Column("descr").StringValue
gender = reg.Column("gender").IntegerValue
descriptionfield.Text = descr
GenderRadioButtons(gender).Value = True
