Android Question Safe getting String and Int from SQLite

[peacemaker]

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 ?

'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

 

[LucaMs]

-1 could be a valid number of some field.

Use always version 2 of ExecQuerys.

 

[peacemaker]

always version 2 of ExecQuerys.

Getting a cursor ? The main question is: how to check the returned result and normalize it to the "normal" value variant to avoid null-errors ... And all this in one sub to use easily and safe.
-1 here is just non-used value, can be changed.

 

[LucaMs]

Getting a cursor ?

Always, quering a db, get used to, avoid the non-parameterized version.

-1 here is just non-used value, can be changed.

Since I suppose you want to use those routines on any database, you have to consider that -1 can be valid data in a table, you shouldn't use it as a non-value.

As for the validity of the routines... I should read them better but they seem good to me (except for that -1)

 

[peacemaker]

Universally, sure, impossible to use any number value as "incorrect" variant. But how to be null-error free for numbers ?

 

[LucaMs]

Not tested
ExecQuerySingleResult
but
GetInt2

In case of Null, it returns a very dangerous 0 (B4J).

 

[peacemaker]

Cursor.GetInt2 returns 0 ?
And what is difference from my -1 without Cursor

 

[LucaMs]

Cursor.GetInt2 returns 0 ?
And what is difference from my -1 without Cursor

None, both are dangerous; I'm just writing this to "alert all of us".

As I wrote, I have not tried ExecQuerySingleResult but if this also returns 0 when a field is Null, your routine will not work as expected:

If a = Null Then

 

[LucaMs]

As I wrote, I have not tried ExecQuerySingleResult but if this also returns 0 when a field is Null, your routine will not work as expected:

ExecQuerySingleResult returns a String, "null" if the field data is null.

The big problem is with the Cursor's GetInt.

 

[peacemaker]

So, my code is OK, if -1 is really non-used value in this db ?

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

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

 

[LucaMs]

So, my code is OK, if -1 is really non-used value in this db ?

In your GetIntFromSQL

If a = Null Then

If a = "null" Then
is enough.

 

[Erel]

I would never use such code.

1. Don't catch and hide errors.
2. Either run a query that always returns a result (eg: SELECT count(*) ...) or don't use ExecQuerySingleResult.
3. Never use ExecQuery or ExecQuerySingleResult. Use ExecQuery2 or ExecQuerySingleResult2.

 

[peacemaker]

3) Why 2-versions are better ?
So, safe variant is always to check first SELECT count(*) ... ?

 

[Erel]

Why 2-versions are better ?

Because you should use parameterized queries.

So, safe variant is always to check first SELECT count(*) ... ?

Not exactly. Either make queries that always return results or don't use ExecQuerySingleResult(2).

 

[peacemaker]

So - how correctly (error-free) to get single Int value from a SQL table (that may be empty) ? By some sub that will be often used to check this Int value.

 

[Frankie Lagrange]

There are two cases to consider:
1. Either no row is returned and you have to deal with the exception.
2. A row is returned but the column is null. The cleanest way is to wrap the column with the Sqlite function ifnull() . In other words, deal with it within the query itself.

 

[Frankie Lagrange]

@peacemaker, here's an SQL trick that should simplify your life:

Let's assume you have a table called Cards. When you query it you get, for example, this:

Select *
From  Cards;

id    ndo    value   suit  
AH    1       Ace  Hearts  
2H    2       2    Hearts  
3H    3       3    Hearts 
4H    4       4    Hearts  
5H    5       5    Hearts  
6H    6       6    Hearts  
7H    7       7    Hearts 
8H    8       8    Hearts

Now if you were to query this table with this Sql statement:

Select *
From Cards
Where id = 'N/A';

No rows would be returned and an exception raised.

Here's a work around which is easy to implement and costs hardly anything, time wise that is:

select COUNT(*) AS RowNo, *
From cards
Where ID = 'N/A';

RowNo id    ndo    value   suit  
0     NULL  NULL   NULL    NULL

In essence, you're guaranteed to have a row returned and you can check with (pseudo) column RowNo (or whatever name you want) how many rows (0 or 1) were returned. You combine two queries in one.

 

[peacemaker]

I'm trying to use now as the safe errorless sub:

'return -2147483648, if no valid number data
Sub GetIntFromSQL (SQL As SQL, table As String, req As String) As Int
    Dim Const NegativeRes As Int = -2147483648
    If TableExists(SQL, table) = False Then
        Log("GetIntFromSQL(" & table & ") does not exist")
        Return NegativeRes
    End If
    Dim q As Int = SQL.ExecQuerySingleResult("SELECT count(*) FROM " & table)
    If q = 0 Then
        Return NegativeRes
    End If
    Dim a As String
    Try
        'req may be wrong syntax, dangerous
        a = SQL.ExecQuerySingleResult(req)
    Catch
        Log("GetIntFromSQL(" & req & ").error = " & LastException)
        Return NegativeRes
    End Try

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

If any interest - offer your code sub: main is the app crash free solution.

 

[Frankie Lagrange]

Could post an example of SQL statement you use to call GetIntFromSQL, so I'm sure I understand what you want.

 

[peacemaker]

an example of SQL statement you use

Dynamically creating and filling table of the system state, that being checked in many points:

    Dim LatestStatus As Int = DBUtils.GetIntFromSQL(Starter.SQL, "stops", "SELECT status FROM stops ORDER BY id DESC LIMIT 1")
    If LatestStatus = 0 Then
        others.Save_Stop("Start", 1)
    else
        others.Save_Stop("Stop", 0)
    End If