Listing the Database Table Names

[blueb]

I'm having good success using Pure's database commands.

After opening an ODBC database, I'd like a list of the tables inside this database so I can apply SQL commands to a certain table.

But I can't find any Pure command that will do this... I also cannot find an SQL command that will provide a list of tables in a database.

It seems like a simple thing... I've Googled the topic, but haven't found any suitable answers.

Perhaps this is a command that could be added in the future... GetDatabaseTables()

--blueb

[Oly]

I do not know the purebasic code but the below sql code list the tables in the currently opened database hope this is helpful.

Show Tables;

[blueb]

Sorry Oly,

I'm using the database in the PureBasic example folder as my guide...

Show Tables returns 'Bad Query'

This might be a command that is specific to DB2 or another RDBMS, but it is not 'generic' to all ODBC drivers.

Thanks for the help.

All suggestions are welcome.

--blueb

[ColBoy]

Sorry Oly,

I'm using the database in the PureBasic example folder as my guide...

Show Tables returns 'Bad Query'

This might be a command that is specific to DB2 or another RDBMS, but it is not 'generic' to all ODBC drivers.

Thanks for the help.

All suggestions are welcome.

--blueb

I've not done much using ODBC, but in MS-SQL to get a list of tables you would do the following :

select table_name from information_schema.tables order by table_name

I would imagine most SQL variants have something similar.

[Manolo]

I don`t problemns with table-names from one database.

The solution in MySQL is with one query, example:

SHOW TABLES FROM YouDatabaseName

The show command is only available from mysql, but another databases
have this or similar. Read the especific manuals.

Manolo

[blueb]

Thanks for the help fellas.

I'm trying to build a 'generic' application that can open any ODBC capable database in the user's system. ( I can do this now). This is why I do not want to get tied to one particular program. For example I have SQLite, Firebird, Access, dBase, Pervasive data files on my machine, and they each have their own method to list tables.

What I would like to do now is have the application list the tables in the database that I have opened so that the user can use additional SQL commands to manipulate the data.

I have found the Windows API command - SQLTables() but I have to determine how to implement it properly (it needs 1 or 2 structures).

Thanks again,

--blueb

[collectordave]

Sorry to bump an Old thread but having to do this again and remembered the query builder code.

Knocked this up to kist tables and defined queries in database and fields in selected table or query should be able to list field types as well only SQlite can it be extended to use other DBs as well?

Here Is the Code:

Code: Select all

UseSQLiteDatabase()

Global Window_0

Global btnOpenDB, btnNewDB, strCurrentDB, txtCurrentdB, txtTables, lstTables, txtFields, lstFields, btnDone

Global QueryDBName.s,QueryDB.l

Procedure.s SelectDB()
   
  File$ = OpenFileRequester("Please choose database", "C:\", "Database (*.db)|*.db", 0)
  If File$
    MessageRequester("Information", "You have selected following file:" + Chr(10) + File$, 0)
    ProcedureReturn File$    
  Else
    MessageRequester("Information", "The requester was canceled.", 0) 
    ProcedureReturn "" 
  EndIf
 
EndProcedure

Procedure.l Open_Database(DBName.s)
  ; Procedure to open the requested database and return a handle to it.
  ; User message provided in case of failure to open.
  
  Define DBHnd.l
  
  ;Open Main Database
  QueryDB = OpenDatabase(#PB_Any, DBName.s, #Empty$, #Empty$)  
  If QueryDB = #False
    
    MessageRequester("Database Error","Failed to open database!")  
    SetGadgetText(strCurrentDB,"")
    ProcedureReturn #False
  Else 
    
    SetGadgetText(strCurrentDB,DBName)
    ProcedureReturn QueryDB
  EndIf
  
EndProcedure

Procedure.l ShowTables()
  ; Fills the Tables Listbox with all the available tables in the database.
  
  Define ObjType.s  ; is the type of each entry on the master table.
  
  ClearGadgetItems(lstTables)
  DatabaseQuery(QueryDB, "SELECT * FROM sqlite_master;")
  FirstDatabaseRow(QueryDB)
  ObjType = GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "type"))  
  If ObjType = "table" Or ObjType = "view" 
    AddGadgetItem(lstTables, -1, GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "tbl_name")))  
  EndIf
  
  While NextDatabaseRow(QueryDB)
    ObjType = GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "type"))  
    If ObjType = "table" Or ObjType = "view" 
      AddGadgetItem(lstTables, -1, GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "tbl_name")))  
    EndIf
  Wend
  
  FinishDatabaseQuery(QueryDB) ;free the query

  ProcedureReturn #True
  
EndProcedure

Procedure ShowFields(TableName.s)

  ClearGadgetItems(lstFields)
  Define iloop.i = 0   
  Define txt.s   = ""
  DatabaseQuery(QueryDB, "SELECT * FROM " + TableName + ";")
  FirstDatabaseRow(QueryDB)
  For iloop = 0 To DatabaseColumns(QueryDB) -1
    txt = DatabaseColumnName(QueryDB, iloop)
    AddGadgetItem(lstFields, -1, txt)  
  Next iloop
  FinishDatabaseQuery(QueryDB) ;free the query
  ProcedureReturn #True
  
EndProcedure


  Window_0 = OpenWindow(#PB_Any, 0, 0, 410, 380, "", #PB_Window_SystemMenu)
  btnOpenDB = ButtonGadget(#PB_Any, 10, 10, 70, 20, "Open DB")
  btnNewDB = ButtonGadget(#PB_Any, 90, 10, 70, 20, "New DB")
  strCurrentDB = StringGadget(#PB_Any, 10, 60, 390, 20, "")
  txtCurrentdB = TextGadget(#PB_Any, 10, 40, 120, 20, "Current DB")
  txtTables = TextGadget(#PB_Any, 10, 90, 80, 20, "Tables")
  lstTables = ListViewGadget(#PB_Any, 10, 110, 190, 220)
  txtFields = TextGadget(#PB_Any, 210, 90, 80, 20, "Fields")
  lstFields = ListViewGadget(#PB_Any, 210, 110, 190, 220)
  btnDone = ButtonGadget(#PB_Any, 310, 340, 90, 30, "Done")
  
  Repeat
      
      Event = WaitWindowEvent()
      Select Event
        Case #PB_Event_CloseWindow
          End
  
      Case #PB_Event_Menu
        Select EventMenu()
        EndSelect
  
      Case #PB_Event_Gadget
        Select EventGadget()
            
          Case btnOpenDB
            QueryDBName = SelectDB()
            If QueryDBName > ""
              Open_Database(QueryDBName)
              ShowTables()
              ClearGadgetItems(lstFields)
            Else
              ClearGadgetItems(lstTables)
              ClearGadgetItems(lstFields)          
            EndIf
            
          Case btnDone
            End
            
          Case lstTables
            ShowFields(GetGadgetItemText(lstTables, GetGadgetState(lstTables)))  
            
        EndSelect
    EndSelect
    
  ForEver

regards

cd

[mk-soft]

ExDatabase.pbi (Windows Only)

Link: viewtopic.php?f=12&t=67180