Geek Answers Handbook

Problem with SQLite DB for Android

I am new to Android and follow tutorials on creating an application. I'm having difficulty embedding data in a database. I tried everything I could think of, but to no avail. Database Class:

package com.mckallip.BeerOnTheWall; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class DatabaseUtil{ private static final String TAG = "DatabaseUtil"; /** * Database Name */ private static final String DATABASE_NAME = "Beer_Database"; /** * Database Version */ private static final int DATABASE_VERSION = 1; /** * Table Name */ private static final String DATABASE_TABLE = "Beer_List"; /** * Table columns */ public static final String KEY_BEER_NAME = "beer_name"; public static final String KEY_BEER_STYLE = "beer_style"; public static final String KEY_BREWERY = "beer_brewery"; public static final String KEY_ABV = "beer_abv"; public static final String KEY_BEER_SCORE = "beer_score"; public static final String KEY_BEER_IMAGE = "beer_image"; public static final String KEY_BEER_COMMENTS = "beer_comments"; public static final String KEY_ROWID = "_id"; /** * Database creation sql statement */ private static final String CREATE_BEER_TABLE = "CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_BEER_NAME + " TEXT, " + KEY_BEER_STYLE + " TEXT, " + KEY_BREWERY + " TEXT, " + KEY_ABV + " TEXT, " + KEY_BEER_SCORE + "TEXT, " + KEY_BEER_IMAGE + " TEXT, " + KEY_BEER_COMMENTS + "TEXT );"; /** * Context */ private final Context mCtx; private DatabaseHelper mDbHelper; private SQLiteDatabase mDb; /** * Inner private class. Database Helper class for creating and updating database. */ private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } /** * onCreate method is called for the 1st time when database doesn't exists. */ @Override public void onCreate(SQLiteDatabase db) { Log.i(TAG, "Creating DataBase: " + CREATE_BEER_TABLE); db.execSQL(CREATE_BEER_TABLE); } /** * onUpgrade method is called when database version changes. */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion); } } protected SQLiteDatabase getDataBase(){ return mDb; } /** * Constructor - takes the context to allow the database to be * opened/created * * @param ctx the Context within which to work */ public DatabaseUtil(Context ctx) { this.mCtx = ctx; } /** * This method is used for creating/opening connection * @return instance of DatabaseUtil * @throws SQLException */ public DatabaseUtil open() throws SQLException { mDbHelper = new DatabaseHelper(mCtx); mDb = mDbHelper.getWritableDatabase(); return this; } /** * This method is used for closing the connection. */ public void close() { mDbHelper.close(); } /** * This method is used to create/insert new record Beer record. * @param sbeer_name * @param sbeer_style * @param sbeer_score * @param sbrewery * @param sabv * @param simageLoc * @param scomments * @return long */ public long createBeer(String beer_name, String beer_style, String beer_score, String brewery, String abv, String imageLoc, String comments) { ContentValues initialValues = new ContentValues(); initialValues.put(KEY_BEER_NAME, beer_name); initialValues.put(KEY_BEER_STYLE, beer_style); initialValues.put(KEY_BREWERY, brewery); initialValues.put(KEY_ABV, abv); initialValues.put(KEY_BEER_SCORE, beer_score); initialValues.put(KEY_BEER_IMAGE, imageLoc); initialValues.put(KEY_BEER_COMMENTS, comments); return mDb.insert(DATABASE_TABLE, null, initialValues); } /** * This method will delete Beer record. * @param rowId * @return boolean */ public boolean deleteBeer(long rowId) { return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0; } /** * This method will return Cursor holding all the Beer records. * @return Cursor */ public Cursor fetchAllBeers() { return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_BEER_NAME, KEY_BEER_STYLE, KEY_BREWERY, KEY_ABV, KEY_BEER_SCORE, KEY_BEER_IMAGE, KEY_BEER_COMMENTS}, null, null, null, null, null); } /** * This method will return Cursor holding the specific Beer record. * @param id * @return Cursor * @throws SQLException */ public Cursor fetchBeer(long id) throws SQLException { Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_BEER_NAME, KEY_BEER_STYLE, KEY_BREWERY, KEY_ABV, KEY_BEER_SCORE, KEY_BEER_IMAGE, KEY_BEER_COMMENTS }, KEY_ROWID + "=" + id, null, null, null, null, null); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } /** * This method will update Beer record. * @param id * @param name * @param standard * @return boolean */ public boolean updateBeer(int id, String beer_name, String beer_style, String beer_score, String brewery, String abv, String imageLoc, String comments) { ContentValues args = new ContentValues(); args.put(KEY_BEER_NAME, beer_name); args.put(KEY_BEER_STYLE, beer_style); args.put(KEY_BREWERY, brewery); args.put(KEY_BEER_SCORE, beer_score); args.put(KEY_ABV, abv); args.put(KEY_BEER_IMAGE, imageLoc); args.put(KEY_BEER_COMMENTS, comments); return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + id, null) > 0; } }

Class for adding data:

  package com.mckallip.BeerOnTheWall; import android.content.Context; import android.content.Intent; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.EditText; import android.widget.Spinner; import android.widget.TextView; import android.widget.Toast; public class AddBeerActivity extends BeerOnTheWallActivity { private EditText et1; private EditText et2; private EditText et3; private EditText et4; private Spinner score_spin; private EditText et5; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.add_beer); mDatabase.open(); Context e = getApplicationContext(); String test = "read only"; if (mDatabase.getDataBase().isReadOnly()){ test = "Not ReadOnly" + mDatabase.getDataBase().getPath(); } Toast t = Toast.makeText(e, test, 2); t.show(); et1 = (EditText) findViewById(R.id.add_beer_name); et2 = (EditText)findViewById(R.id.add_beer_style); et3 = (EditText) findViewById(R.id.add_brewery); et4 = (EditText) findViewById(R.id.add_abv); score_spin = (Spinner) findViewById(R.id.score_spinner); et5 = (EditText) findViewById(R.id.add_comments); ((Button)findViewById(R.id.add_submit)).setOnClickListener(new OnClickListener() { public void onClick( View v ){ if (mDatabase.getDataBase().isReadOnly())mDatabase.open(); long beer_Id = mDatabase.createBeer( et1.toString(), et2.toString(), score_spin.toString(), et3.toString(), et4.toString(), "image", et5.toString() ); Context context = getApplicationContext(); CharSequence text = "Your beer was added at database position " + beer_Id; if ( beer_Id == -1 ){ text = "There was an error and the beer could not be added."; } Toast toast = Toast.makeText(context, text, 2); toast.show(); // reset form if ( beer_Id != -1 ){ et1.setText(null); et2.setText(null); et3.setText(null); et4.setText(null); et5.setText(null); } } }); // Handle Go to List button final Button gotoList = (Button) findViewById(R.id.beer_list); gotoList.setOnClickListener(new View.OnClickListener() { public void onClick(View v) { // Go to other activity that displays beer list Intent intent = new Intent(AddBeerActivity.this, BeerListActivity.class); startActivity(intent); } }); } }

I added toasts as a way to try and debug it. The first claims that it is read-only, and I cannot understand why and how to make it writable.

I also create an instance of DatabaseUtil in BeerOnTheWallActivity as a static variable, since ...

 public class BeerOnTheWallActivity extends Activity { protected DatabaseUtil mDatabase = null; //protected Cursor mCursor = null; //protected SQLiteDatabase mDB = null; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); mDatabase = new DatabaseUtil(this.getApplicationContext()); mDatabase.open(); }

When I click the button to add data, the error log is:

07-14 00: 31: 45.549: ERROR / Database (31512): Insert error beer_abv=android.widget.EditText@47a27ea8 beer_style=android.widget.EditText@47a25d28 beer_score=android.widget.Spinner@47a2a768 beer_name = android.widget.EditText@47a245a8 beer_comments=android.widget.EditText@47a2b560 beer_image = image beer_brewery=android.widget.EditText@47a26de8

07-14 00: 31: 45.549: ERROR / Database (31512): android.database.sqlite.SQLiteException: table Beer_List does not have a column named beer_score: when compiling: INSERT INTO Beer_List (beer_abv, beer_style, beer_score, beer_name, beer_comments , beer_image, beer_brewery) VALUES (?,?,?,?,?,?,?);

I think that is all. Any help would be greatly appreciated.

+4
source share
2 answers

Please check below.

your code

 private static final String CREATE_BEER_TABLE = "CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_BEER_NAME + " TEXT, " + KEY_BEER_STYLE + " TEXT, " + KEY_BREWERY + " TEXT, " + KEY_ABV + " TEXT, " + KEY_BEER_SCORE + "TEXT, " + KEY_BEER_IMAGE + " TEXT, " + KEY_BEER_COMMENTS + "TEXT );";

Modified to use this

 private static final String CREATE_BEER_TABLE = "CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_BEER_NAME + " TEXT, " + KEY_BEER_STYLE + " TEXT, " + KEY_BREWERY + " TEXT, " + KEY_ABV + " TEXT, " + KEY_BEER_SCORE + " TEXT, " + KEY_BEER_IMAGE + " TEXT, " + KEY_BEER_COMMENTS + " TEXT );";
+2
source

This is not true

  long beer_Id = mDatabase.createBeer( et1.toString(), et2.toString(), score_spin.toString(), et3.toString(), et4.toString(), "image", et5.toString() );

Here you do not get the text e1. Try e1.getText().toString()

This leads to the following errors:

07-14 00: 31: 45.549: ERROR / Database (31512): error insert beer_abv=android.widget.EditText@47a27ea8 beer_style=android.widget.EditText@47a25d28 beer_score=android.widget.Spinner@47a2a768 beer_name=android.widget.EditText@47a245a8 beer_comments=android.widget.EditText@47a2b560 beer_image = image beer_brewery=android.widget.EditText@47a26de8

+1
source

More articles:


All Articles