Android Question SQL not finding "Table" SOLVED

Roger Daley · Jun 26, 2019

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 **
~w:1004,main,144
** 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(SQLiteConnection.java:895)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:506)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:726)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1428)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1367)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at horsetrailer.B4A.AntennaBearingTool.main._executehtml(main.java:5737)
at horsetrailer.B4A.AntennaBearingTool.main._showtable(main.java:7578)
at horsetrailer.B4A.AntennaBearingTool.main._activity_resume(main.java:1425)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:196)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:176)

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)

B4X:

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))   
    ReadDataBaseRowIDs
End Sub

B4X:

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)
    Else
        ResultSet1 = SQL.ExecQuery(Query)     'This is B4A line 2571,  the line that throws the error.
    End If
    Private sb As StringBuilder
    sb.Initialize
    sb.Append("<html><body>").Append(CRLF)
    sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
    sb.Append("<table><tr>").Append(CRLF)
    For i = 0 To ResultSet1.ColumnCount - 1
        sb.Append("<th>").Append(ResultSet1.GetColumnName(i)).Append("</th>")
    Next
    
    sb.Append("</tr>").Append(CRLF)
    
    Private row As Int
    row = 0
    Do While ResultSet1.NextRow
        If row Mod 2 = 0 Then
            sb.Append("<tr>")
        Else
            sb.Append("<tr class='odd'>")
        End If
        For i = 0 To ResultSet1.ColumnCount - 1
            sb.Append("<td>")
            If Clickable Then
                sb.Append("<a href='http://").Append(i).Append(".")
                sb.Append(row)
                sb.Append(".stub'>").Append(ResultSet1.GetString2(i)).Append("</a>")
            Else
                sb.Append(ResultSet1.GetString2(i))
            End If
            sb.Append("</td>")
        Next
        sb.Append("</tr>").Append(CRLF)
        row = row + 1
    Loop
    
    ResultSet1.Close
    sb.Append("</table></body></html>")
    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

DonManfred · Jun 26, 2019

Upload a small project which shows the issue. Make sure to include the DB.

Roger Daley · Jun 26, 2019

DonManfred,

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"

DonManfred · Jun 26, 2019

Roger Daley said:
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.

Jmu5667 · Jun 26, 2019

Can you show the code to create the dB tables.

klaus · Jun 26, 2019

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 · Jun 26, 2019

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.

Jmu5667 · Jun 26, 2019

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

B4X:

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       
           Try
               SQL.ExecNonQuery("ALTER TABLE [" & TableName & "] ADD COLUMN [" & field & "] " & ftype & pk)
           Catch
               Log("CreateTable() " & field & " already exists")
           End Try
       End If

   Next
   
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
           Exit
       End If
   Next

   Return result
End Sub

Hope this is of help.

Regards

John.

Android Question SQL not finding "Table" SOLVED

Roger Daley

Well-Known Member

DonManfred

Expert

Roger Daley

Well-Known Member

DonManfred

Expert

Jmu5667

Well-Known Member

klaus

Expert

Roger Daley

Well-Known Member

Jmu5667

Well-Known Member

Similar Threads