Android Tutorial DBUtils - Android databases are now simple!

Discussion in 'Tutorials & Examples' started by Erel, Mar 30, 2011.

  1. Erel

    Erel Administrator Staff Member Licensed User

    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:
    Code:
    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:
    Code:
    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:
    Code:
    Dim gen As JSONGenerator 'Requires a reference to the JSON library.
        gen.Initialize(DBUtils.ExecuteJSON(SQL"SELECT Id, [Last Name], Birthday FROM Students"Null, _
            
    0Array 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.

    [​IMG]

    [​IMG]

    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:
    Code:
    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
     

    Attached Files:

    Last edited: Jul 3, 2017
    Matias Ferrer, metrick, fredo and 8 others like this.
  2. stevel05

    stevel05 Expert Licensed User

    This will be extremely useful, Thanks Erel

    Steve
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    V1.01 posted with ExecuteJSON which exports the result as JSON text.
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    V1.05 posted with ExecuteHtml. This one is really useful. It allows you to display tables easily. See the above screenshot.
     
    Devan likes this.
  5. derez

    derez Expert Licensed User

    John smith1 is three years old, no wonder he failed on exam #6.
    John smith8 was born 1916 and is still a student !
     
    ocalle, GMan and ilan like this.
  6. Erel

    Erel Administrator Staff Member Licensed User

    I admire his determination.
     
    ilan likes this.
  7. FrankR

    FrankR Member Licensed User

    An amazing addition to an amazing product.
     
  8. JMB

    JMB Active Member Licensed User

    I've ripped out about 30% of my rather inflexible code and replaced it with calls to DBUtils!

    Absolutely superb. Thanks again. What a superb product this is!
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    Version 1.06 is attached to the first post. It fixes a serious bug discovered by JMB.

    You can apply the fix by changing line 141 to:
    Code:
    Dim values(c.ColumnCount) As String
    Instead of:
    Code:
    Dim values(c.RowsCount) As String
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    Using the new WebView_OverrideUrl event, the html table can now be made of clickable links instead of just text. This allows you to catch a cell selection event with the following code (included in the example):
    Code:
    Sub WebView1_OverrideUrl (Url As StringAs Boolean
        
    '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)
        row = values(
    1)
        
    ToastMessageShow("User pressed on column: " & col & " and row: " & row, False)
        
    Return True 'Don't try to navigate to this URL
    End Sub
    The current CSS style makes the links look like regular text.

    If you need the actual value you can first call ExecuteMemoryTable and then when the user clicks on a cell you can fetch the values from the memory table (list of arrays).
     
  11. skizzo

    skizzo Member Licensed User

    Hi Erel. How do I post the link for any element of html in the function ExecuteHtml?
     
  12. junaidahmed

    junaidahmed Active Member Licensed User

    WebView1_OverrideUrl is fine to get row and col number, but we would like to know how to get selected cell value.pls advise
     
  13. Erel

    Erel Administrator Staff Member Licensed User

  14. skizzo

    skizzo Member Licensed User

    To get the number of row and column is necessary that every element of the WebView has a link. In the function dbutils.ExecuteHtml it'is not expected. How can 'implement?
     
    Straker likes this.
  15. Erel

    Erel Administrator Staff Member Licensed User

    This is already implemented in the updated DBUtils.
     
  16. skizzo

    skizzo Member Licensed User

    Ok, i have upgrade Dbutils. Thanks.
     
  17. skizzo

    skizzo Member Licensed User

    WebView2_OverrideUrl fails

    Erel in the attached example, the event WebView2_OverrideUrl it's not executed. I can not understand what else is wrong.
     

    Attached Files:

  18. skizzo

    skizzo Member Licensed User

    Erel. I solved the problem. Fantastic ..
     
  19. junaidahmed

    junaidahmed Active Member Licensed User

    pls send me sample code to get value from webview cell when click,it's very very urgent for me.
     
  20. skizzo

    skizzo Member Licensed User

    You have to cacth the OverrideUrl for example:

    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

    by
    Steve (skizzo)
     
Loading...