Android Code Snippet [B4X] [SQLite] Get all tables, column names and definitions of a db

This snippet gets all tables, column names and definitions (e.g. INTEGER, TEXT, etc.) and returns a list with maps.

It is good for:

- when you want a dynamic layout (e.g. ONE Activity to display & edit several tables) it is good to know all the parms before you display the data (like number of columns, columnnames, etc.)
- formatting the data you display (e.g. you want to format the column values it is good to know which format it has before you display it -> REAL values = 23.0 -> 23.00)
- when you insert/update a row you need to know the format

Sub GetTablePragmas(DBDir As String, DBName As String) As List
    Dim SQLP As SQL
    #if B4J
         SQLP.InitializeSQLite(DBDir, DBName , True)
    #Else
         SQLP.Initialize(DBDir, DBName , True)
    #End If
    Dim ReturnList As List
    ReturnList.Initialize
    Private DBQuery, TBQuery As String
    Dim TBName, ColumnName, ColumnType As String
    Log("DB-Name: " & DBName)
    DBQuery = "Select name FROM sqlite_master WHERE Type='table' ORDER BY name"
    Dim RSDB As ResultSet=SQLP.ExecQuery(DBQuery)
    Do While RSDB.NextRow
        TBName=RSDB.GetString2(0)
        Log("   Table: " & TBName)
        If TBName.StartsWith("SQlite") = False Then
            Dim TBMap As Map
            TBMap.Initialize
            TBMap.Put("tbname",TBName)
            TBQuery = "PRAGMA table_info(" & TBName & ")"
            Dim TBRS As ResultSet=SQLP.ExecQuery(TBQuery)
            Dim ColumnParmsMap As Map
            ColumnParmsMap.Initialize
            Do While TBRS.NextRow
                For i=0 To TBRS.ColumnCount-1
                    Log ("     " & i & ": " &  TBRS.GetColumnName(i) & "->" & TBRS.GetString2(i))
                    ColumnParmsMap.Put(TBRS.GetColumnName(i), TBRS.GetString2(i))
                Next
            Loop
            TBMap.Put("columnparms",ColumnParmsMap)
            ReturnList.Add(TBMap)
            TBRS.Close
        End If
    Loop
    RSDB.Close
    SQLP.Close
    Return ReturnList
End Sub

Call it:

Dim TableInfo As List
    TableInfo.Initialize
    TableInfo=GetTablePragmas(Dir,DbName)

 

[Erel]

Two tips:
1. Add [B4X] to threads that are "cross platform". Such threads are returned even when the product filter is set.
2.

Dim SQLP As SQL
#if B4J
SQLP.InitializeSQLite(DBDir, DBName , True)
#Else
SQLP.Initialize(DBDir, DBName , True)
#End If

 

[KMatle]

Add [B4X] to threads that are "cross platform". Such threads are returned even when the product filter is set

Updated now

 

[Mashiane]

Nice, perhaps also explore MySQL with similar functionality in mind. Would be useful or is there a DBUtils for MySQL available here?

 

[OliverA]

is there a DBUtils for MySQL available here

There is, but it does not offer the above functionality, may only be B4J specific and has not been updated to the latest DBUtils 2+ (https://www.b4x.com/android/forum/threads/module-universal-dbutils.80569/).

 

[Mashiane]

Thanks @OliverA

 

[mmieher]

Thanks, KMatle!

This is darn close to what I want. I am new to DBUtils and am having a heck of time figuring out which column names are in which index position.

This works but TBName.RSDB.GetString2(0) always returns the value of the first column in the query. I had to hard-code TBNAME to make this work. This is probably because I raped your code.

Any idea what I am doing wrong?

Thanks,
Marc

Sub LogIRecord(inSQL As String)
  
    '  inSql is "SELECT * FROM pmenuitems WHERE mcatID = 12 ORDER BY msort"
  
    Private DBQuery, TBQuery As String
    Dim TBName As String

    DBQuery = inSQL

    Dim RSDB As ResultSet=SQL1.ExecQuery(DBQuery)

    Do While RSDB.NextRow
  
        TBName=RSDB.GetString2(0)    '    This keeps returning value in first column of recordset
        TBName = "pmenuitems"
      
        Log("   Table: " & TBName)
  
        TBQuery = "PRAGMA table_info(" & TBName & ")"
        Dim TBRS As ResultSet=SQL1.ExecQuery(TBQuery)
        Do While TBRS.NextRow
          
            Log("Column Pos  " & 0 & ": " &  TBRS.GetColumnName(0) & "->" & TBRS.GetString2(0))
            Log("Column Name  " & 1 & ": " &  TBRS.GetColumnName(1) & "->" & TBRS.GetString2(1))
            Log("  ")
          
        Loop

    Loop
  
End Sub

 

[mmieher]

DOH! Column order is from CREATE TABLE. Still curious about TBNAME issue abovr.

 

[Mashiane]

This is an example of my pragma method..

'get all column names from a table
private Sub GetTableTextColumnNames(jsql As SQL, tblName As String) As List
    Dim strFld As String
    Dim fType As String
    Dim curFields As List
    Dim cur As ResultSet
    curFields.Initialize
    cur = jsql.ExecQuery("PRAGMA table_info ('" & tblName & "')")
    Do While cur.NextRow
        strFld = cur.GetString("name")
        fType = cur.GetString("type")
        If fType.ToLowerCase = "text" Then curFields.Add(strFld)
    Loop
    cur.close
    Return curFields
End Sub

The method will return the same type of information anytime you run it. You can refer the field names of the table directly with .GetString, the columnnames are on the name field and their types in type etc etc.

 

[Mashiane]

Thanks, KMatle!

This is darn close to what I want. I am new to DBUtils and am having a heck of time figuring out which column names are in which index position.

This works but TBName.RSDB.GetString2(0) always returns the value of the first column in the query. I had to hard-code TBNAME to make this work. This is probably because I raped your code.

Any idea what I am doing wrong?

Thanks,
Marc

Sub LogIRecord(inSQL As String)
 
    '  inSql is "SELECT * FROM pmenuitems WHERE mcatID = 12 ORDER BY msort"
 
    Private DBQuery, TBQuery As String
    Dim TBName As String

    DBQuery = inSQL

    Dim RSDB As ResultSet=SQL1.ExecQuery(DBQuery)

    Do While RSDB.NextRow
 
        TBName=RSDB.GetString2(0)    '    This keeps returning value in first column of recordset
        TBName = "pmenuitems"
     
        Log("   Table: " & TBName)
 
        TBQuery = "PRAGMA table_info(" & TBName & ")"
        Dim TBRS As ResultSet=SQL1.ExecQuery(TBQuery)
        Do While TBRS.NextRow
         
            Log("Column Pos  " & 0 & ": " &  TBRS.GetColumnName(0) & "->" & TBRS.GetString2(0))
            Log("Column Name  " & 1 & ": " &  TBRS.GetColumnName(1) & "->" & TBRS.GetString2(1))
            Log("  ")
         
        Loop

    Loop
 
End Sub

What are you trying to achieve here? You are looping through each record in the table and then running a pragma statement inside that too? If you wanted to return a list of records from the database as a map that will have map keys as field names, you can use executemaps..

Example of ExecuteMaps..

Public Sub ExecuteMaps(jSQL As SQL, Query As String, StringArgs() As String) As List
    If Query.trim.ToLowerCase.StartsWith("select ") Then
    else If Query.trim.ToLowerCase.StartsWith("pragma ") Then
    else If Query.trim.ToLowerCase.StartsWith("show ") Then
    else If Query.trim.ToLowerCase.StartsWith("describe ") Then
    else If Query.trim.ToLowerCase.StartsWith("insert ") Then
    else If Query.trim.ToLowerCase.StartsWith("update ") Then
    else If Query.trim.ToLowerCase.StartsWith("delete ") Then
    Else
        Query = $"select * from ${Query}"$
    End If
    Dim lst As List
    lst.Initialize
    Try
        Dim res As Map
        Dim cur As ResultSet
        If     StringArgs <> Null Then
            cur = jSQL.ExecQuery2(Query, StringArgs)
        Else
            cur = jSQL.ExecQuery(Query)
        End If
        Do While cur.NextRow
            res.Initialize
            For i = 0 To cur.ColumnCount - 1
                Dim fValue As String = cur.GetString2(i)
                fValue = FixNull(fValue)
                res.Put(cur.GetColumnName(i).ToLowerCase, fValue)
            Next
            lst.Add(res)
        Loop
        cur.Close
        Return lst
    Catch
        Log(Query)
        Log("ExecuteMaps: " & LastException.Message)
        Return lst
    End Try
End Sub

Usage dim lstRecords as List = DbUtils.ExecuteMaps(SQL,"Contacts",null) then

for each recMap as map in lstRecords
dim cfirstname as string = recmap.getdefault("firstname","")
etc etc
next

 

[OliverA]

TBName=RSDB.GetString2(0) ' This keeps returning value in first column of recordset

There is nothing wrong with the return value of that code. It will always return the value contained in the first column of a row of a recordset. Nothing (that I'm aware off) returns the table name(s) that is/are involved in creating the recordset (please remember that a SQL query may involve 1 or more tables, subqueries, etc.). So what is the purpose here? To determine the column names? You can do that already with GetColumnName. If you query only uses 2 of 20 columns of a table, then what is the purpose of pulling all column names with the PRAGMA command? The way you are using PRAGMA assumes that all queries are made up of simple single table query that returns all columns of that table.

DbUtils.ExecuteMaps

How is this better than just using the GetDouble, GetInt, GetString, etc. methods that use column names instead of GetDouble2, GetInt2, GetString2 methods that use column positions of the resultset?

 

[Mashiane]

My question is related to post #7 what is the user trying to achieve?

Do While RSDB.NextRow
 
        TBName=RSDB.GetString2(0)    '    This keeps returning value in first column of recordset
        TBName = "pmenuitems"
      
        Log("   Table: " & TBName)
 
        TBQuery = "PRAGMA table_info(" & TBName & ")"
        Dim TBRS As ResultSet=SQL1.ExecQuery(TBQuery)
        Do While TBRS.NextRow
          
            Log("Column Pos  " & 0 & ": " &  TBRS.GetColumnName(0) & "->" & TBRS.GetString2(0))
            Log("Column Name  " & 1 & ": " &  TBRS.GetColumnName(1) & "->" & TBRS.GetString2(1))
            Log("  ")
          
        Loop

    Loop

 

[mmieher]

Thanks for everyone's replies. In answer to what I was trying to achieve, well I am embarrassed to admit how new I am at DBUTILS. I was trying to figure out which fields were in which columns. Too ignorant to realize it is the order specified in the CREATE statement.

 

[Mashiane]

There isnt a need to be embarassed, we all start by being new to things and learn as we go along and I am sure you will get all the help you need from the forum. I ask questions too when I dont understand or lost about stuff and would encourage you to do so too as much as you need. All the best with your coding.