Android Tutorial Access any database or system with B4AServer

Users who are not familiar with B4AServer project should start with this tutorial.

It is sometimes much easier to access data from a desktop than to access the data directly from the Android device.
Using B4AServer, a device can connect to the desktop server, run a program that will dump the required data, download the data and then work with this data.

The program that dumps the data is not part of B4AServer. B4AServer only runs it.

In this specific example we want to access data stored in an Excel document.
Reading an Excel document directly in Android is a very difficult task. Instead we use a VBScript script that converts Excel documents to CSV files.

The script is based on this script.
The following line was added to the server configuration:

Shell.DumpData=cmd.exe /c DumpData.bat

DumpData is a batch file that first deletes the old csv file and then runs the script:

del files\data.csv
cscript XlsToCsv.vbs C:\Users\H\Documents\countries.xls C:\Users\H\Documents\java\B4A_Server\files\data.csv

Working with batch files is convenient as you can edit the batch file without restarting the server.

In this case we are dumping the whole data. In other cases the device can send the last updated time as an argument and then we will only dump the latest data.

The device program will first send a Shell request with DumpData command.
When the task completes it will download the CSV file, parse it and insert the data to a SQL table. Using DBUtils.ExecuteHtmlTable the data is displayed in a WebView.

The main activity code:

'Activity module
Sub Process_Globals
    Dim ServerName As String
    ServerName = "test1" '*********** Change to your server value
    Dim taskRunShell, taskDownloadData As Int
    Dim SQL1 As SQL
End Sub

Sub Globals
    Dim btnDownloadData As Button
    Dim WebView1 As WebView
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        B4AServer.Initialize("http://66.84.14.57/android/b4a_server.php", ServerName, "Main")
        SQL1.Initialize(File.DirRootExternal, "1.db", True)
    End If
    btnDownloadData.Initialize("btnDownloadData")
    btnDownloadData.Text = "Download data"
    WebView1.Initialize("")
    Activity.AddView(WebView1, 5dip, 110dip, 100%x - 5dip, 100%y - 110dip)
    Activity.AddView(btnDownloadData, 10dip, 10dip, 300dip, 100dip)
End Sub
    
Sub Activity_Resume
    'Raise events for tasks that completed while our activity was paused
    For i = 0 To B4AServer.TasksFinishedWhilePaused.Size - 1
        Dim Result As TaskResult
        Result = B4AServer.TasksFinishedWhilePaused.Get(i)
        Log("From resume: " & Result)
        TaskComplete(Result)
    Next
    B4AServer.TasksFinishedWhilePaused.Clear
End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub btnDownloadData_Click
    taskRunShell = B4AServer.Shell("DumpData", Null)
    btnDownloadData.Enabled = False
End Sub

Sub TaskComplete(Result As TaskResult)
    Log("Task=" & Result.Id & " completed. Success=" & Result.Success & ", Message=" & Result.Message)
    If Result.Success Then
        Select Result.Id
            Case taskRunShell
                taskDownloadData = B4AServer.ReceiveFile("data.csv")
            Case taskDownloadData
                btnDownloadData.Enabled = True
                HandleData(Result.Id)
        End Select
    Else
        Msgbox(Result.Message, "Error occurred")
        btnDownloadData.Enabled = True
    End If
End Sub
Sub HandleData(FileName As String)
    DBUtils.DropTable(SQL1, "countries")
    Dim fields As Map
    fields.Initialize
    fields.Put("Name", DBUtils.DB_TEXT)
    fields.Put("Id", DBUtils.DB_TEXT)
    fields.Put("Population", DBUtils.DB_INTEGER)
    DBUtils.CreateTable(SQL1, "countries", fields, "")

    'parse the CSV file and add the fields to the table
    Dim Table As List
    Dim Headers As List
    Dim su As StringUtils
    Table = su.LoadCSV2(B4AServer.DownloadFilesFolder, FileName, ",", Headers)
    Dim maps As List
    maps.Initialize
    For i = 0 To Table.Size - 1
        Dim mapRow As Map
        Dim row() As String
        row = Table.Get(i)
        mapRow.Initialize
        mapRow.Put("Name", row(0)) 
        mapRow.Put("Id", row(1)) 
        mapRow.Put("Population", row(2)) 
        maps.Add(mapRow)
    Next
    DBUtils.InsertMaps(SQL1, "countries", maps)
    
    'Show the data in the WebView
    WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1, "SELECT * FROM countries", Null, 0, False))
End Sub

To run this project you will need to add the following code modules: B4AServer, B4AServerService and DBUtils.
The following libraries are required: Network, Http, RandomAccessFile and SQL.