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:
- UpdateRecord: Updates an existing record. Accepts the field name with the new value and a map of the 'where' fields.
Example:
- 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:
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:
See RuntimePermissions tutorial for more information: https://www.b4x.com/android/forum/threads/runtime-permissions-android-6-0-permissions.67689/#content
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)Example:
B4X:
Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("id", "1234")
WhereFields.Put("test", "test #5")
DBUtils.UpdateRecord(SQL, "Grades", "Grade", 100, WhereFields)- 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, "")- 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" />
)
Last edited:
I think I have found a bug or, at least, an annoying limitation in DBUtils.
