Android Question android.database.sqlite.SQLiteException: no such table: products (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM products

[PercyKumah]

I'm new to B4A and android. In fact I'm building my first test app. I followed this tutorial: and attempted to create my first app. Below is mu code:

If File.Exists(File.DirInternal,"sql.db") = False Then
      File.Copy(File.DirAssets,"easyverify.db", File.DirInternal, "sql.db")
End If
'make a connection to the copied internal dir file
sql.Initialize(File.DirInternal, "sql.db", True)
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    Private btnHello As Button
    Private txtSearch As EditText
    Dim cur As Cursor
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("Layout")
    cur = Starter.sql.ExecQuery("select id, fname from table1")
    cur.Position = 0
    txtSearch.Text = cur.GetString("fname")
    cur.Close
End Sub

When I debug I get the following error:
"android.database.sqlite.SQLiteException: no such table: products (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM products"

I have followed various posts in this forum and on google search that suggested removing the app from the emulator and reinstalling, deleting the db and recreating among others but nothing works.

Am I doing this thing wrong, are there other parameters I need to specify?

Thanks in advance

 

[PaulMeuris]

You shouldn't use the starter module.
In the Libraries Manager you have to select the SQL library.
In the Files Manager you have to add the file easyverify.db
This is some code that works:

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Private xui As XUI
    Private sql As SQL
    Private qry As String
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    Private EditText1 As EditText
 End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        If File.Exists(File.DirInternal,"sql.db") = False Then
            File.Copy(File.DirAssets,"easyverify.db", File.DirInternal, "sql.db")
        End If
    End If
    sql.Initialize(File.DirInternal, "sql.db", True)
    Activity.LoadLayout("Layout")
    qry = "select * from products"
    Private rs As ResultSet = sql.ExecQuery(qry)
    Do While rs.NextRow
        ' multiline edittext field
        EditText1.Text = EditText1.Text & rs.GetString("fname") & CRLF
    Loop
End Sub

Make sure you change the EditText1 field to multiline (uncheck the property Single Line).
You can always find more information in the booklets: SQLite database guide

 

[PaulMeuris]

If you want to create the database and tables in code then below you can find some example code.
To fill the database table with samples you can use the add_records subroutine.
This is how it looks like:

Sub Activity_Create(FirstTime As Boolean)
'    If FirstTime Then
'        If File.Exists(File.DirInternal,"sql.db") = False Then
'            File.Copy(File.DirAssets,"easyverify.db", File.DirInternal, "sql.db")
'        End If
'    End If
    File.Delete(xui.DefaultFolder,"sql.db")        ' remove db for testing
    sql.Initialize(File.DirInternal, "sql.db", True)
    create_tables
    add_records
    Activity.LoadLayout("Layout")
    qry = "select * from products"
    Private rs As ResultSet = sql.ExecQuery(qry)
    Do While rs.NextRow
        ' multiline edittext field
        EditText1.Text = EditText1.Text & rs.GetString("fname") & CRLF
    Loop
End Sub

Sub create_tables
    If table_exists("products") = False Then
        qry = "CREATE TABLE products (fname TEXT)"
        sql.ExecNonQuery2(qry,Null)
    End If
End Sub
Public Sub table_exists(table As String) As Boolean
    Private rs As ResultSet = sql.ExecQuery2("SELECT name FROM sqlite_master WHERE type='table' AND name=?",Array As String(table))
    If rs.RowCount > 0 Then
        Return True
    Else
        Return False
    End If
End Sub
Sub add_records
    qry = "INSERT INTO products VALUES (?)"
    For i = 1 To 3
        sql.ExecNonQuery2(qry,Array As String("product " & i))
    Next
End Sub

You can comment out the file.delete statement after the first successful run.
The second run 3 more products are added.
Happy coding!
Paul

 

[PercyKumah]

Thank you Paul! I actually got it to work before seeing your reply. It was a matter of the database not being saved properly after I created it using the DB browser.
However, your explanation on where to make my declarations is very helpful. What is the reason not to use the starter module to copy the internal DB file?

 

[PaulMeuris]

In the SQLite Database booklet at page 34 is mentioned:

Initialize it in the Service_Create routine in the Starter Service. Or in the Main module with If FirstTime Then / End If

So you have a choice. I usually don't use the Starter module and use B4XPages in my projects.
I have written my own database class module that contains all the database related methods.
If you look at the tutorials i wrote you will find an example of such a class (in the forum Teaching... B4A-database).
Happy coding!
Paul