B4J Question [SOLVED] stmt pointer is closed

[aeric]

I am getting this error in Release but not during Debug in my Web API Template when calling an API using ajax. I am using SQLite database.

(SQLException) java.sql.SQLException: stmt pointer is closed

Private Sub Search (SearchForText As String)
    Dim con As SQL = Main.DB.GetConnection
    Try
        Dim List1 As List
        List1.Initialize
               
        Dim keys() As String = Regex.Split2(" ", 2, SearchForText)
        If keys.Length < 2 Then
            Dim s1 As String = SearchForText.Trim
            'Log(s1)
           
            If s1 = "" Then
                Dim strSQL As String = $"SELECT P.id As aa, P.product_code As bb, C.`category_name` As cc, P.product_name As dd, P.product_price As ee, P.`category_id` As ff
                FROM `tbl_products` P JOIN `tbl_category` C ON P.`category_id` = C.`id`"$
                Dim res As ResultSet = con.ExecQuery(strSQL)
            Else
                Dim strSQL As String = Main.queries.Get("SEARCH_PRODUCT_BY_CATEGORY_CODE_AND_NAME_ONEWORD_ORDERED")
                Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String("%" & s1 & "%", "%" & s1 & "%", "%" & s1 & "%"))
            End If
        Else
            Dim s1 As String = keys(0).Trim
            Dim s2 As String = SearchForText.Replace(keys(0), "").Trim
            'Log(s1 & "," & s2)
            Dim strSQL As String = Main.queries.Get("SEARCH_PRODUCT_BY_CATEGORY_CODE_AND_NAME_TWOWORDS_ORDERED")
            Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String("%" & s1 & "%", "%" & s1 & "%", "%" & s1 & "%", _
            "%" & s2 & "%", "%" & s2 & "%", "%" & s2 & "%"))
        End If
        Do While res.NextRow
            Dim Map2 As Map
            Map2.Initialize
            For i = 0 To res.ColumnCount - 1
                Select res.GetColumnName(i)
                    Case "aa"
                        Map2.Put(res.GetColumnName(i), res.GetInt2(i))
                    Case "ee"
                        Map2.Put(res.GetColumnName(i), NumberFormat2(res.GetDouble2(i), 1, 2, 2, True))
                    Case Else
                        Map2.Put(res.GetColumnName(i), res.GetString2(i))
                End Select
            Next
            List1.Add(Map2)
        Loop
        res.Close
        Utility.ReturnSuccess2(List1, 200, Response)
    Catch
        LogError(LastException)
        HRM.ResponseCode = 422
        HRM.ResponseError = "Error Execute Query " & LastException.Message
        Utility.ReturnHttpResponse(HRM, Response)
    End Try
    Main.DB.CloseDB(con)
End Sub

 

[Erel]

Does it happen when multiple clients are connected?

Have you enabled wal mode: [WebApp] Concurrent access to SQLite databases ?

 

[aeric]

Does it happen when multiple clients are connected?

I am testing only on localhost with web browser loading an index page.

Have you enabled wal mode: [WebApp] Concurrent access to SQLite databases ?

Yes, I am using WAL mode.

 

[aeric]

Try run my Web API Template project in Release mode and refresh the page (http://127.0.0.1:19800/v1/)

 

[aeric]

When the page loads, it is calling HomeHandler and Search("") sub (as in post #1) which triggered from a request in JavaSript in \Objects\www\assets\js\webapisearch.js on line #12

  $.getJSON("/v1/?default=1", function (response) {
      // javaScript
  }

 

[aeric]

If I use the old sqlite library sqlite-jdbc-3.7.2, after refresh a few times, the app crashed.

Web API Server (version = 1.16) is running on port 19800
Open the following URL from your web browser
http://127.0.0.1:19800/v1/
#
# A fatal error has been detected by the Java Runtime Environment:
#
#  EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x0000000069347ce1, pid=15824, tid=7428
#
# JRE version: OpenJDK Runtime Environment (11.0.1+13) (build 11.0.1+13)
# Java VM: OpenJDK 64-Bit Server VM (11.0.1+13, mixed mode, tiered, compressed oops, g1 gc, windows-amd64)
# Problematic frame:
# C  [sqlite-unknown-sqlitejdbc.dll+0x7ce1]
#
# No core dump will be written. Minidumps are not enabled by default on client versions of Windows
#
# An error report file with more information is saved as:
# C:\B4X\DEVELO~1\WEC5F9~1\Objects\hs_err_pid15824.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.java.com/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#

 

[aeric]

To solve this issue, I cannot close the db in GetCategories sub.
Main.DB.CloseDB(con)