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

peacemaker

Expert
Licensed User
Longtime User
Where can I find latest version. In list of libraries link for DBUtils is leading to this post. In attached zip there is no xml or jar files. How to install this library?
It is not alibrary, it is the class module, to be added via "Project" menu. And used via the name "DButils" after activate SQL and JSON libs.
 

Dario126

Member
Licensed User
Longtime User
I was mislead because I had "DBUtils v1.0" installed in Libraries from before (not sure how), and expected same for new version. Thank You ..
 

Sathish VM

Member
Licensed User
Longtime User
Hi,

I'm using
B4X:
sqlLocalFile.BeginTransaction
strQuery =    "update stockreturns set threeyly = ( Select 100* (price/last - 1) from stockprice join " & str3YrTableName& " on symbol = sc_code where stockreturns.[Symbol] = stockprice.[Symbol] )"
DBUtils.ExecuteMemoryTable(sqlLocalFile,strQuery,Null,0)
sqlLocalFile.EndTransaction

I'm quite sure this command updates the Data.db file that sqlLocalfile refers to, since I'm able to read this data and show it in Android screen. However when I try to open the Data.db file after closing the application, SQ Lite Expert Personal tells me the below.

wGktLrT.png


I'm able to open the file in Sqlite3 command line, dump the file as sql statements and then recover the Data.db file, but then I see that stockreturns table, that the DBUtils command updates, is empty.

Could this be because I never close the sqlLocalFile anytime? If I have to close it at the time of application close, in which activity/event should I put the code in?

I see Activity_Create but I don't see any Activity_Close in B4A? Do I have to code it myself?
 

Philip Prins

Active Member
Licensed User
Longtime User
I do a HTTP request and write it to DB , it works but stops everytime after 5000 updates?
I get the following error:



dbutils_updaterecord (java line: 709)
android.database.sqlite.SQLiteCantOpenDatabaseException: error code 14: unable to open database file
at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:95)
at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:73)
at anywheresoftware.b4a.sql.SQL.ExecNonQuery2(SQL.java:84)
at com.sample.SigFoxTD1205v2.dbutils._updaterecord(dbutils.java:709)
at com.sample.SigFoxTD1205v2.sigfox._devicesbytype(sigfox.java:388)
at com.sample.SigFoxTD1205v2.sigfox._jobdone(sigfox.java:669)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at anywheresoftware.b4a.keywords.Common$5.run(Common.java:958)
at android.os.Handler.handleCallback(Handler.java:605)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:4624)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:809)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:576)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: java.lang.RuntimeException: android.database.sqlite.SQLiteCantOpenDatabaseException: error code 14: unable to open database file
at anywheresoftware.b4a.keywords.Common$5.run(Common.java:961)
at android.os.Handler.handleCallback(Handler.java:605)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:4624)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:809)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:576)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.RuntimeException: android.database.sqlite.SQLiteCantOpenDatabaseException: error code 14: unable to open database file
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:196)
at anywheresoftware.b4a.keywords.Common$5.run(Common.java:958)
... 9 more
Caused by: android.database.sqlite.SQLiteCantOpenDatabaseException: error code 14: unable to open database file

Or i get error
java.io.FileNotFoundException: /data/data/com.sample.SigFoxTD1205v2/cache/506: open failed: EMFILE (Too many open files)


I cannot find the reason,
 
Last edited:

Reviewnow

Active Member
Licensed User
Longtime User
New DbUtils Feature
Export SQLite tables to xml
Required xmlBuilder Library http://www.b4x.com/android/forum/attachments/xmlbuilder-zip.11015/

ExportTableToXml - Export the contents of a table to a single xml file
ExportTableToXmlString - Usefull for posting the entire contents of a table to a remote server for parsing / web services
ExportDbToXml - Exports every table in the database to a single xml file usefull for backing up your database

More instructions and examples are provided within the attachment look at the procedure exporttoxml in the main activity

These functions work as intended but if anyone has enhancements or suggested changes please feel free to let me know.
 

Attachments

  • DBUtils.zip
    19.6 KB · Views: 626

pantaraf

Member
Licensed User
Longtime User
Hi everyone, if I'm not completely wrong :D I think I have found a bug or, at least, an annoying limitation in DBUtils.
In the case of a query where there are two (or more I guess) columns with similar name, the map object will be loaded only with the first one. Let me explain with a pratical case:
B4X:
Dim m as Map
m.Initialize

m=DBUTILS.ExecuteMap(SQL,"SELECT A.ID, B.ID FROM TABLE1 A, TABLE2 B",Null)
In this case inspecting m will show that only an "ID" field has been loaded and not "A.ID" and "B.ID" as the SQL statement actually returns. Accessing m.GetValueAt(1) will, of course, throw an exception.

The simpler way to work around this limitation is to rename similiar columns using SQL

B4X:
Dim m as Map
m.Initialize

m=DBUTILS.ExecuteMap(SQL,"SELECT A.ID AS JIM, B.ID AS JOE FROM TABLE1 A, TABLE2 B",Null)
In this case the map object will be loaded correctly using "JIM" and "JOE"

Ciao.
Raffaele
 

Myr0n

Active Member
Licensed User
Longtime User
Thank you so much for this wonderful module
 

IslamQabel

Active Member
Licensed User
Longtime User
what is that tool? In GPS tutorial , you advised me to record my data on txt file or use database which is better...now i want to show the contents of database file which is stored my mobile or how to open it in the app?
 

Myr0n

Active Member
Licensed User
Longtime User

walterf25

Expert
Licensed User
Longtime User
Hi Erel and everyone else who may be able to answer this question, i'm working on an app which requires a listview, i purchased the Ultimate ListView and although i love it i'm having a performance issue with this listview, i have over 10000 items that need to go on that list, unfortunately the list takes around 15 seconds to load all the items. I just tried loading those items using the webview and the DBUtilities class and it works way better, the 10000 items load in under a second.
Now my questions are the following
#1 how can i adjust the width of the columns?
#2 is there a way to insert other views in the webview, ie checkbox
#3 How can i retrieve when pressed on a cell the information contained within.

If anyone has dealt with this before, i'd like to hear about it and thanks so much in advance for your help.

Thanks,
Walter
 
Top