Android Question error trapping in DButils

peacemaker

Expert
Licensed User
HI, All

I'm trying to automate the JSON parsing to SQLite DB.
I have such subs to do it:

B4X:
'checks if the new table structure is received
Sub JSON_isNewJsonMap (SQL As SQL, Table As String, SourceMap As Map) As Boolean
    Dim Res As ResultSet = SQL.ExecQuery($"PRAGMA table_info(${Table})"$)
    Dim db_fields As Map:db_fields.Initialize
    Do While Res.NextRow
        Dim name As String = Res.GetString("name")    'db table existing field name
        If name = "rowid" Then
            Continue
        End If
        db_fields.Put(name, "")
    Loop
    Res.Close
    For i = 0 To SourceMap.Size - 1
        Dim key As String = SourceMap.GetKeyAt(i)    'a field from JSON to be checked
        If db_fields.ContainsKey(key) = False Then
            Return True    'old DB has no field from new JSON
        End If
    Next 
    Return False
End Sub

'RowsListOfMaps - parsed from JSON: say Items=[{PositionType=1, Name=Name1, Count=1}, {PositionType=2, Name=Name2, Count=0.3}]
Sub JSON_CreateTableAuto(SQL As SQL, TableName As String, RowsListOfMaps As List)
    If RowsListOfMaps.Size = 0 Then Return
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("(")
    Dim row As Map = RowsListOfMaps.Get(0)
    For i = 0 To row.Size - 1
        Dim field, ftype, fvalue As String
        field = row.GetKeyAt(i)
        fvalue = row.GetValueAt(i)
        If isNumber3(fvalue) = False Then
            ftype = DBUtils.DB_TEXT
        Else
            If fvalue.Contains(".") Then
                ftype = DBUtils.DB_REAL
            Else
                ftype = DBUtils.DB_INTEGER
            End If
        End If
        If i > 0 Then sb.Append(", ")
        sb.Append("[").Append(field).Append("]").Append(" ").Append(ftype)
    Next
    sb.Append(", id INTEGER PRIMARY KEY")
    sb.Append(")")
    Dim query As String = "CREATE TABLE IF NOT EXISTS [" & TableName & "] " & sb.ToString
    SQL.ExecNonQuery(query)
End Sub

Sub JSON_CreateTableStructure(SQL As SQL, TableName As String, RowMap As Map)
    If RowMap.IsInitialized = False Then Return
    If RowMap.Size = 0 Then Return
    Dim ft As Map:ft.Initialize
    Dim field, ftype, fvalue As String
    For i = 0 To RowMap.Size - 1
        field = RowMap.GetKeyAt(i)
        fvalue = RowMap.GetValueAt(i)
        If isNumber3(fvalue) = False Then
            ftype = DBUtils.DB_TEXT
        Else
            If fvalue.Contains(".") Then
                ftype = DBUtils.DB_REAL
            Else
                ftype = DBUtils.DB_INTEGER
            End If
        End If
        ft.Put(field, ftype)
    Next
    DBUtils.CreateTable(SQL, TableName, ft, "id", True)    'table in DB
End Sub

Sub JSON_GetObjFromPath(obj As Object, path As String) As Object
    Dim keys() = Regex.Split("/", path) As String : path = ""
    If keys.Length == 0 Then Return obj
    '--------------------------------------------------------
    Dim key As String
    For i = 0 To (keys.Length - 1)
        key = keys(i) : If key <> "" Then Exit
    Next
    If key.Length == 0 Then Return obj
    '--------------------------------------------------------
    For j = i + 1 To (keys.Length - 1)
        path = path & keys(j) & "/"
    Next
    If obj Is Map Then
        Dim map = obj As Map
        obj = JSON_GetObjFromPath(map.GetDefault(key, Null), path)
    Else If obj Is List Then
        Dim lst = obj As List
        Try
            obj = JSON_GetObjFromPath(lst.Get(key), path)
        Catch
            obj = Null
        End Try
    End If
    Return obj
End Sub

Sub isNumber3(s As Object) As Boolean
    Dim str As String = s
    If IsNumber(str) Then
        If str.StartsWith("+") Then
            Return False
        Else
            Dim d As Double = str
            Return d <> d + 1
        End If
    Else
        Return False
    End If
End Sub

And if to try to insert a new set of records from JSON:
B4X:
'...API...
If Job.Success Then
  Dim jp As JSONParser
  Select Job.JobName
    Case "some_reply"
            jp.Initialize(Job.GetString)
            Dim L As List = jp.NextArray
            If L.Size > 0 Then
                Dim m As Map = L.Get(0)
                If others.JSON_isNewJsonMap(Starter.SQL, "table", m) Then
                    DBUtils.DropTable(Starter.SQL, "table")
                    others.JSON_CreateTableStructure(Starter.SQL, "table", m)
                End If
                DBUtils.InsertMaps(Starter.SQL, "table", L)   'Sure here error
            End If
...sure it's error, unique primary key does not allow to add the same records - and it's very good:

SQLite system error:
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: sms.id (code 1555)

But how correctly to check and trap such SQL system errors, if the data structure is not detail parsed, incapsulated in these subs and our "standard" DButils ?
 
Top