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

Mahares · May 13, 2023

LucaMs said:
I assumed that a record with those values certainly existed.

aeric said:
Yes, unless you are very sure about your data.

I modified @aeric's code to make sure I choose a parameter that does not exist, but it still did not crash. It still returns a null value. I think your problem is somewhere else. I do not want to beat a dead horse, but we have to:

Code:

Sub RunStressTest
    File.Delete(File.DirApp, "Data.db")
    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 WHERE id = ?"
    Dim Result As Object  
    Result = DB.ExecQuerySingleResult2(Query, Array As Int(101))
    Log(Result)
    DB.Close
End Sub

aeric · May 14, 2023

Mahares said:

I modified @aeric's code to make sure I choose a parameter that does not exist, but it still did not crash. It still returns a null value. I think your problem is somewhere else. I do not want to beat a dead horse, but we have to:

Code:

Sub RunStressTest
    File.Delete(File.DirApp, "Data.db")
    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 WHERE id = ?"
    Dim Result As Object 
    Result = DB.ExecQuerySingleResult2(Query, Array As Int(101))
    Log(Result)
    DB.Close
End Sub

I think the issue is when you pass a null value into the query. In the above case, replace 101 with null.

OliverA · May 14, 2023

Mahares said:
It still returns a null value.

Yes. See https://www.b4x.com/android/forum/threads/null-and-what-is-the-best-practice.124885/post-780068

The problem happens with queries that don't return any result. In that case ExecQuerySingleResult returns Null which is a (historic) mistake.

OliverA · May 14, 2023

Mahares said:
I think your problem is somewhere else

I think the issue is as @eric points out in post #8, where the return result is assigned without checking if it is null

Code:

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

That will blow up with an exception when null is returned. If one insists on using ExecQuerySingleResult/ExecQuerySingleResult2, then one could use some defensive programming

Code:

Dim result as Long
Dim queryResult As String = DB.ExecQuerySingleResult2(Query, Array As Int(1000))
If queryResult <> Null And queryResult <> "null" Then
  result = queryResult ' Note: Even this will blow up if queryResult is not a stringified number that can be cast to a number variable. You probably should try/catch this
Else
  'Whatever should result be if the answer is null. In this case, I pick 0
  result = 0
End If

LucaMs · May 14, 2023

OliverA said:
If queryResult <> Null And queryResult <> "null" Then

Which type of the two nulls? Will it depend on what type of value is expected? If this were the case, double checking would not be necessary; but even if its type were independent a "single if" would suffice.

LucaMs · May 14, 2023

LucaMs said:
Which type of the two nulls? Will it depend on what type of value is expected? If this were the case, double checking would not be necessary; but even if its type were independent a "single if" would suffice.

Just tried.

B4X:

Dim Result As Object
Result = DB.ExecQuerySingleResult(Query)
Log(GetType(Result))

Log: java.lang.String

and Result is NOT:
"Null"
"null"
empty-string

and it is not a Null object!

P.S. Result is a 2 character long non-ASCII string, apparently.

Mahares · May 14, 2023

aeric said:
I think the issue is when you pass a null value into the query. In the above case, replace 101 with null.

I don;t know what you mean by issue. If you mean a crash, then there is no crash. It returns Null

Code:

Dim Query As String = "SELECT id  FROM Test WHERE id = ?"
Dim Result As Object
Result = DB.ExecQuerySingleResult2(Query, Array As String(Null))
Log(Result)
DB.Close

LucaMs · May 14, 2023

Mahares said:
hen there is no crash. It returns Null

Maybe you missed my last post; in my case the query does NOT return Null, of any type.

Mahares · May 14, 2023

LucaMs said:
Maybe you missed my last post; in my case the query does NOT return Null, of any type.

I am still addressing your original claim, that when you insert values for parameters that do not exist, the app crashes. Well, it does not crash when I put null or 101 when neither exists in the table, regardless of whether it is a type or not. Aren't we still debating that thread title or did we completely make a 180 degree switch to something else. If we switched to a different topic, please count me out.

LucaMs · May 14, 2023

Mahares said:
I am still addressing your original claim, that when you insert values for parameters that do not exist, the app crashes. Well, it does not crash when I put null or 101 when neither exists in the table, regardless of whether it is a type or not. Aren't we still debating that thread title or did we completely make a 180 degree switch to something else. If we switched to a different topic, please count me out.

Mahares said:
Result = DB.ExecQuerySingleResult2(Query, Array As String(Null))

I don't pass null or weird values. I have a date field, a text field and 5 integers, say D1, D2, D3, D4 and D5.
If I execute a Select asking that one of 5 numeric fields contains for example 100 and another 200 and these values are not in any record, the app crashes.
If I declare Result as Object, I get what I wrote in this post.

LucaMs · May 14, 2023

LucaMs said:
I don't pass null or weird values. I have a date field, a text field and 5 integers, say D1, D2, D3, D4 and D5.
If I execute a Select asking that one of 5 numeric fields contains for example 100 and another 200 and these values are not in any record, the app crashes.
If I declare Result as Object, I get what I wrote in this post.

I forgot to write that the resulting data, if there is a record whose fields contain both values, is the rowid (not exactly, it is the difference between Max(rowid) and the rowid (not) found)

Mahares · May 14, 2023

LucaMs said:
If I execute a Select asking that one of 5 numeric fields contains for example 100 and another 200 and these values are not in any record, the app crashes

Ok Mario. Thank you. We could have resolved this thread if you had included a project in the early stages of this thread.

OliverA · May 14, 2023

LucaMs said:
Log: java.lang.String

Yes, because the underlying code for ExecQuerySingleResult returns a string, not an object, so don't do

Code:

Dim result as Object

But just follow my code and dim the result you expect from ExecQuerySingleResult / ExecQuerySingleResult2 as a String.

Source: https://github.com/AnywhereSoftware...rc/anywheresoftware/b4j/objects/SQL.java#L419

Code:

     * Executes the query and returns the value in the first column and the first row (in the result set).
     *Returns Null if no results were found.
     *Example:<code>
     *Dim NumberOfMatches As Int
     *NumberOfMatches = SQL1.ExecQuerySingleResult("SELECT count(*) FROM table1 WHERE col2 > 300")</code>
     */
    public String ExecQuerySingleResult(String Query) throws SQLException {
        return ExecQuerySingleResult2(Query, null);
    }
    /**
     * Executes the query and returns the value in the first column and the first row (in the result set).
     *Returns Null if no results were found.
     *Example:<code>
     *Dim NumberOfMatches As Int
     *NumberOfMatches = SQL1.ExecQuerySingleResult2("SELECT count(*) FROM table1 WHERE col2 > ?", Array As String(300))</code>
     */
    public String ExecQuerySingleResult2(String Query, List Args) throws SQLException {
        checkNull();
        ResultSetWrapper cursor = ExecQuery2(Query, Args);
        try {
            if (!cursor.NextRow())
                return null;
            if (cursor.getColumnCount() == 0)
                return null;
            return cursor.GetString2(0);
        } finally {
            cursor.Close();
        }
    }

LucaMs said:
Which type of the two nulls?

Just test it. I've included both types of tests because I've encountered issues where a string object is a null object (first test), whereas sometimes the string is a null string (second test).

OliverA · May 14, 2023

Finally, please see point #23 in https://www.b4x.com/android/forum/threads/b4x-features-that-erel-recommends-to-avoid.133280/ (I guess I should have just posted this first, to re-affirm that @LucaMs's choice of using ExecQuery is the correct solution)

Mahares · May 14, 2023

OliverA said:
just follow my code and dim the result you expect from ExecQuerySingleResult / ExecQuerySingleResult2 as a String.

Can you attach a B4A/B4X project instead of a java project.

OliverA · May 14, 2023

Mahares said:
Can you attach a B4A/B4X project instead of a java project.

The Java code I posted in post # 33 is from the source of jSQL. I've posted it to show that the return value of ExecQuerySingleResult / ExecQuerySingleResult2 is a String object (be it Null, "null" or contain a value in string form). The B4X code is in post # 24 of this thread.

Links:
Post #24: https://www.b4x.com/android/forum/t...ecquerysingleresult-sqlite.147895/post-937791
Post #33: https://www.b4x.com/android/forum/t...ecquerysingleresult-sqlite.147895/post-937871

Mahares · May 14, 2023

OliverA said:
The B4X code is in post # 24 of this thread.

I think there is too much code in this thread coming from several different directions, including mine. I thought one full project from you will put things to rest. That is why I am not a fan of threads that exceed 10 posts. Things get lost in the translation and interpretation. I have used ExecSIngleResult before and never had any trouble. So I am familiar with avoiding pitfalls.

LucaMs · May 14, 2023

Mahares said:
Ok Mario. Thank you. We could have resolved this thread if you had included a project in the early stages of this thread.

The problem has been solved for a long time, in fact I changed the title, as having understood the reason for the crash, I wrote that this is not a bug and in my opinion the matter is solved by not using ExecQuerySingleResult in case you are not sure of getting a result and then to use ExecQuery (or ExecQuery2).

Nice to be called Mario ? (although in this case it sounds a bit like a threat ?)

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

Mahares

Expert

aeric

Expert

OliverA

Expert

OliverA

Expert

LucaMs

Expert

LucaMs

Expert

Mahares

Expert

LucaMs

Expert

Mahares

Expert

LucaMs

Expert

LucaMs

Expert

Mahares

Expert

OliverA

Expert

OliverA

Expert

Mahares

Expert

OliverA

Expert

Mahares

Expert

LucaMs

Expert

Similar Threads