B4J Library [B4X] DBUtils 2

Erel · Jul 3, 2017

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

Erel · Feb 9, 2020

Which PRAGMA statement is used by DBUtils?

LucaMs · Feb 9, 2020

Erel said:
Which PRAGMA statement is used by DBUtils?

My GetFieldsInfo, not already added to DBUtils, of course.

Erel · Feb 9, 2020

table_info is NOT deprecated:

temp_store_directory for example is deprecated.

LucaMs · Feb 9, 2020

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.

LucaMs · Feb 9, 2020

Unofficial

B4X DBUtils v. 2.07

(Erel, please, check it and then use it or not and remove this post )

OliverA · Feb 13, 2020

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.

Erel · Feb 14, 2020

V2.08 released as a b4xlib with the fix and the two methods Luca added in v2.07.

Alexander Stolte · Feb 14, 2020

Erel said:
V2.08 released as a b4xlib

Version in the manifest is still 2.07.

DonManfred · Feb 14, 2020

Alexander Stolte said:
Version in the manifest is still 2.07.

the bas file is v2.08.
I suggest to edit the manifest.txt inside the b4xlib to match 2.08

Alexander Stolte · Feb 14, 2020

DonManfred said:
manifest.txt inside the b4xli

This is exactly that what I meant ?

Erel · Feb 16, 2020

Alexander Stolte said:
Version in the manifest is still 2.07.

Fixed.

Erel · Feb 26, 2020

V2.09 - HtmlCSS is now public.

Xfood · May 26, 2020

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

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 · May 26, 2020

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

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.

Xfood · May 26, 2020

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.

you have an example of upsert b4j/b4a?

Toky Olivier · May 28, 2020

Xfood said:
you have an example of upsert b4j/b4a?

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.

Xfood · May 28, 2020

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.

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 · May 28, 2020

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 · May 28, 2020

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?

I haven't tried it yet, I wanted to know if it was correct to do so.

Toky Olivier · May 28, 2020

Xfood said:
I haven't tried it yet, I wanted to know if it was correct to do so.

Normally, it should work. Anyway, need to try.

B4J Library [B4X] DBUtils 2

Attachments

B4X founder

Expert

B4X founder

Expert

Expert

Attachments

Expert

B4X founder

Expert

Expert

Expert

B4X founder

B4X founder

Expert

Active Member

Expert

Active Member

Attachments

Expert

Active Member

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency