Bug? [EDIT: NO BUGS HERE] ExecQuerySingleResult (SQLite)

LucaMs

Expert
Licensed User
Longtime User
ExecQuerySingleResult seems to crash after a certain number of executions (in my case, in debug mode, about 25500)

I placed some xxxxx in the log just for privacy reasons.

java.lang.NullPointerException
at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1838)
at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
at java.lang.Double.parseDouble(Double.java:538)
at b4j.example.moddbhelper._xxxxxxxxxxxxxxx(moddbhelper.java:557)
at b4j.example.main._btnxxxxxxxxxxxx_click(main.java:506)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA$1.run(BA.java:236)
at com.sun.javafx.application.PlatformImpl.lambda$null$5(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$6(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$4(WinApplication.java:186)
at java.lang.Thread.run(Thread.java:748)
 

aeric

Expert
Licensed User
Longtime User
I tried up to 30,000 - OK
B4X:
'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region
 #AdditionalJar: sqlite-jdbc-3.39.3.0
 
Sub Process_Globals
    Dim DB As SQL
End Sub

Sub AppStart (Args() As String)
    Log("** Test Begin **")
    RunStressTest
    StartMessageLoop
End Sub

Sub RunStressTest
    If File.Exists(File.DirApp, "Data.db") = False Then       
        DB.InitializeSQLite(File.DirApp, "Data.db", True)
    Else
        DB.InitializeSQLite(File.DirApp, "Data.db", False)
    End If
    Dim Query As String = "SELECT ? + ?"
    
    For i = 0 To 30000
        Dim Result As Long
        Result = DB.ExecQuerySingleResult2(Query, Array As Int(0, i))
        Log(Result)
    Next
    DB.Close
    Log("** Test End **")
End Sub
 

aeric

Expert
Licensed User
Longtime User
I tried create a table, insert a value and query this value. This time iterate to 100,000 - OK
B4X:
'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region
 #AdditionalJar: sqlite-jdbc-3.39.3.0
 
Sub Process_Globals
    Dim DB As SQL
End Sub

Sub AppStart (Args() As String)
    Log("** Test Begin **")
    RunStressTest
    StartMessageLoop
End Sub

Sub RunStressTest
    If File.Exists(File.DirApp, "Data.db") = False Then       
        DB.InitializeSQLite(File.DirApp, "Data.db", True)
        Dim Query As String = "CREATE TABLE Test (id INTEGER)"
        DB.ExecNonQuery(Query)
        Dim Query As String = "INSERT INTO Test (id) VALUES (100)"
        DB.ExecNonQuery(Query)
    Else
        DB.InitializeSQLite(File.DirApp, "Data.db", False)
    End If
    Dim Query As String = "SELECT id + ? + ? FROM Test"
    For i = 0 To 100000
        Dim Result As Long
        Result = DB.ExecQuerySingleResult2(Query, Array As Int(1000, i))
        Log(i & " -> " & Result)
    Next
    DB.Close
    Log("** Test End **")
End Sub
 

aeric

Expert
Licensed User
Longtime User
Try supply less parameter, you get the errror.
Maybe in the midst of looping, you have a null value in one of your parameters.

B4X:
'Non-UI application (console / server application)
#Region Project Attributes 
    #CommandLineArgs:
    #MergeLibraries: True 
#End Region
 #AdditionalJar: sqlite-jdbc-3.39.3.0
 
Sub Process_Globals
    Dim DB As SQL
End Sub

Sub AppStart (Args() As String)
    Log("** Test Begin **")
    RunStressTest
    StartMessageLoop
End Sub

Sub RunStressTest
    If File.Exists(File.DirApp, "Data.db") = False Then        
        DB.InitializeSQLite(File.DirApp, "Data.db", True)
        Dim Query As String = "CREATE TABLE Test (id INTEGER)"
        DB.ExecNonQuery(Query)
        Dim Query As String = "INSERT INTO Test (id) VALUES (100)"
        DB.ExecNonQuery(Query)
    Else
        DB.InitializeSQLite(File.DirApp, "Data.db", False)
    End If
    Dim Query As String = "SELECT id + ? + ? FROM Test"
    For i = 0 To 100000
        Dim Result As Long
        If i = 10 Then
            Result = DB.ExecQuerySingleResult2(Query, Array As Int(1000))
        Else
            Result = DB.ExecQuerySingleResult2(Query, Array As Int(1000, i))
        End If
        Log(i & " -> " & Result)
    Next
    DB.Close
    Log("** Test End **")
End Sub

Line 32:
B4X:
Result = DB.ExecQuerySingleResult2(Query, Array As Int(1000))

B4X:
Waiting for debugger to connect...
Program started.
** Test Begin **
0 -> 1100
1 -> 1101
2 -> 1102
3 -> 1103
4 -> 1104
5 -> 1105
6 -> 1106
7 -> 1107
8 -> 1108
9 -> 1109
Error occurred on line: 32 (Main)
java.lang.NullPointerException
    at java.base/jdk.internal.math.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1838)
    at java.base/jdk.internal.math.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
    at java.base/java.lang.Double.parseDouble(Double.java:543)
    at b4j.example.main._runstresstest(main.java:121)
    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:234)
    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:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at b4j.example.main.main(main.java:29)
 
Last edited:

LucaMs

Expert
Licensed User
Longtime User
I made some tests and wasn't able to reproduce. It looks like a bug on your side Luca...
Very strange.
B4X:
    'This crashes only after about 25500 times.
    Result = DB.ExecQuerySingleResult(Query)

    'No crashes (same query, of course, consecutive lines of code)
'    Dim RS As ResultSet
'    RS = DB.ExecQuery(Query)
'    Do While RS.NextRow
'        Result = RS.GetInt2(0)
'    Loop
'    RS.Close

I will try to update the driver.
#AdditionalJar: sqlite-jdbc-3.39.3.0

Thank you.
 

LucaMs

Expert
Licensed User
Longtime User
Solved the "mystery".

I had worked around this before using ExecQuery and that's where the difference lies. ExecQuerySingleResult did not return any results, so the associated unreturned value cannot be assigned to the Result variable:
Result = DB.ExecQuerySingleResult(Query)
Using ExecQuery, the Do While loop is not executed and the function returns the default value of Result.

Sorry for the inconvenience :(
 

Daestrum

Expert
Licensed User
Longtime User
I think Luca has explained the golden rule of databases, do not assume a query will return a result, check if it does before using the returned value.
 

LucaMs

Expert
Licensed User
Longtime User
You still did not explain why it did not return a result. What did you find in your data that made it fail
There are no records whose fields contain the values requested in the query.

Something like:

SELECT * FROM MyTable WHERE Field1 = 10 AND Field2 = 20

No record contains those two values.

(in my tool)
1683888346642.png
 

Mahares

Expert
Licensed User
Longtime User
SELECT * FROM MyTable WHERE Field1 = 10 AND Field2 = 20
Now, you are cooking. Your post #10 does not show a parameterized query. That is why I asked the question. It is settled now.

do not assume a query will return a result, check if it does before using the returned value.
Based on your statement, we should try to avoid the use of ExecSingleResult
 
Last edited:

LucaMs

Expert
Licensed User
Longtime User
Now, you are cooking. Your post #10 does not show a parameterized query. That is why I asked the question. It is settled now.
I don't think it's necessary. The same query, with 2 numeric parameters, works fine about 25500 times, then crashes, for no reason (I checked the query and the values, everything is fine).

I solved using ExecQuery, which I think takes longer.
 

Mahares

Expert
Licensed User
Longtime User
n this case I thought there was all
Where is the code for the SQL library located. Maybe we can look at it and see if the ExecSingleResult can be modified to prevent such error without knowing the content of your data in the table.
 

LucaMs

Expert
Licensed User
Longtime User
Where is the code for the SQL library located. Maybe we can look at it and see if the ExecSingleResult can be modified to prevent such error without knowing the content of your data in the table.
I don't think it's necessary.
In that case it was my evaluation error, I assumed that a record with those values certainly existed. If this is not certain, however, we will use ExecQuery.
You could always do it, to avoid problems, but in that project I needed to quickly execute over 40,000 queries and the result of each one had to be a single Int data, so...!
 
Top