Android Question Safe getting String and Int from SQLite

peacemaker · Apr 1, 2020

HI, All

How to get String or Int values from SQLite for sure and null-error-free ?
I have done such subs for DBUtils, are they correct or maybe there is more clear solution ?

B4X:

'return "", if no string data
Sub GetStringFromSQL (SQL As SQL, req As String) As String
    Dim a As String
    Try
        a = SQL.ExecQuerySingleResult(req)
    Catch
        Return ""
    End Try

    If a = Null Then
        Return ""
    Else If a.ToLowerCase = "null" Then
        Return ""
    Else If a = "" Then
        Return ""
    Else
        Return a
    End If
End Sub

'return -1, if no number data
Sub GetIntFromSQL (SQL As SQL, req As String) As Int
    Dim a As String
    Dim Const NegativeRes As Int = -1
    Try
        a = SQL.ExecQuerySingleResult(req)
    Catch
        Return NegativeRes
    End Try

    If a = Null Then
        Return NegativeRes
    Else If a.ToLowerCase = "null" Then
        Return NegativeRes
    Else If IsNumber(a) = False Then
        Return NegativeRes
    Else
        Dim res As Int = a
        Return res
    End If
End Sub

Frankie Lagrange · Apr 1, 2020

OK, will get back to you soon, if you care to wait, as I need to set-up a project with a database. A question though: Has the table [Stops] more than one row? Not that it would change anything, but I'm just curious about the implementation.

BTW, is there a reason what my posts get queued to be approved by the moderator. Is that normal?

Alex_197 · Apr 1, 2020

Why you don't use ifnull?

IFNULL:

select ifnull(cutomername,'') as CustomerName,
ifnull(customerage,0) as CustomerAge
from tblCustomers

Frankie Lagrange · Apr 1, 2020

Here are two possible implementations. Pay attention how I changed the query to use count(*) and ifNull() that I mentioned in my first post. Modify to suit your needs. Also, the table parameter is not required, but I left it in the Sub's signature; you may want to remove it.

1. The first implementation matches what you wanted, I believe

B4X:

    Dim LatestStatus As Int = DBUtils.GetIntFromSQL(Starter.SQL, "stops", "SELECT Count(*) AS RowNum, ifNull(status,-1) FROM stops ORDER BY id DESC LIMIT 1")
    If LatestStatus = 0 Then
        Log("Start")
    Else
        Log("Stop")
    End If

'' In DBUTILS
Sub GetIntFromSQL (SQL As SQL, table As String, req As String) As Int
    Dim Const NegativeRes As Int = -2147483648
    Try
        Dim cur As Cursor = SQL.ExecQuery(req)
        cur.Position = 0

        Dim rowCount As Int = cur.GetInt2(0)
        If (rowCount = 0) Then
            Return NegativeRes
        Else
            If  IsNumber(cur.GetString2(1)) Then
                Return  cur.GetInt2(1)
            Else
                Return NegativeRes
            End If
        End If
    Catch
        Log("GetIntFromSQL(" & req & ").error = " & LastException.Message)
        Return NegativeRes
    End Try
End Sub

2. The second returns true or false, and is sweet and short if you're only interested in 0 or non 0 - this is counter-intuitive and you may want to reverse that.

B4X:

    Dim boolLatestStatus As Boolean = DBUtils.GetStatus(Starter.SQL, "stops", "SELECT Count(*) AS RowNum, ifNull(status,-1) FROM stops ORDER BY id DESC LIMIT 1")
    If boolLatestStatus Then
        Log("Start")
    Else
        Log("Stop")
    End If

'' In DBUTILS
'return True if result = 0 otherwise False
Sub GetStatus(SQL As SQL, table As String, req As String) As Boolean
    Try
        Dim cur As Cursor = SQL.ExecQuery(req)
        cur.Position = 0

        Dim rowCount As Int = cur.GetInt2(0)
        If (rowCount = 0) Then
            Return False
        Else
            Return (IsNumber(cur.GetString2(1)) And (cur.GetInt2(1) == 0))
        End If
    Catch
        Log("GetIntFromSQL(" & req & ").error = " & LastException.Message)
        Return False
    End Try
End Sub

Finally, if you want to make short (but less readable), you can rewrite both Subs posted above as these and they'll give the same result, as it's not necessary to check the number of rows returned.

B4X:

Sub GetIntFromSQL2 (SQL As SQL, table As String, req As String) As Int
    Dim Const NegativeRes As Int = -2147483648
    Try
        Dim cur As Cursor = SQL.ExecQuery(req)
        cur.Position = 0
      
        If  IsNumber(cur.GetString2(1)) Then
            Return  cur.GetInt2(1)
        Else
            Return NegativeRes
        End If
    Catch
        Log("GetIntFromSQL(" & req & ").error = " & LastException.Message)
        Return NegativeRes
    End Try
End Sub
'''
'''
Sub GetStatus2(SQL As SQL, table As String, req As String) As Boolean
    Try
        Dim cur As Cursor = SQL.ExecQuery(req)
        cur.Position = 0
      
        Return (IsNumber(cur.GetString2(1)) And (cur.GetInt2(1) == 0))
    Catch
        Log("GetIntFromSQL(" & req & ").error = " & LastException.Message)
        Return False
    End Try
End Sub

peacemaker · Apr 2, 2020

Empty table is not covered, i guess

Frankie Lagrange · Apr 2, 2020

All cases are covered, empty or missing table. Pay attention to the query. Try the code and see.

peacemaker · Apr 2, 2020

Thanks, but actually what is the root difference from my code ? The same TRY\CATCH, the same returning values... no any "new magic"

Frankie Lagrange · Apr 2, 2020

Yours has 3 queries, my code only one and in all is shorter and easier to read.

And you missed the boolean implementation.

But, if you don't like it, don't use it.

Android Question Safe getting String and Int from SQLite

peacemaker

Expert

Frankie Lagrange

Member

Alex_197

Well-Known Member

Frankie Lagrange

Member

peacemaker

Expert

Frankie Lagrange

Member

peacemaker

Expert

Frankie Lagrange

Member

Similar Threads

Android Question Safe getting String and Int from SQLite

Expert

Member

Well-Known Member

Member

Expert

Member

Expert

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency