SQLDatabaseMBS "Tables"

Databases
1

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?

Is there a shortcut?

2

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.

3

Nope. I mean to list tables.

4

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
7 Likes
5

That’s a great piece of code! Thanks.

1 Like
6

David…
Well you are certainly on my Christmas card list now!
Thank you.

1 Like
7

My work here is done [drops mic and walks off]

3 Likes
8

Awesome, works like a charm. Thank you, David!!

1 Like
9

This may need some refinement when people use schemas in PostgreSQL. I know people who don’t use the public schema at all.