The most common way to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system.
Earlier in my post I have mentioned a way of connection via mysql connector Link
This example source code provides two way synchronization (Phone to Server and from Server to Phone)
Send Data from SQLite to MySQL
//Retrieve query results in cursor format
startManagingCursor(cs);
if(cs!=null)
{
cs.moveToFirst();
jsonarray = new JSONArray();
while (cs.isAfterLast() == false) {
json = new JSONObject();
try {
json.put("a",cs.getString(cs.getColumnIndex("a")));
json.put("b",cs.getString(cs.getColumnIndex("b")));
json.put("c",cs.getString(cs.getColumnIndex("c")));
jsonarray.put(json);
cs.moveToNext();
}
catch (Exception e) {
Log.d("Android", "JSON Error");
}
}
try {
// Create a new HttpClient and Post Header
HttpClient httpclient = new DefaultHttpClient();
HttpPost httppost = new HttpPost("http://192.168.1.1/set_file.php");
// Post the data:
StringEntity se = new StringEntity(jsonarray.toString());
httppost.setEntity(se);
httppost.setHeader("Accept", "application/json");
httppost.setHeader("Content-type", "application/json");
// Execute HTTP Post Request
System.out.print(json);
HttpResponse response = httpclient.execute(httppost);
// for JSON:
if(response != null)
{
InputStream is = response.getEntity().getContent();
BufferedReader reader = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line = null;
try {
while ((line = reader.readLine()) != null) {
sb.append(line + "\n");
}
} catch (IOException e) {
e.printStackTrace();
}
finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
catch (ClientProtocolException e) {
} catch (IOException e) {
}
}
cs.close();Retreive Data from MySQL to SQLite
String result = "";
//http get
try{
lastsync = URLEncoder.encode(lastsync,"utf-8");
String address = url + "/get.php?variable1="+variable1+"&variable2="+variable2;
Log.v("Sync", address);
HttpClient httpclient = new DefaultHttpClient();
HttpGet httpget = new HttpGet(address);
response = httpclient.execute(httpget);
HttpEntity entity = response.getEntity();
is = entity.getContent();
}catch(Exception e){
Log.e("log_tag", "Error in http connection "+e.toString());
}
//convert response to 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) {
Log.v("Test",line);
sb.append(line + "\n");
}
is.close();
result=sb.toString();
}catch(Exception e){
Log.e("log_tag", "Error converting result "+e.toString());
}
set_tms_logsheet();
if(result.length()==5)
{
}
else {
//parse json data
try{
JSONArray jArray = new JSONArray(result);
for(int i=0;i<jArray.length();i++){
JSONObject json_data = jArray.getJSONObject(i);
String querylog= "insert or replace into table(a,b,c) values('"+json_data.getString("a")+"','"+json_data.getString("b")+"','"+json_data.getString("c")+"')";
dbA.insert(querylog);
}
}
catch(JSONException e){
Log.e("log_tag", "Error parsing data "+e.toString());
}
}
get.php Code (Push Data to Phone from MySQL)
<?php
require_once('Connections/connection.php');
mysql_select_db($database_localhost,$con);
$timestamp = $_GET['timestamp'];
$query_search = "select * from table where a = '".$timestamp."' ";
$query_exec = mysql_query($query_search) or die(mysql_error());
if (mysql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mysql_error();
}
else {
if( $query_exec!=null){
while($row=mysql_fetch_assoc($query_exec))
$output[]=$row;
print(json_encode($output));
}
else {echo "No Data"; }
mysql_close();
}
?>
set.php Code (Push Data to MySQL form SQLite)
<?php
$json = file_get_contents('php://input');
$data = json_decode($json);
require_once('Connections/connection.php');
mysql_select_db($database_localhost,$con);
foreach ($data as $id => $jsons) {
$query= "insert into table(a,b,c) values(".$jsons->a.",".$jsons->b.",".$jsons->c.",'".$jsons->c."')";
//echo $query;
echo "\n";
$query_exec = mysql_query($query) or die(mysql_error());
}
mysql_close();
echo "Success";
?>
Connection.php (Database Connection)
<?php
$hostname_localhost ="localhost:3306";
$database_localhost ="dbname";
$username_localhost ="username";
$password_localhost ="password";
$con = mysql_connect($hostname_localhost,$username_localhost,$password_localhost)
or
trigger_error(mysql_error(),E_USER_ERROR);
?>
