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

LucaMs · May 10, 2023

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)

Sandman · May 10, 2023

Post relevant code?

LucaMs · May 10, 2023

Sandman said:
Post relevant code?

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.

Erel · May 10, 2023

Before I test

does it happen in release mode as well?

LucaMs · May 10, 2023

Erel said:
Before I test does it happen in release mode as well?

Yes.

aeric · May 10, 2023

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 · May 10, 2023

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 · May 10, 2023

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)

Erel · May 11, 2023

I made some tests and wasn't able to reproduce. It looks like a bug on your side Luca...

LucaMs · May 11, 2023

Erel said:
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.

aeric said:
#AdditionalJar: sqlite-jdbc-3.39.3.0

Thank you.

LucaMs · May 11, 2023

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:

LucaMs said:
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

Mahares · May 12, 2023

LucaMs said:
ExecQuerySingleResult did not return any results

You still did not explain why it did not return a result. What did you find in your data that made it fail

Daestrum · May 12, 2023

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 · May 12, 2023

Mahares said:
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)

LucaMs · May 12, 2023

Daestrum said:
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.

In this case I thought there was all ?

Mahares · May 12, 2023

LucaMs said:
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.

Daestrum said:
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

LucaMs · May 12, 2023

Mahares said:
Now, you are cooking. Your post #10 does not show a parameterized query. That is why I asked the question. It is settled now.

LucaMs said:
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.

aeric · May 12, 2023

Mahares said:
Based on your statement, we should try to avoid the use of ExecSingleResult

Yes, unless you are very sure about your data.

Mahares · May 12, 2023

LucaMs said:
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 · May 12, 2023

Mahares said:
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...!

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

Expert

Expert

Expert

B4X founder

Expert

Expert

Expert

Expert

B4X founder

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert