Error saving Sqlite Data (in such table)

Question

Databasehelper class:

public class DatabaseHelper extends SQLiteOpenHelper {
private static final String BANCO_DADOS = "MakeRequestApp";
private static int VERSAO = 1;

public DatabaseHelper(Context context){
    super(context, BANCO_DADOS, null, VERSAO);
}

@Override
public void onCreate(SQLiteDatabase db){
    db.execSQL("CREATE TABLE mesa (_id INTEGER PRIMARY KEY, descricao TEXT)");
    db.execSQL("CREATE TABLE pedido(_id INTEGER PRIMARY KEY, numero_mesa_id INTEGER, " +
            "FOREIGN KEY(numero_mesa_id) REFERENCES mesa(_id))");
    db.execSQL("CREATE TABLE itens_pedido (_id INTEGER PRIMARY KEY, produto_id INTEGER, quantidade INTEGER," +
            "FOREIGN KEY(produto_id) REFERENCES produto(_id))");
    db.execSQL("CREATE TABLE pedido(_id INTEGER PRIMARY KEY, numero_mesa_id INTEGER)" +
            "FOREIGN KEY(numero_mesa_id) REFERENCES mesa(_id)");
    db.execSQL("CREATE TABLE produto (_id INTEGER PRIMARY KEY, descricao TEXT, valor DOUBLE)");
}

Class of my Activity:

public class MesaActivity extends AppCompatActivity {
private DatabaseHelper helper;
private EditText descricao;
public static final String MESA_ID = "_id";
private String id;

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

    descricao = (EditText)findViewById(R.id.edtDescricao);
    helper = new DatabaseHelper(this);

    id = getIntent().getStringExtra(MESA_ID);

    if (id != null){
        prepararEdicao();
    }
}

private void prepararEdicao(){

}

@Override
protected void onDestroy(){
    helper.close();
    super.onDestroy();
}

public void salvarMesa(View view){
    SQLiteDatabase db = helper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put("descricao", descricao.getText().toString());

    long resultado;


    if (id == null){
        resultado = db.insert("mesa", null, values);
    } else {
        resultado = db.update("mesa", values, "_ID= ?", new String[]{id});
    }

    if (resultado != -1){
        Toast.makeText(this, "Dados salvos com sucesso!", Toast.LENGTH_SHORT).show();
    } else {
        Toast.makeText(this, "Erro ao salvar dados!", Toast.LENGTH_SHORT).show();
    }
}

While trying to save data gives the following error:

E/SQLiteLog: (1) no such table: mesa
E/SQLiteDatabase: Error inserting descricao=8
              android.database.sqlite.SQLiteException: no such table: mesa (code 1): , while compiling: INSERT INTO mesa(descricao) VALUES (?)
              #################################################################
              Error Code : 1 (SQLITE_ERROR)
              Caused By : SQL(query) error or missing database.
                (no such table: mesa (code 1): , while compiling: INSERT INTO mesa(descricao) VALUES (?))

Answer 1

The only thing wrong I see in your code (regarding the creation of the bank) is that you are creating the "request" table twice.

Due to this, when I tested your code I received the error:

android.database.sqlite.Sqliteexception: table pedido already exists (code 1): , while compiling: CREATE TABLE pedido(_id INTEGER PRIMARY KEY, numero_mesa_id INTEGER)FOREIGN KEY(numero_mesa_id) REFERENCES mesa(_id)

Otherwise your code works as expected.

If by chance the table "table" has been added after the database has been created, you need to increment the version number and implement the method onUpdate() so that changes can be made.

Alternatively, if existing data in the database can be discarded, uninstall the application and re-install.

During the development process the implementation of the method onUpdate() can only do the DROP and re-create them.

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String BANCO_DADOS = "MakeRequestApp";
    private static int VERSAO = 1;

    public DatabaseHelper(Context context) {
        super(context, BANCO_DADOS, null, VERSAO);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        criaTabelas(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        db.execSQL("Drop Table IF EXISTS produto");
        db.execSQL("Drop Table IF EXISTS itens_pedido");
        db.execSQL("Drop Table IF EXISTS pedido");
        db.execSQL("Drop Table IF EXISTS mesa");

        criaTabelas(db);
    }

    private void criaTabelas(SQLiteDatabase db){
        db.execSQL("CREATE TABLE mesa (_id INTEGER PRIMARY KEY, descricao TEXT)");
        db.execSQL("CREATE TABLE pedido(_id INTEGER PRIMARY KEY, numero_mesa_id INTEGER, " +
                "FOREIGN KEY(numero_mesa_id) REFERENCES mesa(_id))");
        db.execSQL("CREATE TABLE itens_pedido (_id INTEGER PRIMARY KEY, produto_id INTEGER, quantidade INTEGER," +
                "FOREIGN KEY(produto_id) REFERENCES produto(_id))");

        db.execSQL("CREATE TABLE produto (_id INTEGER PRIMARY KEY, descricao TEXT, valor DOUBLE)");
    }
}

Answer 2

From what you understand you try to create 2 tables with the name pedido, so see below the error occurrence below:

Sqliteexception: table pedido already exists (code 1): while compiling: CREATE TABLE request(_id INTEGER PRIMARY KEY, numero_mesa_id INTEGER) FOREIGN KEY(numero_mesa_id) REFERENCES table(_id)

I made the change in your method onCreate() removing the row that tries to create the second table with the name pedido and was thus below:

 @Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE mesa (_id INTEGER PRIMARY KEY, descricao TEXT)");
    db.execSQL("CREATE TABLE pedido(_id INTEGER PRIMARY KEY, numero_mesa_id INTEGER, " +
            " FOREIGN KEY(numero_mesa_id) REFERENCES mesa(_id))");
    db.execSQL("CREATE TABLE itens_pedido (_id INTEGER PRIMARY KEY, produto_id INTEGER, quantidade INTEGER," +
            " FOREIGN KEY(produto_id) REFERENCES produto(_id))");
    db.execSQL("CREATE TABLE produto (_id INTEGER PRIMARY KEY, descricao TEXT, valor DOUBLE)");
}

I even made a method to return all values entered in the table mesa in the DatabaseHelper and returns perfectly the column values descricao:

public List<String> getAll() {
    List<String> todos = new ArrayList<String>();
    String selectQuery = "SELECT * FROM mesa";

    Log.e("HELPER", selectQuery);

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

    if (c.moveToFirst()) {
        do {
            String item = c.getString((c.getColumnIndex("descricao")));
            // adding to todo list
            todos.add(item);
        } while (c.moveToNext());
    }
    return todos;
}