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]

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

This is a way to normalize the keys. Otherwise the case depends on the case returned from the database. I think that it is easier when you know for sure that it is always lower-case. You can of course remove the call to ToLowerCase.

About the second question. You should check if the key exists in the Map before fetching it.

 

[Penko]

I will just write a Get2 method doing it. Thanks.

Sent from my HTC Desire using Tapatalk

 

[junaidahmed]

ExecuteMemory Table is fine to get cell value from memory table.if i load sql server data via asp script (Webview.Loadurl("http://202.89.55.78/Myweb/Data.asp") ) then how can I get cell value.pls advise ASAP

 

[Erel]

You should not use WebView if you want to extract the data. You should instead use HttpUtils or HttpClient and parse the results.

 

[peacemaker]

Seems

Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
....
Table1.Add(values)
...

should be edited to:

Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
....
Table1.AddAll(values)
...

 

[Erel]

No. Each item in the Table is an array. If you use AddAll it will add each of the column values as a separate item.

 

[august8music]

Thanks

Thanks this demo really helped me with my simple project.

 

[kaputo]

Hi, I am trying to populate a ListView (DBUtils.ExecuteListView) with two fields from sqllite, of which one of them is a bitmap. Is this possible (when I try there is no error but the bitmap is not displayed).
Is there an alternate way to populate the ListView from the db to include the bitmap (e.g. lv1.add2linesandbitmap)
Thanks

 

[Erel]

DBUtils currently doesn't support it. You will need to read the image as blobs and convert them to images. See the SQL tutorial.

 

[BrutalSnyper]

Encryption

Is it possible to encrypt the database made using DButils?

 

[Erel]

Yes, with SQLCipher.

 

[BrutalSnyper]

Yes, with SQLCipher.

Wonderful, thank you Erel. You have made my day yet again!

 

[Phantomco]

Little Mod to prevent constraint errors on insertmaps

Here's a little mod to DButils that allows insertmaps to update existing records, or add just new ones.

Setting constraint to 1 will replace existing records with the new one, 2 will ignore existing records but not throw an error, and 3 will fail but will keep any records added prior to the error.

These are unique to SQLite working with unique key records.

Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List, Constraint As Int)
   Dim sb, columns, values As StringBuilder
   'Small check for a common error where the same map is used in a loop
   If ListOfMaps.Size > 1 AND ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
      Log("Same Map found twice in list. Each item in the list should include a different map object.")
      ToastMessageShow("Same Map found twice in list. Each item in the list should include a different map object.", True)
      Return
   End If
   Dim Sqlqual As String
   Select Case Constraint
      Case 0
         Sqlqual = "INSERT INTO "
      Case 1
         Sqlqual = "INSERT or REPLACE INTO "
      Case 2
         Sqlqual = "INSERT or IGNORE INTO "
      Case 3
         Sqlqual = "INSERT or FAIL INTO "
   End Select
   SQL.BeginTransaction
   Try
'      Msgbox(ListOfMaps.Size,"Inspections")
      For i1 = 0 To ListOfMaps.Size - 1
         
         sb.Initialize
         columns.Initialize
         values.Initialize
         Dim listOfValues As List
         listOfValues.Initialize
         sb.Append( Sqlqual & "[" & TableName & "] (")
         Dim m As Map
         m = ListOfMaps.Get(i1)
         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(")")
         If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
         SQL.ExecNonQuery2(sb.ToString, listOfValues)
      Next
      SQL.TransactionSuccessful
   Catch
      ToastMessageShow(LastException.Message, True)
      Log(LastException)
   End Try
   SQL.EndTransaction
End Sub

 

[Ennesima77]

I added my personale modify to ExecuteListView

Sub ExecuteListViewWithBlob(SQL As SQL, Query As String, StringArgs() As String, Limit As Int,  _
   ListView1 As ListView, BlobCol As String, ValueCol As String)
   
ListView1.Clear
   Dim Table As Cursor, bmpMom As Bitmap 
   Table = SQL.ExecQuery(Query)
   
   For i = 0 To Table.RowCount - 1
      Table.Position=i
      If Table.GetBlob( BlobCol) = Null Then
         bmpMom.InitializeMutable (100,100)
      Else
          Dim Buffer() As Byte 
          Buffer = Table.GetBlob( BlobCol)
         Dim InputStream1 As InputStream
          InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
          
          bmpMom.Initialize2(InputStream1)
       End If
       
      If ValueCol = Null Then
         ListView1.AddTwoLinesAndBitmap(Table.GetString2( 0), Table.GetString2(1), bmpMom )
      Else
         ListView1.AddTwoLinesAndBitmap2(Table.GetString2( 0), Table.GetString2(1), bmpMom, Table.GetString(ValueCol) )
      End If
      
   Next
   
   
End Sub

It's possible to use it to fill a ListView with an Image, if DB contain a BLOB column.
There's a control when BLOB is null.

It's also possible set what column containe value to return in click event.

Rimember the first 2 declared column must be the First and Second line in ListView like in follow code:

Select FirstName, SecondName, BlobCol, IDCol from persone.........

In ListView first line will be FirstName and the second one will be SecondName.


I hope this can be usefull.

 

[Erel]

You can use Table class to show the data in a table with fixed header.

 

[gapi]

I've three spinner .... three subs for populate it ... first ok ... second spinner is blank !!

' SUB FOR POPULATE THE SPINNER (x3)
Sub popolaSpinnerDestinazioneMerci()
Dim query As String
   query = "SELECT destinazione_merci.col5 " & _
   "FROM destinazione_merci INNER JOIN clienti " & _
   "ON destinazione_merci.col1= clienti.col2 " & _
   "WHERE clienti.col2=" & QUOTE & showOneCliente.cliente_codice & QUOTE
   Log("query_DestM=" & query)
   DBUtils.ExecuteSpinner(Main.SQL1, query, Null, 0, spinDestMerci)
End Sub

... why, If I ask some query with

'
Dim Cursor as Cursor
Cursor1 = Main.SQL1.ExecQuery(query)
Cursor1.position=0
Msgbox(Cursor1.GetString("col25")," query result")

it return me right value ... in spinner not (blank)

thanks :BangHead:

 

[Erel]

Use the debugger to step into the code and see the results.

 

[gapi]

Use the debugger to step into the code and see the results.

the log return me correct value of record, what I must debug ?

 

[AscySoft]

the log return me correct value of record, what I must debug ?

Use a brake point on the first line of the sub that don't work, then, instead of build application with release/obfuscate, use debug from menu,... the code stops at the (red line) brake point, and you could advance line by line and see the variables values with F8

 

[klaus]

You should post your project as a zip file (IDE menu Files / Export As Zip), or at least a smaller one that shows the problem.
Without seeing more code it's impossible to give you a concrete answer.
With the project we could test it to see what happens

Best regards.