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

 

[LucaMs]

If you have the last version of B4A, you shoud move this code to the Starter Service

 

[Declan]

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:

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:

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]

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:

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:

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]

Thanks Colin,
That was it.
I changed some code

    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]

Hello, hello!

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

'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

 

[Erel]

It is better to start a new thread for this.

 

[Erel]

New version was uploaded to the first post.

Fixed all the warnings and improved the layout.

 

[konradwalsh]

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

 

[MbedAndroid]

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

 

[konradwalsh]

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

thanks

 

[StefanoAccorsi]

Hi there. Strange issue with CopyDBFromAssets ...

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

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

but I got an error inside CopyDBFromAssets:

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:

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.

 

[LucaMs]

Likely the problem is related to this topic.

 

[StefanoAccorsi]

Thank you @LucaMe but my targetSdkVersion is 19 so I don't think could be the problem

 

[LucaMs]

Thank you @LucaMe but my targetSdkVersion is 19 so I don't think could be the problem

Schiaffaci un bel DirInternal che è poi il posto migliore (come traduco "schiaffaci"? ).

Use DirInternal, it's the best place for app's data.

Schiaffaci = slap in

 

[Erel]

Why did you modify this sub?

Try to uninstall your app and then clean the project (Ctrl + P) and run it again.

 

[StefanoAccorsi]

@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.

 

[Erel]

V1.20 is now available. The targetSdkVersion is set to 23. DBUtils uses RuntimePermissions.GetSafeDirDefaultExternal to get the path to a writable folder.

 

[alon]

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

 

[Erel]

ALTER TABLE is explained here: https://www.sqlite.org/lang_altertable.html

 

[klaus]

B4A User's Guide chapter 5 SQLite Database, and more specific chapter 5.1.14 Add a column.