Thursday, September 8, 2011

Android MySQL Connectivity via JSON


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);  
 ?>  

22 comments:

  1. Can Anybody help me i got this type of error again and again

    03-10 11:05:05.868: E/log_tag(539): Error in http connection org.apache.http.conn.HttpHostConnectException: Connection to http://127.0.0.1 refused
    03-10 11:05:05.868: E/log_tag(539): Error converting result java.lang.NullPointerException
    03-10 11:05:05.868: E/log_tag(539): Error parsing data org.json.JSONException: End of input at character 0 of

    ReplyDelete
    Replies
    1. You have to enter a real ip access. 127.0.0.1 is loopback ip address which is the ip address of the phone

      Delete
  2. i have connected android with mysql using php...

    data is moving to database is successfully.........

    i gave the data is "9999999999999"

    nameValuePairs.add(new BasicNameValuePair("f3","9999999999"));

    i was set length is 20 for that column..

    but data is storing always as "2147483647" in my table...

    Give some solutions for that..

    ReplyDelete
    Replies
    1. Check this link for the solution
      http://stackoverflow.com/questions/10718968/confusion-in-int-conversion-of-a-string-in-php

      Delete
  3. Please, can anybody explain me this line:
    $json = file_get_contents('php://input');
    thanks

    ReplyDelete
    Replies
    1. Actually php://input allows you to read raw POST data. It is a less memory intensive alternative to $HTTP_RAW_POST_DATA and does not need any special php.ini directives. php://input is not available with enctype="multipart/form-data.

      Reference: http://php.net/manual/en/wrappers.php.php

      Source http://stackoverflow.com/questions/2731297/file-get-contentsphp-input-or-http-raw-post-data-which-one-is-better-to

      Delete
  4. Can you post a src again ?
    now I study about two way connection btw SQLite and sql.
    Thank

    ReplyDelete
  5. my friend thank you very much for helping me out through this blog. But I do not know php, so kindly send me jsp or servlet code..

    ReplyDelete
  6. hello Praga, first i would like to thank you for the tutorial. it looks straight forward though i cannot get it to work for. it gives me some message in the log cat " Default buffer size used in BufferedReader constructor. It would be better to be explicit if an 8k-char buffer is required." i cannot figure out what could be wrong would be greatful if you helped me out here.

    ReplyDelete
  7. Hello ,
    This tutorial helps me a lot , thanks for the post ..
    CAN YOU PLZ SEND ME THE SOURCE CODE FOR THIS PROJECT ..

    My E-mail : karthi.vels@gmail.com
    thanks in advance ...

    ReplyDelete
  8. Thanks for the tutorial. It Helps me a lot. Bec of i m a starter ,I need source code to understand clear. can u please send it to my mail (prathip.rajan4@gmail.com ). Thanks in advance

    ReplyDelete
  9. Hi thanks for nice tutorial can u send me the code to my mail id : ( shiva101983@gmail.com )

    ReplyDelete
  10. can u pls send me the code to arikapudi.swarna@gmail.com

    ReplyDelete
  11. nice tutorial but can u send me the source code? i can understand clear withot full code. i need this tutor for my app. (sonef24@gmail.com)

    ReplyDelete
  12. can you sent me a source to email? masogiogi@gmail.com. thx

    ReplyDelete
  13. can you sent me a source to email? sad2mar@yahoo.fr thx

    ReplyDelete
  14. I could not understand how to use php for this connection ? It will be very helpful for me, if u send this project pls.. (raminscs@gmail.com)

    ReplyDelete
  15. can you please send me the source code for this project.I'm a student of computer science and need to study for a project. my e-mail: nicolasmelo2@hotmail.com or nicolasmelo3@gmail.com

    thanks

    ReplyDelete
  16. can u send me the source code, it will very useful for me to finish up the project. thanks in advance. my email (tracy5280@hotmail.com).

    ReplyDelete
  17. greetings from me sir, nice tutorial... maybe you can embed link download source code on your post like as your member suggested :)

    ReplyDelete