Android Sqlite "No Such Table" Error

Question

I am trying to learn about Sqlite databases in android but i can not handle simple jobs. When i insert a record in database with "insertWord" i got this error. It seems interesting because types table is created and inserted values by the program. What am i doing wrong?.

This is my code (Database.Java).

package com.pekgenc.mehmet.spinnerexample;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.NonNull;
import android.util.Log;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;

/**
 * Created by Mehmet on 18.08.2017.
 */

public class Database extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "mywordbank";

    // Labels table name
    private static final String TABLE_TYPES = "types";

    // Labels table column names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";

    // Words table name
    private static final String TABLE_WORDS = "words";

    // Words table column names
    private static final String WKEY_ID = "id";
    private static final String WKEY_TUR_NAME = "tur_id";
    private static final String WKEY_ENGLISH = "english";
    private static final String WKEY_TURKISH = "turkish";
    private static final String WKEY_EXAMPLE = "example";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_TYPES + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + " TEXT)";

        String CREATE_WORDS_TABLE = "CREATE TABLE " + TABLE_WORDS + "("
                + WKEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + WKEY_TUR_NAME + " INTEGER,"
                + WKEY_ENGLISH + " TEXT," + WKEY_TURKISH + " TEXT," + WKEY_EXAMPLE + " TEXT)";

        db.execSQL(CREATE_CATEGORIES_TABLE);
        try {
            db.execSQL(CREATE_WORDS_TABLE);
        }
        catch (SQLiteException es)
        {
            Log.e("ERR: ", es.getMessage());
        }


        // Inserting label values
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Adjective')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Adverb')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Conjunction')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Determiner')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Idiom')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Interjection')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Noun')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Phrasal Verb')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Phrase')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Preposition')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Pronoun')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Verb')");
        db.execSQL("INSERT INTO " + TABLE_TYPES + "(" + KEY_NAME + ")" + " VALUES ('Other')");
    }

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

    // Get all types
    public List<String> getAllLabels() {
        List<String> labels = new ArrayList<String>();

        String selectQuery = "SELECT * FROM " + TABLE_TYPES;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                labels.add(cursor.getString(1));
            } while(cursor.moveToNext());
        }

        cursor.close();
        db.close();

        return labels;
    }

    // inserting a word in database
    public void insertWord(int tur_id, String english, String turkish, String example) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(WKEY_TUR_NAME, tur_id);
        values.put(WKEY_ENGLISH, english);
        values.put(WKEY_TURKISH, turkish);
        values.put(WKEY_EXAMPLE, example);

        try {
            db.insertOrThrow(TABLE_WORDS, null, values);
        }
        catch (SQLiteException e) {
            Log.e("ERR: ", e.getMessage());
        }
        db.close();
    }

    public List<String> fetchAllWords() {
        List<String> words = new ArrayList<String>();
        String selectQuery = "SELECT english, turkish FROM " + TABLE_WORDS;

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

        if (cursor.moveToFirst()) {
            do {
                words.add(cursor.getString(1));
            } while(cursor.moveToNext());
        }

        cursor.close();
        db.close();

        return words;
    }
}

(AddActivity.Java)

package com.pekgenc.mehmet.spinnerexample;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;

import java.util.List;

public class AddActivity extends AppCompatActivity {

    EditText textEng, textTur, textExp;
    Button btnAdd, btnBack;
    Spinner sp;
    ArrayAdapter<String> adaptor;
    int selectedIndex;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_add);

        textEng = (EditText) findViewById(R.id.editTextEnglish);
        textTur = (EditText) findViewById(R.id.editTextTurkish);
        textExp = (EditText) findViewById(R.id.editTextExample);
        btnAdd  = (Button) findViewById(R.id.buttonAdd);
        btnBack = (Button) findViewById(R.id.buttonBack);
        sp      = (Spinner)findViewById(R.id.spinnerType);

        loadSpinnerData();

        sp.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                selectedIndex = parent.getSelectedItemPosition() + 1;
            }

            @Override
            public void onNothingSelected(AdapterView<?> parent) {

            }
        });

        btnAdd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Database db = new Database(getApplicationContext());
                db.insertWord(selectedIndex, textEng.getText().toString(), textTur.getText().toString(), textExp.getText().toString());
                db.close();
            }
        });
    }

    private void loadSpinnerData() {

        Database db = new Database(getApplicationContext());
        List<String> labels = db.getAllLabels();

        adaptor = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_dropdown_item, labels);
        adaptor.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        sp.setAdapter(adaptor);
    }
}

(LogCat)

08-20 20:29:42.621 16118-16118/com.pekgenc.mehmet.spinnerexample E/SQLiteLog: (1) no such table: words
08-20 20:29:42.622 16118-16118/com.pekgenc.mehmet.spinnerexample E/ERR:: no such table: words (code 1): , while compiling: INSERT INTO words(example,english,turkish,tur_id) VALUES (?,?,?,?)

Thank you.

If you have added table **words** since first running and creating table **types**, then you need to **a)** delete the App's data, or **b)** Uninstall the App or **c)** increase `DATABASE_VERSION` e.g. to **2**. All 3 will result in the `onCreate` method being run. `onCreate` will only automatically be run if the database doesn't exist (hence options a and b). It will also be run as part of your `onUpgrade` (hence option c). — MikeT, Aug 20 '17 at 21:13
@MikeT, I haven't used the openhelper for quite a few years, but are you sure increasing the version will invoke onCreate() ? I believe it will call onUpgrade only. — Nick, Aug 20 '17 at 21:17
@Nick in the question's code you will see that `onCreate` is called from within `onUpgrade`. i.e. It depends upon the code in `onUpgarde` including`onCreate (and of course appropriately deleting the tables). — MikeT, Aug 20 '17 at 21:20
https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html yeah, onUpgrade only — Nick, Aug 20 '17 at 21:20
@MikeT sorry, missed that. OP definitely has a version problem then — Nick, Aug 20 '17 at 21:21
@Nick you can simply delete the database as an alternative (i.e. uninstall the App or clear the App's data). Not necessarily an issue with version numbers, just that the it's very common for people to think that `onCreate` runs eveytime the App is started. — MikeT, Aug 20 '17 at 21:23
@Nick I tried uninstall the App and it worked successful. I couldn't think that because of i have been checked the code for 2 days. Thank you for your help. — Mehmet P., Aug 20 '17 at 21:29

score 0 · Accepted Answer · edited May 30 '18 at 19:28

0

Your code seems ok, could it be that you already had the DB when you added the words table? try removing the app and launch again, that should recreate all tables safely. If that's the issue, use the DB version field properly in the future

I would recommend using GreenDAO for sqlite with Android, it will makes things a lot easier

edited May 30 '18 at 19:28

Brad Larson

170,088
45
397
571

answered Aug 20 '17 at 21:14

Nick

585
4
11

score 0 · Answer 2 · answered Aug 20 '17 at 21:14

Probably, it's because version of the database in yours device/emulator doesn't contains the table "words". You change the schema of a database and doesn't increment the version. So, the onCreate() method doesn't called and the table "words" doesn't exists. To fix it just change private static final int DATABASE_VERSION = 1; to the private static final int DATABASE_VERSION = 2;

Android Sqlite "No Such Table" Error

2 Answers2