Weird SQLite Issue

Databases
1

Hi all,

I haven’t used SQLite for many years but wanting a local database, so created one in DB Browser for SQLite, used the Xojo connector and open on launch. However when I insert some lines into it, no errors caught, no exceptions but the actual file never seems to get touched. Modified time stays same and when I open the SQLite database in the browser it does not have the data added at all.

Yet, if I create an MYSQL connector, same table structure, it adds the data fine.

I feel I’m missing something simple. (Xojo 2024r3.1).

for each objScene as Object in arrScenes
  szName = Dictionary(objScene).Value("sceneName")
  szUuid = Dictionary(objScene).Value("sceneUuid")
  iSceneIndex = Dictionary(objScene).Value("sceneIndex")
  Var szSQL As String
  szSQL = "INSERT INTO obsScenes (sceneIndex, sceneName, sceneUuid) VALUES (?,?,?)"
  
  db1.ExecuteSQL(szSQL, iSceneIndex, szName, szUuid)
next

Works fine in MySQL, not SQLite. Any thoughts? Thanks

2

Sure, but first publish an entire sample we can run. Since the DB creation to the end. Zip the project and upload here.

3

db-sqlite-test.zip (7.8 KB)

This seems to show my issue. Two buttons, one is AddRow, the other by SQL. Neither seem to add to the database but neither throw an error.

4

I didn’t download the project but if this is API 2 then this is the way I found it to work. I couldn’t get SQLite to work without being within a Try…Catch block.

Try
    Db1.BeginTransaction
    Db1.ExecuteSQL(szSQL, iSceneIndex, szName, szUuid)
    Db1.CommitTransaction
Catch error As DatabaseException
    Db1.RollbackTransaction
End Try
5

Show us your SQL statement. That is, what does szSQL contain?

I don’t believe for one moment there is anything wrong with SQLite. And you shouldn’t need the Begin/Commit statements either.

6

You mean that:


Sub Pressed() Handles Pressed
  Var szSQL As String
  szSQL = "INSERT INTO obsScenes (sceneIndex, sceneName, sceneUuid) VALUES (?,?,?)"
  
  db.ExecuteSQL(szSQL, 4, "TEST", "TEST UUID")
  
End Sub
7

Totally agree, SQLite is very mature. If I swap db to be an MySQL connection, works just fine.

the SQLite file sits in the same folder as the project and everytime I run debug I get that permission to access files in documents message. (I check db.IsConnected at runtime). Hit yes everytime. I will try to relocate that database file to a specialfolder later this weekend when I can get back to it.

8

SQLite will have issues if the file is considered read only.
If your db file is in the resources folder having been dragged into the project, then it will not be usable.

"local to the project’ is the most common cause of ‘things dont save’ in this forum - because the path ‘local to the app’ is different depending upon whether you are running debug or release.

Always have your sqllite file in specialfolder.applicationdata (or maybe documents but thats best avoided on machines using iCloud)

And the other biggie, which crops up time after time, is that the database file you have connected to, is not the one you think you are updating.

1 Like
9

I never use database row so have no knowledge of it. I also create all databases at runtime (as needed) rather than dragging one into the project. You have ticked “connect on launch” but at what point hat happens during the startup sequence I know not.

If I add a break inside the btnSQL Pressed handler, I can’t even see db, much less inspect it inside the debugger to see what state it’s in.

10

I guess it may be some “new feature” hell from that DB Kit thing. Maybe if you stick with classic it works.

11

Update: if I build this project and run it, then both buttons update the database as desired - and with it inside the Resources folder.

Update2: Same applies to the debug build. The database is inside the Resources folder of the debug build and gets updated as expected.

Conclusion: There is no error - all is working as expected.

This one applies.

1 Like
12

Thanks Tim.

Build or debug, same thing. I’ll do the create/connect manually rather than rely on the connector.

1 Like
13

before execute you could use a break point and look into the db1 properties.

14

If you have it as part of the project it’ll still be copied into the Resources folder.

15

When you Run/Debug, you are in a completely different folder. The contents of that folder are deleted when the debug run quits, along with your database. Don’t put any important files “next to the project”.

16

My .debug applications are never created next to my project, but in a dedicated folder (for all projects) inside my home (macOS).
So, your explanation does not works for me.

17

And because you are not the original poster, the comment was not directed to you.

1 Like
18

I reformulate:

frop my pov, thus explanation (already given) does not works:
the project and support data are in a folder,
the .debug application is in another folder,
the final application is in a different folder (Applicarions / Programmes).

19

What is your point? I’m sorry, but I don’t understand. Does it work? Does it not work?

20

The difficulty the OP was having is that he included the database into the project. Which means that a copy of it is put into the Resources folder (either of the debug app or the built app), and that that one is the one that the app works with, not his original one. And that will be true regardless of whether his original database was next to the project or not.

3 Likes