Connecting android app with sql database?

Question

I tried this example for connecting with database, i have simple database, and this is the java class:

package com.cvele.android.sqlconn;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.LinearLayout;
import android.widget.TextView;


public class SqlconnActivity extends Activity {
    /** Called when the activity is first created. */

    TextView txt;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        LinearLayout rootLayout = new LinearLayout(getApplicationContext());  
        txt = new TextView(getApplicationContext());  
        rootLayout.addView(txt);  
        setContentView(rootLayout);  
        txt.setText("Connecting..."); 
        txt.setText(getServerData(LINK_PHP)); 
    }
    public static final String LINK_PHP = "http://10.0.2.2/sajt.php"; 

    private String getServerData(String returnString) {

        InputStream is = null;
        String result = "";
        ArrayList<NameValuePair>nameValuePairs = new ArrayList<NameValuePair>();
        nameValuePairs.add(new BasicNameValuePair("year","1970"));

        try{
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(LINK_PHP);
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost);
            HttpEntity entity = response.getEntity();
            is = entity.getContent();
        }catch(Exception e){
            Log.e("log_tag", "Error in http connection "+e.toString());
        }

        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 e){
            Log.e("log_tag", "Error converting result "+e.toString());
        }

        try{
            JSONArray jArray = new JSONArray(result);
            for(int i=0;i<jArray.length();i++){
                JSONObject json_data = jArray.getJSONObject(i);
                Log.i("log_tag","id: "+json_data.getInt("id")+
                    ", name: "+json_data.getString("name")+
                    ", sex: "+json_data.getString("sex")+
                    ", birthyear: "+json_data.getInt("birthyear")
                    );

                returnString += "\n\t" + jArray.getJSONObject(i); 
            }
        }catch(JSONException e){
            Log.e("log_tag", "Error parsing data "+e.toString());
        }
        return returnString; 
    }
}

and this is the php:

<?

$databasehost = "127.0.0.1";
$databasename = "peopledata";
$databaseusername ="nikola";
$databasepassword = "nikola";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = mysql_query("SELECT * FROM people WHERE birthyear >'".$_REQUEST['year']."'");


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($query)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

So when I build this, it always just write something what is between " " in string LINK_PHP ="http://10.0.2.2/sajt.php". I can't get any data from my database. My question is, why it is always written that on my app ..? Thanks in advance. this is what I get: enter image description here

Ok, this is what I get in LogCat:

04-11 16:02:33.064: I/dalvikvm(811): threadid=3: reacting to signal 3
04-11 16:02:33.256: I/dalvikvm(811): Wrote stack traces to '/data/anr/traces.txt'
04-11 16:02:33.544: I/dalvikvm(811): threadid=3: reacting to signal 3
04-11 16:02:33.604: I/dalvikvm(811): Wrote stack traces to '/data/anr/traces.txt'
04-11 16:02:33.724: E/log_tag(811): Error in http connection android.os.NetworkOnMainThreadException
04-11 16:02:33.724: E/log_tag(811): Error converting result java.lang.NullPointerException
04-11 16:02:33.744: E/log_tag(811): Error parsing data org.json.JSONException: End of input at character 0 of 
04-11 16:02:33.944: D/gralloc_goldfish(811): Emulator without GPU emulation detected.
04-11 16:02:34.044: I/dalvikvm(811): threadid=3: reacting to signal 3
04-11 16:02:34.104: I/dalvikvm(811): Wrote stack traces to '/data/anr/traces.txt'

If you work in emulator check with WireShark if you actually receive them from your server. — Artem Oboturov, Apr 11 '12 at 16:15

score 2 · Answer 1 · answered Apr 11 '12 at 17:54

You cannot run your NETWORK activity on the same thread (from the newer versions of Android SDK).
To do so, go ahead by implementing Asynctask. It will handle all your task even considering memory too.
There is Runnable thread too but go through the AsyncTask.
Get the input stream. make a while loop to read stream and append it with String.
Now, if you've XML, then go through parsing the XML by Document object. For this, go through DocumentBuilderFactory. (example for xml).
Now, get your data from Document (instance in object doc) object like something doc.getElementByTagName("yourxmltaghere");
However, if you're with JSON then, hold your JSON. If it has child objects, then make for loop till the mArray.length and then fetch your data one by one.

Above, you said Value <?xml of type java.lang.String cannot be converted to JSONArray, it means you're trying to CAST your XML to JSON. So, I don't have your CASTING code here. However, I did this by the following way.

InputStream is = response.getEntity().getContent();
BufferedReader reader = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line = 
while ((line = reader.readLine()) != null) {
        sb.append(line + "\n");
} 



String xml = sb.toString();                         
JSONObject baseObject = XML.toJSONObject(xml);

JSONObject todo_items = baseObject.getJSONObject("todo-items");
JSONArray todo_item = todo_items.getJSONArray("todo-item");
tempItems = GenerateVO(todo_item);

Here, XML is the class, I loaded. I didn't found any other so used this. I got this class from org.json package provided by JSON. Go through google to have it or get from here. XML LINK

Roman Mazur · Answer 2 · 2012-04-11T16:33:16.450

1

Consult the LogCat output. Think, you'll see a message about the exception there.

Using Eclipse you can open the LogCat output with Window>Show View>LogCat.

According to this line

Error in http connection android.os.NetworkOnMainThreadException

you've targeted your app to Honeycomb or later. The exception is thrown because you are performing network operations from the main (GUI) thread, which is strongly prohibited :) Try to target your app to API level 10 (Gingerbread) just to try your code. But further move your network operations to AsyncTask.

edited Apr 11 '12 at 16:33

answered Apr 11 '12 at 16:14

Roman Mazur

3,084
1
20
24

I see, I added LogCat...Can you explain a little bit? Thanks. – cvele Apr 11 '12 at 16:18
It would have some sense if he'd put something into logs, not only errors. – Artem Oboturov Apr 11 '12 at 16:19
[http://stackoverflow.com/questions/2206822/no-internet-on-android-emulator-why-and-how-to-fix](http://stackoverflow.com/questions/2206822/no-internet-on-android-emulator-why-and-how-to-fix) – Artem Oboturov Apr 11 '12 at 16:22
Thanks Roman, I'll try it. :) Artem I cheked, internet on my emulator is working, but thanks anyway! – cvele Apr 11 '12 at 16:36
I tried, same thing happens... Any other suggestion? Now it says in LogCat this: Error parsing data org.json.JSONException: Value – cvele Apr 11 '12 at 16:58
Now check the value of `result` variable before creating a `JSONArray` instance (e.g. with dumping it to LogCat too). It contains your server response and must be a valid JSON. If not, you have problems on the server side. – Roman Mazur Apr 11 '12 at 21:03

score 1 · Answer 3 · answered Apr 11 '12 at 17:34

1

Again, check the actual received HTTP message: seems like you get XML instead of JSON.

answered Apr 11 '12 at 17:34

Artem Oboturov

4,344
2
30
48

or you could post the content of response you get from your server connection. Add the "is" content to your question. – Artem Oboturov Apr 11 '12 at 17:44

Connecting android app with sql database?

3 Answers3