Android Question no such table: table1 (code 1): , while compiling: select * from table1

yzeflores · Dec 7, 2020

Why is this error keeps popping up on my proj? here is my Main activity code:

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("DatabaseLayout")
    
    Dim rs As ResultSet
    rs = Starter.SQL1.ExecQuery("select * from table1")
    rs.Position = 0

    Do While rs.NextRow
        Dim p As B4XView = xui.CreatePanel("")
        p.LoadLayout("DB")
        p.SetLayoutAnimated(0,0,0,CLVStudentList.AsView.Width,60dip)
        Dim Label1 As Label =  p.GetView(0)
        Label1.Text = rs.GetString("Name")
        CLVStudentList.Add(p,CheckBox1)
        Log(rs.GetString("Name"))
    Loop
    
    Dim now As Long = DateTime.Now
    Dim dt As String
    dt = DateTime.Date(now)
    Label1.Text = dt
    
End Sub

when I try to run it the application stops working and this error occurred.

main_activity_create (java line: 355)
android.database.sqlite.SQLiteException: no such table: table1 (code 1): , while compiling: select * from table1
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
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:1314)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.example.main._activity_create(main.java:355)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:196)
at b4a.example.main.afterFirstLayout(main.java:104)
at b4a.example.main.access$000(main.java:17)
at b4a.example.main$WaitForLayout.run(main.java:82)

eps · Dec 7, 2020

That database table, table1 doesn't exist in the database..

yzeflores · Dec 7, 2020

eps said:
That database table, table1 doesn't exist in the database..

I tried to execute the table in DB browser and it works just fine... which is why I'm confused as to why it doesn't work.

Erel · Dec 8, 2020

1. If this is a new project then switch to B4XPages.
2. You are correctly using ResultSet, but you are using it incorrectly. Don't set the position.
3. You should first set the panel size and only then load the layout.
4. Don't forget to close the ResultSet.

eps · Dec 8, 2020

Have you opened the database in your B4A App?

Something along the lines of this e.g.

Database - is it pre-populated? If so, it needs to be copied (ideally once) to the read-write area for Apps. When it is packaged it is in a read only directory.

Then open it.

My code below works - but it might be overkill and possibly slightly dated - caveats apply! I can't remember why I put in code do disable write ahead logging - but obviously it was required at the time, but might not be needed any more. Plus I think the use of Cursors is now frowned upon..

I've put the two main parts you need to focus on in bold below - copying the DB file to a read-write area (but you should check it's existence before, otherwise you will lose any changes made after the last time it was copied) and Initialise the database for use - but again check to see if it isn't already initialised.

Code: Copy

                'Database hasn't been copied to read/write area
                If FirstTime = True Then
                If File.Exists(File.DirInternal,"themsc.db") = False Then

                    'ToastMessageShow("Database hasn't been copied to read/write area",True)

                    File.Copy(File.DirAssets,"themsc.db",File.DirInternal,"themsc.db")

                    'Database is in the read/write area, check the db_ver matches the app_ver

                    If VersionLabel.Text <> db_ver Then
                        ListViewSaveFavs.Initialize("ListViewSaveFavs")
                        Dim number_of_favs As Int
                        number_of_favs = 0
                            If SQL1.IsInitialized = False Then
                                SQL1.Initialize(File.DirInternal,"themsc.db", True)


                    If p.SdkVersion >= 26 Then

                        Dim rr As Reflector

                        rr.Target = SQL1

                        rr.Target = rr.GetField("db")

                        rr.RunMethod("disableWriteAheadLogging")
                        File.Delete(File.DirInternal,"themsc.db-wal")
                        File.Delete(File.DirInternal,"themsc.db-shm")
                    End If


                            End If
                        'DB version is greater than the app version, we need to store favourites, update the DB and restore favourites
                        Cursor = SQL1.ExecQuery("SELECT fk_event_id FROM favourite")
                        For i=0 To Cursor.RowCount-1
                            Cursor.Position=i
                            ListViewSaveFavs.AddSingleLine(Cursor.GetInt("fk_event_id"))
            '                ListViewSaveFavs.Tag(Cursor.GetInt("_id")
                            number_of_favs = number_of_favs+1
                        Next

                        Cursor.Close

                        'remove the current database file

                        If i > 0 Then

                            SQL1.ExecNonQuery("DELETE FROM favourite")

                        End If

                        File.Delete(File.DirInternal,"themsc.db")

                        'put the new database file in place

                        File.Copy(File.DirAssets,"themsc.db",File.DirInternal,"themsc.db")

                        SQL1.Initialize(File.DirInternal, "themsc.db", True)


                If p.SdkVersion >= 26 Then

                    Dim rr As Reflector
                    rr.Target = SQL1
                    rr.Target = rr.GetField("db")


                    rr.RunMethod("disableWriteAheadLogging")


                    File.Delete(File.DirInternal,"themsc.db-wal")

                    File.Delete(File.DirInternal,"themsc.db-shm")

                End If

                        'put the favourites back in the database

                        SQL1.BeginTransaction

                        Try

                            For i=0 To number_of_favs-1

                                SQL1.ExecNonQuery2("INSERT INTO favourite VALUES (?,?)", Array As Object(i+1, ListViewSaveFavs.GetItem(i)))


                            Next

                            SQL1.TransactionSuccessful

                        Catch

                                Log(LastException.Message)

                        End Try

                        SQL1.EndTransaction

                        SQL1.Close

                    End If

                Else

                    'Database is in the read/write area, check the db_ver matches the app_ver


                    'ToastMessageShow("Database is in the read/write area",True)


                    If VersionLabel.Text <> db_ver Then

                        ListViewSaveFavs.Initialize("ListViewSaveFavs")

                        Dim number_of_favs As Int

                        number_of_favs = 0

                            If SQL1.IsInitialized = False Then

                                SQL1.Initialize(File.DirInternal,"themsc.db", True)

                    If p.SdkVersion >= 26 Then

                        Dim rr As Reflector

                        rr.Target = SQL1

                        rr.Target = rr.GetField("db")

                        rr.RunMethod("disableWriteAheadLogging")

                        File.Delete(File.DirInternal,"themsc.db-wal")
                        File.Delete(File.DirInternal,"themsc.db-shm")

                    End If

                            End If

                        'DB version is greater than the app version, we need to store favourites, update the DB and restore favourites

                        Cursor = SQL1.ExecQuery("SELECT fk_event_id FROM favourite")

                        For i=0 To Cursor.RowCount-1

                            Cursor.Position=i

                            ListViewSaveFavs.AddSingleLine(Cursor.GetInt("fk_event_id"))

                            number_of_favs = number_of_favs+1

                        Next

                        Cursor.Close

                        'remove the current database file

                        If i > 0 Then

                            SQL1.ExecNonQuery("DELETE FROM favourite")

                        End If


                        File.Delete(File.DirInternal,"themsc.db")

                        'put the new database file in place

                        File.Copy(File.DirAssets,"themsc.db",File.DirInternal,"themsc.db")


                        SQL1.Initialize(File.DirInternal, "themsc.db", True)

                If p.SdkVersion >= 26 Then


                    Dim rr As Reflector


                    rr.Target = SQL1


                    rr.Target = rr.GetField("db")


                    rr.RunMethod("disableWriteAheadLogging")


                    File.Delete(File.DirInternal,"themsc.db-wal")


                    File.Delete(File.DirInternal,"themsc.db-shm")


                End If


                        'put the favourites back in the database


                        SQL1.BeginTransaction

                        Try

                            For i=0 To number_of_favs-1

                                SQL1.ExecNonQuery2("INSERT INTO favourite VALUES (?,?)", Array As Object(i+1, ListViewSaveFavs.GetItem(i)))


                            Next


                            SQL1.TransactionSuccessful


                        Catch


                                Log(LastException.Message)


                        End Try


                        SQL1.EndTransaction

                        SQL1.Close

                    End If  

                End If

                End If



                If SQL1.IsInitialized = False Then
                        SQL1.Initialize(File.DirInternal,"themsc.db", True)


        If p.SdkVersion >= 26 Then

            Dim rr As Reflector

            rr.Target = SQL1

            rr.Target = rr.GetField("db")

            rr.RunMethod("disableWriteAheadLogging")

            File.Delete(File.DirInternal,"themsc.db-wal")
            File.Delete(File.DirInternal,"themsc.db-shm")
        End If
    End If

I permit Users to set their own favourites and so on and 'series link' so these are stored in the DB once it has been initially copied. I also store them in CSV files, so they can be rebuilt.

eps · Dec 8, 2020

Just to explain - so your database does have that table, but not the one you're looking at (probably)

Mahares · Dec 8, 2020

yzeflores said:
which is why I'm confused as to why it doesn't work.

Your problem seems to be simple enough to solve if you create a small project and upload to the forum. Users on this forum love to solve SQLite issues because of the popularity of SQLite and the vast knowledge. Someone will figure it out.
As was mentioned by Erel, remove this line from the code: rs.Position = 0
Also make the order of these 3 lines to what I show it below:

Code: Copy

Dim p As B4XView = xui.CreatePanel("")
p.SetLayoutAnimated(0,0,0,CLVStudentList.AsView.Width,60dip)
p.LoadLayout("DB")

yzeflores · Dec 8, 2020

thank you all for replying to my question. it finally worked! I removed the rs.position and set the panel size first. I also initialized the database in my main activity. it won't connect when I typed it in the starter module. My next question now is that is it possible to save my data by the date which the data was saved?

eps · Dec 8, 2020

I think we'll need a little more information to help you. It probably is possible but I'm not really sure what you are trying to achieve.

yzeflores · Dec 9, 2020

eps said:
I think we'll need a little more information to help you

I am building an app that can save data according to the dates they were saved. Like attendance, for example, The user will check the checkbox if the people in the list were present on 12/08/20 and save the data. Then today 12/09/20 the user can do the same thing. since the data was saved the user can also look back on the people who were present on 12/08/20 and 12/09/20. I hope you understood what I am trying to say

eps · Dec 9, 2020

You'll need to do a bit of data-modelling first. Try and work out how to model the data to support your requirement.

So you have Person, Event and Attendance (plus possibly some other tables), maybe Registrant.

Person, their name and other details (GDPR taken into consideration).

Event, the name of the Event, plus other details, location, facilities etc..

Registrant (or similar), links a Person to an Event. i.e. they registered to attend. (foreign keys to Person and Event)

Attendance, links to the Registrant record and saves the date, i.e. indicates that they attended (on that date + others). (so foreign key to Registrant)

There might be other considerations, but that's it from a really simple approach. You'll know more about your own requirements.

eps · Dec 9, 2020

Using the above you should be able to list everyone who is registered and then 'mark' their attendance on a particular day.

Then you can list the Registrants for the Event with an Attendance record, or multiple Attendance records or possibly from the Attendence records if you add in a foreign key to the Event at that point, which permits you to query that table.

yzeflores · Dec 9, 2020

Thank you so much this is very helpful ?

eps · Dec 9, 2020

But please tell me this isn't a school/college/uni project

Android Question no such table: table1 (code 1): , while compiling: select * from table1

yzeflores

Member

eps

Expert

yzeflores

Member

Erel

B4X founder

eps

Expert

eps

Expert

Mahares

Expert

yzeflores

Member

eps

Expert

yzeflores

Member

eps

Expert

eps

Expert

yzeflores

Member

eps

Expert

Similar Threads