I've tried writing to SQlite databases in Ventura

Targets macOS
1

I have an app that I can compile in Catalina. I use a sqlite database. Reading to it is easy… Writing seems to have no affect.
I can communicate with my online database, read and write to that. The local database connects, but whenever I try to edit it, the changes don’t work… I tried lots of things. I wrote the program on the 2019r1 version… In catalina it writes to the sqlite database just fine. I take the same version to Ventura and writing doesn’t work… I compiled the software for 2024r1.1 same problem. here’s my code (all the folder items are pointing to the correct locations).

GraphicBD= New SQLiteDatabase
GraphicBD.DatabaseFile=specialfolder.ApplicationData.child("Guitar SightReader Toolbox").child("BG").child("Dependants.sqlite")
GraphicBD.EncryptionKey="140ljmdf5dc"
If GraphicBD.Connect() then
end if
ol= db.SQLSelect("SELECT * FROM wp_gsi_licenses WHERE uid + "+str(ll)+" and license_serial='"+hashcode+"'")
if ol.Field("status").StringValue="1" and ol<>nil  then
  msgbox ("No problem!  This computer is already activated!  Please restart Guitar SightReader Toolbox for registration to be enabled.")
  go_forward=False
  Data_info.text=""
  rs = GraphicBD.SQLSelect("select * from Identify where PersonID='1'")
  rs.Edit
  rs.field("Enabled").StringValue="True"
  rs.Field("Email").StringValue =TextField1.text.Trim
  rs.Field("Hardware").StringValue =  Hashcode
  rs.Field("Name").StringValue =  TextField2.text.trim
  rs.Update
  GraphicBD.Commit
2

do you have a primary key in your table ? if not the edit/update methods of xojo don’t work.
it would also be helpful to display any error occuring after the update command.

3

I hope that’s not your live EncryptionKey.

1 Like
4

It’s no biggy because the database only contains the license info.

5 
rem API 2 Examble
Var row As RowSet = db.SelectSQL("SELECT * FROM customers WHERE ID=?",nr)
row.EditRow
 var d as  DateTime
d = datetime.now
row.Column("lastcontact").StringValue = d.SQLDate
row.SaveRow
row.Close

examble

Contaner-mysqlkit.xojo_binary_project.zip (38.7 KB)

6

There are many other types of Databases than SQLite… I’ll try the other ones on offer… perhaps they work without the hoops that SQLite has been giving me :slight_smile:

7

If you do not give more details than that, nobody can help you.

On the other hand, start without ```
EncryptionKey


At last, check the documentation (the current documentation)…
[SQLite Basics](https://documentation.xojo.com/topics/databases/supported_engines/sqlite/sqlite_basics.html#topics-databases-supported-engines-sqlite-sqlite-basics-sqlite-database-anatomy) for example, and start from there (add Encryption later).
I hope this help.
8

We need more information.

Does your code hit the message box? Have you checked for errors after the Update and Commit commands?

Note. This line of code doesn’t work properly. You should be checking for nil first.
if ol.Field("status").StringValue="1" and ol<>nil then

1 Like
9

One difference of SQLite vs. the others is that it has no separate server.

10

You forget “far more easy to use”…

11

I’ve spent at least 72 hours on this. On XOJO 2024, I tried (after connecting the database via file and encyption key)

Var rs As RowSet
Try
  rs = GraphicBD.SelectSQL("select * from Identify where PersonID='1'")
  rs.EditRow
  rs.Column("Enabled").StringValue = "True"
  rs.SaveRow
  rs.Close
Catch error As DatabaseException
  MessageBox(GraphicBD.ErrorMessage)
End Try

With the app hanging on rs.Rowset. It’s exception being this:
attempt to write a read-only database.

I can’t even, using 2024 create said database - this is in the me>library>Application> my stuff folder.
Needless, to say… in catalina (I’m using parallels), i can do all of this, write to my database. I compile the app and export to ventura and all of the sqlite write stuff doesn’t work (Everything else about my app including recording audio, displaying chord and scale graphics all work. It’s just this that is so frustrating. I’m almost at the point of writing to a text file instead of using sqlite.
Does anybody have any ideas about this?

12

I should mention that This is not my final database setup - just a testing setup… I would be out of my mind to post exactly what it will eventually be :slight_smile:

13

Should it not be:

MessageBox (error.ErrorMessage)

Perhaps you’re not allowed write to that myStuff folder. You could do a test of using Xojo to write a textfile to it.

Does ApplicationData/Guitar SightReader Toolbox/BG/ exist?

14

try to store your database somewhere else, like the desktop or the document folder ?

15

Yes - actually I created a quite sophisticated “Database” that uses text files that writes to that folder… So I can write to it.
I see that Sqlite is the only real Local Database with Xojo.
I went into the info file of the database - changed the permissions - still no dice in Ventura.
If I can get the edit/update feature working in Ventura - then I will be happy. I know all the other features of my app runs in sonoma. It’s this one thingg (SQlite editing) that is holding me back from releasing a current apple version to go with the current Window working working version!

16

Xojo doesn’t support all the different encrypted modifications to SQLite. What kind of encryption is this database using? I ran into something similar needing to modify a SQLite database that was encrypted using SQLCipher.

In my case, I ended up needing SQLDatabaseMBS, and had to compile and sign my own dylibs for sqlite3 and libcipher.

Then again you said you have this working on Windows and older MacOS versions so that’s probably not it.

Are you able to write to the database via SQLiteStudio or some other third party utility?

17

Or try with the sqlite3 CLI.

18

Another thing to try is a simple select and then update approach:

Var  enablefl As Boolean
rs = GraphicBD.SelectSQL("select enabled from Identify where PersonID=1")
enablefl = rs.Column("Enabled").BooleanValue

Try
  GraphicBD.ExecuteSQL ("update Identify set enabled=?", enablefl)
Catch error As DatabaseException
  MessageBox(error.ErrorMessage)
End Try

In fact you could limit this test to just the Try/End Try portion here.

19

Okay - this is very weird. I just copied the files over to a version of Sonoma running on my laptop. SQLite works perfectly. I built this on my Imac running Ventura using parallels running Catalina (where i built my app). Somewhere here, Ventura “broke”, stopping me from running Sqlite. Very strange! The good news is that I can now setup to deploy my app. Thank for everybody’s kind suggestions.

20

This is a big problem.
If you have been able to write to it on some machine, you have been lucky.
Your database should be in some subfolder of specialfolder.applicationdata or specialfolder.documents

It’s not weird - neither Windows nor Mac machines currently allow you to write to files in the ‘application’ folders (without granting elevated permissions, at least)

1 Like