Where to open my SQLite DB

Your control related statement was for a control on the window, correct? With Implicit Instance on, this would indeed cause Xojo to open that window before App.Open completed, hence your issue. You should keep this in mind for any other operations in App.Open. It is generally a bad idea to directly access a Window or it’s controls from the App.Open event, as that changes the usual order of events (again, with Implicit Instance on).

Thanks Jay, yes, implicit instance is on, and the control related statement referred to a control in the main window.

I have removed the control statement completely and all is working again as expected.


I am late to the game on this thread but here is my two cents.

if you have a single DB that you will ever use, have a global variable as the db handle (some use app.db, I use appGlobals.db where appglobals is a module). now if I open multiple DBs, then I do that at the main window (or other window) level. for instance I have an app that opens a single DB per “main” window. as I a using the DB as a document file format. so when I open each document in its own window, it opens that specific DB. and no DB is opened in app.open().

I have all my dbase open/close code in methods. that way I can call them wherever I need to. and I use the same format for these in all my apps to make it as constant as I can (to save me headaches).

now in the open/close methods (and any other methods I write for the DB) I check the error codes everytime I do anything as small little errors can pop up in the most bizarre areas and it will drive you mad trying to debug. Good news (and I am not sure if I am suppose to say this but it was said to all of XDC so I will), the next API framework (API 2.0) will move the database (among other things) from just filling a property of error codes to actually throwing an exception for these errors. like it should. now that is not coming out tomorrow so in the present time, we have to double check for errors.

my biggest suggestion when dealing with databases, and it doenst matter which one. Check for Errors. and since the framework doesnt do exceptions yet, you have to manually check for these (everywhere).

Thanks Scott,

My current error checking code where the data is loaded into the control(s) looks like this :

If FinanceDB.IsConnected Then Dim AccountRS As RecordSet = FinanceDB.SQLSelect("SELECT AccountID, AccountDescription FROM Account ORDER BY AccountDisplayID ASC") If FinanceDB.Error Then Msgbox("Error loading Account list" + Chr(13) + Chr(13) + "Code : " + Str(FinanceDB.ErrorCode) + Chr(13) + Chr(13) + FinanceDB.ErrorMessage) FinanceDB.Close Else wMain.pmAccount.DeleteAllRows If AccountRS <> Nil Then While Not AccountRS.EOF wMain.pmAccount.AddRow(AccountRS.IdxField(2).StringValue) wMain.pmAccount.RowTag(wMain.pmAccount.ListCount - 1) = AccountRS.IdxField(1).IntegerValue AccountRS.MoveNext Wend AccountRS.Close End If wMain.pmAccount.ListIndex = -1 wMain.cvAccount.Visible = False wMain.lbTransactions.SetFocus End If End If

Is this a reasonable approach ?

yes. but in you code that you open FinanceDB I would do more error checking there. you want to alert the application/user as close to where the error occurs as you can. my general rule of thumb (not a hard rule) is every place I touch the database, I check for errors. the more error checking the better for you as a developer/maintainer. the down side is more code to write and longer methods. in my opinion the former out weights the later.

the one thing I see in your code is you check to see if the database is connected but dont do anything if it isnt. I would put in an else clause (if the FinanceDB is not connected) to either log that there is an issue or throw a message box or something. you cant tell when the method runs and the database is not connected. this is more for you to see what is happening.

I have a lot of

#If DebugBug Then ... some type of checking and logging ... #EndIF

all through out my database code so when I am debug I can see what is going on. but it doesnt flood the logs/screen/whatever when I have a compiled/GA/Production application.

Thanks again.

I am going to add further error checking to the open code, and also the additional else clause as you point out. I’ve just added Daniels suggestion for extending with the IsConnected, so the ‘else’ will be added to the end of that.

The ‘IsConnected’ is working really well too. I wouldn’t have thought of doing it that way.

Glad to hear it. The reason I check connections this way is because a client/server database operating over a network can open successfully but the connection can be lost at a later time. But that’s not something I call before every operation. My higher level logic is generally crafted so that a database call isn’t going to occur unless the database was successfully opened. If this is a valid assumption then there’s no need to check the connection before every SQL operation as long as you are checking for errors after each operation.

It’s easier to think of this in a situation where each window has its own connection. If the connection failed when the window opened, then I probably notified the user and closed the window. Let’s say it succeeded but the connection is quietly lost 5 minutes later because someone tripped over an Ethernet cable. The user clicks a button which triggers a SQLSelect. As long as I check that SQLSelect for an error there’s really no need to call IsConnected first.

As to the broader question of state: if there were a number of things I had to track about a specific, never-going-to-change type of database connection, and if those things were generic to the database connection itself (and not specific to what the project is doing with that connection), I would subclass that database type and store state in properties of that subclass. The only example I can think of this would be if you added generic SQL capabilities not directly supported by SQLiteDatabase. Or if you wanted to change the way the class works, i.e. overriding all the methods and throwing exceptions on any errors.

If the database connection type had to be flexible, I might subclass the Database base class itself and have it hold an instance of the specific connection type. That’s more work because you would have to provide wrapper methods which called the underlying connection’s methods. This would effectively be the Adapter Pattern even though we’re not really changing the Database class interface.

Now, if I had to track state which was database related but project specific, I might store that state in a class which publicly exposed the underlying connection. Something like…

if myDataServerObject.StateCheck = True then
end if

Which pattern is best just depends on what you’re trying to accomplish. If IsConnected is the only state you have to track then there’s nothing wrong with dealing with the ‘bare’ SQLiteDatabase and using the method I posted. This would be common. I’ve only subclassed a database type or utilized Adapter pattern when the need arose.

As for converting database errors into exceptions without subclasses or Adapter pattern:

Public Sub ThrowError(Extends d As Database)
  If d.Error = False Then Return
  Dim e As New RuntimeException
  e.ErrorNumber = d.ErrorCode
  e.Message = d.ErrorMessage
  Raise e
End Sub

Now you can throw a database error into your normal exception handling with a single line after each operation.

dim r as recordset = d.SQLSelect("some SQL")

d.SQLExecute("some more SQL")

I’ve used all of the above in different projects to get db errors into exceptions, depending on what the other project requirements were.