B4J Question SQLite / Problems after deleting an entry

Tecuma · May 28, 2022

Hello Community,

in my actual project I use a SQLite DB.

I am able to create the DB and add entries.

After deleting an entry I got an error after I read the DB and show it in a xCustomListView.

The error says that the recordset is already closed.

Before I had delete the record I had 18 rows.

When I check the DB with DB Browser I can see 17 records which should be ok. Also the RowCount in the code shows 17.

I checked the SQLite Guide from Klaus and some posts here in the forum but found nothing.

Did I understand something wrong or do I have an error in my code?

I am using
- B4J 9.30
- jSQL 1.61

I use the following code.

Delete DB entry:

Sub btnDelete_Click
    Private Query As String
    'Private ResultSet1 As ResultSet
    Query = "DELETE FROM SpiritLogTable WHERE rowid = " & CurrentIndex
    SpiritLogDB.ExecNonQuery(Query)
    Log("Entry deleted")
    ShowTable
    
End Sub

ShowTable:

Sub ShowTable
    Private RowCount As Int
    ReadDataBaseRowIDs
    CLVMain.Clear
    RowCount = SpiritLogDB.ExecQuerySingleResult("SELECT count(*) FROM SpiritLogTable")
    
    Log($"IndexMax: ${IndexMax}"$)
    Log($"RowCount: ${RowCount}"$)
    
    For i = 1 To IndexMax
        Dim pnl As B4XView = CreateItem(i, IndexMax)
        CLVMain.Add(pnl, i)
    Next
    
End Sub

Read row IDs:

Sub ReadDataBaseRowIDs
    Private ResultSet1 As ResultSet
        
    ResultSet1 = SpiritLogDB.ExecQuery("SELECT rowid FROM SpiritLogTable")
    
    'We read only the rowid column and put them in the IDList
    RowIDList.Initialize                'initialize the ID list
    Do While ResultSet1.NextRow
        RowIDList.Add(ResultSet1.GetInt2(0))        'add the ID's to the ID list
    Loop
    
    If RowIDList.Size > 0 Then
        If CurrentIndex = -1 Or CurrentIndex > RowIDList.Size - 1 Then
            CurrentIndex = 0            'set the current index to 0
        End If
    Else
        CurrentIndex = -1            'set the current index to -1, no selected item
        'ToastMessage.Show("No items found")
    End If
    
    IndexMax = RowIDList.Size
    
    ResultSet1.Close                            'close the ResultSet, we don't need it anymore
    
End Sub

Create items in the CustomListView on a panel:

Private Sub CreateItem(localNumber As Int, localMaxIndex As Int) As B4XView
    Private p As B4XView = xui.CreatePanel("")
    Private Query As String
    Private ResultSet1 As ResultSet
    Log($"localNumber: ${localNumber}"$)
    p.LoadLayout("LayoutMainItems")
    p.SetLayoutAnimated(0, 0, 0, CLVMain.AsView.Width, 60dip)
    Query = "SELECT * FROM SpiritLogTable WHERE rowid = " & localNumber
    ResultSet1 = SpiritLogDB.ExecQuery(Query)
    lblDate.Text = ResultSet1.GetString("Date") 'This is line 212 from the error message
    lblTime.Text = ResultSet1.GetString("Time")
    lblValuation.Text = ResultSet1.GetInt("Valuation")
    lblTag.Text = ResultSet1.GetString("Tag")
    lblComment.Text = ResultSet1.GetString("Comment")
    Return p
End Sub

ErrorLog:

