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

[LucaMs]

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

Spoiler: Err msg

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]

Post relevant code?

 

[LucaMs]

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]

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

 

[LucaMs]

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

Yes.

 

[aeric]

I tried up to 30,000 - OK

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

I tried create a table, insert a value and query this value. This time iterate to 100,000 - OK

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

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

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

Result = DB.ExecQuerySingleResult2(Query, Array As Int(1000))

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]

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

 

[LucaMs]

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

Very strange.

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

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

 

[Mahares]

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]

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]

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]

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]

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

 

[LucaMs]

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.

 

[aeric]

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

Yes, unless you are very sure about your data.

 

[Mahares]

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]

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...!