Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I've been following a tutorial to perform basic reads/writes to a database using SQLite.

On the loaddata action in AddWord.java (the Activity) the program freezes. I'm not sure from the debugger which line is failing. The program is split into three classes as follows:

DBHandler.Java:

package com.AH.memorisethai;

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

public class DBHandler extends SQLiteOpenHelper {

    private static final String KEY_ID = "id";
    private static final String KEY_ENGLISH = "english";
    private static final String KEY_THAI = "thai";
    private static final String KEY_WORDTYPE = "wordtype";
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "wordsmanager";
    private static final String TABLE_WORDS = "words";
    private static final String TABLE_CREATE = "CREATE TABLE " + TABLE_WORDS
                                + "(" + KEY_ID + " AUTOINCREMENT," + KEY_ENGLISH + " TEXT NOT NULL," + KEY_THAI + " TEXT NOT NULL" + ");";

    public DBHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(TABLE_CREATE);
    }

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

    public void addEntry(Entry entry) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ENGLISH, entry.getEnglishword()); // Contact Name
        values.put(KEY_THAI, entry.getThaiword()); // Contact Phone Number

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

    // Getting contacts Count
    public int getContactsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_WORDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();
        // return count
        return cursor.getCount();
    }
}

Entry.Java:

package com.AH.memorisethai;

public class Entry {

    private long id;
    private String englishword;
    private String thaiword;

    public Entry(){
    }

    public Entry(int id, String englishword, String thaiword){
        this.id = id;
        this.englishword = englishword;
        this.thaiword = thaiword;
    }

    // constructor
    public Entry(String englishword, String thaiword){
        this.englishword = englishword;
        this.thaiword = thaiword;
    }

    public long getId(){
        return id;
    }

    public String getEnglishword(){
        return englishword;
    }

    public String getThaiword(){
        return thaiword;
    }

    public void setId(long id){
        this.id=id;
    }

    public void setEnglishword(String englishword){
        this.englishword=englishword;
    }

    public void setThaiword(String thaiword){
        this.thaiword=thaiword;
    }
}

And within the Activity:

public int getContactsCount() {
    String countQuery = "SELECT COUNT(*) FROM " + TABLE_WORDS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = -1;
    if (cursor.moveToFirst()) {
        count = cursor.getInt(0);
    }
    cursor.close();
    return count;
}

As there is currently no data ed should return a value of 0 - I thought this was the simplest test to make sure the DBHandler code worked.

share|improve this question
    
What line in loaddata? Put a break point in the method and see how far it goes down before it quits. Also your KEY_ID should be autoincrement and you missed a semi-colon at the end in your TABLE_CREATE string. –  BigT Feb 12 at 15:51
    
Thanks @BigT - I've altered KEY_ID to be autoincrement as suggested and added the semicolon at the end of the TABLE_CREATE string (code in question amended). The line it is failing at is: int a = db.getContactsCount(); –  Andy Feb 12 at 16:19

1 Answer 1

up vote 3 down vote accepted

Multiple issues here.

First, SQL syntax problems here:

private static final String TABLE_CREATE = "CREATE TABLE" + TABLE_WORDS
                                + "(" + KEY_ID + "AUTOINCREMENT," + KEY_ENGLISH + "TEXT NOT NULL," + KEY_THAI + "TEXT NOT NULL" + ");";

You need to add whitespace between keywords and identifiers, like this:

private static final String TABLE_CREATE = "CREATE TABLE " + TABLE_WORDS
                                + "(" + KEY_ID + " AUTOINCREMENT," + KEY_ENGLISH + " TEXT NOT NULL," + KEY_THAI + " TEXT NOT NULL" + ");";

AUTOINCREMENT requires it to be INTEGER PRIMARY KEY AUTOINCREMENT. The semicolon ; at the end is optional.

Whitespace also needed in DROP TABLE:

db.execSQL("DROP TABLE IF EXISTS " + TABLE_WORDS)
//                         here ^

In getContactsCount():

public int getContactsCount() {
    String countQuery = "SELECT  * FROM " + TABLE_WORDS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();
    // return count
    return cursor.getCount();
}

... you're accessing the cursor after it's closed. Also, since you're intetested in count only, you can ask the database engine to count the rows for you:

public int getContactsCount() {
    String countQuery = "SELECT COUNT(*) FROM " + TABLE_WORDS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = -1;
    if (cursor.moveToFirst()) {
        count = cursor.getInt(0);
    }
    cursor.close();
    return count;
}

After editing the database schema in db helper onCreate(), make sure the database is recreated. Easiest way to achieve this is to just uninstall the app so its data files including the database file are removed.

share|improve this answer
    
Thank you very much - I've made the additions and it works perfectly now. As a slight aside, since I'm new to databases within Android (and SQLite) is this a fairly efficient way to set up an SQLite database within Android in terms of lines of code? –  Andy Feb 12 at 17:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.