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.

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.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: 450
Last edited:

Xfood

Well-Known Member
Licensed User
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 (?, ?, ?, ?, ?, ?, ?, ?, ?)

B4X:
'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

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

Xfood

Well-Known Member
Licensed User
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
 

BarryW

Active Member
Licensed User
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

B4X:
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);
 

nedium

Member
Licensed User
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
 
Top