Code for one of two backend database types.

Hi all,
Creating a test application with sqlite database back end.
Thinking of moving to CubeSQL as an upgrade path for upgrading to multiple users and thought there might be way to use either database as the back end.
They are similar and easy to move between but I am having a problem with being able to connect to one or the other at runtime.
I usually connect when the application opens with a global method and global database property BUT obviously the property needs to be the correct type (either sqliltedatabase or CubeSQLDatabase).
Is there a trick to doing this without having to have two property’s and a lot of if statements every time I want to do anything with the database?
Cheers,
Paul

Use the root class as the variable type, i.e. Db As Database. Then, when opening the database do:

Sub OpenSqliteDatabase()
  Dim db as SqliteDatabase
  // blah, open database, blah
  Self.Db = db
End Sub

Sub OpenCubeDatabase()
  Dim db as CubeDatabase
  // blah, open database, blah
  Self.Db = db
End Sub

Then in your application, you can of course access App.Db.*. Is that what you mean? Once you establish the connection, rarely do you need the advanced methods of the database. The only real reason is if you rely on methods such as LastRowId or something. In that case, you would have to abstract that also when connecting to multiple database types.

Use an interface.

You could define your own database class interface. Then you add database classes implementing that interface, like a “mySQLite” class, or a “myCubeSQL” class and so on. In a module you define a property of your interface type. Then you can instantiate your classes (according to a user choice during login, for instance) and store it in that property. From now on you just use that property in order to access the methods of the chosen database.

For example:

  1. Use Insert, Class Interface and name it myDBInterface
  2. Add method definitions to the interface (just name, parameters and return types. You may want to include everything from the generic database class, plus your application specific methods)
  3. Create new classes for each database type you wish to connect and from its Interfaces list select myDBInterface
  4. Implement each method of the interface (Like SQLExecute or SQLSelect, but also your own, like myDBCreateIndexes, etc)
  5. In a module, create a property of type myDBInterface, like myDB As imDBInterface
  6. Add startup code to your app where the database is selected and then stored: myDB = New mySQLite
  7. Now throughout your application, use this interface to interact with your database, like myDB.SQLSelect(“Select * from mytable”), etc

Like this you can more easily write a GUI, independently from your database. You also can implement other databases for your application by simply adding new classes which implement your myDBInterface.

Hi Jeremy,
Yes that is what I was doing (except using one method) - one global property set as “Database” and a method that sets the property to the correct database type (depending on user settings) and a check to make sure that it connects.
That bit works fine but when the method ends, the connection is closed (not by me) I assume as it goes out of scope.
It works fine if I set the global property as the correct database type in the IDE but not if set in code in the global method.
And yes, the lastrowID tripped me up :slight_smile:

Hi Oliver,
Thanks for the example. Some of that stuff is new to me so I will go and have a search on background info then put it in place. Makes sense but need to have a play.

I will come back with the outcome soon.

Cheers

Paul

Paul,

Something is wrong if the database is being closed, it shouldn’t be closed unless the variable is no longer referenced. In the example I gave above, Self.Db holds a reference to the database so it should remain open. Are you sure you are storing an instance of the database in a Global or at least App scoped variable? Maybe you can post your exact code here.

Hi Jeremy, sure -

There is a global Peoperty - mdb as Datasource
Then there is a global method - mdbConnect with the following code -

If pPrefsDictionary.Value("Datasource") = "SQLITE" Then
    
    Dim dbfile As FolderItem
    Dim mDB AS New SQLiteDatabase
    
    dbfile = GetFolderItem(pPrefsDictionary.Value("FileLocation"))
    mDB.DatabaseFile = dbfile
    
    If mDB.Connect Then
      MsgBox("Database opened")
      mIsConnected = True
    Else
      MsgBox("There was a problem connecting to the SQLite Database File - " + mdb.ErrorMessage + " (" + str(mdb.ErrorCode) + ")")
      mIsConnected = False
    End if
    
  Else
    
    Dim mDB as New CubeSQLServer
    mdb.host = pPrefsDictionary.Value("host")
    mdb.port = pPrefsDictionary.Value("port")
    mdb.Username = pPrefsDictionary.Value("username")
    mdb.password = pPrefsDictionary.Value("password")
    
    If mDB.Connect Then
      MsgBox("Database opened")
      mIsConnected = True
    Else
      MsgBox("There was a problem connecting to CubeSQL - " + mdb.ErrorMessage + " (" + str(mdb.ErrorCode) + ")")
      mIsConnected = False
    End if
    
  End If

This code works fine as the database connects (I have tried both).

Then before I do any other database stuff (updates, selects etc) I run this spinet -

 If mDB Is Nil Then
    mIsConnected = False
  End If

And it is returning false (and recordsets are empty etc) because the database has been closed/disconnected.

However if I make the global property a specific database type (eg “SQLiteDatabase” rather than “Database”) and comment out any CubeSQL specific stuff it all works again.

I also tried replacing “Dim mDB as New SQLiteDatabase” with “mDB = New SQlitedatabase” as I thought it might be the dim in the method causing a scope issue. But that only caused problems with the compiler complaining that lines like mdb.host “is not supported by Database” etc.

Cheers,

Paul

OK, I see the problem. Your local variable mDB shadows your global property mdb as Xojo is not case sensitive. Thus your global property is never set.

Click the menu item “Project > Analysis Warnings” and make sure the warning is enabled named “This property shadows one already defined by Item1”. In fact, on all projects I have every warning enabled except the two “Converting from Item1 to Item2…” and “Performing a Item1 comparison on floating-point…”

With those set, you can Analyze the project periodically, Cmd/Ctrl-K correcting any warnings given. It should show in the above method that mDB shadows an existing property.

Hi Jeremy,

Checked the Analysis warnings and that already has a tick in it and it isn’t giving me an error.

I have also gone through the code and made them identical.

I understand that the Global variable is not set because I am declaring it locally and I have tried without creating a local variable (within the Method) but because the global property is set to “Database” it the compiler chokes with trying to set a host or port to the global variable as they are not part of that data type.

I can’t set a global property without restricting the type which I don’t want to do as there are two types of database I want to access.
If I set the property at runtime in a method its scope will only be the life of the method.

So I am not sure where to go with the code I have created.

I am getting myself acquainted with interfaces to try Oliver’s suggestion as they are new to me and whatever the outcome I probably need to know more about them anyway.

Cheers,

Paul

Paul,

Simply change your Global property from mdb to Db, for example. I am assuming that it is accessible by other classes, thus dropping the “m” which usually indicates a private/protected member property. What I would do (not meaning yours is wrong, just giving my example) is I would create a Db property on the App class that is public. I do this because my database connection is related to my application. My application has one database connection. When the app quits, the Database is no longer accessible (obviously). If the database goes away, my app can no longer function. Thus my Database is tied directly to the Application, or the App class.

So, in your code go ahead and create the local mdb variable. At the end of your method, though, be sure to assign it to the App.Db property.

That really should solve your problem. Interfaces are vital to learn and understand going forward with Xojo but I do not think an Interface is required here for this purpose.

Jeremy,

Ok, create new property App.Db, left the local variable as mDB and left that code to do it’s thing.

I have removed the gloabl variable mDB (is that correct?) but what do you mean assign the local mdb variable to the App.Db property? How do I do that?

Cheers,

Paul

Thanks Jeremy,

It all makes sense now, for anyone who is looking at this in the future, follow the instructions as above and the lest step that confused me was -

App.Db = mDB
Sets the global property to the local.

Tested and all works.

Cheers,

Paul