Android Tutorial Access any database or system with B4AServer

Discussion in 'Tutorials & Examples' started by Erel, Jun 14, 2011.

  1. Erel

    Erel Administrator Staff Member Licensed User

    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:
    Code:
    Shell.DumpData=cmd.exe /c DumpData.bat
    DumpData is a batch file that first deletes the old csv file and then runs the script:
    Code:
    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.

    [​IMG]

    [​IMG]

    The main activity code:
    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, 5dip110dip100%x - 5dip100%y - 110dip)
        
    Activity.AddView(btnDownloadData, 10dip10dip300dip100dip)
    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"Null0False))
    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.
     
    Beja and koaunglay like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice