B4J Library [B4X] DBUtils 2

Bob Spielen · Jul 3, 2017

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,

Erel · Jul 4, 2017

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.

Bob Spielen · Jul 4, 2017

Thank you really very much for the, as allways, fast reply!

Bob Spielen · Jul 4, 2017

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?

DonManfred · Jul 4, 2017

Bob Spielen said:
Pls, Where do I get the V2.01 with the fix?

Post #1! https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/

Erel · Jul 4, 2017

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.

Bob Spielen · Jul 4, 2017

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

Erel · Jul 5, 2017

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

Bob Spielen · Jul 5, 2017

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

Erel · Jul 5, 2017

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?

Bob Spielen · Jul 5, 2017

Phone: ASUS_X008D
Android: 6.0
B4A: 6.50

klaus · Jul 5, 2017

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

Bob Spielen · Jul 5, 2017

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

B4X:

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

klaus · Jul 5, 2017

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.

Erel · Jul 6, 2017

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.

Toky Olivier · Jul 29, 2018

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.

B4X:

'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

Toky Olivier · Jul 29, 2018

Sorry, I forget to add this:

B4X:

listOfValues.Add(value_exists.Get(Key))

The complete code is:

B4X:

'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

Bob Spielen · Jul 29, 2018

Toky, thank you very much for sharing your work!!

Jack Cole · Jan 21, 2019

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.

B4X:

'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

B4J Library [B4X] DBUtils 2

Attachments

Active Member

B4X founder

Active Member

Active Member

Expert

B4X founder

Attachments

Active Member

B4X founder

Active Member

B4X founder

Active Member

Expert

Active Member

Expert

B4X founder

Active Member

Active Member

Attachments

Active Member

Well-Known Member

Similar Threads