Android Question SQL not finding "Table" SOLVED

Discussion in 'Android Questions' started by Roger Daley, Jun 26, 2019.

Similar threads

B4A Tutorial CharSequence / CSBuilder Tutorial
B4A Question Changing name of "name.db" in SQL. (Solved)
B4A Code Snippet [B4X] GUID
B4A Tutorial Text files
B4A Tutorial [B4X] SQL with Wait For
  1. Roger Daley

    Roger Daley Well-Known Member Licensed User

    Hi All,

    Another problem with SQL.

    First up, the App was running and then I did decided to check a clean install.
    I deleted all entries, uninstalled the App from the phone and installed it as a completely new App.
    I am now locked in to this error.
    I have tried editing the Table, tried bringing in a database file from elsewhere and editing it. No go.

    I read that the program can't find the "sites" table although it does show in DB Browser.

    NOTE1: The code around SQLLite is largely taken from Klaus' SQLliteLight2 example. This is not the problem, I just note that so you may reference the original.

    NOTE2: My App is too large to upload completely, I can't make a simple example because I would need to know the problem to duplicate it. Catch 22

    Below is the following Code around the problem.
    ShowTable writes the string "Query" which is passed to ExecuteHtml(SQL1,Query, Null, True)

    Private Sub ShowTable
    Private Query As String
        Query = 
    "SELECT SiteName As [Site Name], Longitude As [Longitude], Latitude as [Latitude] FROM sites ORDER BY UPPER(SiteName) ASC"

    'displays the database in a table
        wbvTable.LoadHtml(ExecuteHtml(SQL1, Query, NullTrue))   
    End Sub
    Private Sub ExecuteHtml(SQL As SQL, Query As String, StringArgs() As String, Clickable As Boolean) As String
    Private ResultSet1 As ResultSet
    If StringArgs <> Null Then
            ResultSet1 = 
    SQL.ExecQuery2(Query, StringArgs)
            ResultSet1 = 
    SQL.ExecQuery(Query)     'This is B4A line 2571,  the line that throws the error.
        End If
    Private sb As StringBuilder
    "<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
    For i = 0 To ResultSet1.ColumnCount - 1
    Private row As Int
        row = 
    Do While ResultSet1.NextRow
    If row Mod 2 = 0 Then
    "<tr class='odd'>")
    End If
    For i = 0 To ResultSet1.ColumnCount - 1
    If Clickable Then
    "<a href='http://").Append(i).Append(".")
    End If
            row = row + 
    Return sb.ToString
    End Sub
    This is a screen shot of sites.db in DB Browser.

    Obviously I am again out of my depth so any assistance will be appreciated. My only alternative is to go back to the previous working version and start over.

    Regards Roger
  2. DonManfred

    DonManfred Expert Licensed User

    Upload a small project which shows the issue. Make sure to include the DB.
    Peter Simpson likes this.
  3. Roger Daley

    Roger Daley Well-Known Member Licensed User


    From the original post:
    "NOTE2: My App is too large to upload completely, I can't make a simple example because I would need to know the problem to duplicate it. Catch 22"
  4. DonManfred

    DonManfred Expert Licensed User

    no problem. Create a small project only containing the database and the code to read data. Upload this project.

    If you changed the databasestructure; make sure you also did the changes in the DB on he device.
    Usually you copy the initial db from the assets to dirinternal when the database is not available here.
    After a update the db i already at dirinternal and no new db is copied.

    We hardly can´t help with the information you provide. I´m out here; good luck.
    Peter Simpson and Roger Daley like this.
  5. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    Can you show the code to create the dB tables.
    Roger Daley likes this.
  6. klaus

    klaus Expert Licensed User

    How do you initialize the SQL object?
    As DonManfred already pointed out, do you copy the original db from DieAssets to another folder on the device, and which one?
    Roger Daley likes this.
  7. Roger Daley

    Roger Daley Well-Known Member Licensed User

    Thanks Gentlemen,

    I have found a solution. I went to the recycle bin and recovered a copy from 2 days ago. It is working and I have made the changes. I can't see anything to cause the issue but will move on.

    Klaus, I have plagiarised your SQL2 example, so it was copying an existing db.
    Donmanfred/Jmu5667 I tried for a small code example but could no recreate the problem.
  8. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    There is a simple solution available to modifying a DB table, that is adding new fields, modify DButils with the following code:

    public Sub CreateTable(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
    Dim pk As String, startMap As Int

    ' // create the table
       If Not(TableExist(SQL,TableName)) Then
    ' // start map at 1
           startMap = 1
    ' // set primary key
           If FieldsAndTypes.GetKeyAt(0) = PrimaryKey Then
               pk = 
    End If
    ' // create table and first col
           SQL.ExecNonQuery("CREATE TABLE [" & TableName & "] (" & _
    0) & " " & _
    0)& pk & ")")
    End If
    ' // add rest of cols to table
       For i = startMap To FieldsAndTypes.Size - 1
    Dim field, ftype As String
           field = FieldsAndTypes.GetKeyAt(i)
           ftype = FieldsAndTypes.GetValueAt(i)
           pk = 
    If field = PrimaryKey Then
               pk = 
    End If
    If Not(FieldExists(SQL,TableName,field)) Then
    ' // add the colum       
    SQL.ExecNonQuery("ALTER TABLE [" & TableName & "] ADD COLUMN [" & field & "] " & ftype & pk)
    Log("CreateTable() " & field & " already exists")
    End Try
    End If

    End Sub

    public Sub FieldExists(SQL As SQL, TableName As String, TableField As StringAs Boolean
    Dim result As Boolean
       result = 

    Dim t As List
       t = ExecuteMemoryTable(
    SQL"PRAGMA table_info ('" & TableName & "')"Null0)

    For i = 0 To t.Size - 1
    Dim values() As String
           values = t.Get(i)

    If values(1).ToLowerCase = TableField.ToLowerCase Then  ' case insensitive
               result = True
    End If

    Return result
    End Sub
    Hope this is of help.


  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice