Android Tutorial DBUtils - Android databases are now simple!

Erel · Mar 30, 2011

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.

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

lorebarita · Nov 23, 2011

Thanks.

cirollo · Dec 24, 2011

cannot update a record with dbutils

Hello!

I cannot understand why I cannot update a record on a table this way:

Sub BtnSalva_Click
Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("IdConfig", "EMMECI")
DBUtils.UpdateRecord(SQL, "Impostazioni", "IdAge", "ciro", WhereFields)
End Sub

I got an error msg in the dbutils module at line 127!

Erel · Dec 25, 2011

Please use [ code ] tags (without the spaces).
What is line 127?

What error message do you get?

cirollo · Dec 26, 2011

here's the error

this is my 127 line of dbutils module

SQL.ExecNonQuery2(sb.ToString, args)

the error it raises is:

LastException.android.database.SQLLite.SQLLiteconstraintexception:error code 19: constraint failed

I don't know what could be :-(

Erel · Dec 26, 2011

You are probably trying to set a non-unique value to a column that must be unique (like primary key).

cirollo · Dec 27, 2011

trying to update

Well. Erel
what i'm trying to do istituto to update the fields of the record with primary key
idconfig=emmeci
maybe i didn't understand how update works

Erel · Dec 28, 2011

Make sure that you change the primary key value to a value that doesn't already exist.

cirollo · Dec 28, 2011

but....

with this:

Sub BtnSalva_Click
Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("IdConfig", "EMMECI")
DBUtils.UpdateRecord(SQL, "Impostazioni", "IdAge", "ciro", WhereFields)
End Sub

I'm not updating an existing record identified by the primary key='EMMECI'????

I don't want to inser a new record, only to change some fields in an existing one.

what's wrong???
thanks

Erel · Dec 29, 2011

If you like you can upload your complete project (or a working subset of it) and I will check it.

rickydalley · Jan 9, 2012

Not working in emulator

The project doesn't work in the emulator.

It says LastException android.database.sqlite.SQLiteException: unable to open database

B4X:

   If FirstTime Then
      SQL.Initialize(File.DirRootExternal, "mytest.db",True)
   End If

but if I run this on a Samsung Galaxy Tablet it runs fine.

What am I doing wrong? My first assumption would be that the app can't write to the emulator and if it is that I don't know what to do.

regards, Ricky

Erel · Jan 9, 2012

I guess that the emulator you set was configured without a storage card. Create a new emulator (AVD) and set the storage card size to 16mb.

Ricky D · Jan 9, 2012

Thanks

my new username wouldn't work for some reason.

This is my old one from my b4ppc days on my win phone 6.1 lol

I recreated the emulator and it works. Thanks

regards, Ricky

Ricky D · Jan 20, 2012

executehtml question

I am wondering what the list argument in the call is? I've tried to search but it's not anywhere.
Regards, Ricky

Erel · Jan 22, 2012

You can open the DBUtils module and see the method (or you can select the method and press F7 to get to the method or you can press Ctrl + E and write this method name).

See this post for more information about the "clickable" parameter: http://www.b4x.com/forum/basic4andr...s-android-databases-now-simple.html#post50625

splatt · Feb 4, 2012

HTML Column Width

In the ExecuteHtml sub routine, I assume the width of the columns is based on the column name. Is it possible to make this width greater, so that longer text items display in a more readable way?

Erel · Feb 5, 2012

The width is set automatically by WebView. It considers both the title and the cells text.

If you want to manually set the width you can modify ExecuteHtml sub.
For example to set the second column width you can use this code:

B4X:

For i = 0 To cur.ColumnCount - 1
      If i = 1 Then
         sb.Append("<th style='width:200px;'>").Append(cur.GetColumnName(i)).Append("</th>")
      Else
         sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
      End If
   Next

splatt · Feb 6, 2012

I tried this and it did not make any difference.

The resulting table has three columns of equal width, which appears to be dictated by the width of the webview.

Is it possible to produce wider tables, as we have the ability to zoom in or out anyway?

Erel · Feb 7, 2012

The table width is set by the value of HtmlCSS variable.

splatt · Feb 7, 2012

Excellent. Thanks for that Erel. I've set my report width to 150% and it works fine now.

Penko · Mar 13, 2012

Erel, what is the reason to lowercase the database field names in ExecuteMap?

I was searching for an error in my code until I used Log for my map object only to see that my key is "list" instead of "LIST".

I am just asking if there is any reason to do so provided that I use uppercase fields when designing databases(no matter MySQL, SQLite or anything else) because it's easier to read them(at least for me).

Of course I can pass the lowercase value of my name but I wondered why is this?

Edit: I have another question. By returning "Null" in various occassions, what is the best way to get my results? I've used:

B4X:

txtFileName.Text = values.Get("list")
   txtNotes.Text = values.Get("notes")

This crashes when my database field is empty. I had to apply the following change to DBUtils in order to make it return a blank string which won't crash my application.

B4X:

Dim val As Object
   
   For i = 0 To cur.ColumnCount - 1
   
   val = cur.GetString2(i)
   
   If(val = Null) Then
      val = ""
   End If

      res.Put(cur.GetColumnName(i).ToLowerCase, val)
   Next

Unfortunately, it won't work for INTEGER.

Android Tutorial DBUtils - Android databases are now simple!

Attachments

Member

Active Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

New Member

B4X founder

Well-Known Member

Well-Known Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency