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

mkvidyashankar · Nov 12, 2014

@LucasMS
yes LucaMs
Column name is correct

LucaMs · Nov 12, 2014

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

Ehm... my version of DBUtils contains

B4X:

Sub UpdateRecord(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map)

LucaMs · Nov 12, 2014

11 months ago I changed "DBUtils" for my purposes.

I have:

for single field:
Sub UpdateRecordField(SQL As SQL, TableName As String, Field As String, NewValue As Object, _
WhereFieldEquals As Map) 'ignore

for more fields:
Sub UpdateRecord(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map) 'ignore

But I made the mistake of not correctly updating the "header" of the module

Now I can see:
' - Added UpdateRecord2 updates more than one field in a record,
in the module attached here.

I'm sorry

mkvidyashankar · Nov 12, 2014

thanks Lucas

it seems to be that module is not attached

LucaMs · Nov 12, 2014

mkvidyashankar said:
thanks Lucas

it seems to be that module is not attached

I was referring to the module attached to the first post of this thread.

mkvidyashankar · Nov 12, 2014

ok ok thank you
i will try again

Mashiane · Jan 8, 2015

Thanks for the XML exports, was about to have my db as xml file and found this!! wow. great stuff.

Tom1s · Mar 27, 2015

I have a table in flexible table and when clicking the row I would like to move it to another table.
How should I do it the easiest way? I can have row number when clicking. Below is not working yet

B4X:

Dim Rowmap As Map
    Rowmap.Initialize
    Rowmap=DBUtils.Executemap(SQL1,"Select Timestamp,Car from DBTableName1 WHERE ID= row",Null)

DBUtils.InsertMaps(SQL1,"Loaded",Rowmap) 'and put in new data

Thanks

klaus · Mar 27, 2015

This looks wrong to me:
Rowmap=DBUtils.Executemap(SQL1,"Select Timestamp,Car from DBTableName1 WHERE ID= row",Null)
I suppose that DBTableName1 and row are variables.
Try tis code:
Rowmap=DBUtils.Executemap(SQL1,"Select Timestamp,Car from " & DBTableName1 & "WHERE ID= " & row,Null)

Tom1s · Apr 1, 2015

Thanks.

Now I have an another problem. Table1 contains: ID INTEGER AUTO_INCREMENT PRIMARY KEY , PID bigint, Timeloaded DATETIME, Name TEXT)
TAble2 contains ID INTEGER AUTO_INCREMENT PRIMARY KEY , PID bigint, Timeloaded DATETIME, Name TEXT, col1, col2)

I try to read the table1 and then put extra info(fill col1 and col2) and then save it to table2. I know how to make it in SQL but now with dbutils I don't know.
Should the map be the same order than columns or is it enough if there is a col1,value ?
I got an error row77 all the time.

Is there a simple way of doing this or should I make a new map case by case with right order? What should i put in that ID because it is auto increment.

Thanks for your help.

B4X:

Sub Table1_celllongClick (Col As Int, Row As Int)
    Activity.Title = Table1.GetValue(Col, Row)
    SelectedRow = Row
    Dim row2 As String = Row+1
    Dim unloadresult As String
    unloadresult = Msgbox2("Unload Row"& row2,"Unloading..","UNLOAD","CANCEL","",Null)
    If unloadresult = DialogResponse.POSITIVE Then
    Dim Rowmap As Map
    Rowmap.Initialize
    Log(Row)
'    Rowmap=DBUtils.Executemap(SQL1,"Select PID,Timeloaded,Name FROM Loaded WHERE ID = "&row2, Null)
    Rowmap=DBUtils.Executemap(SQL1,"Select * FROM table1 WHERE ID = "&row2, Null)
    Log(Rowmap)
    If Rowmap = Null Then
       ToastMessageShow("Error Copying the Row" ,True)
    Else

    Rowmap.Put("col1","32255")
    Rowmap.Put("col2","545")
  '  Rowmap.put("col3","5454")

    Log(Rowmap)
    DBUtils.InsertMaps(SQL1,"table2",Rowmap) 'and put in new data

klaus · Apr 1, 2015

DBUtils.InsertMaps(SQL1,"table2",Rowmap)
Expects a List of Maps !
So you need to define a List and add the Map to it and then call DBUtils.InsertMaps with the List as the second parameter.

I know how to make it in SQL but now with dbutils I don't know.

You could also do it with SQL even though you use DBUtils.

Tom1s · Apr 1, 2015

Ok....i ment sql like mssql and tried sql commands but dbutils are much simpler even if it takes me some time to learn it.

Erel · Sep 16, 2015

Please start a new thread and post the code you use to insert the data.

anaylor01 · Sep 17, 2015

If I uncomment the delete statement it works. But if I leave it commented then I get this error.
An error has occurred in sub:
main_activity_create(B4A line: 76)
Sql1.initialize(dbfiledir,dbfilename,true)
android.database.sqlite.
sqliteCantOpenDatabaseException: unable to open database file

Line 76 is this:
SQL1.Initialize(dbfiledir, dbfilename, True)

Here is all the code:
Sub Activity_Create(FirstTime As Boolean)
'Do not forget to load the layout file created with the visual designer. For example:
Dim dbfilename As String
Dim dbfiledir As String
dbfilename = "ThinkFast.db"
If FirstTime Then
' File.Delete(File.DirDefaultExternal, dbfilename) ' for testing
If File.Exists(File.DirDefaultExternal, dbfilename) = False Then
dbfiledir = DBUtils.CopyDBFromAssets(dbfilename)
End If

SQL1.Initialize(dbfiledir, dbfilename, True)
End If

Any help would be greatly appreciated. Thanks.

LucaMs · Sep 17, 2015

If your db exists, dbfiledir will be a null string, then you try to initialize SQL1 passing "" and "ThinkFast.db".
Put Log("dbfiledir = " & dbfiledir) before SQL1.Initialize(dbfiledir, dbfilename, True)

anaylor01 · Sep 17, 2015

Yes it is null. How do I fix it?

anaylor01 · Sep 17, 2015

This might be the wrong way of doing it but I am thinking the first time I store the value in a database and then if it exists then pull that value out of the database. What do you think?

anaylor01 · Sep 17, 2015

Ok. That won't work. So how would I do it?

Computersmith64 · Sep 17, 2015

anaylor01 said:
Ok. That won't work. So how would I do it?

In your File.Exists statement you are assuming that if the DB is there is will be in File.DirDefaultExternal, so just initialize your dbfiledir with File.DirDefaultExternal:

B4X:

Private dbfiledir as String = File.DirDefaultExternal

That way, if the File.Exists is true you won't have a Null string in your dbfiledir variable & if it doesn't exist, dbfiledir will be changed by DBUtils.CopyDBFromAssets().

- Colin.

anaylor01 · Sep 17, 2015

Thank you.

Android Tutorial DBUtils - Android databases are now simple!

Attachments

Active Member

Expert

Expert

Active Member

Expert

Active Member

Expert

Member

Expert

Member

Expert

Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency