B4J Tutorial DBUtils Example

Latest version available here: https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/

This example demonstrates how DBUtils module can be used in B4J.

DBUtils was modified to match the slightly different API of B4J jSQL library.

Other changes:
- ExecuteSpinner and ExecuteListView were removed. Instead there is an ExecuteList methods that can be used with ComboBox and ListView.
- ExecuteHtml was removed. Currently there isn't a node similar to WebView in B4J (it will be added in the near future).
- ExecuteTable was added. It fills the result set in a TableView.

 

[TomDuncan]

Is it possible to have an ExecuteJson that does not require the field types to be entered.
i.e. get the field types from the database and not have to enter them.

Tom

 

[Erel]

You can treat all fields as strings. Use GetString2 to get the values of all fields.

 

[TomDuncan]

Oh thanks I will.

 

[Mashiane]

You can use this snippet to list a list of map records from a table. Works likes ExecuteMap but returns a list which you can loop through each record to read the map.

Public Sub ExecuteMaps(SQL As SQL, Query As String, StringArgs() As String) As List
    Dim lst As List
    Dim res As Map
    Dim cur As ResultSet
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteMaps: " & Query)
    lst.initialize
    Do While cur.NextRow
        res.Initialize
        For i = 0 To cur.ColumnCount - 1
            res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
        Next
        lst.Add(res)
    Loop
    cur.Close
    Return lst
End Sub

 

[Mashiane]

You can use this snippet to just add 1 record map to the table, works like InsertMaps but instead you pass it the map to add. Works with only 1 record at a time.

public Sub InsertRecord(SQL As SQL, TableName As String, nRecord As Map)
    Dim nList As List
    nList.Initialize
    nList.Add(nRecord)
    InsertMaps(SQL, TableName, nList)
End Sub

 

[Mashiane]

You can use this snippet to update all records in a table at once. This is the same as UpdateRecord2 but without the where clause

Public Sub UpdateRecord3(SQL As SQL, TableName As String, Fields As Map)
    If Fields.Size = 0 Then
        Log("Fields empty")
        Return
    End If
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("UPDATE ").Append(EscapeField(TableName)).Append(" SET ")
    Dim args As List
    args.Initialize
    For i=0 To Fields.Size-1
        If i<>Fields.Size-1 Then
            sb.Append(EscapeField(Fields.GetKeyAt(i))).Append("=?,")
        Else
            sb.Append(EscapeField(Fields.GetKeyAt(i))).Append("=?")
        End If
        args.Add(Fields.GetValueAt(i))
    Next
    Log("UpdateRecord: " & sb.ToString)
    SQL.ExecNonQuery2(sb.ToString, args)
End Sub

 

[Philip Prins]

Can i use this with mySQL?

 

[Erel]

You will need to modify it a bit as it uses SQLite syntax.

 

[IslandMedic]

Has anyone modified this class to work with mysql? I am getting an error but I didn't realize that the class was just for sqlite and not mysql.

thanks
Brad

 

[AzureCrystal]

This example demonstrates how DBUtils module can be used in B4J.

Great code example, thank you Erel Since you mentioned Scene Builder is being more or less deprecated in B4x, was there ever a port of Layout1.fxml to a B4J form? Also; These DBUtils B4x modules; Are they fully generic or tailored to the specific database/table for a given project? At 1st glance they seem generic, just saving some Grokking time so I don't have to mod them if I don't have to.

Thanks in advance.

 

[Erel]

There was never a port of the layout. Maybe in the future.

DBUtils is tailored for SQLite databases. You will need to make some modifications if you use a different database.

 

[AzureCrystal]

There was never a port of the layout. Maybe in the future.

DBUtils is tailored for SQLite databases. You will need to make some modifications if you use a different database.

Yes, I saw that, probably best to use SQLite for mobile apps anyway, thank you for the prompt reply.

 

[narek adonts]

DBUtils gives a lot of error with MySQL (CreateTable,...)
SQLite is ok.

 

[Erel]

DBUtils will only work properly with SQLite databases. It requires some changes to work with other databases.

 

[ThRuST]

Have DBUtils been updated to support MySQL and not only SQLite? for example can ExecuteHtml read directly from a server? that would be useful.
I have posted a request in the wish forum, thanks.

 

[OliverA]

@ThRuST

Just for fun (it's Friday) I modified DBUtils and the example that @Erel provides here -> (https://www.b4x.com/android/forum/threads/dbutils-example.34611/)

In both the demo and DBUtils.bas, the modifications were minor in order to accommodate MySQL.

Summary of DBUtils changes:
1) Added the public variables DB_VARCHAR and DB_BIGINT. MySQL cannot index TEXT fields, thus I had change the Student's Id to DB_VARCHAR. Also, MySQL's INTEGER field could not hold DateTime's long values and thus I added DB_BIGINT.

2) Added a couple of private variables and a new public function (SetEscapeChars) that lets you set the DB's escape character. I also modified EscapeField to use these new variables and made the method Public (since it seemed useful for modifying some code in the demo).

3) InsertMaps had an SQL statement that hard coded the escape characters and I changed it to use the EscapeField method

That was it for DBUtils! Please note that you would need to add more DB_* variables if you need support for the plethora of column types that MySQL has (compared to sqlite).

Summary of the demo changes:
(Before Process_Globals)
1) Add #AdditionalJar: mysql-connector-java-5.1.40-bin. Please note, this can change (with never version of the jar).
(In AppStart)
2) Replace the sqlite specific SQL1.InitializeSQLite method with the SQL1.Initialize2 method
3) Used the new DBUtils.SetEscapeChars to set MySQL's escape characters
4) Changed the Student's database to use the new DB_* variables
5) Did the same for Grades
6) Change SQL statement to use public version of EscapeField - this is a generic change and not MySQL specific
(In cmbStudentID_ValueChanged)
7) Change SQL statement to use public version of EscapeField - this is a generic change and not MySQL specific
(In FindFailedTests)
8) Changed SQL statement to use MySQL concatenation operator - THIS CAN BE VERY DB SPECIFIC!!!!!

As to having ExecuteHTML call the server - Why? Do it like the demo and create you SQL connection, create your query string and you string list of arguments and then call ExecuteHTML. From looking at it's code, it is very DB agnostic. Adding code to it to connect to a SQL server would make it far less generic.

I'm attaching the modified DBUtils and demo. Before running against MySQL server, change the SQL.Initialize2 to your setup

Note: Search the forum and there are lots of examples of additional functions that could be put into the DBUtils.bas module.

 

[Erel]

It is better to start a new thread for the modified DBUtils in the libraries forum.

 

[ThRuST]

@OliverA Thank you very much for this contribution. I will take a closer look at your modifications. I am currently using DBUtils v1.01 as a module. Dunno if this is the latest or not but it works well with SQLite v3. will do tests to implement it into my project and release a beta version later on with or without multiuser mySQL support.
So far single user functinality works well as it relys on SQLite. However thanks for improving DBUtils it will be of great usage for many users including myself.

 

[ThRuST]

Btw I think Erel got a point by saying Modified DBUtils. Perhaps it should be called modDBUtils to not confuse it with the official DBUtils maintained by Erel.
Or Perhaps DBUtils_mySQL to clear doubts out. What do you others suggest? Anyway, thanks.