Where to open my SQLite DB

Databases
1

I have a little niggling issue that I hope the resident wizards may be able to assist me with.

I’m working on a small desktop app, that is single user and only ever used by myself. It connects to a SQLite DB.

In the main window of the app, there are four controls that require data, 1 listbox and 3 popupmenu’s.

The code to interrogate the data and populate the controls works flawlessly, as does the code to connect to the database.

The sticky part is this :

I split the database related code into 2 areas. First is the connection stuff, second is creating and populating the recordsets into the controls.

When I call the DB.Connect, I set a ‘DBState’ property, located in a module, to the result code of the Connect statement. The DB is not closed at this point, and DBState will usually contain an integer value of zero.

The code that interrogates the DB, creates the recordsets and populates the controls is attached to the open event of the controls themselves.

This approach works for me because I dont need to open and close the database multiple times, I simply connect once, interrogate the database as and when required, then close the database as the program exits.

The code that creates and populates the recordsets will only do so if the DBState has a value of zero.

The crux of this issue is that when the controls are initiated, the DBState is still at -1 (Default), so the DB connection has not yet completed.

What makes it more confusing is that if I remove the code that checks DBState, the controls populate just fine without any errors.

To give this some perspective :

DBOpen method
// define database file, call connect and set DBState to zero if we have connected. Connection remains open.
End

Listbox / PopupMenu open event
// Check DBState for zero value and if so, create recordset and populate control
End

DBState is a property of type integer, default value of -1

What I ultimately want to do is get the db open statements somewhere in the program where they are guaranteed to complete before the open event of the controls attempt to retrieve the data.

I have tried moving the db open code to App.Open, Window.Open and to a ‘DBOpen’ method I created within a module, but the result is the same.

Any thoughts as to what I may be doing wrong here, or how to address this ?

Thanks.

2

App.open is fine for opening your database. But it sounds like the result of the opening of the SQLite database isn’t communicated properly to your window.

The window should be able to open without data. Then your SQLite database is opened. Now you need to communicate to your window by notification or with an observer pattern or directly “you can now talk to the database and get some data”.

3

^^ That sounds pretty much like the issue, and the reason I used the DBState property.

I get the feeling it may be connected with some kind of execution order internally or maybe if there is some parallel execution going on when the program executes.

The window and controls open just fine without the data, but I cant think of a more logical sequence to make this happen:

App.Open -> Open database and connect, set DBState to 0

Window.Open

Control.Open -> Check DBState for zero and interrogate for data.

Looking at it in reverse, I am assuming that all controls have to have opened before Window.Open is considered to be complete, and that the defaultwindow in an app has to have fully opened and completed before App.Open is considered to be complete.

I have also tried opening the database in the ‘Activate’ events, but the result is the same there as well.

The ideal location to open the database would be somewhere where it is executed before the window is opened.

4

You can open the database before you open the window.

However, you shouldn’t assume that the controls do what you want when you want it. That’s why I recommended to open the window independently of opening the database.

5

My window does open independently, that is not the issue.

6

@Stephen Thomas perhaps move the db-related code out of the controls’ open event into their own methods (of the control), then you can call them from the window’s or the app’s open event (App.Window1.Contol1.PopulateFromDB) after the db has actually been opened.

I usually have a method for each window named Initialize that handles the populating of the controls and setting the initial state, and this is called from wherever is opening the window. If you turn off the window’s “implicit instance” property, and open the window manually you will have more control over when things happen.

7

Where do you create the instance of you DB?
is it in a GLOBAL location? or do you have a

Dim myDB as SQLiteDatabase
8

To elaborate further :

I have a module called ‘dbModule’. Within that I have ‘FinanceDB’ as type SQLiteDatabase (Global).

In App.Open I have:

FinanceDB = New SQLiteDatabase
FinanceDB.DatabaseFile = GetFolderItem(“Finance.sqlite”)
FinanceDB.EncryptionKey = myencryptionstring

If FinanceDB.Connect Then
DBState = 0
Else
DBState = FinanceDB.ErrorCode
End If

I know I can put the FinanceDB.ErrorCode into DBState regardless of whether the connection is successful or not, but it sill shows as the default value of -1 at the time the controls are opened in the main window.

9

DBState exists as a property within the dbModule, as type integer and default value of -1.

10

