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

  • DBUtils.b4xlib
    4.9 KB · Views: 3,049
Last edited:
Click to expand...
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
table_info is NOT deprecated:

firefox_9JsHP2bxYa.png


temp_store_directory for example is deprecated.
 
LucaMs

LucaMs

Expert
Licensed User
Longtime User
If I understand correctly, the use of PRAGMA is deprecated and my routine for obtaining the properties of fields uses it.

However, I have now created a new SQLite DB to test with a recent version of a tool and developed a test b4j project, which worked (it lists, for each table, table name and "all" the properties of each field).

I'd say I could add that function anyway.
 
OliverA

OliverA

Expert
Licensed User
Longtime User
In version 2.06 (posted in post#1), there are two methods for B4A (ExecuteSpinner and ExecuteListView) and one for B4J (ExecuteTableView) that are surrounded with #If/#Else If/#End If compiler conditionals. The B4J compiler conditional is
B4X: 
#Else If B4J
This produces an error when used in a NON_UI application (the TableView object is not available in a NON_UI application) and therefore should be changed to
B4X: 
#Else If UI
I've not check out the unofficial version above to see if it has the same issue.
 
Xfood

Xfood

Expert
Licensed User
Toky Olivier said:
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
Click to expand...
I can't make this instruction work, can you have a correct example?
Thanks

B4X: 
 TextReader1.Initialize(File.OpenInput(File.DirApp & "/db/", "ANAG.DAT"))
    line = TextReader1.ReadLine
    nRiga=nRiga +1
    Progress = Progress + 1
    ProgressBar1.Progress = Progress / TotalRec
    Label_Progress.Text = $"${NumberFormat(ProgressBar1.Progress,0,0)}%"$
Do While line <> Null
        Log(nRiga &"|" & line) 'write the line to LogCat
        line=line.Trim
        cEan=line.SubString2(1,13)
        cDescriz=line.SubString2(13,30)
        cUnMis = line.SubString2(43,46)
        cPzxC=line.SubString2(46,51)
        cPrezzo=line.SubString2(13,30)
        cRep=line.SubString2(13,30)
        cCodArt="00001"
        cIva="22"
        cCosto="0002000"
        cEsiste="20"
        
'////////////////////////////////////////////////////////
        Dim m As Map
        m.Initialize
        m.Put("ean", cEan)
        m.Put("codice", cCodArt)
        m.Put("DESCRIZ", cDescriz)
        m.Put("Um", cUnMis)
        m.Put("pzxc", cPzxC)
        m.Put("Prezzo", cPrezzo)
        m.Put("rep", cRep)
        m.Put("Iva", cIva)
        m.Put("Costo", cCosto)
        m.Put("ESISTE",cEsiste)
        m.Put("id",Null)
        arc.Add(m)
        line = TextReader1.ReadLine
        nRiga=nRiga +1
        Loop

    TextReader1.Close
    Log("Inserisco i dati in anagrafica")
    Label1.Text="Inserisco i dati nel db"
    Log("SCRITTURA IN CORSO....")
    DBUtils.InsertOrUpdateMaps(Sql1, "anag", "ean",arc)
    CloseDB
    
    
  '' log 
''    SELECT [ean] FROM [anag] WHERE [ean] = '031195000041'
''    value_exists: (Map) Not initialized
. ExecuteMap: SELECT [ean] FROM [anag] WHERE [ean] = '000000855555'
' No records found.'
 
Toky Olivier

Toky Olivier

Active Member
Licensed User
Longtime User
What exactly is your issue?

If this is the log:
Xfood said:
'' log '' SELECT [ean] FROM [anag] WHERE [ean] = '031195000041' '' value_exists: (Map) Not initialized . ExecuteMap: SELECT [ean] FROM [anag] WHERE [ean] = '000000855555' ' No records found.'
Click to expand...

What I see is that you tried to set [ean] field with the value "031195000041" but you use another value "000000855555" when you check the record in the table? Or I'm wrong? If it's like that, it's normal that you cannot find any record. Try to excecute "SELECT * FROM [anag]" to see all record in the table.

PS: Now, I'm not sure also if the manner I used to implement "Insert or Update" is right. Performance will not be good with many records. May be it's better to use UPSERT syntax: https://www.sqlite.org/lang_UPSERT.html

Edit: UPSERT syntax was only introduced on the version 3.24 issued on 2018-06-04 (https://www.sqlite.org/changes.html). May be finally it's not a good idea to use it now.
 
Last edited:
Xfood

Xfood

Expert
Licensed User
Toky Olivier said:
What exactly is your issue?

If this is the log:


What I see is that you tried to set [ean] field with the value "031195000041" but you use another value "000000855555" when you check the record in the table? Or I'm wrong? If it's like that, it's normal that you cannot find any record. Try to excecute "SELECT * FROM [anag]" to see all record in the table.

PS: Now, I'm not sure also if the manner I used to implement "Insert or Update" is right. Performance will not be good with many records. May be it's better to use UPSERT syntax: https://www.sqlite.org/lang_UPSERT.html

Edit: UPSERT syntax was only introduced on the version 3.24 issued on 2018-06-04 (https://www.sqlite.org/changes.html). May be finally it's not a good idea to use it now.
Click to expand...
you have an example of upsert b4j/b4a?
 
Toky Olivier

Toky Olivier

Active Member
Licensed User
Longtime User
Xfood said:
you have an example of upsert b4j/b4a?
Click to expand...
As I said, it's not a good idea to use Upsert finally, better use "INSERT OR REPLACE".
B4X: 
Sub Button1_Click
    If EditText1.Text.Trim = "" Then
        ToastMessageShow("Key shouldn't be empty", True)
        Return
    End If
    
    sql.ExecNonQuery2("INSERT OR REPLACE INTO mytable (mykey, myvalue) VALUES (?, ?)", _
                      Array As String(EditText1.Text, EditText2.Text))
    
    EditText1.Text = ""
    EditText2.Text = ""
End Sub

Enclosed a sample b4a project.
 

Attachments

  • InsertOrReplace.zip
    14.4 KB · Views: 440
Xfood

Xfood

Expert
Licensed User
Toky Olivier said:
As I said, it's not a good idea to use Upsert finally, better use "INSERT OR REPLACE".
B4X: 
Sub Button1_Click
    If EditText1.Text.Trim = "" Then
        ToastMessageShow("Key shouldn't be empty", True)
        Return
    End If
  
    sql.ExecNonQuery2("INSERT OR REPLACE INTO mytable (mykey, myvalue) VALUES (?, ?)", _
                      Array As String(EditText1.Text, EditText2.Text))
  
    EditText1.Text = ""
    EditText2.Text = ""
End Sub

Enclosed a sample b4a project.
Click to expand...
Thanks for replying, so I don't have to specify the key field for "INSERT OR REPLACE"? if I wanted to use a map with the INSERT OR REPLACE command, I can modify the existing original map like this:
'sb.Append("INSERT INTO [" & TableName & "] (")
sb.Append("INSERT OR REPLACE INTO [" & TableName & "] (") ' <---------- MODIFICA

B4X: 
Public Sub InsertMaps(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 INTO [" & TableName & "] (")
          sb.Append("INSERT OR REPLACE INTO [" & TableName & "] (")     ' <---------- MODIFICA
            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("InsertMaps (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
 
Toky Olivier

Toky Olivier

Active Member
Licensed User
Longtime User
You should have UNIQUE or PRIMARY KEY in your table to be able use INSERT OR REPLACE.
Then you use it only like you use INSERT.

What error you get with this modification?
 
Xfood

Xfood

Expert
Licensed User
Toky Olivier said:
You should have UNIQUE or PRIMARY KEY in your table to be able use INSERT OR REPLACE.
Then you use it only like you use INSERT.

What error you get with this modification?
Click to expand...
I haven't tried it yet, I wanted to know if it was correct to do so.
 
You must log in or register to reply here.

Similar Threads

LucaMs
  • Article
B4A Library [B4X] lmB4XTableExt
Replies
13
Views
5K
LucaMs
LucaMs
aeric
  • Article
B4J Code Snippet [B4X] MiniORMUtils - SQL Query Builder
2 3
Replies
40
Views
8K
aeric
aeric
Star-Dust
  • Article
B4J Library [B4X] [B4XLib] SD_SQL (direct access to MySQL, MariaDB, MS SQL, FireBird)
2 3 4
Replies
64
Views
23K
Star-Dust
Star-Dust
Erel
  • Locked
  • Article
B4A Library [B4X] KVS - KeyValueStore library
2 3
Replies
44
Views
27K
Erel
Erel
Erel
  • Article
B4A Library [B4X] .Net Framework implementation of B4XSerializator
Replies
9
Views
12K
Erel
Erel
Top