Using the SQLDatabaseMBS plugin for so many conveniences that it brings, but of course there are differences.
The tables command isn’t supported.
I’m guessing I need to track which database I’m using and issue different sql for each type to get database list, table list, table schema etc?
Do you mean the CREATE TABLE … (id INTEGER, …) command?
I create all my table definitions in SQLite format then parse them at execution time to adjust them for MySQL, MSSQL and PostgreSQL. There is very little that needs to be changed. If I am correct I can give you my code details.
I don’t have every SQL Brand, but here are the four I have populated so far:
Protected Function getTablesWAD(db As SQLDatabaseMBS, SQLBrand As String, databaseName As String) as String()
Var rs As RowSet
Var tempSQL As String
Var tempString As String
Var myTable() As String
Select Case SQLBrand 'define the SQL necessary
Case "DB2"
Case "Firebird"
Case "InterBase"
Case "Informix"
Case "MSSQL"
Try
db.ExecuteSQL("USE [" + databaseName + "]")
Catch Error
CommonWindow.doDisplayErrorWAD(Error.Message, CurrentMethodName, Error.ErrorNumber)
Return myTable
End Try
tempSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';"
Case "MySQL"
tempSQL = "SHOW TABLES IN " + databaseName 'can't use quotes here!
Case "ODBC"
Case "Oracle"
Case "PostgreSQL" 'assumes database is connected already
tempSQL = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"
Case "SQLbase"
Case "SQLite"
tempSQL = "SELECT name FROM sqlite_master WHERE type = 'table' and not name LIKE 'sqlite_%' "
Case "Sybase"
End Select
Try
rs = db.SelectSQL(tempSQL) 'do the table lookup
Catch Error
CommonWindow.doDisplayErrorWAD(Error.Message, CurrentMethodName, Error.ErrorNumber)
Return
End Try
While Not rs.AfterLastRow 'build the table array
Select Case SQLBrand
Case "DB2"
Case "Firebird"
Case "InterBase"
Case "Informix"
Case "MSSQL"
myTable.AddRow(rs.Column("TABLE_NAME").StringValue)
Case "MySQL"
myTable.AddRow(rs.ColumnAt(0).StringValue)
Case "ODBC"
Case "Oracle"
Case "PostgreSQL"
myTable.AddRow(rs.ColumnAt(0).StringValue)
Case "SQLbase"
Case "SQLite" 'only one database
myTable.AddRow(rs.Column("name").StringValue)
Case "Sybase"
End Select
rs.MoveToNextRow
Wend
Return myTable
End Function