B4J Question SQL: List Tables in a ListView?

Discussion in 'B4J Questions' started by UFOCoder13, Jul 29, 2015.

  1. UFOCoder13

    UFOCoder13 Well-Known Member Licensed User

    I'm working on my next app and I have a SQL DB that I need to list the tables in the DB in a ListView. This seems to be the only aspect of my app that I'm stuck on.

    Any ideas?
     
  2. derez

    derez Expert Licensed User

    Here is an example:
    Code:
    Sub make_item
    Dim p As AnchorPane
    p.Initialize(
    "p")
    p.LoadLayout(
    "listitem")
    For Each v As Node In p.GetAllViewsRecursive
        
    If v Is ImageView Then
            
    If res.GetInt("Pic") = 1 Then
                
    Dim iv As ImageView = v
                
    Dim iconimg As Image
                iconimg.Initialize(
    File.DirApp & "\jpg", res.GetInt("No") & ".jpg")
                iv.SetImage(iconimg)
            
    End If
        
    End If
        
    If v Is Label Then
            
    Dim l As Label = v
            l.Text = res.GetString(
    "Surname")  & "    " & res.GetString("Name")
            l.Style = 
    "-fx-font-size: x-large"
            
    If res.GetInt("Sex") = 1 Then
                l.TextColor = fx.Colors.blue
            
    Else
                l.TextColor = fx.Colors.red
            
    End If
        
    End If
    Next
    lview.Items.Add(p)
    listno.Add(res.GetInt(
    "No"))
    End Sub
    The listview contain items which are based on "listitem" layout which include an anchorpane with label and imageview.
    The application has (already, prior to this sub) provided res with the outcome of a querry.
    This is inhabited into the labels and the images.

    the result looks like the attached picture.
     

    Attached Files:

    Last edited: Jul 29, 2015
  3. rwblinn

    rwblinn Well-Known Member Licensed User

    Hi,

    this is an example to list the tables stored in a SQLite database in a Listview:

    Get the list of tables for a database
    Code:
    Sub Database_GetTables As List
      
    Dim result As List
      result.Initialize
      
    Dim cursor As ResultSet
      
    Dim cmd As String = "SELECT * FROM sqlite_master WHERE TYPE=""table"" ORDER BY name;"
      
    Try
          
    cursor = gSQL.ExecQuery (cmd)
          
    Do While cursor.NextRow
            result.Add(
    cursor.GetString("name"))
          
    Loop
      
    Catch
          
    Log("ERROR: " & LastException.Message)
        
    End Try
      
    Return result
    End Sub
    Add the tables to the Listview (declared as Private lvwDBTables As ListView)
    Code:
    lvwDBTables.Items.Clear
    lvwDBTables.Items.AddAll(Database_GetTables)
    The listview_selection change event can be used to retrieve the table content using SQLQuery.

    Hints:
    When using SQLite, do not forget to add the SQLite external library, stored in the B4J addittional library folder, as an external jar in the Project Properties
    Code:
    #AdditionalJar: sqlite-jdbc-3.7.2
    and to add the jSQL library and a global SQL object (like Private gSQL As SQL)
     
Loading...