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

Y

yzeflores

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

B4X: 
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)
 
Sort by date Sort by votes
eps

eps

Expert
Licensed User
Longtime User
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.

B4X: 
                '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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
yzeflores said:
which is why I'm confused as to why it doesn't work.
Click to expand...
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:
B4X: 
Dim p As B4XView = xui.CreatePanel("")
p.SetLayoutAnimated(0,0,0,CLVStudentList.AsView.Width,60dip)
p.LoadLayout("DB")
 
Upvote 0
Y

yzeflores

Member
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?
 
Upvote 0
Y

yzeflores

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

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
 
Upvote 0
eps

eps

Expert
Licensed User
Longtime User
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.
 
Upvote 0
eps

eps

Expert
Licensed User
Longtime User
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.
 
Upvote 0
You must log in or register to reply here.
Top