B4J Library [B4X] DBUtils 2

Discussion in 'B4J Libraries & Classes' started by Erel, Jul 3, 2017.

  1. Erel

    Erel Administrator Staff Member Licensed User

    DBUtils is a code module 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.

    The module is attached. You can add it with Project - Add Existing Modules or by copying it to the project folder if it already exists.

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

    Updates:

    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).
     

    Attached Files:

    Last edited: Apr 20, 2018
  2. Bob Spielen

    Bob Spielen Member Licensed User

    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,
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    V2.01 is released with a fix related to the clickable html table.

    @Bob Spielen please update to v2.01. Make sure to return True from WebView1_OverrideUrl.
     
  4. Bob Spielen

    Bob Spielen Member Licensed User

    Thank you really very much for the, as allways, fast reply!
     
  5. Bob Spielen

    Bob Spielen Member Licensed User

    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: Jul 4, 2017
  6. DonManfred

    DonManfred Expert Licensed User

  7. Erel

    Erel Administrator Staff Member Licensed User

    V2.02 is released. It is available in the first post.

    DBUtils.UpdateRecord is now implemented with a call to UpdateRecord2.

    ExecuteHtml works properly here.
    An example is attached.
     

    Attached Files:

    koaunglay and ellpopeb4a like this.
  8. Bob Spielen

    Bob Spielen Member Licensed User

    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: Jul 4, 2017
  9. Erel

    Erel Administrator Staff Member Licensed User

    Lets start with the click issue. Have you tried the example from post #7? Is the click event working properly?
     
  10. Bob Spielen

    Bob Spielen Member Licensed User

    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
     
  11. Erel

    Erel Administrator Staff Member Licensed User

    Please use [code]code here...[/code] tags when posting code.

    The click event doesn't work with the example I posted? Which phone and OS version are you using?
     
  12. Bob Spielen

    Bob Spielen Member Licensed User

    Phone: ASUS_X008D
    Android: 6.0
    B4A: 6.50
     
  13. klaus

    klaus Expert Licensed User

    Why don't you answer Erels question?
    Does the example in post#7 work?
    It works for me!
    Samsung S6
    Android 7.0
    B4A 7.01
     
  14. Bob Spielen

    Bob Spielen Member Licensed User

    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 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
        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, Null0'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(0False
        
    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) , 10)  '
            '=====================================================================================
    '        '    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(
    1False)
      
        
    '
        '
        '   
        MontaTela

    Return
     
  15. klaus

    klaus Expert Licensed User

    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.
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    As Klaus wrote, it is impossible to help you if we don't understand what you are doing. I asked 3 times whether it worked for you with the example that I posted (specifically for you). You still didn't answer.
    Anyway, for further discussion please start a new thread in the questions forum.
     
  17. Toky Olivier

    Toky Olivier Member Licensed User

    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(1Then
            
    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
     
    Mashiane, Erel and rboeck like this.
  18. Toky Olivier

    Toky Olivier Member Licensed User

    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(1Then
            
    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
     
    asales and Bob Spielen like this.
  19. Bob Spielen

    Bob Spielen Member Licensed User

    Toky, thank you very much for sharing your work!!
     
    Peter Simpson and Toky Olivier like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice