error in code to view sqlLite Database

Question

I am trying to view the contents of a database in a TextView by using Cursor but when i try running the app crashes...I cannot identify it the error

package aditya.testapp;

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;

public class SqlData {

public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "persons_name";
public static final String KEY_NUMBER = "persons_number";

private static final String DATABASE_NAME = "DataDb";
private static final String DATABASE_TABLE = "peopleTable";
private static final int DATABASE_VERSION =  2 ;

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

private static class DbHelper extends SQLiteOpenHelper{

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }


    // onCreate method only used once when a database is created....other times onUpgrade methode is used
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
        KEY_ROWID + "INTEGER PRIMARY KEY, " +
        KEY_NAME + "TEXT NOT NULL, " +
        KEY_NUMBER + "TEXT NOT NULL);"
                );
    }

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

// initializing context ourContext
public SqlData (Context c){
    ourContext = c;
    }
//XX
public SqlData open() throws SQLException{
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
}
public void close(){
    ourHelper.close(); 
}

public long createEntry(String name, String number) {
    // TODO Auto-generated method stub
    // in table ContentValues is used instead of bundle
    ContentValues cv = new ContentValues();
    cv.put(KEY_NAME, name);
    cv.put(KEY_NUMBER, number);
    return ourDatabase.insert(DATABASE_TABLE, null, cv);
}

public String getData() {

    // TODO Auto-generated method stub
    String[] columns = new String[]{KEY_ROWID , KEY_NAME , KEY_NUMBER};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
    String result = "";

    int iRow = c.getColumnIndex(KEY_ROWID);
    int iName = c.getColumnIndex(KEY_NAME);
    int iNumber = c.getColumnIndex(KEY_NUMBER);

    for( c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
        result = result + c.getString(iRow) + "   " + c.getString(iName) + "    " + c.getString(iNumber) + "/n";
    } 
    c.close();
    return result;


}
}

and this code is for the class which is used to view the database

import android.app.Activity;
import android.os.Bundle;
import android.widget.TextView;

public class SqlView extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sqlview);
    TextView lblSqlInfo = (TextView) findViewById(R.id.lblSqlInfo);
    SqlData info = new SqlData(this);
    info.open();
    String data = info.getData();    // getData is a method in SqlData class
    info.close();
    lblSqlInfo.setText(data);
}

}

Please help to find out the error.... The log cat shows this error

10-22 12:51:56.483: E/AndroidRuntime(1815): FATAL EXCEPTION: main
10-22 12:51:56.483: E/AndroidRuntime(1815): java.lang.RuntimeException: Unable to start activity ComponentInfo{aditya.testapp/aditya.testapp.SqlView}: android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id, persons_name, persons_number FROM peopleTable
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.os.Handler.dispatchMessage(Handler.java:99)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.os.Looper.loop(Looper.java:137)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.app.ActivityThread.main(ActivityThread.java:5041)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at java.lang.reflect.Method.invokeNative(Native Method)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at java.lang.reflect.Method.invoke(Method.java:511)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at dalvik.system.NativeStart.main(Native Method)
10-22 12:51:56.483: E/AndroidRuntime(1815): Caused by: android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id, persons_name, persons_number FROM peopleTable
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at aditya.testapp.SqlData.getData(SqlData.java:79)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at aditya.testapp.SqlView.onCreate(SqlView.java:16)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.app.Activity.performCreate(Activity.java:5104)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
10-22 12:51:56.483: E/AndroidRuntime(1815):     at   android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
    10-22 12:51:56.483: E/AndroidRuntime(1815):     ... 11 more

please post the log cat in order to know what error u are getting — Rat-a-tat-a-tat Ratatouille, Oct 22 '13 at 11:38
1. See the logcat for exception stacktrace. 2. See the line number in the exception and locate the corresponding code. 3. Try to figure out what could cause that exception on that line of code. 4. Edit the question to contain additional information from previous steps. — laalto, Oct 22 '13 at 11:39
@user2552774 http://stackoverflow.com/questions/11608718/how-to-copy-logcat-output-to-clipboard — Piovezan, Oct 22 '13 at 12:06
the log cat shows this: 10-22 11:58:36.863: E/Trace(817): error opening trace file: No such file or directory (2) — AD1TYA, Oct 22 '13 at 12:11
That's not an exception stacktrace. Look for something like `E/AndroidRuntime: FATAL EXCEPTION` and then the stacktrace is on the following lines. — laalto, Oct 22 '13 at 12:42
it shows this 10-22 12:10:57.373: E/AndroidRuntime(1066): FATAL EXCEPTION: main — AD1TYA, Oct 22 '13 at 12:52

score 0 · Answer 1 · answered Oct 22 '13 at 11:46

0

You are not closing the cursor after using it. Add the following line right before return result;:

c.close(); // add this line to your code
return result;

answered Oct 22 '13 at 11:46

Piovezan

3,215
1
28
45

This is helpful but doesn't explain the crash. – laalto Oct 22 '13 at 11:50
The log cat shows this: 10-22 11:58:36.863: E/Trace(817): error opening trace file: No such file or directory (2) what does this mean? – AD1TYA Oct 22 '13 at 12:19

score 0 · Answer 2 · answered Oct 22 '13 at 12:12

Initialize an ArrayList<String> result = new ArrayList<String>(); instead of String result

Change your loop to:

while(c.moveToNext())
{
result.add(c.getString(iRow) + "   " + c.getString(iName) + "    " + c.getString(iNumber));
Log.i("data", c.getString(iRow) + "   " + c.getString(iName) + "    " + c.getString(iNumber));
}

c.close();

return result;

Change your return type of getData() to ArrayList.

And at last you can iterate over ArrayList and print in console log using Log class. Check if get the log...

laalto · Answer 3 · 2013-10-22T13:55:09.207

android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id, persons_name, persons_number FROM peopleTable

Your table doesn't have a column called _id. Add it there and recreate the database. Or remove it from your selection columns.

Seems like there are SQL syntax problems in onCreate():

db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
KEY_ROWID + "INTEGER PRIMARY KEY, " +
KEY_NAME + "TEXT NOT NULL, " +
KEY_NUMBER + "TEXT NOT NULL);"

There should be space between column name and type, e.g.

db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
KEY_ROWID + " INTEGER PRIMARY KEY, " +
KEY_NAME + " TEXT NOT NULL, " +
KEY_NUMBER + " TEXT NOT NULL);"

This is a tricky issue since e.g. _idINTEGER PRIMARY KEY is a valid column specification, it just doesn't specify a column named _id.

When changing the schema like this, remember to bump up the database version number.

error in code to view sqlLite Database

3 Answers3