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)

    Code:
    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))   
        ReadDataBaseRowIDs
    End Sub
    Code:
    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.
    ScreenShot.jpg

    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

    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"
     
  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:

    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       
               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 StringAs Boolean
       
    Dim result As Boolean
       result = 
    False

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

       
    Return result
    End Sub
    Hope this is of help.

    Regards

    John.
     
Loading...
  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