B4J Question SQLite / Problems after deleting an entry

[Tecuma]

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.

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

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

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

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

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]

You cannot reuse a resultset that was closed.

 

[Magma]

You cannot reuse a resultset that was closed.

You must call it again..

ResultSet1 = SpiritLogDB.ExecQuery("SELECT...

 

[Tecuma]

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

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]

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]

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

    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]

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]

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:

' 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]

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