No such table - sqlite error

Question

Everytime I need to call my method RowID I'm getting an No Such Table error message. I would accept it, if the table didn't exist, but it does, so I don't get what's wrong with it.

Here is the error message:

05-06 13:51:23.429    4633-4633/pt.smartgeo.aees E/AndroidRuntime﹕ FATAL EXCEPTION: main
    Process: pt.smartgeo.aees, PID: 4633
    android.database.sqlite.SQLiteException: no such table: redes (code 1): , while compiling: SELECT  * FROM redes
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
            at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
            at pt.smartgeo.aees.NetworksDBAdapter.RowID(NetworksDBAdapter.java:114)
            at pt.smartgeo.aees.CreateNetwork$1.onClick(CreateNetwork.java:57)
            at android.view.View.performClick(View.java:4438)
            at android.view.View$PerformClick.run(View.java:18422)
            at android.os.Handler.handleCallback(Handler.java:733)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:136)
            at android.app.ActivityThread.main(ActivityThread.java:5017)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
            at dalvik.system.NativeStart.main(Native Method)

wish is in this method:

public class NetworksDBAdapter {
private SQLiteDatabase database;
private NetworksDB networks;
private String[] allColumns = {NetworksDB.ID, NetworksDB.NETWORKD_ID, NetworksDB.NAME, NetworksDB.MACRO};

public NetworksDBAdapter(Context context) {
    networks = new NetworksDB(context);

    open();
}

public void open() throws SQLException {
    database = networks.getWritableDatabase();
}

public Network createNetwork(String id, String name, int macro) {
    ContentValues values = new ContentValues();
    values.put(NetworksDB.NETWORKD_ID, id);
    values.put(NetworksDB.NAME, name);
    values.put(NetworksDB.MACRO, macro);
    long insertId = database.insert(NetworksDB.TABLE_NAME, null, values);
    // To show how to query

    Cursor cursor = database.query(NetworksDB.TABLE_NAME, allColumns, PointsDB.ID + " = " +
            insertId, null,null, null, null);
    cursor.moveToFirst();
    return cursorToPoint(cursor);
}

private Network cursorToPoint(Cursor cursor) {
    Network network = new
            Network(cursor.getString(1), cursor.getString(2), Integer.parseInt(cursor.getString(3)));
    return network;
}

public void deleteNetwork(int idPoint){
    database.delete(NetworksDB.TABLE_NAME, PointsDB.ID + " = " + idPoint,
            null);
}

public void updateNetwork(int id, String idNetwork, String name, int macro){
    String where = "_id=?";
    String[] whereArgs = new String[] {String.valueOf(id)};
    ContentValues values = new ContentValues();
    values.put(NetworksDB.NETWORKD_ID, idNetwork);
    values.put(NetworksDB.NAME, name);
    values.put(NetworksDB.MACRO, macro);
    database.update(PointsDB.TABLE_NAME, values, where, whereArgs);
}

public Cursor getNetworks(){
    Cursor cursor = database.rawQuery("SELECT _id, NetworkID, name, macro from redes", null);
    return cursor;
}

public Network getNetwork(int id){
    Cursor cursor = database.query(PointsDB.TABLE_NAME, allColumns, NetworksDB.ID + " = " +
            id, null,null, null, null);
    if (cursor.moveToFirst()) {
        return cursorToPoint(cursor);
    }
    return null;
}

public long count() {
    return DatabaseUtils.queryNumEntries(database,NetworksDB.TABLE_NAME);
}

public List<String> RowID(){
    List<String> List = new ArrayList<String>();
    String selectQuery = "SELECT  * FROM " + NetworksDB.TABLE_NAME;
    Cursor cursor = database.rawQuery(selectQuery, null);
    if (cursor.moveToFirst()) {
        do {
            List.add(cursor.getString(0));
        } while (cursor.moveToNext());
    }
    return List;
}

}

This is the line by the way:

String selectQuery = "SELECT  * FROM " + NetworksDB.TABLE_NAME;

And this is my NetworksDB class

public class NetworksDB extends SQLiteOpenHelper {

    public static final String ID = "_id";
    public static final String NETWORKD_ID = "NetworkID";
    public static final String NAME = "name";
    public static final String MACRO = "macro";
    private static final int DATABASE_VERSION = 3;

    private static final String DATABASE_NAME = "aees.db";
    public static final String TABLE_NAME = "redes";
    private static final String DATABASE_CREATE = "create table "
            + TABLE_NAME + "( " + ID
            + " integer primary key autoincrement, " + NETWORKD_ID
            + " text not null, " + NAME + " text not null, "
            + MACRO + " text not null);";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(PointsDB.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data"
        );
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
}

Anyone has any ideia what's wrong with it? :/

How do you obtain the `database` in `RowID()`? Have you already uninstalled the app / cleared its data so that the database is recreated? — laalto, May 06 '14 at 13:01
@laalto just cleared all the data and it's now working. so the problem is solved, post solution so I can accept. Thanks! — heisenberg, May 06 '14 at 13:08

Phantômaxx · Answer 1 · 2014-05-06T13:04:25.413

2

Your table is not being created, because of this line:

+ MACRO + " text not );";

It should be:

+ MACRO + " text not null);";

[EDIT]

This was true before you edited your question.

edited May 06 '14 at 13:04

answered May 06 '14 at 12:59

Phantômaxx

37,901
21
84
115

Doesn't explain the stacktrace though - for a syntax problem like this the exception would be different. Apparently the table creation code has not been run. – laalto May 06 '14 at 13:00
fixed it, it seems that was not the problem – heisenberg May 06 '14 at 13:01
Did you open your db first? – Phantômaxx May 06 '14 at 13:02
just post my DabataseAdapter class where I'm opening – heisenberg May 06 '14 at 13:05

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

Based on the comments clearing app data helped. Apparently your device already had a database file with the same name and version but without the table. Calling getWritableDatabase() just happily opened it but no lifecycle methods such as onCreate() or onUpgrade() were invoked.

Further reading: When is SQLiteOpenHelper onCreate() / onUpgrade() run?

edited May 23 '17 at 11:57

Community

1
1

answered May 06 '14 at 13:11

laalto

150,114
66
286
303

No such table - sqlite error

2 Answers2