Passing JSON data into SQLite and Retrieving it

Question

I've made an app that takes JSON data from an online webserver and then puts the data into an SQLite database. However when i try and retrieve said information the app crashes. Heres my code:

MainActivty.java

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.database.Cursor;
import android.net.ConnectivityManager;
import android.net.NetworkInfo;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import android.app.Activity;

public class MainActivity extends Activity {

private DBHelper mydb;

EditText etResponse;
TextView tvIsConnected;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    // get reference to the views
    etResponse = (EditText) findViewById(R.id.etResponse);
    tvIsConnected = (TextView) findViewById(R.id.tvIsConnected);

    mydb = new DBHelper(this);

    // check if you are connected or not
    if(isConnected()){
        tvIsConnected.setBackgroundColor(0xFF00CC00);
        tvIsConnected.setText("You are connected");
    }
    else{
        tvIsConnected.setText("You are NOT connected");
    }

    // call AsynTask to perform network operation on separate thread
    new HttpAsyncTask().execute("http://test.com/android_connect/get_all_events.php");
  }

  public static String GET(String url){
    InputStream inputStream = null;
    String result = "";
    try {

        // create HttpClient
        HttpClient httpclient = new DefaultHttpClient();

        // make GET request to the given URL
        HttpResponse httpResponse = httpclient.execute(new HttpGet(url));

        // receive response as inputStream
        inputStream = httpResponse.getEntity().getContent();

        // convert inputstream to string
        if(inputStream != null)
            result = convertInputStreamToString(inputStream);
        else
            result = "Did not work!";

    } catch (Exception e) {
        Log.d("InputStream", e.getLocalizedMessage());
    }

    return result;
}

   private static String convertInputStreamToString(InputStream inputStream) throws IOException{
    BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(inputStream));
    String line = "";
    String result = "";
    while((line = bufferedReader.readLine()) != null)
        result += line;

    inputStream.close();
    return result;

}

   public boolean isConnected(){
    ConnectivityManager connMgr = (ConnectivityManager)       getSystemService(Activity.CONNECTIVITY_SERVICE);
    NetworkInfo networkInfo = connMgr.getActiveNetworkInfo();
    if (networkInfo != null && networkInfo.isConnected())
        return true;
    else
        return false;
}
private class HttpAsyncTask extends AsyncTask<String, Void, String> {
    @Override
    protected String doInBackground(String... urls) {

        return GET(urls[0]);
    }
    // onPostExecute displays the results of the AsyncTask.
    @Override
    protected void onPostExecute(String result) {
        Toast.makeText(getBaseContext(), "Received!", Toast.LENGTH_LONG).show();

       updateDatabase(result);


        etResponse.setText(mydb.getData("FND"));

    }
}

  private void updateDatabase(String result){



    try {
        JSONObject json = new JSONObject(result);
        JSONArray events = json.getJSONArray("events");

       for (int i=0; i<events.length(); i++){


            String name = events.getJSONObject(i).getString("name");
            String date =events.getJSONObject(i).getString("date");
            String category = events.getJSONObject(i).getString("category");
            String description = events.getJSONObject(i).getString("description");
            String time_start = events.getJSONObject(i).getString("time_start");
            String time_end = events.getJSONObject(i).getString("time_end");
            String address = events.getJSONObject(i).getString("address");
            String postcode = events.getJSONObject(i).getString("postcode");

            mydb.insertEvent(name,date,category,description,time_start,time_end,address,postcode);

        }


    } catch (JSONException e) {
        e.printStackTrace();
    }
  }
}

DBHelper.java

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;

public class DBHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "Events.db";
public static final String EVENTS_TABLE_NAME = "events";
public static final String EVENTS_COLUMN_ID = "event_ID";
public static final String EVENTS_COLUMN_NAME = "name";
public static final String EVENTS_COLUMN_DATE = "date";
public static final String EVENTS_COLUMN_CATEGORY = "category";
public static final String EVENTS_COLUMN_DESCRIPTION = "description";
public static final String EVENTS_COLUMN_TIME_START = "time_start";
public static final String EVENTS_COLUMN_TIME_END = "time_end";
public static final String EVENTS_COLUMN_ADDRESS = "address";
public static final String EVENTS_COLUMN_POSTCODE = "postcode";

private HashMap hp;

public DBHelper(Context context)
{
    super(context, DATABASE_NAME , null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table events " +
                    "(event_ID integer primary key, name text,date text,category text, description text,time_start text," +
                    "time_end text, address text, postcode text)"
    );
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS events");
    onCreate(db);
}

public boolean insertEvent  (String name, String date, String category, String description,String time_start,String time_end, String address,String postcode)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();

    contentValues.put("name", name);
    contentValues.put("date", date);
    contentValues.put("category", category);
    contentValues.put("description", description);
    contentValues.put("time_start", time_start);
    contentValues.put("time_end", time_end);
    contentValues.put("address", address);
    contentValues.put("postcode", postcode);

    db.insert("events", null, contentValues);
    return true;
 }
 public String getData(String name){
    String response = null;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery("select * from events where name=" + name + "", null);
    res.moveToFirst();
    while(res.isAfterLast() == false){
        response += (res.getString(res.getColumnIndex(EVENTS_COLUMN_CATEGORY)));
        res.moveToNext();
    }
    return response;
 }
public int numberOfRows(){
    SQLiteDatabase db = this.getReadableDatabase();
    int numRows = (int) DatabaseUtils.queryNumEntries(db, EVENTS_TABLE_NAME);
    return numRows;
  }
}

