Retrive values from SQLite database

Question

I want to retrieve values from SQLite database in my Android application. Here in my program first I stored two strings in to SQLite and my table name is 'name'. In second part I retrieve two strings which were stored in the SQLite database and display them in edit text field. But after I run the program, it displays empty screen only. Please give me a solution or any changes in my code.

     package com.ret;

     import java.util.Locale;

     import android.app.Activity;
     import android.content.ContentValues;
     import android.content.Intent;
     import android.database.Cursor;
     import android.database.sqlite.SQLiteDatabase;
     import android.os.Bundle;
     import android.view.View;
     import android.widget.Button;
     import android.widget.EditText;
     import android.R.id;

     public class RetriveActivity extends Activity {
          ContentValues values= new ContentValues();
      SQLiteDatabase db;
      String first="android";
      String last="apps";
      String f,s;
         @Override
     public void onCreate(Bundle savedInstanceState) {
     super.onCreate(savedInstanceState);
     setContentView(R.layout.main);
     db=openOrCreateDatabase("test5.db",SQLiteDatabase.CREATE_IF_NECESSARY,null);

     db.setVersion(1);
     db.setLocale(Locale.getDefault());
     db.setLockingEnabled(true);
     final String table_create="CREATE TABLE name("+"column1 TEXT,"+"column2 TEXT);";
     db.execSQL(table_create);
     values.put("column1",first);
     values.put("column2",last);
     db.insert("name", null, values);
     Cursor c = db.query("name",null,null, null, null, null,null);
     while(c.moveToFirst())
     {
     f=c.getString(c.getColumnIndex("column1"));
     s=c.getString(c.getColumnIndex("column2"));
     db.close();
     }
     EditText e1=(EditText)findViewById(R.id.editText1);
     e1.setText(""+f+""+s);
     db.close();

     }
 }

Answer 1

Try the below modifications in your code..

db = db.getWritableDatabase();        
         db.execSQL(table_create);
         values.put("column1",first);
         values.put("column2",last);
         db.insert("name", null, values);
String columns[]={"column1","column2"};
         Cursor c = db.query("name",columns,null, null, null, null,null);

If it does not work then pls first close your db after insert statement and open it again and write the cursor line.

Answer 2

Please look at to this example, it shows how to retrieve data from sqlite database.

package com.collabera.labs.sai.db;

import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.util.Log;
import android.widget.ArrayAdapter;

public class CRUDonDB extends ListActivity {

    private final String SAMPLE_DB_NAME = "myFriendsDb";
    private final String SAMPLE_TABLE_NAME = "friends";

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);


        ArrayList<String> results = new ArrayList<String>();
        SQLiteDatabase sampleDB = null;

        try {
            sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

            sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                    SAMPLE_TABLE_NAME +
                    " (LastName VARCHAR, FirstName VARCHAR," +
                    " Country VARCHAR, Age INT(3));");

            sampleDB.execSQL("INSERT INTO " +
                    SAMPLE_TABLE_NAME +
                    " Values ('Makam','Sai Geetha','India',25);");
            sampleDB.execSQL("INSERT INTO " +
                    SAMPLE_TABLE_NAME +
                    " Values ('Chittur','Raman','India',25);");
            sampleDB.execSQL("INSERT INTO " +
                    SAMPLE_TABLE_NAME +
                    " Values ('Solutions','Collabera','India',20);");

            Cursor c = sampleDB.rawQuery("SELECT FirstName, Age FROM " +
                    SAMPLE_TABLE_NAME +
                    " where Age > 10 LIMIT 5", null);

            if (c != null ) {
                if  (c.moveToFirst()) {
                    do {
                        String firstName = c.getString(c.getColumnIndex("FirstName"));
                        int age = c.getInt(c.getColumnIndex("Age"));
                        results.add("" + firstName + ",Age: " + age);
                    }while (c.moveToNext());
                } 
            }

            this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));

        } catch (SQLiteException se ) {
            Log.e(getClass().getSimpleName(), "Could not create or Open the database");
        } finally {
            if (sampleDB != null) 
                sampleDB.execSQL("DELETE FROM " + SAMPLE_TABLE_NAME);
                sampleDB.close();
        }
    }
}