I know I am being really dense here, but cannot get my head round this:
Scenario is: Desktop App with an SQLite database (file name “DB1.sqlite”). The db file will always be located in the user’s “My Documents/DB1” folder (Windows) or “Documents/DB1” folder (MAC OSX).
At runtime I want the app to create global instance of class SQLiteDatabase which remains connected and instantiated for life of the app session.
Any idea how? Any code examples?
All help gratefully received!
Add a property to your App class and initiate it in the Open event 
I do it exactly the same way as Alex von Siebenthal and close the database in the “close” event of the “app” class.
In the past I experimented with creating or checking the whole database in a class named “CreateDB”. This was a general class and put the checking and creation code in its constructor. However while working perfectly for my case, I abandoned this idea because I found it too restrictive and was still using methodes and functions outside this class.
Better to use a dedcated module and put methods, functions and classes in there. That is how I am doing it now.
Alex, thanks! This idea sounds very promising.
But, I am still very new to XOJO, so could use a little more help / detail.
For “App Class” I assume that is the Application Name shown at the top of the Contents window at the left side of the IDE?
I have added a property to this. When adding the property, the right side of the IDE then has options to fill in for Name / Type / Default / Scope. I have filled in the Name as DB1 Type as SQLiteDatabase, left Default blank, and left Scope as Public.
I then put the following code in the App Class Open event:
[code] Dim dbFile as FolderItem
dbFile =SpecialFolder.Documents.Child(“CCdata”).Child(“CDB1.sqlite”)
If dbFile.Exists Then
CurrencyClub.DB1.DatabaseFile = dbFile
If CurrencyClub.DB1.Connect Then
// Use the database
MsgBox("DB1 connection successful!")
End If
End If[/code]
However, this generates an error when compiling that “DB1 does not exist”. What have I missed?
Create an SQLiteDatabase object in code, connect to it and on success assign it to CurrencyClub.DB1
What is CurrencyClub? Your property is named DB1, so with code on App, you just refer to it as DB1. For example:
DB1.DatabaseFile = dbFileOutside of App, you would refer to it as App.DB1, such as:
App.DB1.SQLSelect("SELECT * FROM table")Hi Paul,
CurrencyClub is the App name.
I did try
DB1.DatabaseFile = dbFile
//followed by
If DB1.Connect Then
//Etc.
I got a successful connection, but only from within the App Open event. If DB1 is accessed from anywhere outside this Open event, I get a nil object error. It seems that DB1 does not have global scope?
I also tried Alex’s suggestion of creating an SQLiteDatabase object, connecting it, and then assigning it to the DB1 object, but result is same as yours.
Would really appreciate more detailed IDE setups / or code snippets to help push my stupid brain in the right direction!!
Many thanks for your time to reply to my problems
Can you upload your project (or a small piece of it) somewhere so we can have a look at it?
That sounds like you also have a line
dim DB1 as SqliteDatabase
in app.open. If so, remove it.
Tony, having the SQLiteDatabase object declared as property in the App section is good, and you can do the connect in the App.Open event.
If you are anywhere other than in the App section, say in Window1 for example, you will need to access the database as App.DB1.[whatever] to ensure that the program knows where to find the object. For example, if this is in the Window1.Open event…
dim rs as recordSet
rs = app.DB1.SQLSelect( [your sql here] )
Another two points to keep in mind: 1) Check to see if the database exists before connecting and 2) if you need to do a CreateDatabase it will create it (if it can) and will automatically do the connect for you.
hi i do a class to conect to db
and when i need i load
Very many thanks to everyone who has kindly contributed ideas. I am working through them!
For now, Paul, I have (I believe!) set up the app as per yours and Alex’s suggestion. I have included 3 screenshots below:-
1st shot shows the IDE setup with the left column showing app class, properties, events.
2nd shot shows the App Open Event code (which runs without error and all MsgBox get displayed at runtime, which suggests DB1 and TestDB are instantiated and connected.)
3rd shot shows code for Main Window open event, but always fails to compile due to DB1 “does not exist” - Why??
Thanks for your patience in trying to help me - just wish I was not so dense!!
Shot 1)
Shot 2)
Shot 3)
Use App.DB1 instead of CurrencyClub.DB1. I think this is a deficiency in the new IDE layout. It used to be crystal clear that you should refer to app properties by prefixing with “app”. Now it is much less clear, unintuitive even.
I added a feature request to rename the navigator item “App”. Hopefully, that would make the syntax easier to see. <https://xojo.com/issue/29739>
Hi Tim,
Brilliant!!! That works perfectly. Solves all my problems - thank you so much for spotting that!