logcat

12-09 05:46:26.420    1701-1701/team08.httpapp D/dalvikvm﹕ Not late-enabling CheckJNI (already on)
12-09 05:46:26.590    1701-1701/team08.httpapp D/﹕ HostConnection::get() New Host Connection established 0xb7a7f180, tid 1701
12-09 05:46:26.620    1701-1701/team08.httpapp W/EGL_emulation﹕ eglSurfaceAttrib not implemented
12-09 05:46:26.640    1701-1721/team08.httpapp D/dalvikvm﹕ GC_FOR_ALLOC freed 142K, 8% free 3197K/3448K, paused 3ms, total 5ms
12-09 05:46:26.640    1701-1701/team08.httpapp D/OpenGLRenderer﹕ Enabling debug mode 0
12-09 05:46:27.290    1701-1701/team08.httpapp E/SQLiteLog﹕ (1) no such column: FND
12-09 05:46:27.290    1701-1701/team08.httpapp D/AndroidRuntime﹕ Shutting down VM
12-09 05:46:27.290    1701-1701/team08.httpapp W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xb0c98b20)
12-09 05:46:27.290    1701-1701/team08.httpapp E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: team08.httpapp, PID: 1701
android.database.sqlite.SQLiteException: no such column: FND (code 1): , while compiling: select * from events where name=FND
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
        at team08.httpapp.DBHelper.getData(DBHelper.java:72)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:118)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:104)
        at android.os.AsyncTask.finish(AsyncTask.java:632)
        at android.os.AsyncTask.access$600(AsyncTask.java:177)
        at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:136)
        at android.app.ActivityThread.main(ActivityThread.java:5017)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:515)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
        at dalvik.system.NativeStart.main(Native Method)
12-09 06:06:06.815    1845-1845/team08.httpapp D/dalvikvm﹕ Not late-enabling CheckJNI (already on)
12-09 06:06:07.005    1845-1858/team08.httpapp D/dalvikvm﹕ GC_FOR_ALLOC freed 130K, 7% free 3208K/3448K, paused 4ms, total 9ms
12-09 06:06:07.025    1845-1845/team08.httpapp D/﹕ HostConnection::get() New Host Connection established 0xb7b74070, tid 1845
12-09 06:06:07.075    1845-1845/team08.httpapp W/EGL_emulation﹕ eglSurfaceAttrib not implemented
12-09 06:06:07.085    1845-1845/team08.httpapp D/OpenGLRenderer﹕ Enabling debug mode 0
12-09 06:06:07.625    1845-1845/team08.httpapp E/SQLiteLog﹕ (1) no such column: FND
12-09 06:06:07.625    1845-1845/team08.httpapp D/AndroidRuntime﹕ Shutting down VM
12-09 06:06:07.625    1845-1845/team08.httpapp W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xb0c98b20)
12-09 06:06:07.635    1845-1845/team08.httpapp E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: team08.httpapp, PID: 1845
android.database.sqlite.SQLiteException: no such column: FND (code 1): , while compiling: select *    from events where name=FND
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at      android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
        at team08.httpapp.DBHelper.getData(DBHelper.java:72)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:118)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:104)
        at android.os.AsyncTask.finish(AsyncTask.java:632)
        at android.os.AsyncTask.access$600(AsyncTask.java:177)
        at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:136)
        at android.app.ActivityThread.main(ActivityThread.java:5017)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:515)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
        at dalvik.system.NativeStart.main(Native Method)

seems like you are'nt opening database connection before updateDatabase method call — Veaceslav Gaidarji
– Veaceslav Gaidarji, Commented Dec 9, 2014 at 11:04
@VeaceslavGaidarji In the insertEvent() method call it opens the database connection. — user2311710
– user2311710, Commented Dec 9, 2014 at 11:26

Veaceslav Gaidarji · Accepted Answer · 2014-12-09 11:28:56Z

1

Update your getData method, add quotes for values:

db.rawQuery("select * from events where name='" + name + "'", null);

answered Dec 9, 2014 at 11:28

Veaceslav Gaidarji

4,3313 gold badges41 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amrut Bidri · Accepted Answer · 2014-12-09 11:39:54Z

0

change your query like below:

Cursor res =  db.rawQuery("select * from events where name='" + name + "'", null);

answered Dec 9, 2014 at 11:39

Amrut Bidri

6,3607 gold badges42 silver badges81 bronze badges

Comments

Zubair Ahmed · Accepted Answer · 2014-12-09 11:55:48Z

0

I think you should use 'LIKE' in your Query to match string values:

Cursor res =  db.rawQuery("select * from events where name like '%" + name + "%'", null);

answered Dec 9, 2014 at 11:55

Zubair Ahmed

2,9072 gold badges29 silver badges48 bronze badges

Comments

Amsheer · Accepted Answer · 2015-01-14 10:04:19Z

0

**Suggestion:**

Please check your data (name). Because if you have single quote in your data(name) you may get this error.

Cursor res =  db.rawQuery("select * from events where name='" + name + "", null);

Before executing above line of code check the data of name.

Please check that.

If that is the error then use the following line before executing.

name =name.replace("'", "''");
  Cursor res =  db.rawQuery("select * from events where name='" + name + "", null);

This is just an possibility.

edited Jan 14, 2015 at 10:04

answered Dec 9, 2014 at 11:52

Amsheer

7,1618 gold badges52 silver badges83 bronze badges

Collectives™ on Stack Overflow

Passing JSON data into SQLite and Retrieving it

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related