Android Question SQL not finding "Table" SOLVED

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.

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
main_executehtml (B4A line: 2571)
ResultSet1 = SQL.ExecQuery(Query)
android.database.sqlite.SQLiteException: no such table: sites (code 1): , while compiling: SELECT SiteName As [Site Name], Longitude As [Longitude], Latitude as [Latitude] FROM sites ORDER BY UPPER(SiteName) ASC
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(
at android.database.sqlite.SQLiteConnection.prepare(
at android.database.sqlite.SQLiteSession.prepare(
at android.database.sqlite.SQLiteProgram.<init>(
at android.database.sqlite.SQLiteQuery.<init>(
at android.database.sqlite.SQLiteDirectCursorDriver.query(
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(
at android.database.sqlite.SQLiteDatabase.rawQuery(
at anywheresoftware.b4a.sql.SQL.ExecQuery2(
at anywheresoftware.b4a.sql.SQL.ExecQuery(
at horsetrailer.B4A.AntennaBearingTool.main._executehtml(
at horsetrailer.B4A.AntennaBearingTool.main._showtable(
at horsetrailer.B4A.AntennaBearingTool.main._activity_resume(
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(
at anywheresoftware.b4a.BA.raiseEvent(
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, Null, True))   
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
    sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
    For i = 0 To ResultSet1.ColumnCount - 1
    Private row As Int
    row = 0
    Do While ResultSet1.NextRow
        If row Mod 2 = 0 Then
            sb.Append("<tr class='odd'>")
        End If
        For i = 0 To ResultSet1.ColumnCount - 1
            If Clickable Then
                sb.Append("<a href='http://").Append(i).Append(".")
            End If
        row = row + 1
    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


Licensed User
My App is too large to upload completely
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.


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

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.


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 = " PRIMARY KEY"
       End If
       ' // create table and first col
       SQL.ExecNonQuery("CREATE TABLE [" & TableName & "] (" & _
                   FieldsAndTypes.GetKeyAt(0) & " " & _
                   FieldsAndTypes.GetValueAt(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 = " PRIMARY KEY"
       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 String) As Boolean
   Dim result As Boolean
   result = False

   Dim t As List
   t = ExecuteMemoryTable(SQL, "PRAGMA table_info ('" & TableName & "')", Null, 0)

   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.