Writing code that branches between different DB types

I have a Web app that I initially coded to directly access a SQLite database, but which I later coded to access that SQLite database via CubeSQLServer. When I made that change, I kept the SQLite lines in there for future use (commented out, of course), just in case I change back to direct access to the database.

Now I’d like to rewrite that code so that I can more easily switch back to direct SQLite access if needed in the future. Currently, if I make that switch, I would have to go into each method that uses Prepared Statements and uncomment the appropriate SQLite lines and comment out the corresponding CubeSQLServer lines (CubeSQLServer does a PS differently than SQLite). I was hoping code like this would work in such methods:

If Session.DB IsA SQLiteDatabase Then ... ElseIf Session.DB IsA CubeSQLServer Then ... End If

This way I would only have to change code in only one place to make switch (the Session.DB property).

But that fails during compilation. E.g., if Session.DB IsA CubeSQLServer, then the PS the lines in the first branch above will fail during compilation. Is there a way to accomplish what I’m trying to do here?

You should be able to have them side by side without commenting them out. You will probably have to cast the cubeSQL database before using it though as it uses different PreparedStatements.

[code]If Session.DB IsA SQLiteDatabase Then
dim dbSQLite as SQLiteDatabase = Session.db
//do prepared statements here

ElseIf Session.DB IsA CubeSQLServer Then
dim dbCube as CubeSQLServer = Session.db
//do prepared statements here
End If[/code]

Hmmm…when I do that I still get the same problem. If property Session.db is CubeSQLServer, then

dim dbSQLite as SQLiteDatabase = Session.db

will spit out a compilation error that tells me this is a type mismatch, expecting class SQLiteDatabase but getting class CubeSQLServer.

Ralph - You’d need to cast those variable declarations like this:

Session.db = new CubeSQLServer

if Session.db isa SQLiteDatabase then
dim sqdb as SQLiteDatabase = SQLiteDatabase(Session.db)
MsgBox “hooray!”
elseif Session.db isa CubeSQLServer then
dim cudb as CubeSQLServer = CubeSQLServer(Session.db)
MsgBox “hurrah!”
end if[/code]

This code assumes that Session.DB’s type is “Database”

Public db As Database

Just before Anthony replied, I tried this:

[code]If Session.DB IsA SQLiteDatabase Then
dim dbSQLite as SQLiteDatabase = SQLiteDatabse(Session.db)
//do prepared statements here

ElseIf Session.DB IsA CubeSQLServer Then
dim dbCube as CubeSQLServer = CubeSQLServer(Session.db)
//do prepared statements here
End If[/code]

It seems to work. Looks like the same idea.

FYI - You can just use Session.DB as your variable and cast it wherever it’s used.

To the best of my knowledge, it’s simply a preference and shouldn’t have impact on performance. (someone with deeper knowledge of the compiler than I could verify that)

In other words both




are acceptable syntax.

Also, be advised that prepared statements are different in CubeSQL. You probably already know that, but FYI:


I believe that’s still how CubeSQL prepared statements work. (it’s an older post)

Thanks to all here. Casting solves this nicely.

FYI I made a class MyDatabase, with properties one for each database type
then I made each method I want to have in my class like sqlselect, sqlexecute, open, connect, …
and I dispatch for the database that is associated to mydatabase class
then all the rest of the application call the mydatabase methods.

then, later, ifyou want to add another database, you only have to modify the mydatabase class, all the app will work with no other change. you will save a lot of time in maintenance.

My approach is to define an interface IDatabase listing the methods required be the app. , then provide helper classes for each supported database that implements the interface and a factory that returns the correct instance depending on configuration.

Thanks, Jean-Yves and James. Both sound like great ideas. For now, I’m just changing each method that uses PS lines like this:

If Session.DB IsA SQLiteDatabase Then Dim ps As SQLitePreparedStatement ps = SQLiteDatabase(Session.DB).Prepare(sql) . . . etc . . . Else If Session.DB IsA CubeSQLServer Then Dim ps As CubeSQLVM ps = CubeSQLServer(Session.DB).VMPrepare(sql) . . . etc . . . End If

As James said : I’d recommend using an interface

Sample project here:

Tutorial video here:


What a nice video! Totally relevant to me here. Thank you.


You may want to try our latest version of SQLdeLite: https://forum.xojo.com/35079-sqldelite-version-2-ios-compatible-open-source

With one query you can use cubeSQL or SQLite interchangeably. It handles all the mechanics of prepared statements for you. Your queries might have to change for MySQL and other database servers but still it relieves you of a lot of the work involved.

Thanks, Phillip. I’ll definitely try SQLdeLite again. I tried it a while ago, and had problems implementing it. But I was brand new to Xojo then, so I’ll take another stab at it now that I have more Xojo experience under my belt.

The first version was a little over-complicated that provided some very very niche features like migrations. Turns out even I didn’t use those 99% of the time so v2 is focused solely on making queries less annoying.

I’m really liking the new SQLdeLite. For instance, here’s what Prepared Statement coding now looks like for me when querying a database via 4 fields in the Agents table, where the user entered their search string in SearchField:

Dim row As New SQLdeLite.Record Dim searchString As String = "%" + SearchField.Text + "%" row.FirstName = searchString.ToText row.MiddleName = searchString.ToText row.LastName = searchString.ToText row.Email = searchString.ToText Dim sql As Text sql = "SELECT * FROM Agents WHERE FirstName LIKE $Firstname OR " +_ MiddleName LIKE $MiddleName OR LastName LIKE $LastName OR Email LIKE $Email" rs = Session.DB.SQLdeLiteSelect(sql, row)

That works for both SQLite and CubeSQLServer, and others if I want (like PostgreSQL). Very short and elegant when contrasted with what I’d have to do normally (branching between SQLite and CubeSQLServer code, and using 8 Bind lines for SQLite and 4 Bind lines for CubeSQLServer).