B4J Library [B4X] DBUtils 2

DBUtils is a b4xlib with useful utilities related to the SQL library. It is designed to work with SQLite databases.
There have been three versions of DBUtils, one for each platform (except of B4R).

DBUtils v2.0 merges the three modules to a single module that is compatible with B4A, B4i and B4J.

The module behavior is mostly identical to previous versions.
Differences are:
- ExecuteMap in B4A version returned Null in some cases. It now returns an uninitialized Map if there are no results.
- B4J ExecuteList, which filled a given list is now named ExecuteList2.
- B4J ExecuteHtml includes the clickable parameter.

Instructions: put the library in the additional libraries folder.

It depends on: SQL or jSQL or iSQL
B4A also depends on RuntimePermissions.

Updates:

V2.11 - Fixed issue with logs output from UpdateRecord even when DBUTILS_NOLOGS is set.
V2.10 - InsertMaps returns True if the maps were inserted successfully.
You can disable logs by adding DBUTILS_NOLOGS as a conditional symbol (Ctrl + B).

V2.09 - HtmlCSS is now public.
V2.08 - Fixes issue with B4J non-ui apps.
V2.07 (by Luca): New GetFieldsInfo and GetTables methods.
V2.06 - Fixes an issue with TableExists in B4A.
V2.05 - New TableExists method to check whether a table exists (the table name is case insensitive).
 

Attachments

Last edited:
Click to expand...
Question.....pls help me...

After I click the first time on a "ShowTableInWebView"-table and then catch the tables position at WebView1_OverrideUrl, perform some DBUtils.UpdateRecord's.
The table becomes NOT clickable any more.
I have to finish the app and restart it to be able to click table again.

In advance thank you,
 
I'm using B4A v6.50,

I installed the V2.00 and getting a problem during UpdateRecord at in DButils :

SQL.ExecNonQuery2(sb.ToString, args), where

sb.ToString is "UPDATE [null] SET [null] = ? WHERE [null] = ?"

could it be that the reason is the "EscapeField()" function at

sb.Append("UPDATE ").Append(EscapeField( TableName)).Append(" SET ").Append(EscapeField(Field)) _
.Append(" = ? WHERE ")

?
With V2.00, still have problems with the "clickability" at Sub ShowTableInWebView and WebView1_OverrideUrl() combination. It works only at the first click.

Pls, Where do I get the V2.01 with the fix?
 
Last edited:
I must be doing something wrong.......
My code:


Dim BuscaMap As Map
BuscaMap.Initialize
BuscaMap = DBUtils.ExecuteMap(SQL1, "SELECT nomecli, codcli, tempodispi, idtt, tici FROM tectryp WHERE aberta=1 AND actcode=2 AND tici < " & DT.TicsHJ000 & " ORDER BY tici DESC",Null)


DButil error at:

Log("UpdateRecord: " & sb.ToString)
SQL.ExecNonQuery2(sb.ToString, args)

with:

sb.tostring: 'UPDATE null SET null=? WHERE null = ?


PS.: Bypassing EscapeField() works fine.

"Clickability" still not working.....
I'm trying to edit a value of the table. When I catch the col, row value and change the SQL1 value after a TimeDialog, the

WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1, SQLStr, Null, 0, True))

is not clickable any more......
 
Last edited:
Good morning,

The click doesn'to work. Only the first click....the 2nd on any more...
I came up with a dirty solution thats for now is working fine.....

At the end of the

WebView1_OverrideUrl (Url As String) As Boolean function .....


A)

Starter.linha=Lista.Get(Starter.row) ' send the row and col vars to Starter.vars[0=3, 1=Trabalho , 2=24/06 16:04, 3=24/06 16:04]

TabStrip1.ScrollTo(0, False) go back

Return True


Going back......

B)

TabStrip1_PageSelected (Position As Int)
Log($"Current page: ${Position}"$)
If Position = 0 Then
If Starter.col>-1 Then
EditaValores().....................This is where I use the TimeDialog
End If



C) At the end of the Editavalores() function , I go back to the WebView1 page with


.
.
.

