Android Tutorial DBUtils - Android databases are now simple!

Latest version available here: https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/

The DBUtils code module is designed to help you integrate SQLite databases in your program.
It contains a set of methods for common SQL tasks. Issues like escaping strings and identifiers are handled automatically.
The code module is included in the attached example project.
As of version 1.07, the following methods are included:

- CopyDBFromAssets: Copies a database file stored in the APK package (added in the Files tab) to a writable location. Only copies if the file doesn't yet exist.

- CreateTable: Creates a SQL table with the given fields and types. Also sets the primary key (optionally).

- DropTable: Deletes a table.

- InsertMaps: Efficiently inserts records to a table. The data is passed as a List that contains maps as items. Each map holds the fields and their values.
Example:

    Dim ListOfMaps As List
    ListOfMaps.Initialize
    For i = 1 To 40
        Dim m As Map
        m.Initialize
        m.Put("Id", Id)
        m.Put("First Name", "John")
        m.Put("Last Name", "Smith" & i)
        ListOfMaps.Add(m)
    Next
    DBUtils.InsertMaps(SQL, "Students", ListOfMaps)

- UpdateRecord: Updates an existing record. Accepts the field name with the new value and a map of the 'where' fields.
Example:

    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put("id", "1234")
    WhereFields.Put("test", "test #5")
    DBUtils.UpdateRecord(SQL, "Grades", "Grade", 100, WhereFields)

- ExecuteMemoryTable: Executes a SELECT query and reads all the records into a list. Each item in the list is an array of strings that represents a record in the result set.

- ExecuteMap: Executes a SELECT query and returns the first record stored in a Map object. The columns names are the keys and the values are the map values.

- ExecuteSpinner: Executes a SELECT query and fills a Spinner view (ComboBox) with the values of the first column.

- ExecuteListView: Executes a SELECT query and fills the ListView with the values of the first column and optionally of the second column.

- ExecuteJSON: Executes a SELECT query and returns a Map which you can pass to JSONGenerator to generate JSON text.
Example:

    Dim gen As JSONGenerator 'Requires a reference to the JSON library.
    gen.Initialize(DBUtils.ExecuteJSON(SQL, "SELECT Id, [Last Name], Birthday FROM Students", Null, _
        0, Array As String(DBUtils.DB_TEXT, DBUtils.DB_TEXT, DBUtils.DB_INTEGER)))
    Dim JSONString As String
    JSONString = gen.ToPrettyString(4)
    Msgbox(JSONString, "")

See the attached example and the DBUtils code module for more information about each method.

- ExecuteHtml: Creates a nice looking html table out of the results. You can show the results using WebView. This method is very useful both for development, as it allows you to see the data and also for reports.
You can change the table style by changing the CSS variable.
New: The last parameter defines whether the values will be clickable or not.
If the values are clickable then you should catch WebView_OverrideUrl event to find the clicked cell.

- GetDBVersion / SetDBVersion: Gets or sets the database version. The value is stored in a separate table named DBVersion. This is useful to manage updates of existing databases.
This feature was implemented by corwin42. Thank you!

About the example. In this example we are creating two tables: Students and Grades. The students table lists all the students.
The grades table lists the grade of each student in each test.

Uncomment ExportToJSON or ShowTableInWebView calls to see those features.

To hide the table, comment the call to ShowTableInWebView in Activity_Create.

Current version: 1.20
It depends on the following libraries: JSON, RuntimePermissions, SQL and StringUtils.

Make sure to add the following snippet to the manifest editor:

AddManifestText(
<uses-permission
  android:name="android.permission.WRITE_EXTERNAL_STORAGE"
  android:maxSdkVersion="18" />
)

See RuntimePermissions tutorial for more information: https://www.b4x.com/android/forum/threads/runtime-permissions-android-6-0-permissions.67689/#content

 

[Erel]

Please start a new thread for this question.

 

[Tonycompu]

Hello,

I'm testing the code but something strange is happening.

I have my db with a table called tblSetting and a single row. I'm trying to use this code

     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

but I got this error:
** Activity (main) Create, isFirst = true **


Error occurred on line: 0 (dbutils)
android.database.sqlite.SQLiteException: no such table: tblSetting (code 1): , while compiling: SELECT * FROM tblSetting


at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:167)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:155)


at b4a.example.dbutils._executemap(dbutils.java:39)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
at b4a.example.main.afterFirstLayout(main.java:98)
at b4a.example.main.access$100(main.java:16)
at b4a.example.main$WaitForLayout.run(main.java:76)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5017)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
at dalvik.system.NativeStart.main(Native Method)

