I am getting an error saying"no column exist" when i execute readNumber() method, the "CREATE" query seems to be correct

Question

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "NumberDB";
    private static final int DATABASE_VERSION = 1;
    private static final String CREATE = "create table "+DBcontract.TABLE_NAME+ "(id integer primary key,"+DBcontract.INCOMING_NUMBER+" text);";
    private static final String DROP_TABLE = "drop table if exists "+DBcontract.TABLE_NAME;
    public DbHelper(Context context){
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL(DROP_TABLE);
        onCreate(sqLiteDatabase);
    }
    public void saveNumber(String number,SQLiteDatabase sqLiteDatabase){
        ContentValues contentValues = new ContentValues();
        contentValues.put(DBcontract.INCOMING_NUMBER,number);
        sqLiteDatabase.insert(DBcontract.TABLE_NAME,null,contentValues);
    }
    public Cursor readNumber(SQLiteDatabase database){
        String[] projections = {"id",DBcontract.INCOMING_NUMBER};
        // Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM IncomingNumber",null);
        // Log.i("Incoming number", String.valueOf(c));
        // Log.i("database name",sqLiteDatabase.toString());
        return( database.query(DBcontract.TABLE_NAME,  projections,null,null,null,null,null));
    }
}

In the above code, I've created a String value and a constructor to create a database and again overridden the method to create a table.

This gives me the error in readNumber method saying

"no column found: incoming_number.

DBCONTRACT.TABLE_NAME = "incomingInfo"

DBCONTRACT.INCOMING_NUMBER = "incoming_number".

Answer 1

I believe that your issue is that you have changed the table structure or have corrected errors and thus changed the table create SQL after attempting to run the App which has actually got to the stage on invoking the onCreate method.

I can be pretty sure that the above is the case as I have copied your DbHelper class, created a DBcontract class, created create a class named CommonSQLiteUtilities copying the code from Are there any methods that assist with resolving common SQLite issues? .

I then invoked it from an Activity as per :-

public class MainActivity extends AppCompatActivity {

    SO49702870DbHelper mDBHlper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlper = new SO49702870DbHelper(this);

        //Test
        //    Display Database Information
        CommonSQLiteUtilities.logDatabaseInfo(mDBHlper.getWritableDatabase());
        // Get A cursor
        Cursor csr = mDBHlper.readNumber(mDBHlper.getWritableDatabase());
        CommonSQLiteUtilities.logCursorColumns(csr);
    }
}

No amendments to you code were needed and the output was completely as expected i.e. as per :-

04-07 05:56:15.136 1301-1301/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/soanswers.soanswers/databases/NumberDB
    Database Version = 1
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
    Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = incomingInfo Created Using = CREATE TABLE incomingInfo(id integer primary key,incoming_number text)
    Table = incomingInfo ColumnName = id ColumnType = integer Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = incomingInfo ColumnName = incoming_number ColumnType = text Default Value = null PRIMARY KEY SEQUENCE = 0
    logCursorColumns invoked. Cursor has the following 2 columns.
04-07 05:56:15.140 1301-1301/? D/SQLITE_CSU: Column Name 1 is id
    Column Name 2 is incoming_number

i.e. There are two tables :-

1. android_metadata
   • which gets created by the Database Helper and stores the locale.
2. incomingInfo
   • i.e. your table
   • it was created using CREATE TABLE incomingInfo(id integer primary key,incoming_number text)
   • it has two defined columns id and incoming_number

The underlying Cause

A very common misunderstanding is that the onCreatemethod runs every time the Database Helper (a subclass of SQLiteOpenHelper) is instantiated.

This is not the case the onCreate method is invoked once only if the database doesn't exist. Thus any changes made to what is done in the onCreate method are not actioned if the database exists.

As the onCreate method is called after the database has been created, any failures e.g. an SQL error creating a table does not delete the database so corrections to the SQL do not get applied.

The Fix

As long as there data (if any) does not need to be preserved then there are three ways in which onCreate can be invoked :-

1. Delete the App's data and rerun the App.
2. Uninstall the App and rerun the App.
3. If the onUpgrade method drops the table(s) and invokes the onCreate method (yours does) then the Database Version number, as passed to the SQLiteOpenHelper's constructor, can be increased and the App rerun.
   • Your onUpgrade method meets the criteria so you could change private static final int DATABASE_VERSION = 1; to be private static final int DATABASE_VERSION = 2;