Workaround for removed RealSQLDatabase in 2025r3?

General
1

We have some legacy apps that would take significant time to re-write that are using RealSQLDatabase class. These are commercial apps that have thousands of users.

When trying to run these apps in 2025r3 we get a huge number of errors because they removed RealSQlDatabase class. Anyone have an idea for a workaround other than remaining in 2025r2?

2

EDIT:

sqlitedatabse is current in Xojo

Please follow the other suggestions in this thread

Sorry I misread and thought you were referring to RealSQLServer that was a thing for a few years

3

It was replaced by SQLiteDataBase so many years ago (before 2013).

That said, SQLiteDataBase is the way to go IMHO.

And while you are at it, check for other/possible old technologies that have a replacement to plan it / replace it now before these will be removed. Look at the Release Notes / in the Documentation for Deprecated entries.

PS: I was surprised (last year) when I saw it was still in the code / working. I saw an example from an old REALbasic or Real Studio book and I copy/pasted it in Xojo… and that was working.

4

Oh, I haven’t read about that yet :wink:

Anyway - you could just use SQLiteDatabase.
As far as I remember it’s about 97% the same as REALSQLDatabase used to be (plus all the API 2 support).
We’ve switched somewhen to use SQLiteDatabase - even in all those Projects that are still on API 1. And I don’t remember this as a difficult change (basically just switch the class).

1 Like
5

The problem is we have a number of commercial applications that were written around the 2014 time frame. These are widely used throughout the USA. We have since started using SQLiteDatabase but changing these legacy applications would require hundreds of hours of work because of dependencies were created in modules and classes. And we can’t change one app, we’d have to change them all because of the dependencies.

6

The usual workaround for such problems is a wrapper. Make an SQLite subclass with the name of RealSQLDatabase and map the interface of the old RealSQLDatabase to SQLite. Then you only need to change one class and not everything.

6 Likes
7

…or continue using the old IDE until you’ve converted that code

3 Likes
8

That is a really good idea, but there are a number of issues that I’ve already run into and I barely got started.

Transactions - SQLiteDatabase’s require a transaction (begin, commit, rollback) and you’d have to go through the code and add this wherever you are changing data.

Rowsets - if you add a method to handle the differences between SQLSelect (realsqldatabase) and Select SQL (sqlitedatabase), you have to change all your rowsets to use rowset.column().value because it will no longer accept rowset.field().Value

9

What people are suggesting is that you create a “shim” or wrapper which does this for you, something like this:

Public Function Field(extends rs as RowSet, name as String) As DatabaseColumn
  // extension method to implement .field()

  return rs.Column(name)

End Function

This way, you don’t have to change all your existing code.

Are you familiar with Extends?

2 Likes
10

Not true - transactions are optional.

Here’s code which creates a new SQLite database, creates a table, and adds 2 rows to it - no Transactions are used:

Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child("MyDatabase.sqlite")
Try
db.CreateDatabase
Catch error As IOException
MessageBox("The database file could not be created: " + error.Message)
End Try

Var sql As String = "DROP TABLE People"
db.ExecuteSQL(sql)

sql = "CREATE TABLE People (id INTEGER PRIMARY KEY, Name TEXT);"
Try
db.ExecuteSQL(sql)
MessageBox("People table created successfully.")
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

var dbr as DatabaseRow
dbr = new DatabaseRow
dbr.column("Name") = "Alice"
db.AddRow("People", dbr)

dbr = new DatabaseRow
dbr.column("Name") = "Bob"
db.AddRow("People", dbr)

sql = "SELECT * from People"
var rs as RowSet = db.SelectSQL(sql)
var n as integer = rs.RowCount

// n = 2 People

Then in Terminal:

sqlite3 ~/Desktop/MyDatabase.sqlite "SELECT * from People"

1|Alice

2|Bob
1 Like
11

Well yes - if you’re going to convert from REALSQLDatabase(API 1) to SQLiteDatabase (API 2) - you’ll need to touch quite a bit.

If you use SQLiteDatabase (API 1), then…
…just change the first line to: Dim db As New SQLiteDatabase and see yourself:

Dim db As New REALSQLDatabase
Call db.connect
db.SQLExecute("CREATE TABLE Test (id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT)")
db.SQLExecute("INSERT INTO Test (Name) VALUES ('Test1')")
db.SQLExecute("INSERT INTO Test (Name) VALUES ('Test2')")
Dim s() As String
Dim rs As RecordSet = db.SQLSelect("SELECT * FROM Test")
rs.MoveFirst
While (Not rs.EOF)
  s.Append(rs.Field("Name").StringValue)
  rs.MoveNext
Wend
MsgBox Join(s, ", ")
1 Like
12

You do not need to use transactions.

You can continue to use SQLSelect as always, with no change to your code.

SQLiteDatabase should be a drop-in replacement.

1 Like
13

I got sidetracked for a while working on some other legacy projects written in another language. I did create a RealSQLDatabase with only a few methods where necessary and added this to a project.

I’ve had some success with one project that was pretty easy to convert, but a much more complicated one created errors with a database error stating “no transaction started” when it hit a db.commit. As a test, I added a db.beginTransaction before the execute command and it resolved the issue, so I’m not sure if it is correct that you don’t need to use transactions when switching to sqlitedatabase.

14

You need a BeginTransaction if you use Commit, however, neither is required.

Commit does nothing without a transaction. Its only job is to make all the statements in the transaction permanent.

1 Like