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

[Mahares]

I assumed that a record with those values certainly existed.

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:

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]

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:

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]

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]

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

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

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]

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]

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.

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]

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

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]

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]

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]

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.

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]

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]

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]

Log: java.lang.String

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

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

     * 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();
        }
    }

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]

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]

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]

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]

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]

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 )