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:
B4X:
    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:
B4X:
    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:
B4X:
    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.

dbutils_1.png


dbutils_3.png


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:
B4X:
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
 

Attachments

  • DBUtils.zip
    14.4 KB · Views: 5,463
Last edited:

Declan

Well-Known Member
Licensed User
Longtime User
I am using the DBUtils eample and editing it for use in my app.
I am attempting to change the id field in the students table to an alpha text string.
I have a EditText "txtChangeID" and a Button "btnChangeID"
My Code is:
B4X:
Sub btnChangeID_click
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put("id", spnrStudentId.SelectedItem)
    DBUtils.UpdateRecord(SQL, "students", "id", txtChangeID.Text, WhereFields)
   
    'Fill the students id spinner (aka combo box).
    DBUtils.ExecuteSpinner(SQL, "SELECT Id FROM Students", Null, 0, spnrStudentId)
    spnrStudentId_ItemClick(0, spnrStudentId.GetItem(0))
End Sub

The above works and the new id is written to the table correctly.
However, when I select the "new" text string id I get the following error:
An error has occurred in
sub:main_spnrstudentid_itemclick (java line:
485)
java.lang.IndexOutOfBoundsException: Invalid index 0, size is 0
Continue?

The code in spnrStudentId_Itemclick is:
B4X:
Sub spnrStudentId_ItemClick (Position As Int, Value As Object)
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT Id, [First Name], [Last Name], Birthday FROM students WHERE id = ?", _
        Array As String(Value))
    If m = Null Or m.IsInitialized = False Then 'Null will return if there is no match
        lblStudentName.Text = "N/A"
        lblBirthday.Text = ""
    Else
        lblStudentName.Text = m.Get("first name") & " " & m.Get("last name") 'keys are lower cased!
        lblBirthday.Text = DateTime.Date(m.Get("birthday"))
    End If
    'Get the tests for this specific student (currently it is all tests).
    DBUtils.ExecuteSpinner(SQL, "SELECT test FROM Grades WHERE id = ?", _
        Array As String(Value), 0, spnrTests)
    spnrTests.SelectedIndex = 0
    spnrTests_ItemClick(0, spnrTests.GetItem(0))
    'FindFailedTests(Value)
End Sub
 

Computersmith64

Well-Known Member
Licensed User
Longtime User
I am using the DBUtils eample and editing it for use in my app.
I am attempting to change the id field in the students table to an alpha text string.
I have a EditText "txtChangeID" and a Button "btnChangeID"
My Code is:
B4X:
Sub btnChangeID_click
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put("id", spnrStudentId.SelectedItem)
    DBUtils.UpdateRecord(SQL, "students", "id", txtChangeID.Text, WhereFields)
 
    'Fill the students id spinner (aka combo box).
    DBUtils.ExecuteSpinner(SQL, "SELECT Id FROM Students", Null, 0, spnrStudentId)
    spnrStudentId_ItemClick(0, spnrStudentId.GetItem(0))
End Sub

The above works and the new id is written to the table correctly.
However, when I select the "new" text string id I get the following error:


The code in spnrStudentId_Itemclick is:
B4X:
Sub spnrStudentId_ItemClick (Position As Int, Value As Object)
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT Id, [First Name], [Last Name], Birthday FROM students WHERE id = ?", _
        Array As String(Value))
    If m = Null Or m.IsInitialized = False Then 'Null will return if there is no match
        lblStudentName.Text = "N/A"
        lblBirthday.Text = ""
    Else
        lblStudentName.Text = m.Get("first name") & " " & m.Get("last name") 'keys are lower cased!
        lblBirthday.Text = DateTime.Date(m.Get("birthday"))
    End If
    'Get the tests for this specific student (currently it is all tests).
    DBUtils.ExecuteSpinner(SQL, "SELECT test FROM Grades WHERE id = ?", _
        Array As String(Value), 0, spnrTests)
    spnrTests.SelectedIndex = 0
    spnrTests_ItemClick(0, spnrTests.GetItem(0))
    'FindFailedTests(Value)
End Sub

Where is the app crashing? It seems that you are assuming it's spnrStudentId that is causing it, but could it be the call you are making to spnrTests_ItemClick on the last line of your spnrStudentId_ItemClick sub? Are there records in the Grades table with a matching id?