does FinanceDB.DatabaseFile = GetFolderItem(“Finance.sqlite”) exist? since you are not checking for an error condtion
so FinanceDB may never be created, therefore it cannot contain an error message

and regardless of the fact this is for a personal project, application databases (and other resources) should NOT be placed in the same folder (in some situations, the app itself may be “translocated” causing it to fail to find the resource)

11

Yes Dave, the database opens flawlessly. The code to open it is not an issue.

It seems to me that its about when the code to open the database actually executes.

The database is only in the same folder while I am doing this early stage of development.

12

Clifford, I kind of see what you are getting at.

I have always put the code to populate a control into its open event, which seems the logical place (Up to now anyway), plus I always know exactly where to go looking if there is an issue with that code.

Maybe I need to rethink that approach.

13

those two statements contradict each other… If it opens flawlessly, then DBState would be ZERO

FYI… other than the location… that code should work… I have written hundreds of SQLite programs, and the “open” code is some variation of that same theme

14

Dave, it opens flawlessly during testing of course. I use DBState as a simple sanity check when populating the controls.

15

Ok… good luck… I’m not going to waste more of my time, when you can’t seem to describe what you consider to be the issue…

16

I thought I had described the issue in my first post.

My code to connect to the database does not appear to complete in time for when the controls interrogate the database.

17

I just tried replicating your code in a test project and the firing order is App.Open, Control.Open, Window.Open. The database opens in App.Open and DBState is set to 0 before Control.Open fires. This is with a single, default window that’s an implicit instance. (I was curious to see if a default, implicit instance window would open before App.Open fired, which would arguably be a bug.)

You can’t put the database initialization code in Window.Open because Control.Open fires first for each control. But I can’t imagine why it’s failing when placed in App.Open unless you have code somewhere else that’s setting DBState to a non-zero value. You said if you remove the code that checks DBState then the controls populate, which can only happen if the database opened in App.Open. Is there other database related code which sets DBState? Is one of your controls hitting an error while populating and recording that error in DBState?

I agree with Beatrix and Clifford that the code which loads data into the window should be separate from the open events of the window controls. I would add that using globals to track the state of something like a database is a bad idea that will lead to problems down the road. That said, I don’t see how this could be an issue of execution order.

Edit: Control.Open and Window.Open could fire before the database opens if you are explicitly creating a new window instance in App.Open before your db code executes. But I assume that’s not the case since you can remove the DBState check and the controls will populate.

18

Thanks for checking that Daniel.

The code to open the DB is currently in App.Open, and the DBState is only changed from within that code. I keep thinking this is a timing issue in that the opening of the database and setting of the DBState does not appear to complete before the open event of the controls checks the DBState, hence it still sees a value of -1.

If I remove the checking of DBState in the open event of the controls, it works without issue.

DBState is simply not being updated in time before the open event of the controls actually fire.

If I add a ‘msgbox(str(FinanceDB.ErrorCode))’ immediately after the database connects, it correctly shows as zero.

This is not something I have experienced before with Xojo.

I’m going to try moving some code around a bit and see if that makes a difference.

Regarding your last paragraph, what would be the better option to track the connection state of a database ? I’m more than happy to change what I am doing if it is not good practice or simply wrong.

19

How is your window opened? Is it an implicit instance, the default window for the app? Or do you explicitly create it? If so, when/where? I’m asking because I don’t see how Window events could start firing between FinanceDB.Connect and DBState = 0. All of these UI related events fire on the main thread (which I just double checked) which means Window events can’t just start firing between those two lines.

Another thought: is DBState declared in more than one location? Is there an App.DBState (for example)?

I can think of a few different approaches depending on project needs and coding style. One very simple approach would be to use an Extends method like this:

Public Function IsConnected(Extends d As Database) as Boolean
  dim r As RecordSet = d.SQLSelect("select 1;")
  Return not d.Error
End Function
20

I think I have the source of the issue. When I was previously copy/pasting the DB connect code to test it out in various parts of the program (App.Open, Window.Open, custom Method in a module), I had a control related statement BEFORE setting the DBState, all within the connect code. Setting the DBState before calling the control i.e swapping them around, fixes the issue.

So it was a delay, albeit one I had created myself. I’m going to move the control related statement elsewhere.

Daniel, I will implement your suggestion for keeping a check of the database connection state.

Thankyou everyone for the input, it is always welcome.