Error saving SQLite data (no such table)

2

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)");
}

My Activity Class:

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();
    }
}

When trying to save data it 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 (?))
    
asked by anonymous 29.01.2017 / 14:17

2 answers

6

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

Because of this, when I tested your code I got the error:

  

android.database.sqlite.SQLiteException: table already exists (code 1):, while compiling: CREATE TABLE request (_id INTEGER PRIMARY KEY, table_id INTEGER) FOREIGN KEY (table_id_name) REFERENCES table (_id)

Otherwise your code works as expected.

If the "table" table has been added after the database has been created, you must increment the version number and implement the% cos_de% method so that the changes are made.

Alternatively, if the data in the database can be discarded uninstall the application and reinstall.

During the development process the implementation of the onUpdate() method can just do the DROP of the table 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)");
    }
}
    
04.02.2017 / 17:30
2

So you realize you try to create 2 tables with the name pedido , so look below for the error below:

  

SQLiteException: table request already exists (code 1):, while   compiling: CREATE TABLE request (_id INTEGER PRIMARY KEY, table_id   INTEGER) FOREIGN KEY (number_table_id) REFERENCES table (_id)

I made the change in your method onCreate() by removing the line that tries to create the second table with the name pedido and it looks like this 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 made up a method to return all values entered in table mesa in DatabaseHelper and perfectly returns values of column 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;
}
    
04.02.2017 / 17:24