[Erel]

This tutorial explains the basic concepts required for creating a connection between Android device and a remote server. In this case it is a MySQL database.
A tutorial for connecting to SQL Server is available here.

Android cannot connect directly to the database server. Therefore we need to create a simple web service that will pass the requests to the database and will return the response.

For this example I've created a new database that lists the countries population. The data was derived from a UN database.

The database includes a single table named "countries" with three columns:


PHP Script

The web service is made of a single PHP script running on the same server.
You have several options for the web service implementation. You can create several prepared statements which will be filled with parameters passed in the request.
Or you can take the complete query from the request and pass it to the database server.
There are high security risks with issuing queries received from an unknown user. If your service is public then you will probably want to choose the first option.
You should also make sure to correctly escape the parameters. See this php method: PHP: mysql_real_escape_string - Manual

In our example I've chosen the second option which takes the query and passes it to the database directly.
I've restricted the database user to SELECT queries (in MySQL configuration).

The PHP code:

PHP Code:

<?

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = file_get_contents("php://input"); 
$sth = mysql_query($query);

if (mysql_errno()) { 
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error(); 
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

The script takes the query from the POST data and submits it to the database.
The result is then written in JSON format.

Basic4android code

Our application displays a list of countries. When the user presses on a country, its population is retrieved from the database and displayed.



This is the code (project is attached):

Sub Process_Globals
    Dim hc As HttpClient
    Dim COUNTRIES_LIST, COUNTRY_POPULATION As Int
    COUNTRIES_LIST = 1
    COUNTRY_POPULATION = 2
End Sub

Sub Globals
    Type TwoLines (First As String, Second As String)
    Dim lblPopulation As Label
    Dim ListView1 As ListView
    Dim lblCountry As Label
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        hc.Initialize("hc")
    End If
    Activity.LoadLayout("1")
    FetchCountriesList
End Sub
Sub FetchCountriesList
    ProgressDialogShow("Fetching list of countries")
    'Gets all the available countries
    ExecuteRemoteQuery("SELECT name, id FROM countries ORDER BY id", COUNTRIES_LIST)
End Sub
Sub ListView1_ItemClick (Position As Int, Value As Object)
    If IsBackgroundTaskRunning(hc, COUNTRY_POPULATION) Then
        ToastMessageShow("Wait for previous call to finish.", False)
        Return
    End If
    Dim tl As TwoLines
    tl = Value
    lblCountry.Text = tl.Second
    lblPopulation.Text = "Calling server..."
    ExecuteRemoteQuery("SELECT population FROM countries WHERE id='" & tl.First & "'", COUNTRY_POPULATION)
End Sub
Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
    Dim req As HttpRequest
    req.InitializePost2("http://www.basic4ppc.com/android/countries.php", query.GetBytes("UTF8"))
    hc.Execute(req, TaskId)
End Sub
Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Log("Error: " & Reason & ", StatusCode: " & StatusCode)
    If Response <> Null Then
        Log(Response.GetString("UTF8"))
        Response.Release
    End If
    ProgressDialogHide
End Sub
Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    Dim res As String
    res = Response.GetString("UTF8")
    Log("Response from server: " & res)
    Dim parser As JSONParser
    parser.Initialize(res)
    Select TaskId
        Case COUNTRIES_LIST
            'add the countries to the ListView
            Dim countries As List
            countries = parser.NextArray 'returns a list with maps
            For i = 0 To countries.Size - 1
                Dim m As Map
                m = countries.Get(i)
                'We are using a custom type named TwoLines (declared in Sub Globals).
                'It allows us to later get the two values when the user presses on an item.
                Dim tl As TwoLines
                tl.First = m.Get("id")
                tl.Second = m.Get("name")
                ListView1.AddTwoLines2(tl.First, tl.Second, tl)
            Next
            ProgressDialogHide
        Case COUNTRY_POPULATION
            Dim l As List
            l = parser.NextArray
            If l.Size = 0 Then
                lblPopulation.Text = "N/A"
            Else
                Dim m As Map
                m = l.Get(0)
                lblPopulation.Text = NumberFormat2(m.Get("population"),0, 0, 0, True) & " (K)"
            End If
    End Select
    response.Release
End Sub

The code sends the query and then when the result arrives the JSON is parsed and displayed.
Note that in this case the JSON object is made of an array of one or more maps.