DBUtils.UpdateRecord(SQL1, "tectryp", "temposisf", RData.Get(1), WhereFields)
DBUtils.UpdateRecord(SQL1, "tectryp", "tempodispf", RData.Get(3), WhereFields)
End If

End If

Starter.col=-1 ..... so by the next time I go over TabStrip1_PageSelected (Position As Int),
it will not execute the TimeDialog function...

TabStrip1.ScrollTo(1, False)

'
Return

End Sub
 
Sorry, but if you see post#10 I wrote:

"Good morning,
The click doesn'to work. Only the first click....the 2nd on any more..."

May be I made myself not clear enough. The click works only for catching row and col number..... but as soon as I put a function with a TimeDialog in between it doesnt work any more for more then 1 click.....

So as I told I came up with a not elegant solution (see attatched code) :

"Bitte seien Sie nicht böse mit mir. Ich bin wirklich ein Anfänger aber tuhe immer mein möglichst um zum lernen. Im voraus bedannke ich mich höflichst für Ihre Bemühungen."

Code: 
Sub TabStrip1_PageSelected (Position As Int)
    Log($"Current page: ${Position}"$)
    If Position = 0 Then
        If Starter.col>-1 Then
            EditaValores
        End If
      
    else If Position=1    Then
        ShowTableInWebView
    End If
End Sub





Sub WebView1_OverrideUrl (Url As String) As 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
    Starter.col = values(0)
    Starter.row = values(1)
    ToastMessageShow("User pressed on column: " & Starter.col & " and row: " & Starter.row, False)

    '==================================================================================
    Dim Lista As List
    Lista.Initialize
    Dim stringa_SQL As String ="SELECT idtt, tici, ticf, tempodispi, tempodispf, actname FROM tectryp  ORDER BY tici ASC"
    '
    'Dim linha() As String ' this is for storing the row selected
    Lista = DBUtils.ExecuteMemoryTable(SQL1, "" & stringa_SQL, Null, 0) 'Lista é o DataTable
    '    ' lista now have the table value with in the first column the index

    Starter.linha=Lista.Get(Starter.row)                       '[0=3, 1=Trabalho , 2=24/06 16:04, 3=24/06 16:04]
    TabStrip1.ScrollTo(0, False) 
    Return True 'Don't try to navigate to this URL
End Sub


Sub EditaValores
    Dim DummyIdtt As Int  

    DummyIdtt = Starter.Linha(0) ' this is the index of the row selected in db,    = idtt
'    '======================================================================================
'  
'    'linha= [0=3, 1=1499115600000, 2=1499126400000, 3=03/07 18:00, 4=03/07 21:00, 5=Viagem R ]
'  
'  
    If Starter.col=0 Then   'Apagar a linha
        Private Answ As Int
        'ask the user for confirmation
        Answ = Msgbox2("Apaga:" & Starter.Linha(5) & " " & Starter.Linha(3) & "... ?", "Apagar registro.", "Sim", "", "Não", Null)
        If Answ = DialogResponse.POSITIVE Then 'if yes, delete the entry
            SQL1.ExecNonQuery("DELETE FROM tectryp WHERE idtt = " & DummyIdtt) 'delete the entry
        End If
    Else 'Editar a hora
        '==========  Definição da Data    ==================================================
        Dim RData As Map
        Dim H As Long
        H=DT.Voltatics2(Starter.DiaHora)   '  DiaHora=Ano&Mes&Dia&Hora&Minuto
        RData=DT.HoraEdit(H, "Editando a hora: " & Starter.linha(Starter.col+2) , 1, 0)  '
        '=====================================================================================
'        '    DummyIdtt=idtt     col=1 datai,     col=2 dataf

        Dim WhereFields As Map
        WhereFields.Initialize
        WhereFields.Put("idtt", DummyIdtt) 'BuscaMap é o map no início
      
      
        If Starter.col=1 Then 'Data INICIO
            DBUtils.UpdateRecord(SQL1, "tectryp", "tici", RData.Get(7) , WhereFields) 'DataHora do Fechamento da viagem
            DBUtils.UpdateRecord(SQL1, "tectryp", "temposisi",  RData.Get(1), WhereFields)
            DBUtils.UpdateRecord(SQL1, "tectryp", "tempodispi",  RData.Get(3), WhereFields)
        else if Starter.col=2 Then 'Data FIM
            DBUtils.UpdateRecord(SQL1, "tectryp", "ticf", RData.Get(7) , WhereFields) 'DataHora do Fechamento da viagem
            DBUtils.UpdateRecord(SQL1, "tectryp", "temposisf",  RData.Get(1), WhereFields)
            DBUtils.UpdateRecord(SQL1, "tectryp", "tempodispf",  RData.Get(3), WhereFields)
        End If
      
    End If
    Starter.col=-1
    TabStrip1.ScrollTo(1, False)
  
    '
    '
    '   
    MontaTela

Return
 
I read your post #10, but you don't answer the question.
Does the example in post#7 work?
Yes or No !?
If you change the code it would be much more efficient if you posted a small project showing the problem.
So we could see what you have done and how and test it in the same conditions as you do.
Just with code snippets it is difficult to help.
 
For my needs, I have added the function bellow if someone need It. It is based on Erel's InsertMaps but It tests before if the value of the column "key" exists or not: if it exists, it updates the value, if not, it inserts the new map.

Code: 
'Inserts or Update the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Public Sub InsertOrUpdateMaps(SQL As SQL, TableName As String, Key As String, ListOfMaps As List)
    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.")
        Return
    End If
    SQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            Dim m As Map = ListOfMaps.Get(i1)
            Dim value_exists As Map = ExecuteMap(SQL, "SELECT " & EscapeField(Key) & " FROM " & EscapeField(TableName) & " WHERE " & EscapeField(Key) & " = '" & m.Get(Key) & "'", Null)
            Log("value_exists: " & value_exists)
            If value_exists.IsInitialized=False Then
                sb.Append("INSERT INTO [" & TableName & "] (")
                For Each col As String In m.Keys
                    Dim value As Object = m.Get(col)
                    If listOfValues.Size > 0 Then
                        columns.Append(", ")
                        values.Append(", ")
                    End If
                    columns.Append(EscapeField(col))
                    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)
            Else
                sb.Append("UPDATE [" & TableName & "] SET ")
                For Each col As String In m.Keys
                    If listOfValues.Size>0 Then
                        sb.Append(",")
                    End If
                    sb.Append(EscapeField(col)).Append("=?")
                    listOfValues.Add(m.Get(col))
                Next
                sb.Append(" WHERE [" & Key & "] = ?")
                If i1 = 0 Then Log("UpdateMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
                SQL.ExecNonQuery2(sb.ToString, listOfValues)
            End If
        Next
        SQL.TransactionSuccessful
    Catch
        Log(LastException)
        #If B4i OR B4J
        SQL.Rollback
        #End If
    End Try
#If B4A
    SQL.EndTransaction
#End If
End Sub
 
Sorry, I forget to add this:
Code: 
listOfValues.Add(value_exists.Get(Key))

The complete code is:
Code: 
'Inserts or Update the data to/in the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Public Sub InsertOrUpdateMaps(SQL As SQL, TableName As String, Key As String, ListOfMaps As List)
    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.")
        Return
    End If
    SQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            Dim m As Map = ListOfMaps.Get(i1)
            Dim value_exists As Map = ExecuteMap(SQL, "SELECT " & EscapeField(Key) & " FROM " & EscapeField(TableName) & " WHERE " & EscapeField(Key) & " = '" & m.Get(Key) & "'", Null)
            Log("value_exists: " & value_exists)
            If value_exists.IsInitialized=False Then
                sb.Append("INSERT INTO [" & TableName & "] (")
                For Each col As String In m.Keys
                    Dim Value As Object = m.Get(col)
                    If listOfValues.Size > 0 Then
                        columns.Append(", ")
                        values.Append(", ")
                    End If
                    columns.Append(EscapeField(col))
                    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)
            Else
                sb.Append("UPDATE [" & TableName & "] SET ")
                For Each col As String In m.Keys
                    If listOfValues.Size>0 Then
                        sb.Append(",")
                    End If
                    sb.Append(EscapeField(col)).Append("=?")
                    listOfValues.Add(m.Get(col))
                Next
                sb.Append(" WHERE [" & Key & "] = ?")
                listOfValues.Add(value_exists.Get(Key))
                If i1 = 0 Then Log("UpdateMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
                SQL.ExecNonQuery2(sb.ToString, listOfValues)
            End If
        Next
        SQL.TransactionSuccessful
    Catch
        Log(LastException)
        #If B4i OR B4J
        SQL.Rollback
        #End If
    End Try
#If B4A
    SQL.EndTransaction
#End If
End Sub

EDIT:
Because this function might impact performance when called with many record to be inserted, this function is an alternative:

B4X: 
'InsertOrReplace version of the already existed Sub: InsertMaps
Public Sub InsertOrReplaceMaps(SQL As SQL, TableName As String, ListOfMaps As List)
    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.")
        Return
    End If
    SQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            sb.Append("INSERT OR REPLACE INTO [" & TableName & "] (")
            Dim m As Map = ListOfMaps.Get(i1)
            For Each col As String In m.Keys
                Dim value As Object = m.Get(col)
                If listOfValues.Size > 0 Then
                    columns.Append(", ")
                    values.Append(", ")
                End If
                columns.Append(EscapeField(col))
                values.Append("?")
                listOfValues.Add(value)
            Next
            sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
            If i1 = 0 Then Log("InsertReplaceMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            SQL.ExecNonQuery2(sb.ToString, listOfValues)
        Next
        SQL.TransactionSuccessful
    Catch
        Log(LastException)
        #If B4i OR B4J
        SQL.Rollback
        #End If
    End Try
#If B4A
    SQL.EndTransaction
#End If
End Sub

Note that "INSERT OR REPLACE" or its alias "REPLACE", as described here https://www.sqlitetutorial.net/sqlite-replace-statement/ works as below:
- It inserts the new row first
- If the Unique key contrainst is violated as the key already exists, the old row will be deleted
So, the Row is not updated but REPLACED (rowid will change).
If you want a INSERT OR UPDATE style, use the previous function: InsertOrUpdateMaps

Another alternative is to use the UPSERT syntax but I think, not yet available to android: https://www.sqlite.org/lang_UPSERT.html
 

Attachments

Last edited:
Here is a suggested addition to this useful class. ExecuteListOfMaps returns a list where each list item is a map. The map keys are the field names. I have tested it with B4A and B4i.

Code: 
'converts a results set to list of maps
Sub ResultSetToListOfMaps(rs As ResultSet) As List
    Dim ReturnList As List
    ReturnList.Initialize
    Do While rs.NextRow
        Dim ThisMap As Map
        ThisMap.Initialize
        For col = 0 To rs.ColumnCount - 1
            ThisMap.Put(rs.GetColumnName(col).ToLowerCase,rs.GetString2(col))
        Next
        ReturnList.Add(ThisMap)
    Loop
    rs.Close
    Return ReturnList
End Sub


'returns a list of maps with the field names in lower case as the map keys
Sub ExecuteListOfMaps(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
    Dim rs As ResultSet
    Dim LimitText=" LIMIT " & Limit As String
    If Limit<1 Then
        LimitText=""
    End If
    rs=SQL.ExecQuery2(Query & LimitText,StringArgs)
    Return ResultSetToListOfMaps(rs)
End Sub
 
You must log in or register to reply here.

Similar Threads

Erel
Games [XUI2D] X2 v2.0 - 2021
Replies
12
Views
23K
Erel
Erel
Erel
  • Locked
  • Article Article
B4A Library [B4X] XUI Views - Cross platform views and dialogs
4 5 6
Replies
102
Views
258K
Erel
Erel
Erel
  • Locked
Games [XUI2D] Example Pack
2
Replies
34
Views
82K
Erel
Erel
Erel
  • Locked
  • Article Article
B4A Class [class][B4X] Google OAuth2
2
Replies
24
Views
48K
Erel
Erel
Erel
  • Locked
  • Article Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
139
Views
206K
Erel
Erel
Back
Top