Android Tutorial DBUtils - Android databases are now simple!

     SQL.Initialize(File.DirInternal, "Data.db", True)
    'Load initial setting
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT * FROM tblSetting", Null)
    If m <> Null Then 
        lblLabel1.Text = m.Get("Field1")
        lblLabel2.Text = m.Get("Field2")
    End If

    'Create the students table which holds data about each student.
    Dim m As Map
    m.Initialize
    m.Put("Id", DBUtils.DB_TEXT)
    m.Put("First Name", DBUtils.DB_TEXT)
    m.Put("Last Name", DBUtils.DB_TEXT)
    m.Put("Birthday", DBUtils.DB_INTEGER)
    DBUtils.CreateTable(SQL, "Students", m, "Id")

Sub Activity_Create(FirstTime As Boolean)
    Dim TargetDir As String
    If FirstTime Then
      TargetDir = DBUtils.CopyDBFromAssets("Data.db")
     SQL.Initialize(TargetDir, "Data.db", True)
    End If
   
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT * FROM tblSetting", Null)
    If m <> Null Then 
        label1.Text = m.Get("Field1")
        label2.Text = m.Get("Field1")
    End If
End Sub

Sub CreateTableDefault(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
   Dim sb As StringBuilder
   sb.Initialize
   sb.Append("(")
   For i = 0 To FieldsAndTypes.Size - 1
     Dim field, ftype As String
     field = FieldsAndTypes.GetKeyAt(i)
     ftype = FieldsAndTypes.GetValueAt(i)
     If i > 0 Then sb.Append(", ")
     sb.Append("[").Append(field).Append("] ").Append(ftype)
     If field = PrimaryKey Then
       sb.Append(" PRIMARY KEY")
     Else
       Select ftype
         Case DBUtils.DB_TEXT                 : sb.Append(" DEFAULT '' ")
         Case DBUtils.DB_REAL, DBUtils.DB_INTEGER    : sb.Append(" DEFAULT 0 ")
         Case DBUtils.DB_BLOB  : sb.Append(" DEFAULT '' ") ' is this usefull ???
       End Select   
     End If     
   Next
   sb.Append(")")
   Dim query As String
   query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] " & sb.ToString
   Log("CreateTable: " & query)
   SQL.ExecNonQuery(query)
End Sub

'Executes the given query and creates a List that you can save as XML using File.WriteList(x,x,x)
'This code won't work with BLOB fields. Putting all BLOB fields at the end of the record and increase
'the -1 of the line "For i = 0 To cur.ColumnCount - 1" with the number of BLOB fields is an option.
Sub ExecuteXML (SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
    Dim table As List
    Dim name, value As String
    Dim cur As Cursor
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteXML: " & Query)
    If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount

    table.Initialize
    table.Add("<?xml version="&Chr(34)&"1.0"&Chr(34)&" encoding="&Chr(34)&"UTF-8"&Chr(34)&"?>")
    table.Add("<Table>")
    For row = 0 To Limit - 1
        cur.Position = row

        table.Add("<Record>")
        For i = 0 To cur.ColumnCount - 1
            value=EscapeXML(cur.GetString2(i))
            name=cur.GetColumnName(i)
            If(value=Null) Then
                table.add("<"&name&"></"&name&">")
            Else
                table.add("<"&name&">"&value&"</"&name&">")
            End If
        Next
        table.Add("</Record>")
    Next
    cur.Close
    table.Add("</Table>")
    Return table
End Sub

Sub EscapeXml(Raw As String) As String
  Dim sb As StringBuilder
  sb.Initialize
  For i = 0 To Raw.Length - 1
    Dim c As Char = Raw.CharAt(i)
    Select c
      Case QUOTE
         sb.Append("&quot;")
      Case "'"
         sb.Append("&apos;")
      Case "<"
         sb.Append("&lt;")
      Case ">"
         sb.Append("&gt;")
      Case "&"
         sb.Append("&amp;")
      Case Else
         sb.Append(c)
    End Select
  Next
  Return sb.ToString
End Sub

Sub ExecuteMapList(SQLAsSQL, Query AsString, StringArgs() AsString, Limit AsInt) As List
Dim cur As Cursor
If StringArgs <> Null Then
cur = SQL.ExecQuery2(Query, StringArgs)
Else
cur = SQL.ExecQuery(Query)
End If
Log("ExecuteMemoryTable: " & Query)
Dim TableList As List
TableList.Initialize
If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
For row = 0 To Limit - 1
cur.Position = row
Dim res As Map
res.Initialize
Dim values(cur.ColumnCount) AsString
For col = 0 To cur.ColumnCount - 1
Try
res.Put(cur.GetColumnName(col).ToLowerCase, cur.GetString2(col))
Catch
Dim byt() AsByte: byt = cur.GetBlob2(col)
res.Put(cur.GetColumnName(col).ToLowerCase, BytesToString(byt,0,byt.Length,"UTF8"))
End Try
Next
TableList.Add(res)
Next
cur.Close
Return TableList
End Sub

Dim TimeList As List
TimeList.Initialize
TimeList = DBUtils.ExecuteMapList(Main.SQL1,SQLScripts.UploadTimeList(WO),Null,0)

For cntr = 0 To TimeList.Size-1
        Dim TimeMap As Map
        TimeMap.Initialize
        TimeMap = TimeList.Get(cntr)
txtWO.text = TimeMap.Get("wdnum")
' etc

Next

  Dim recno As Int
    recno=dbcursor.GetInt("slno")
        Dim m As Map
        m.Initialize
        Dim WhereFieldEquals As Map
        WhereFieldEquals.Initialize
        WhereFieldEquals.Put("slno", recno)
        DBUtils.UpdateRecord(sql1, "table2","fav","yes" , WhereFieldEquals)
        ToastMessageShow("Added to your favourites Successfully",True)

Dim recno As Int
recno = dbcursor.GetInt("slno")

Dim Fields As Map
Fields.Initialize
Fields.Put("fav", "yes)

Dim WhereFieldEquals As Map
WhereFieldEquals.Initialize
WhereFieldEquals.Put("slno", recno)

DBUtils.UpdateRecord(sql1, "table2", Fields, WhereFieldEquals)

ToastMessageShow("Added to your favourites Successfully",True)

DBUtils.UpdateRecord(sql1, "table2","fav","yes" , WhereFieldEquals)

Dim mapFieldsToUpdate As Map
mapFieldsToUpdate.Initialize
mapFieldsToUpdate.Put("FieldName1", "Value1")
mapFieldsToUpdate.Put("FieldName2", "Value2")
mapFieldsToUpdate.Put("fav", "yes")
mapFieldsToUpdate.Put("FieldName3", 3)

Dim recno As Int
recno=dbcursor.GetInt("slno")