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

Discussion in 'Code Snippets' started by KMatle, Nov 9, 2017.

  1. KMatle

    KMatle Expert Licensed User

    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


    Code:
    Sub GetTablePragmas(DBDir As String, DBName As StringAs 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:

    Code:
    Dim TableInfo As List
        TableInfo.Initialize
        TableInfo=GetTablePragmas(Dir,DbName)
     
    Last edited: Nov 9, 2017
  2. Erel

    Erel Administrator Staff Member Licensed User

    Two tips:
    1. Add [B4X] to threads that are "cross platform". Such threads are returned even when the product filter is set.
    2.
    Code:
    Dim SQLP As SQL
    #if B4J
    SQLP.InitializeSQLite(DBDir, DBName , True)
    #Else
    SQLP.Initialize(DBDir, DBName , True)
    #End If
     
    KMatle likes this.
  3. KMatle

    KMatle Expert Licensed User

    Updated now :)
     
    Erel likes this.
  4. Mashiane

    Mashiane Well-Known Member Licensed User

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

    OliverA Well-Known Member Licensed User

  6. Mashiane

    Mashiane Well-Known Member Licensed User

  7. mmieher

    mmieher Member Licensed User

    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

    Code:
    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
     
  8. mmieher

    mmieher Member Licensed User

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

    Mashiane Well-Known Member Licensed User

    This is an example of my pragma method..

    Code:
    'get all column names from a table
    private Sub GetTableTextColumnNames(jsql As SQL, tblName As StringAs 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.

    pragma.png
     
  10. Mashiane

    Mashiane Well-Known Member Licensed User

    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..
    Code:
    Public Sub ExecuteMaps(jSQL As SQL, Query As String, StringArgs() As StringAs 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
     
  11. OliverA

    OliverA Well-Known Member Licensed User

    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.

    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?
     
    Last edited: Dec 6, 2017
  12. Mashiane

    Mashiane Well-Known Member Licensed User

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

    Code:
    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
     
  13. mmieher

    mmieher Member Licensed User

    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.
     
  14. Mashiane

    Mashiane Well-Known Member Licensed User

    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.
     
    OliverA and mmieher like this.
Loading...