Android: saving data into SQLite database

Question

I am trying to save some data from editText to the SQLite database which is based on my Android device (and it should be based only on devices)... so I created DatabaseHelper class and I creating database there, here is code of this file:

package ru.vladimir.droid_spy;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper 
{

    public static final String TABLE_NAME = "phones";
    public static final String COLUMN_PHONES ="phone_number";

    private static final String DATABASE_NAME = "trusted_phone.db";
    private static final int DATABASE_VERSION = 1;

    //create database
    private static final String DATABASE_CREATE = "create table " + TABLE_NAME + " ( " + BaseColumns._ID + " integer primary key autoincrement, " + COLUMN_PHONES + " text not null);";

    public DatabaseHelper(Context context) 
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        // TODO Auto-generated method stub

        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
    {
        // TODO Auto-generated method stub
        Log.w(DatabaseHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
    }

}

and then I want to save some data from my Activity with this code:

try 
{
    DatabaseHelper DBhelper = new DatabaseHelper(getBaseContext());
    //put DB in write mode
    SQLiteDatabase db = DBhelper.getWritableDatabase();
    //put variables
    ContentValues values = new ContentValues();
    //values.put(DatabaseHelper.COLUMN_ID, 1);
    values.put(DatabaseHelper.COLUMN_PHONES, txt_phone.getText().toString());

    //insert variables into DB
    long query = db.insert(DatabaseHelper.TABLE_NAME, null, values);

    //close DB
    db.close();
} 
catch(Exception e) 
{
    System.out.println("Error: "+e.getLocalizedMessage());
}

but when I saw LogCat logs, there was an error message:

07-27 00:04:50.463: E/SQLiteDatabase(15075): Error inserting phone_number=WEB
07-27 00:04:50.463: E/SQLiteDatabase(15075): android.database.sqlite.SQLiteException: table phones has no column named phone_number: , while compiling: INSERT INTO phones(phone_number) VALUES (?)

I think that this error is telling about that I do not create a column in my database, but how I can't create it, if I create this column in CREATE_DATABASE variable?

`Clear Data` and try again. Maybe you had a bad earlier version of your DB still around? — 323go, Jul 26 '13 at 21:17
@323go ok, it helped! thanks... but one more question.. how now I can see what is inserted into my DB? — vladimir, Jul 26 '13 at 21:22
If you're running on emulator, you can directly access the db through the DDMS. If not, you'll have to dump it from your app through a SELECT statement, or copy the DB file to SD card. — 323go, Jul 27 '13 at 05:19

score 0 · Answer 1 · answered Jul 26 '13 at 21:19

I'm just guessing here, but could it be that you ran the app before with with a different table structure? Since your database version is still 1, onUpgrade() gets never called. Neither does onCreate() because the db already exists. Either increase the db version, or uninstall and re-install the app to be sure that the db gets newly created.

score 0 · Answer 2 · edited Apr 12 '17 at 05:54

public class Sqlitedatabase extends SQLiteOpenHelper {

  public Sqlitedatabase(Context context) {
      super(context, "DoctorDatabase.db", null, 1);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
      String tableEmp = "create table Doctor(Name text,Number text)";
      db.execSQL(tableEmp);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  }

  public void insertData(DocotorInformation doctor) {
      SQLiteDatabase db = this.getWritableDatabase();

      ContentValues values = new ContentValues();
      values.put("Name", doctor.getName()); // Contact Name
      values.put("Number", doctor.getNumber()); // Contact Phone Number

      // Inserting Row
      db.insert("Doctor", null, values);
      db.close(); // Closing database connection
  }


  public ArrayList<DocotorInformation> getAllContacts() {
      ArrayList<DocotorInformation> contactList = new ArrayList<DocotorInformation>();
      // Select All Query
      String selectQuery = "SELECT  * FROM Doctor";

      SQLiteDatabase db = this.getWritableDatabase();
      Cursor cursor = db.rawQuery(selectQuery, null);

      // looping through all rows and adding to list
      if (cursor.moveToFirst()) {
          do {
            DocotorInformation contact = new DocotorInformation();
            contact.setName(cursor.getString(0));
            contact.setNumber(cursor.getString(1));

            // Adding contact to list
            contactList.add(contact);
        } while (cursor.moveToNext());
    }

    // return contact list
    return contactList;
    }
}

Android: saving data into SQLite database

2 Answers2