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

 

[Mashiane]

Sorry, I forget to add this:

listOfValues.Add(value_exists.Get(Key))

The complete code is:

'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

For your select statements, why not use ? and then put the m.get(key) as array of string()?

 

[IslandMedic]

I added AUTOINCREMENT to the create table function. Sharing in case someone could use this.

'Creates a new table with the given name.
'FieldsAndTypes - A map with the fields names as keys and the types as values.
'You can use the DB_... constants for the types.
'PrimaryKey - The column that will be the primary key. Pass empty string if not needed.
'AutoInc - The column that will need to be auto incrementing. Pass empty string if not needed.
Public Sub CreateTable(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String, AutoInc As String)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("(")
    For i = 0 To FieldsAndTypes.Size - 1
        Dim field, ftype As String
        field = FieldsAndTypes.GetKeyAt(i)
        ftype = FieldsAndTypes.GetValueAt(i)
        If i > 0 Then sb.Append(", ")
        sb.Append(EscapeField(field)).Append(" ").Append(ftype)
        If field = PrimaryKey Then sb.Append(" PRIMARY KEY")
        If field = AutoInc Then sb.Append(" AUTOINCREMENT")
    Next
    sb.Append(")")
    Dim query As String
    query = "CREATE TABLE IF NOT EXISTS " & EscapeField(TableName) & " " & sb.ToString
    'Log("CreateTable: " & query)
    SQL.ExecNonQuery(query)
End Sub

 

[LucaMs]

Since it (perhaps ? ) served me, I "created" it as b4xlib (great effort, only inserted in a zip file with the addition of a simple manifest file ?).

If you agree, Erel, you could attach it to the first post and I will remove this post (indeed, you could remove it)

 

[klaus]

Hi Luca, unfortunately your manifest.txt file is not complete!
You have not added the RuntimePermissions library dependence for B4A.
You might also add the author:
Version=2.06
Author=Erel
B4J.DependsOn=jSQL
B4A.DependsOn=SQL,RuntimePermissions
B4i.DependsOn=iSQL

 

[LucaMs]

Hi Luca, unfortunately your manifest.txt file is not complete!
You have not added the RuntimePermissions library dependence for B4A.
You might also add the author:
Version=2.06
Author=Erel
B4J.DependsOn=jSQL
B4A.DependsOn=SQL,RuntimePermissions
B4i.DependsOn=iSQL

Uhm... and what is needed for B4J-B4I, about permissions (mainly B4i)? I don't know.

The author, in this case, is not so important ?

Thanks, Klaus.

 

[LucaMs]

However...

Since it (perhaps ? ) served me

I decided to copy & past all Erel's code in my new and personal DBUtils code module. Adding "his library" to my code module would have implied that I should have exposed all the methods of the library ?

 

[LucaMs]

Probably the best thing would be that I suggest Erel the methods I want to add and that he decides whether to add them or not.
Methods I wrote 6 years ago , so I have to check them first too.

What do you think about it?

 

[LucaMs]

Probably the best thing would be that I suggest Erel the methods I want to add and that he decides whether to add them or not.
Methods I wrote 6 years ago , so I have to check them first too.

What do you think about it?

They are only these ( + a Type):

 

[klaus]

Uhm... and what is needed for B4J-B4I, about permissions (mainly B4i)? I don't know.

In post #1, I saw this:
It depends on: SQL or jSQL or iSQL
B4A also depends on RuntimePermissions.
And I checked it.

The author, in this case, is not so important

For me it is!

It's up to Erel to decide.

 

[LucaMs]

You should report the full post!

 

[klaus]

Sorry, but I am more concentratet on texts than on images.

 

[LucaMs]

Sorry, but I am more concentratet on texts than on images.

Without that icon, the whole sense of the phrase changes!

 

[klaus]

Yes, I understood it after your post #30 before posting post #31 .

 

[LucaMs]

Yes, I understood it after your post #30 before posting post #31 .

 

[Erel]

If you agree, Erel, you could attach it to the first post and I will remove this post (indeed, you could remove it)

Where is the updated b4xlib?

 

[LucaMs]

Where is the updated b4xlib?

I really don't know ?.

I was waiting for your decision also about those 3 methods I would like to add.

 

[LucaMs]

    Type tFieldInfo(FieldName As String, DataType As String, _
                            CanBeNull As Boolean, DefValue As Object)

' ----------------------------------------------------------------------
'
' Get all tables names as list
Public Sub GetTables(DB As SQL) As List
    Return ExecuteList(DB, "SELECT name FROM sqlite_master WHERE type = 'table'", Null, 0)
End Sub

' Gets informations about each fields in a table.
' Returns a list of tFieldInfo
Public Sub GetFieldsInfo(DB As SQL, TableName As String) As List

    Dim lstFieldsInfo As List
    lstFieldsInfo.Initialize
   
    Dim FieldsList As List
    FieldsList = ExecuteMemoryTable(DB, "PRAGMA table_info ('" & TableName & "')", Null, 0)
    Dim values() As String
    For I = 0 To FieldsList.Size - 1
        values = FieldsList.Get(I) 'FieldsList is a list of arrays

        Dim FieldInfo As tFieldInfo
        FieldInfo.Initialize
        FieldInfo.FieldName = values(1)
        FieldInfo.DataType = values(2)
        FieldInfo.CanBeNull = (values(3) = 1)
        FieldInfo.DefValue = values(4)

        lstFieldsInfo.Add(FieldInfo)
    Next

    Return lstFieldsInfo
End Sub

(This is my veeery old code; it should work, not tested now)

 

[LucaMs]

(This is my veeery old code; it should work, not tested now)

("old code": about 6 years ago!)

Now it seems that PRAGMA...

(from: https://www.sqlite.org/pragma.html)
Notes:

1. Pragmas whose names are struck through are deprecated. Do not use them. They exist for historical compatibility.
2. These pragmas are only available in builds using non-standard compile-time options.
3. These pragmas are used for testing SQLite and are not recommended for use in application programs.

Uhm...?

 

[Erel]

Not sure what you are saying about PRAGMA.

You should change the type name to DBFieldInfo.

 

[LucaMs]

Not sure what you are saying about PRAGMA.

It seems to be deprecated.