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]
Ralph - You’d need to cast those variable declarations like this:
[code]
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”
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
SQLiteDatabse(Session.db).Prepare(MyStatement)
and
dbSQLite.Prepare(MyStatement)
are acceptable syntax.
Also, be advised that prepared statements are different in CubeSQL. You probably already know that, but FYI:
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
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).