Actually, looking at it again, if it's a problem with 0 records coming from your Grades table, the crash would be caused in your spnrTests.SelectedIndex = 0 line because the spinner will be empty.

- Colin.
 

Declan

Well-Known Member
Licensed User
Longtime User
Thanks Colin,
That was it.
I changed some code
B4X:
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put("id", spnrStudentId.SelectedItem)
    DBUtils.UpdateRecord(SQL, "students", "id", txtChangeID.Text, WhereFields)
    DBUtils.UpdateRecord(SQL, "Grades", "id", txtChangeID.Text, WhereFields)

This also writes the new ID to the Grades table.
 

wonder

Expert
Licensed User
Longtime User
Hello, hello!

Can anyone help me modify the ExecuteMemoryTable method into asynchronous mode?
Many thanks in advance! :)

B4X:
'Executes the query and returns the result as a list of arrays.
'Each item in the list is a strings array.
'StringArgs - Values to replace question marks in the query. Pass Null if not needed.
'Limit - Limits the results. Pass 0 for all results.
Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) 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 table As List
    table.Initialize
    If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
    For row = 0 To Limit - 1
        cur.Position = row
        Dim values(cur.ColumnCount) As String
        For col = 0 To cur.ColumnCount - 1
            values(col) = cur.GetString2(col)
        Next
        table.Add(values)
    Next
    cur.Close
    Return table
End Sub
 

konradwalsh

Active Member
Licensed User
Longtime User
I read this entire thread and checked the examples but cant see how I store a double or decimal..
Maybe thats what real is but not sure

Nevermind... i wasnt testing real correctly
 
Last edited:

MbedAndroid

Active Member
Licensed User
Longtime User
dont use a real, but text instead. In that case you will get more precision. With a real the fraction is limited, see some topics about this
 

StefanoAccorsi

Member
Licensed User
Longtime User
Hi there. Strange issue with CopyDBFromAssets ...

I put my sqlite file in Files tab. Then run:

B4X:
If FirstTime Then
   targetDbDir = DBUtils.CopyDBFromAssets("xxx.sqlite")
   SQLHH.Initialize(targetDbDir, "xxx.sqlite", False)
End If

but I got an error inside CopyDBFromAssets:

B4X:
java.io.FileNotFoundException: /data/user/0/b4a.example/files/virtual_assets/xxx.sqlite: open failed: ENOENT (No such file or directory)

I modified the CopyDBFromAssets as follows:

B4X:
1. Public Sub CopyDBFromAssets (FileName As String) As String
2.     Dim TargetDir As String
3.     If File.ExternalWritable Then TargetDir = File.DirDefaultExternal Else TargetDir = File.DirInternal
4.     If File.Exists(TargetDir, FileName) = False Then
5.         If File.Exists(File.DirAssets, FileName) Then
6.             File.Copy(File.DirAssets, FileName, TargetDir, FileName)
7.         End If
8.     End If
9.     Return TargetDir
10.End Sub

just to find out that the line 5 returns True while the line 6 generates the error reported above.

How is it possible? Thank you.
 

StefanoAccorsi

Member
Licensed User
Longtime User
@Erel I modified the code in DBUtils only to check the strange behaviour I described. Anyway I totally changed the code, for now: for testing purposes I created and populated the DB programmarically. Anyway I created another project and as first thing I tried the code above: no errors this time.

Probably something went wrong with the other project: I tried what Erel suggested but the error was always there.

When I'll have to change the code and "re-integrate" the external sqlite DB in the previous project, I'll let you know if I'll encounter that issue again.
Thank you.
 

alon

Active Member
Licensed User
Longtime User
Modified DBUtils 1.08

I did some small changes to the DBUtils module (and changed version to v1.08).

1. renamed cursor variables from c to cur since I regularly use a code module named C for defining constants. It's not possible to name a variable like a code module.

2. Added small and simple version control to database. With GetDBVersion() you can get the current database version and with SetDBVersion() you can set the current database version. If you release your App to public and make an update to the database later (change table structure) you will have to supply a migration function for your database so old databases get migrated to the new database structure. With the two version subs you can easily handle this. Just check in the beginning of your app, which version your database has. If it's not the current version then you can call the migration sub. A simple example how this can be done is in the example code.

@Erel: Hopefully you will copy this to the first post?

Do you know where can i find a sample or instruction , of how the migration for sql db is working.
Because i need to add new columns from time to time.
Thanks
 
Top