But something more strange is happening when I use this code

    '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")

The app run fine but no table is created.

What's wrong with this.

Please help.

 

[Tonycompu]

You can't use DirInternal for your db, because files in that folder are read-only.

Move it to DirDefaultExternal.

but what happen if I'm using only the emulator for test ?, where should this file be ?

 

[CidTek]

but what happen if I'm using only the emulator for test ?, where should this file be ?

same... DirDefaultExternal

 

[Erel]

You need to add the db file to the Files tab in the IDE and then copy it with DBUtils.CopyDBFromAssets

 

[LucaMs]

but what happen if I'm using only the emulator for test ?, where should this file be ?

I wrote something entirely wrong.

I instinctively read "dirAssets", which contains files read only, not the DirInternal.

Sorry

 

[Tonycompu]

You need to add the db file to the Files tab in the IDE and then copy it with DBUtils.CopyDBFromAssets

Ok, I've added the db file to the Files tab and I did this change:

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

and still it isn't working. I got the same error : "android.database.sqlite.SQLiteException: no such table: tblSetting (code 1): , while compiling: SELECT * FROM tblSetting"

 

[Erel]

There are two options:
1. There is no such table in your database.
2. There is already an existing empty database file from your previous attempts so the new database is not copied (DBUtils.CopyDBFromAssets will not overwrite an existing database).

You can delete it with File.Delete(File.DirDefaultExternal, "Data.db"). Run it before you call CopyDBFromAssets.

 

[Tonycompu]

There are two options:
1. There is no such table in your database.
2. There is already an existing empty database file from your previous attempts so the new database is not copied (DBUtils.CopyDBFromAssets will not overwrite an existing database).

You can delete it with File.Delete(File.DirDefaultExternal, "Data.db"). Run it before you call CopyDBFromAssets.

Excelent. Option 2 was it.

Thanks

But, Now I have another issue. it isn't reading the field info, I'm getting NULL in label1 and label2 even though m is not NULL, it seems that
m.Get("Field1") is not getting the value. I've double checked the field names and they are ok.

how can I fix this ?

thank again

 

[Tonycompu]

Never mind. I've fixed it.

I found the solution in the DBUtils sample: 'keys are lower cased!

Thank you Erel, you're doing a great job.

 

[Hubert Brandel]

Hi,

the function CreateTable() is surly usefull for peoble used to handle SQL servers with NULL values,
but in most cases a DEFAULT "" for TEXT or 0 with numeric fields is better in handling.
So I wrote this function, maybe you want to add this to the lib ?

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

 

[AHilberink]

Hi,

I needed to export to XML, so I made an extra function. May be also usefull for sombody else?

'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

 

[Erel]

Note that you should use the code I posted here: http://www.b4x.com/android/forum/threads/escapexml-code-snippet.35720/#content to escape some of the characters.

 

[AHilberink]

Note that you should use the code I posted here: http://www.b4x.com/android/forum/threads/escapexml-code-snippet.35720/#content to escape some of the characters.

Hi Erel,

Thanks. I added the your code to my previous post. I also added a remark about BLOB fields at the beginning.

Best regards,
André

 

[RVP]

I have added a ExecuteMapList to this It returns a List of Maps.

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

Used like this

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

 

[mkvidyashankar]

I am trying to update a single record using this code

  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)

but the database is not updating, please help

 

[LucaMs]

Unless it is changed, UpdateRecord should receive these parameters:

DB (SQL type);
TableName (String)
FIELDS (MAP)
WhereFieldEquals (Map)

Then you should use:

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)

 

[mkvidyashankar]

Unless it is changed, UpdateRecord should receive these parameters:

DB (SQL type);
TableName (String)
FIELDS (MAP)
WhereFieldEquals (Map)

Then you should use:

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)

Thanks LucaMs for quick reply
i have given these parameters in the code
is there any thing wrong, could you please explain
Thanks in advance

 

[LucaMs]

The code in your post was:

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

If you have a field "fav" and you want to set its content to "yes", using the UpdateRecord method you must use a map as parameter.

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

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


(I assumed that this code is correct:

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

)

 

[klaus]

@LucasMS
you are confusing UpdateRecord with UpdateRecord2.
In UpdateRecord Field is the column name as a String.
In UpdateRecord2 Fields is a Map.

@mkvidyashankar
For me your code should work.
Is the column name OK ?