Waiting for debugger to connect...
Program started.
US
en
DB Does exist
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
IndexMax: 17
RowCount: 17
localNumber: 1
localNumber: 2
localNumber: 3
localNumber: 4
localNumber: 5
localNumber: 6
localNumber: 7
localNumber: 8
localNumber: 9
localNumber: 10
localNumber: 11
localNumber: 12
localNumber: 13
localNumber: 14
localNumber: 15
localNumber: 16
localNumber: 17
Error occurred on line: 212 (B4XMainPage)
java.sql.SQLException: ResultSet closed
    at org.sqlite.RS.checkOpen(RS.java:63)
    at org.sqlite.RS.findColumn(RS.java:108)
    at org.sqlite.RS.getString(RS.java:317)
    at anywheresoftware.b4j.objects.SQL$ResultSetWrapper.GetString(SQL.java:552)
    at b4j.example.b4xmainpage._createitem(b4xmainpage.java:511)
    at b4j.example.b4xmainpage._showtable(b4xmainpage.java:113)
    at b4j.example.b4xmainpage._b4xpage_appear(b4xmainpage.java:70)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:109)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:509)
    at anywheresoftware.b4a.keywords.Common.access$0(Common.java:489)
    at anywheresoftware.b4a.keywords.Common$CallSubDelayedHelper.run(Common.java:563)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
    at java.base/java.lang.Thread.run(Thread.java:834)

jahswant · May 28, 2022

You cannot reuse a resultset that was closed.

Magma · May 28, 2022

jahswant said:
You cannot reuse a resultset that was closed.

You must call it again..

B4X:

ResultSet1 = SpiritLogDB.ExecQuery("SELECT...

Tecuma · May 28, 2022

Many thanks @jahswant and @Magma. I will check it after dinner.

Questions of understanding.
Is the scope of ResultSet1 within the complete code of a page even when I declare it in a Private Sub with Private ResultSet1 e.g

B4X:

Private Sub Test
    Private ResultSet1 As ResultSet

I had commented the close statement.The error was still coming. Is there an automatic close when I am finish with a DB operation?

Magma · May 28, 2022

It is good to close rs every time ending a job (delete, Update etc)... also the db at exit of app...

The Dim of resultset for me is good to do it at subs.. but u can do it also at globals..

But you must remember the last state.. like close... to call/open it if needed again...

Tecuma · May 28, 2022

I have checked my code and the code flow.
I do the recommended Select statement before I got the error.

B4X:

    ResultSet1 = SpiritLogDB.ExecQuery(Query)
    lblDate.Text = ResultSet1.GetString("Date")

The error comes after I read the last entry from the DB.
When I do not read the last entry in the DB the error does not come up.

When I try to access the last entry in the DB the error comes up.

I checked the content from the last entry.
In that row I had a text field with the entry "I found more errors ". (The smiley is : - ) )
When I delete this row it works. I wonder why this was not a problem when this row was not the last entry.

Magma · May 28, 2022

Before lset the value of field to .text check if is null or better the .length to be >0 ..

If still have prob upload your db and your project to check it... sorry ...from Monday..

DarkoT · Jun 6, 2022

Allow me just one simple suggestion... When you working with sql databases and using update, delete, insert, try to use Transactions... From my point is this very very important from data consistency.... Example:

Transactions:

' inserting remark to job
Sub InsertRemarkToJob(Job As String, Remark As String) As Boolean

    ' current date
    DateTime.DateFormat="dd-MM-yy HH:mm:ss"
    Dim DatuminUra As String = DateTime.Date(DateTime.Now)
    
    ' begin transaction
    MySql.BeginTransaction
    
    Try
        
        
        ' inserting remark
        Query = $"insert into wo_comment(wc_tip, wc_job, wc_internal, wc_comment)
                  select 'C', ${Job}, '${Main.ActualZaposleniIme & "- from Start&Stop (" & DatuminUra & ")"}', '${Remark}'"$
        MySql.ExecNonQuery(Query)
            
        
        MySql.TransactionSuccessful
        Main.Msg = "Remark is written to job number: " & Job


    Catch
        ' is error
        Log(LastException)
        MySql.Rollback
    End Try
    
    
    Return True
End Sub

Tecuma · Jun 6, 2022

@DarkoT
Thank you for this example. I will test it.

B4J Question SQLite / Problems after deleting an entry

Tecuma

Member

jahswant

Well-Known Member

Magma

Expert

Tecuma

Member

Magma

Expert

Tecuma

Member

Magma

Expert

DarkoT

Active Member

Tecuma

Member

Similar Threads

B4J Question SQLite / Problems after deleting an entry

Member

Well-Known Member

Expert

Member

Expert

Member

Expert

Active Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency