Followed the official android documentations but still could not use SQLite in app

Question

My DBHelper class

public class DBHelper extends SQLiteOpenHelper {

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

@Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE SIMPLE_TABLE ( " + "ID INTEGER PRIMARY KEY " + "DESC TEXT);"); }

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

} }

Activity class

public class SimpleDatabase extends Activity 
{
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    DBHelper dbHelper = new DBHelper(this);
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    db.execSQL("INSERT INTO SIMPLE_TABLE VALUES (NULL, 'test');");

Cursor cursor = db.rawQuery("SELECT * FROM SIMPLE_TABLE", null); TextView text = (TextView)findViewById(R.id.textbox); text.setText(cursor.getString(0)); } }

I figure it crashed (application has stopped unexpectedly!) at SQLiteDatabase db = ... because if I commented the code out from there to the end then it worked fine. But I have no idea whatsoever why it does that. Any help would be appreciated.

Answer 1

First, to help in your debugging, make sure to use the debug monitor and Log. This will make your life a lot easier in the long run! If you are using Eclipse, select Window -> Open Perspective -> Other -> DDMS. The LogCat will show you what is happening as the program is run. Documentation describes it here. Run your program again and watch the LogCat. It should give you more information and tell you what line of code is crashing.

As far as your code goes, the first thing I notice is that after you get a cursor back, you need to call cursor.moveToFirst(); to select the (first) row. Then when you call cursor.getString(0);, it indicates the zeroeth column of the zeroeth row. If you do not call moveToFirst(); then you cursor.getString(0) is going to get the zeroeth column of the -1st row and be an index out of bounds error. By default, the cursor will start at row -1.

Depending on how you want to move through your cursor, and how many results/rows you get back, you may also need to call cursor.movetoPosition() or cursor.moveToNext(). Check out the documentation on cursors here.

Answer 2

Hi, first of all you should ensure that your SQL statement is correct. If sqlite3 is in your path you could execute the command:

$>: sqlite3 testdb.db

after that you are in an shell where you can test your SQL statements if there are syntactically correct. (Hint: refering the example above: it is not correct).

After that you should handle your cursor correctly as described by Aurora.

Maybe you should implement your onUpgrade() method, e.g:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS SIMPLE_TABLE");
    onCreate(db);
}

Answer 3

Never mind, figured out what I did wrong now.

db.execSQL("CREATE TABLE SIMPLE_TABLE ( " + "ID INTEGER PRIMARY KEY<comma goes here> " + "DESC TEXT);");

Commas are serious businesses. Sorry for the stupid question.