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,461
Last edited:

TyneBridges

Member
Licensed User
Longtime User
I'm probably being dim, but did I miss the instruction on how to create the database? I see the function CopyDBFromAssets, but am at a loss as to how to create the file programmatically, or otherwise without resorting to Java (I've given up on the Eclipse environment in frustration).

I'm a complete beginner with Android programming, although I do have experience of Borland Delphi and various client/server databases.

I'd be grateful for any pointers.
 

klaus

Expert
Licensed User
Longtime User
You should have a look at the Beginner's Guide for the B4A basics.
And also have a look at the User's Guide there is a chapter for SQLLite databases and DBUtils.
You could have look at the SQLiteDB example where a database is created in the code.

Best regards.
 

TyneBridges

Member
Licensed User
Longtime User
Thanks, Klaus. I had assumed that this would be in DBUtils but, searching further, I see that the information I need is in the previous chapter on SQLite databases.
 

Hubert Brandel

Active Member
Licensed User
Longtime User
Hi,

is it possible to write the current version near by the current ZIP - it is hard to read through to check if it is a newer version. I do have 1.08 should be the newest one ;-)
 

B4AJunkie

Member
Licensed User
Longtime User
Hi,
I've added a ExecuteList-Function. It works, but maybe someone can have a look whether this is good coding:

B4X:
'Executes the query and returns a List of maps with the column names as the keys 
'and record values As the entries values.
'The keys are lower cased.
'Returns Null if no results found.
Sub ExecuteList(SQL As SQL, Query As String, StringArgs() As String) As List
   Dim cur As Cursor
   Dim li As List
   li.Initialize
   If StringArgs <> Null Then 
      cur = SQL.ExecQuery2(Query, StringArgs)
   Else
      cur = SQL.ExecQuery(Query)
   End If
   Log("ExecuteMap: " & Query)
   If cur.RowCount = 0 Then
      Log("No records found.")
      Return Null
   End If
   For row=0 To cur.RowCount-1
      Dim res As Map
      res.Initialize
      cur.Position = row
      For i = 0 To cur.ColumnCount - 1
         res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
      Next
      li.Add(res)
   Next
   cur.Close
   Return li
End Sub
 

vecino

Well-Known Member
Licensed User
Longtime User
Hello, I bought a few weeks ago B4A license.
Now I'm trying to download these examples and dbutils, however I get a message saying that I have no permission.
What I can do?

regards
 

vecino

Well-Known Member
Licensed User
Longtime User
Hello, thank you very much, has been solved very quickly, I can finally download the samples.
regards
 

HKChua

Member
Licensed User
Longtime User
Hi Erel,

I downloaded the DBUtils example but I could not get the alternate line blue colour bar as per what you have shown on posting no. 2.

How do I get the blue background for the alternative bar within the list view?

Thanks.
 

HKChua

Member
Licensed User
Longtime User
The alternate blue line color is shown when you load the table into a WebView.

Best regards.

Thanks for thy response.

Is it possible to have alternate blue on the spnrTests_ItemClick and spnrStudentId_ItemClick?

Thanks again.
 

wheretheidivides

Active Member
Licensed User
Longtime User
I was wondering if anyone has made a tutorial on how to do databases in B4A. I have read the info that you are about to suggest, but nothing I have read online here is a tutorial. These are more like tons of code with a little explaination. A tutorial goes line by line. This is how you create a database....this is how you add a record...this is how you get a record...this is how you display a record.

It's like the beginner's tutorial on canvases which doesn't talk about canvases but instead goes into detail about degres to display a compass properly. I'm not being negative, but was wondering if someone has written instructions for a beginner. I just read 3 books on Unity and 1 on JavaScript and that is how they teach. Very easy if you do 1 thing at a time. So has anyone done something like that yet?

(remember the B in BASIC stands for beginner)
 

CidTek

Active Member
Licensed User
Longtime User
Complie error when adding DBUtils to project

All I did was add the module from the sample project (1.08).

I wrote no code that references it. I checked the Libs in the sample and added JSON to my project.

Note the sample project compiles with no error.


Parsing code. 0.05
Compiling code. 0.16
Compiling layouts code. 0.00
Generating R file. 0.09
Compiling generated Java code. Error
B4A line: 52
SQL.ExecNonQuery(query)
javac 1.6.0_21
src\com\domain\easyrider\dbutils.java:87: cannot find symbol
symbol : method ExecNonQuery(java.lang.String)
location: class com.domain.easyrider.sql
mostCurrent._sql.ExecNonQuery(_query);
^
1 error
 

vecino

Well-Known Member
Licensed User
Longtime User
Hello, how I can get to put whatever format each column (field) obtained by ExecuteListView and ExecuteHtml?
In other words, display a table format and alignment corresponding to each column. example:

Thank you very much and greetings
 

Attachments

  • asiq.jpg
    asiq.jpg
    21.8 KB · Views: 482
Top