Sqlite connection help

Databases
1

hi!

i’m confused…

why i need to declare every time this code even after make the mySQLiteDB as a global property?

Var mySQLiteDB As New SQLiteDatabase
Var mySQLiteDBFile As FolderItem = GetFolderItem("").child("Contents").child("Resources").child("FatCatDB.sqlite")
If mySQLiteDBFile <> Nil And mySQLiteDBFile.Exists Then
  mySQLiteDB.DatabaseFile = mySQLiteDBFile
  mySQLiteDB.Connect
End If

if i remove the first line, (and declare it on “app.open”) the code runs, no error. but i can’t insert on database file, only read from it!
why?

i just want to put it all on “app.open” and keep the sqlite connected, until i close it.

why i can’t just do it at “app.open” and keep the db open just like mysql?

mySQLiteDB is on a module

i have this on my app.opening:

mySQLiteDB = New SQLiteDatabase
openFatCatDB()

the openFatCatDB is:

#If (TargetMacOS Or TargetWindows Or TargetLinux) And DebugBuild Then
  
  mySQLiteDB.DatabaseFile = SpecialFolder.Resources.Child("FatCatDB.sqlite")
  mySQLiteDB.Connect

… etc

i can’t do any insert anywhere. ONLY if i put this before the insert code…

Var mySQLiteDB As New SQLiteDatabase
Var mySQLiteDBFile As FolderItem = GetFolderItem("").child("Contents").child("Resources").child("FatCatDB.sqlite")
If mySQLiteDBFile <> Nil And mySQLiteDBFile.Exists Then
  mySQLiteDB.DatabaseFile = mySQLiteDBFile
  mySQLiteDB.Connect
End If
2

Please don’t open a database from app resources. Consider the app itself to be read only. Depending on the computer, you may not have write permissions there. And changing the app bundle would break code signature.

Better make a copy of the database to the application data, preferences or documents folder. And then open it there.

4 Likes
3

VAR created variables ARE NOT GLOBAL PROPERTY.

Add a New Module (if your project does not already have one) and add there a SQLiteDatabase Property. Be sure it is Global there.

Also, avoid .Child as you use (more than one in a line): you never know who is Nil (because you cannot test those .Child). Use instead as needed lines and test each result after that line.

At last as Christian says, use SpécialFolder.ApplicationData: create a Folder there using your app signature (com.alexandre.cunha for example), then you are on your own.

Is it clear enough ?

4

ok. understood. when i publish to apple, the place are changed.

but this is not the problem. the problem is why i need to always use:

Var mySQLiteDB As New SQLiteDatabase

and i can’t use it as a global property!

5

did you read what i wrote?

> mySQLiteDB is on a module

it does not work unless i use: Var mySQLiteDB As New SQLiteDatabase

6

Create a sample project, zip it, upload to this thread.
People will be able to tell you what to change.

1 Like
7

i’m doing some tests with a new empty project.

1 Like
8

Because Var creates a new local variable which is closer in scope, so it gets used instead of the global property. You simply need

mySQLiteDB = New SQLiteDatabase

somewhere in your initialization code.

2 Likes
9

hum…

i think i did a mess with 2 databases. i’m checking all and doing some tests.

10

When I have to deal with SQLite Data base, I go to Database Basics

That page have changed since the last time I get there…

And so, a better page for your case is: SQLite → Overview

Provides better sample code, etc.

There is/are examples in the Examples folder you can download from the IDE (New → Examples → Download).

At last, yes, I read what you wrote and the trouble lies there:

Var properties are active within the Code you wrote that line;

Module properties can be active through your application; and as someone wrote, using both is like having two drivers in your car: this does not works properly (as you experiment). Conflict of interest :frowning: one cannot work for him when in his boss enterprise (shop, whatever; while at work).

Yes, in some cases, a Property in a Module needs extra work to be active as with SQLiteDatabase (and FolderItem, etc.).

11

I think It was a bug on IDE. it was not reading the new row after adding using global database variable.

i quit and reopen IDE and it’s working again.

i’m checking all again and trying to force the bug again.

thank you all for the help.

now my insert method is only this, and it’s working properly.

image
image1180×698 122 KB

12

Did you move your data base file from inside of your application bundle ? (Resources)

Var mySQLiteDB As New SQLiteDatabase
Try
  Var newRow As New DatabaseRow
  newRow.Column("name").StringValue = "name"
  newRow.Column ("dataHora").StringValue = "dataHora"
  newRow.Column ("level") .StringValue = "level"
  newRow.Column ("recordTime") .StringValue = "recordTime"
  mySQLiteDB.AddRow ("record", newRow)
Catch err As DatabaseException
  MessageBox("Error: " + err.Message)
End Try

To be able to do the above (instead of a screen shot), click first in
image then paste your code, select your Pasted code and click in
image

13

Try this example, and look at the used code…

Currency in SQLite.zip (11.0 KB)

  1. I already shared this example (I wrote it for someone else)
  2. Once you run it, click in
    Create
    Import (and choose the provided text file)
    Read (to display the data)
    Eventualy Tag Debut (to display the date as SQLDate stored in RowTag).

You can click in the first column to sort the entries by dates (if you cliked in the “Tag Debug” button).

Or simply ignore this entry: your choice.

PS: the .sqlite file is saved in the project folder, it was just an example.

14

no. it was always outside

15

And your code above do not save the database INSIDE your Application ?

PS: I am not an AI, I may made mistakes, but usually, I do not hallucinate :wink:

2 Likes
16

sorry. inside. but this was not the problem.

I think It was a bug on IDE. it was not reading the new row after adding using global database variable.

i quit and reopen IDE and it’s working again.

17

And how runs my example in your computer ?