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

 

[Xfood]

gives me this mistake

InsertReplaceMaps (first query out of 4): INSERT OR REPLACE [TbArticoli] ([CodArt], [CodEan], [DesArt], [Rep], [Posizione], [Iva], [uCosto], [uPrezzo], [Esistenza]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near "[TbArticoli]": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT OR REPLACE [TbArticoli] ([CodArt], [CodEan], [DesArt], [Rep], [Posizione], [Iva], [uCosto], [uPrezzo], [Esistenza]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

'SQLDataDB.ExecNonQuery("CREATE TABLE TbArticoli (CodArt CHAR(20) NOT NULL, CodEan CHAR(20) PRIMARY KEY, DesArt VARCHAR(40) NOT NULL, Rep INTEGER NOT NULL, Posizione CHAR(10) NOT NULL, Iva INTEGER NOT NULL, uCosto REAL NOT NULL, uPrezzo REAL NOT NULL, Esistenza REAL NOT NULL, Data CHAR(15) NOT NULL, FlModif CHAR(1) NOT NULL)")
If File.Exists(File.DirRootExternal, "download/ean.csv")= True Then
        'LblNote.Text="Inizio acquisizione anagrafica da file Ean.csv"
        'TotaleRighe = File.ReadList(File.DirRootExternal, "download/ean.csv" )
        
        Main.SQLDataDB.Initialize(File.DirDefaultExternal, "Db_Cs.db", True)
        
        ReaderFileTxt.Initialize(File.OpenInput(File.DirRootExternal, "download/ean.csv"))
        
        Line=ReaderFileTxt.ReadLine
        LineCount=0
        DateTime.DateFormat = "yyyyMMddHHmmss"
        cData=DateTime.Date(DateTime.Now)
        arc.Initialize
        Do While Line <> Null
            LineValues=Regex.Split(";", Line)
            
            'Prepara una Map con i valori dei campi           '''''e la memorizza nella lista 'arc'
            '========================================================================================
            cCodArt=LineValues(0)
            If cCodArt = Null Then cCodArt=""
            cCodArt=cCodArt.Trim
            cCodArt=cCodArt & "                    "
            cCodArt=cCodArt.SubString2(0,20)
            
            cCodEan=LineValues(1)
            If cCodEan = Null Then cCodEan=""
            cCodEan=cCodEan.Trim
            cCodEan=cCodEan & "                    "
            cCodEan=cCodEan.SubString2(0,20)
            
            cDesArt=LineValues(2)
            If cDesArt = Null Then cDesArt=""
            cDesArt=cDesArt.Trim
            
            cReparto=LineValues(5)
            If cReparto = Null Then cReparto=""
            cReparto=cReparto.Trim
            
            cIva=LineValues(7)
            If cIva = Null Then cIva=""
            cIva=cIva.Trim
            
            cPosizione=LineValues(8)
            If cPosizione = Null Then cPosizione=""
            cPosizione=cPosizione.Trim
            
            cCosto=LineValues(4)
            If cCosto = Null Then cCosto=""
            nCosto=cCosto
            nCosto=nCosto/100
            cCosto=NumberFormat2(nCosto,0,2,2,False)
            
            cPrezzo=LineValues(3)
            If cPrezzo = Null Then cPrezzo=""
            nPrezzo=cPrezzo
            nPrezzo=nPrezzo/100
            cPrezzo=NumberFormat2(nPrezzo,0,2,2,False)
            
            cEsistenza=LineValues(6)
            If cEsistenza = Null Then cEsistenza=""
            nEsistenza=cEsistenza
            nEsistenza=nEsistenza/1000
            cEsistenza=NumberFormat2(nEsistenza,0,3,3,False)
            
            
            
            '=========================================================================================
            Dim m As Map
            m.Initialize
            m.Put("CodArt", cCodArt)
            m.Put("CodEan", cCodEan)
            m.Put("DesArt", cDesArt)
            m.Put("Rep", cReparto)
            m.Put("Posizione", cPosizione)
            m.Put("Iva", cIva)
            m.Put("uCosto", cCosto)
            m.Put("uPrezzo", cPrezzo)
            m.Put("Esistenza", cEsistenza)
            
            arc.Add(m)
            LblNote2.Text="Rec. Nr.: " & LineCount
            LblView.Text=m.Get("CodArt") & CRLF & m.Get("CodEan") & CRLF & m.Get("DesArt") & CRLF & m.Get("uPrezzo") & CRLF & m.Get("uCosto") & CRLF & m.Get("Rep") & CRLF & m.Get("Esistenza") & CRLF & m.Get("Iva") & CRLF & m.Get("Posizione")
            Line=ReaderFileTxt.ReadLine        'legge prossima riga dal file di testo
            LineCount=LineCount+1            'aggiorna conteggio articoli in archivio
        Loop
            ReaderFileTxt.close
        Log("Numero di linee lette in archivio: "&LineCount)
        '==========================================================================================================
        ' Inserisco nel db
        '==================
        DBUtils.InsertReplaceMaps(Main.SQLDataDB,"TbArticoli",arc)
        LblNote2.Text="Fine importazione, articoli inseriti: " & LineCount
        Main.SQLDataDB.Close
        'File.Copy(File.DirRootExternal, "download/ean.csv",File.DirRootExternal,"download/ean.csv.old")
        'File.Delete(File.DirRootExternal, "download/ean.csv")
    Else
        MsgboxAsync("File Ean.csv non presente.","")
        'Return True
    End If
        
End Sub

'===================================================================================================================
Public Sub InsertReplaceMaps(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 [" & 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
'======================================================================

 

[Toky Olivier]

Have you tried the sample project I sent? It worked?
Have you also an unique or a primary key in the TbArticoli table?

 

[Xfood]

Have you tried the sample project I sent? It worked?
Have you also an unique or a primary key in the TbArticoli table?

'SQLDataDB.ExecNonQuery("CREATE TABLE TbArticoli (CodArt CHAR(20) NOT NULL, CodEan CHAR(20) PRIMARY KEY, DesArt VARCHAR(40) NOT NULL, Rep INTEGER NOT NULL, Posizione CHAR(10) NOT NULL, Iva INTEGER NOT NULL, uCosto REAL NOT NULL, uPrezzo REAL NOT NULL, Esistenza REAL NOT NULL, Data CHAR(15) NOT NULL, FlModif CHAR(1) NOT NULL)")
sorry if I take advantage of your kindness,
I put the string with which I create the tbarticoli table, if you see you find:
CodEan CHAR (20) PRIMARY KEY

Where am I wrong?
I would like to populate everything through a Map, and maybe have as total return records inserted, and total records updated, is there a solution?
Thanks a lot again

 

[Toky Olivier]

INSERT OR REPLACE INTO

The code you sent in pm was not like you post above. Right syntax is INSERT OR REPLACE INTO [tablename] (...) not INSERT OR REPLACE [tablename]

 

[Toky Olivier]

I edited the post #18 (https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/post-603733) to have the InsertOrReplaceMaps sub to use the INSERT OR REPLACE syntax of Sqlite.
Please read in footnote the difference with the InsertOrUpdateMaps function.

 

[BarryW]

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.

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

Error on DBUtils 2.09

Dim FieldsList As List = ExecuteMemoryTable(DB, \
javac 1.8.0_211
shell\src\com\app\title\dbutils_subs_0.java:966: error: incompatible types: db cannot be converted to RemoteObject
_fieldslist = _executememorytable(_ba,dbutils.mostCurrent._db,RemoteObject.concat(RemoteObject.createImmutable("PRAGMA table_info ('"),_tablename,RemoteObject.createImmutable("')")),(dbutils.mostCurrent.__c.getField(false,"Null")),BA.numberCast(int.class, 0));Debug.locals.put("FieldsList", _fieldslist);Debug.locals.put("FieldsList", _fieldslist);

 

[Erel]

Do you have a module named DB in your project?

 

[Erel]

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

 

[nedium]

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

Hello

I add (DBUTILS_NOLOGS), but the logs keep appearing when using a map.

example:
UpdateRecord: UPDATE [User] SET [Name] = ?, [Password] = ?, [Type] =? WHERE [ID] =?

can be hidden or should be

 

[Erel]

V2.11 - Fixed issue with logs output from UpdateRecord even when DBUTILS_NOLOGS is set.

 

[nedium]

V2.11 - Fixed issue with logs output from UpdateRecord even when DBUTILS_NOLOGS is set.

Thanks @Erel

always with the prompt response and solution

regards

 

[Fizzy]

Hello, I'm a complete beginner in B4X and I'm following this tutorial (). In the tutorial, they add DBUtils as a model, but it seems that it's no longer available or is currently provided as a library, if I understand correctly. As a complete beginner, this greatly affects my understanding of the tutorial. I'm wondering if there is a way for me to find the mentioned version in a video?

 

[Erel]

The library is attached to the first post. Configure your additional libraries folder (Tools - Paths Configuration) and put it there. You should then mark it in the libraries tab.

 

[Fizzy]

The library is attached to the first post. Configure your additional libraries folder (Tools - Paths Configuration) and put it there. You should then mark it in the libraries tab.

Thank you for such a quick response.

I have done this, but I still don't know how to open this library now to get the DButils.bas file out and have it appear in the tab above? Is this not necessary?

I have been using your language for just a few hours. I have created a calculator and first application following your tutorials. Now I am trying to create a table that will have an input window for, let's say, 2 data points (name, surname), and it should currently work offline. Later on, I will try to connect it thru web services to a server.

 

[Erel]

You don't need the bas file. Mark the library and the module will be included in your project.

 

[DonManfred]

I have done this,

You should NOT use the program files folder for Additional Libraries. It is a restricted path.
Choose a writeable path.

 

[Fizzy]

You should NOT use the program files folder for Additional Libraries. It is a restricted path.
Choose a writeable path.

Thank you!