On Database interface?

General
1

Recently I wrote a database application. I decided to connect to a mySQL database and use it.
I decided to use the MySQLCommunityServer class.
All the while thinking to myself that I should use the database class.
Thinking if I wanted to expand then all I would have to do is to make a select statement based on a user preference.
something like:

[code]dim db as Database
const TheActualDatabase as string = “MySQLCommunityServer”

Select Case TheActualDatabase
Case “MSSQLServerDatabase”
db = new MSSQLServerDatabase
Case “MySQLCommunityServer”
db = new MySQLCommunityServer
Case “ODBCDatabase”
db = new ODBCDatabase
Case “OracleDatabase”
db = new OracleDatabase
Case “PostgreSQLDatabase”
db = new PostgreSQLDatabase
Case “SQLiteDatabase”
db = new SQLiteDatabase
Else
MsgBox(“42”)
End Select

if db.connect connect then
end if
[/code]

I was under the impression that there was a database interface and a way to pass an interface to an object.
I looked for the DatabaseInterface but couldn’t find it.

2

@Oliver Osswald wrote one (with a great tutorial video as well). More info in this post:

https://forum.xojo.com/31764-any-tutorial-for-database-interface/p1#p267599

3

If you really want to support multiple databases in the one Xojo app with minimal work, I seriously suggest you use SQLDatabaseMBS — this is what it is designed for.

I have one method to connect to my choice of: SQLite, DB2, PostgreSQL, MySQL, MSSQL or CubeSQL. Once I check it isn’t nil, every line of code is the same (with minor SQL syntax differences on occasion).

My biggest issues relate to the Data Types supported when creating the tables (avoid Booleans by using Integer, BLOB types use different words, etc).

I have separate methods to:

  1. escape text when sent to a non-Prepared Statement
  2. convert the MAX(…, …) command to GREATEST()
  3. duplicate an existing record and return the resulting new primary key
  4. convert the CONCAT(…, …) command to the required new syntax, and
  5. convert the LIMIT command