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:

    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:

    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:

    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:

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

 

[Erel]

You should modify ExecuteHtml and add <col> nodes to the output: HTML col align Attribute

See the generated string.

With ListView it is simple. You just need to add:

ListView1.SingleLineLayout.Label.Gravity = Gravity.RIGHT

 

[vecino]

Hi, thanks for the reply, although I understand correctly, what I need is that some columns be aligned to the left, others focused and others shall be aligned to the right.
In addition, some columns, like those of type 'float' wear a mask of type '###,###.##'
In other words, something similar to a component 'dbgrid' in other environments / languages​​.
Is it possible to do that?
Thanks and regards.

 

[vecino]

Hello, I've found several threads on the topic, thanks.

 

[SeaCay]

DBUtils has proven to be of benfit to me and so I would like to submit a routine to the code. My function determines whether a table exists or not.

Please fee free to add it to the next update (or whenever) for DBUtils.Zip

The Code

' Determine if a table exists
' Written by C.Kemp 27th Feb 2013, Kemputer Pty Limited
' Input Params
'          SQL ~ the established connection to an existing SQLite DB
'            tagetTbl ~ the name, as a string, of the table to be verified
' Output Params
'            ans ~ the return value of the call to this routine
'
' Calling this routine
'      if (DBUtils.TableExists(SQL, "MyDBTable") = false) then
'         <Do Something>
'      End If
Sub TableExists(SQL As SQL, targetTbl As String) As Boolean
   Dim resCurs As Cursor
   Dim ans As Boolean
   Dim ExStmnt As String
   
      ExStmnt = "SELECT name FROM sqlite_master WHERE type='table' AND name='" & targetTbl & "'"
      resCurs = SQL.ExecQuery(ExStmnt)
      ans = (resCurs.RowCount = 1)
      resCurs.close
      
      Return ans
            
End Sub

I hope that this is useful to others

regards

SeaCay

 

[Caravelle]

RETRIEVING DATA FROM WEBVIEW POPULATED BY DBUtils.ExecuteHtml

In post # 20 of this thread, this code is given for the task of retrieving the value contained in a clicked cell of an html table:

Sub WebView2_OverrideUrl (Url As String) As Boolean
Dim stringa_SQL As String
Dim SQL As SQL


'parse the row and column numbers from the URL
Dim values() As String
values = Regex.Split("[.]", URL.SubString(7))

Dim col, row As Int
col = values(0) ' this is the column selected
row = values(1) ' this is the row

stringa_SQL="SELECT id_cliente, id_giro, " _
& "seq, nome_cliente as cliente, " _
& "indirizzo, note " _
& "FROM ListaGiornata " _
& "ORDER BY seq DESC"

Dim riga() As String ' this is for storing the row selected
Lista = DBUtils.ExecuteMemoryTable(SQL, "" & stringa_SQL, Null, 0) 
' lista now have the table value with in the first column the index
riga=Lista.Get(row)
main.IDRowSelected=riga(0) ' this is the index of the row selected in db

I have an issue with the last line, which doesn't seem to belong (what is "main.IDRowSelected" ?, and there doesn't seem to be any way indicated of getting the data in the specific column clicked, just the whole row. I attempted to recast it, but was having trouble. Here is my version of the sub:

Sub WebView1_OverrideUrl (Url As String) As Boolean
   'parse the row and column numbers from the URL
    Dim Query As String
   
   Dim URLvalues() As String
   URLvalues = Regex.Split("[.]", Url.SubString(7))
   Dim col, row As Int
   col = URLvalues(0)
   row = URLvalues(1)
   
   Dim DBList As List
        Dim RowData() As String
   Query = "SELECT * FROM Flown"
   DBList = DBUtils.ExecuteMemoryTable(SQLDB, Query , Null, 0)
   RowData = DBList.Get(row)
    ToastMessageShow("User pressed on column: " & col & " and row: " & row & ", value: " & RowData(col), False)
   Return True
   
End Sub

In my version, I have DIM-ed my DBList, unlike the "Lista" in the earlier version, I don't have any extra double quotes in my call to DBUtils.ExecuteMemoryTable and I have removed "Dim SQL As SQL" - until I did that my program stopped at line 187 in DBUtils.bas (v1.09) -

cur = SQL.ExecQuery(Query)

with an exception - "cur (Cursor) Not initialized".

When about to send this message I finally discovered the problem, and while editing out the bits asking for a solution the message somehow got sent prematurely. Oh well. It's taken me two days to work out how to get the value from the webview, it would have been so much easier if it had just been included in the DBUtils test program to start with. Hopefully this will help someone else find the answer rather more quickly.

 

[pereskjo]

Adding Row Number to SQL SELECT result

Hello,
I'm trying to add Row number when displaying a high score.
I've found information from Microsoft that should work.
Function is ROW_NUMBER() or RANK().

this code do not work

WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL, "Select ROW_NUMBER() OVER (ORDER BY score) As Row, name As Name, score As Score FROM main ORDER BY score DESC" , Null, 0, True))

this code works

WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL, "SELECT name As Name, score As Score FROM main ORDER BY score DESC" , Null, 0, True))

Have I coded this wrong, or is the function not accepted in B4A?

Per Emil

 

[Erel]

The local database engine in Android is SQLite. You can see the supported keywords and commands here: SQLite Query Language: SQLite Keywords

 

[tznikos]

dataTYPES

Why this library dose not support data types like datetime double etc ?

 

[Erel]

See the data types supported by SQLite: Datatypes In SQLite Version 3

 

[aklisiewicz]

DBUtils looks great but certain pieces are not fully documented...

Here is an example line from demo App:

"SELECT Id, [First Name] || ' ' || [Last Name] As Name, date(birthday / 1000, 'unixepoch', 'localtime') As Birthday FROM Students" _
, Null, 0, True))


I don't understand the last fragment of the line. Where can I find details for those parameters ?

_ , Null, 0, True))

what is Null
what is 0
what is True


ExecuteMemoryTable

Table = DBUtils.ExecuteMemoryTable(SQL, "SELECT Id FROM Students", Null, 0)

same problem, not infor for last 2 parameters

Arthur

 

[Erel]

The documentation is in the module itself:

'Executes the query and returns the result as a list of arrays.
'Each item in the list is a strings array.
'StringArgs - Values to replace question marks in the query. Pass Null if not needed.
'Limit - Limits the results. Pass 0 for all results.

 

[Mashiane]

Here are some additional methods for the DBUtils... library

' update an existing table and add columns
Sub Table_AddColumns(TableName As String, FieldsAndTypes As Map)
For i = 0 To FieldsAndTypes.Size - 1
Dim sb, field, ftype As String
field = FieldsAndTypes.GetKeyAt(i)
ftype = FieldsAndTypes.GetValueAt(i)
sb = "ALTER TABLE [" & TableName & "] ADD COLUMN [" & field & "] " & ftype
Dim fExist As Boolean = Table_FieldExists(TableName, field)
If fExist = False Then SQLite.ExecNonQuery(sb)
Next
End Sub

' return the number of rows in a database table
Sub Table_CountRows(TableName As String) As Int
Dim rTot As Int
rTot = SQLite.ExecQuerySingleResult("SELECT count(*) FROM [" & TableName & "]")
Return rTot
End Sub

' return a csv string of query results with a header if chosen
Sub Table_ExecuteCSV(Query As String, bIncludeHeader As Boolean, StringArgs() As String) As List
Dim cur As Cursor
Dim colName As String
Dim colValue As String
If StringArgs <> Null Then
cur = SQLite.ExecQuery2(Query, StringArgs)
Else
cur = SQLite.ExecQuery(Query)
End If
Log("ExecuteCSV: " & Query)
Dim nL As List
nL.Initialize
Dim sb As StringBuilder
If bIncludeHeader = True Then
sb.Initialize
For i = 0 To cur.ColumnCount - 1
colName = cur.GetColumnName(i)
sb.Append(colName).Append(",")
Next
colName = sb.ToString
If colName.EndsWith(",") Then colName = colName.SubString2(0, colName.Length-1)
nL.Add(sb)
End If

For row = 0 To cur.RowCount - 1
sb.Initialize
cur.Position = row
For i = 0 To cur.ColumnCount - 1
colValue = cur.GetString2(i)
If colValue = Null Then colValue = ""
sb.Append(colValue).Append(",")
Next
colName = sb.ToString
If colName.EndsWith(",") Then colName = colName.SubString2(0, colName.Length-1)
nL.Add(sb)
Next
cur.Close
Return nL
End Sub

' return all records of the query as a list of maps
' all column names are in lowercase
Sub Table_ExecuteMaps(Query As String, StringArgs() As String) As List
Dim cur As Cursor
Dim colValue As String
Dim colName As String
Dim Limit As Int
If StringArgs <> Null Then
cur = SQLite.ExecQuery2(Query, StringArgs)
Else
cur = SQLite.ExecQuery(Query)
End If
Log("ExecuteMaps: " & Query)
Dim xTable As List
xTable.Initialize
Limit = cur.RowCount
For row = 0 To Limit - 1
cur.Position = row
Dim m As Map
m.Initialize
For col = 0 To cur.ColumnCount - 1
colName = cur.GetColumnName(col).tolowercase
colValue = cur.GetString2(col)
If colValue = Null Then colValue = ""
m.Put(colName, colValue)
Next
xTable.Add(m)
Next
cur.Close
Return xTable
End Sub

' return a list of the table column names as a list
Sub Table_FieldNames(TableName As String) As List
Dim res1 As List
res1.Initialize
Dim cur As Cursor
cur = SQLite.ExecQuery("PRAGMA table_info ([" & TableName & "])")
If cur.RowCount = 0 Then
Log("No records found.")
Return Null
End If
For i = 0 To cur.RowCount - 1
cur.Position = i
res1.Add(cur.GetString("name").ToLowerCase)
Next
cur.close
Return res1
End Sub

' return the table information as a map
Sub Table_Information(TableName As String) As Map
Dim res1 As Map
Dim res As Map
res1.Initialize
Dim cur As Cursor
cur = SQLite.ExecQuery("PRAGMA table_info ([" & TableName & "])")
If cur.RowCount = 0 Then
Log("No records found.")
Return Null
End If
For i = 0 To cur.RowCount - 1
cur.Position = i
res.Initialize
res.put("ID", cur.GetString("cid"))
res.put("Name", cur.GetString("name"))
res.Put("Type", cur.GetString("type"))
res.Put("Default value", cur.GetString("dflt_value"))
res.Put("Not null", cur.GetString("notnull"))
res.Put("Primary key", cur.GetString("pk"))
res1.Put(i, res)
Next
cur.close
Return res1
End Sub

' inset a map into the database table
Sub Table_InsertMap(TableName As String, m As Map)
Dim sb, columns, values As StringBuilder
SQLite.BeginTransaction
Try
sb.Initialize
columns.Initialize
values.Initialize
Dim listOfValues As List
listOfValues.Initialize
sb.Append("INSERT INTO [" & TableName & "] (")
For i2 = 0 To m.Size - 1
Dim col As String
Dim value As Object
col = m.GetKeyAt(i2)
value = m.GetValueAt(i2)
If i2 > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append("[").Append(col).Append("]")
values.Append("?")
listOfValues.Add(value)
Next
sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
Log("InsertMap : " & sb.ToString)
SQLite.ExecNonQuery2(sb.ToString, listOfValues)
SQLite.TransactionSuccessful
Catch
ToastMessageShow(LastException.Message, True)
Log(LastException)
End Try
SQLite.EndTransaction
End Sub

' return the list of values from a table
Public Sub Table_ListOfField(TableName As String, KeyField As String) As List
Dim c As Cursor
TableName = TableName.ToLowerCase
If TableName.StartsWith("select") = True Then
c = SQLite.ExecQuery(TableName)
Else
c = SQLite.ExecQuery("SELECT [" & KeyField & "] FROM [" & TableName & "]")
End If
Dim res As List
Dim strV As String
res.Initialize
For row = 0 To c.RowCount - 1
c.Position = row
strV = c.GetString2(0).Trim
If strV.Length > 0 Then res.Add(strV)
Next
Return res
End Sub

Public Sub Table_RecordExists(TableName As String, FldName As String, FldValue As String) As Boolean
Return SQLite.ExecQuerySingleResult2("SELECT count(" & FldName & ") FROM [" & TableName & "] WHERE [" & FldName & "] = ?", _
Array As String(FldValue)) > 0
End Sub

' rename an existing table
Sub Table_Rename(OldName As String, NewName As String)
SQLite.ExecNonQuery("ALTER TABLE [" & OldName & "] RENAME TO [" & NewName & "]")
End Sub

 

[Mashiane]

Handling of Null values.. (Null Exception Pointer Errors During Run)

Some of my database fields contain Null values as the database created does not yet have functionality to specify Default values on creation via DbUtils. after struggling a lot will Null Exception Pointers on my App, I added this line on ExecuteMap and the rest of the other code sections that read values from the database on the DbUtils module.

If colValue = Null Then colValue = ""

This off course assumes that your Table Columns Data Types are all Text/Strings, and my code works perfectly and no more Null Exception Pointers.

 

[Buks]

If I read a record that contains multiline text with DBUtils.ExecuteHtml, the data does not display in separate lines as in the database.
The data is displayed as a single line, or multiple lines if the length of the line exceeds the width of the webview.

Any suggestions how to force a new line in the html whenever a CRLF character is contained in the data record?

 

[Erel]

Try to replace CRLF with <br/>.

 

[GiulioVale]

How to populate StringArgs for clause 'IN' inside a sql query?
Thanks

 

[Erel]

The same way you do with any other query. You just need to set an item for each question mark: Array As String("a", "b", "c")

 

[GiulioVale]

The same way you do with any other query. You just need to set an item for each question mark: Array As String("a", "b", "c")

Thanks for reply. My problem is that the number of string to search are dinamic so I think have to build a dinamically a number of question mark.

 

[Erel]

You can build the array (and the string) dynamically.
For example:

Dim InString As StringBuilder
InString.Initialize
For i = 0 to n-1
 InString.Append("?")
 If i < n-1 then InString.Append(", ")
Next

Dim args(n) As String
args(0) = ...
args(1) = ...

 

[microbox]

Hello everyone, I'm still learning and trying to understand the following code in order to modify it to fit my need.

Sub spnrTests_ItemClick (Position As Int, Value As Object)
    'Show the grade of this test
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT Grade FROM Grades WHERE id = ? AND test = ?", _
        Array As String(spnrStudentId.SelectedItem, Value))
    If m = Null Then
        txtGrade.Text = "N/A"
    Else
        txtGrade.Text = m.Get("grade")
    End If
End Sub

But I'm not getting closer...I'm simply trying to query of a particular value in a table and if found display it(first data match) with a message box.
Ex. "Select * from datalogs where temp > 35"


Thanks in advance,
microbox