Thursday

Accessing Remote Databases in android

Accessing remote databases with Android is a little more complicated. There is no easy way to do this without a Web service to access the database through; while Java provides direct database access libraries, these are not ideal for mobile internet connections which can be interrupted at any time. Provided is a simple example of remote database access using a PHP webpage as a "middleman," adapted from Hello Android:
Assume we have a SQL database defined with this statement:

CREATE TABLE `people` ( `id` int not null auto_increment primary key, `name` varchar( 100 ) not null, `sex` bool not null default '1', `birthyear` int not null )

And a PHP service that takes a POST request and returns JSON code (or XML, but we'll use JSON for this example):

<?php mysql_connect("host","username","password"); mysql_select_db("PeopleData"); $q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'"); while($e=mysql_fetch_assoc($q)) $output[]=$e; print(json_encode($output)); mysql_close(); ?>

Now all we need to do is pull the data form this webpage. Accessing this page, however, is more complicated than using the simple HTTP download code that we used before; we also need to send an HTTP POST request and parse the JSON response:

String result = "";
ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(); nameValuePairs.add(new BasicNameValuePair("year","1980")); // Send the HTTP POST request.
try {
HttpClient httpclient = new DefaultHttpClient();
HttpPost httppost = new HttpPost("http://example.com/getAllPeopleBornAfter.php"); httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
HttpResponse response = httpclient.execute(httppost);
HttpEntity entity = response.getEntity();
InputStream is = entity.getContent();
}

catch(Exception ex) {
toast("Error in HTTP connection: " + ex.toString());
}

// Read the data into a string.

try {
BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8); StringBuilder sb = new StringBuilder();
String line = null;
while ((line = reader.readLine()) != null) sb.append(line + "\n");
is.close();
result=sb.toString();
}

catch(Exception ex) {
toast("Error reading server response: " + ex.toString());
}
// Parse the JSON data.

try {
JSONArray jArray = new JSONArray(result);
for(int i=0;i<jArray.length();i++) {
JSONObject json_data = jArray.getJSONObject(i);
toast("id: " + json_data.getInt("id") + ", name: " + json_data.getString("name") + ", sex: " + json_data.getInt("sex") + ", birthyear: "+ json_data.getInt("birthyear"));
}
}
catch(JSONException ex) {
toast("Error parsing JSON data: " + ex.toString());
}