0

I want to set up and insert data into a SQL database in Android by using SQLiteOpenHelper. I have the following SQLHelperClass (by followin the youtube tutorial:https://www.youtube.com/watch?v=T0ClYrJukPA)

package com.example.td.barapp;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class DataBaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE = "DrinksDB.db";
    public static final String TABLE_DRINKS = "table_drinks";
    public static final String TABLE_RATINGS = "table_ratings";
    public static final String TABLE_INGREDIENTS = "table_ingredients";
    public static final String TABLE_INGREDIENTS_GERMAN = "table_ingredients_german";

    /*
    Variables for the table "Drinks"
     */
    public static final String DRINK_NAME = "Name";
    public static final String DRINK_NAME_GERMAN = "Name_German";
    public static final String DRINK_TYPE = "Drink_Type";

      /*
     Variables for the table "RATINGS"
     */

    public static final String NUMBER_1STARS = "1_Star";
    public static final String NUMBER_2STARS = "2_Star";
    public static final String NUMBER_3STARS = "3_Star";
    public static final String NUMBER_4STARS = "4_Star";
    public static final String NUMBER_5STARS = "5_Star";

       /*
     Variables for the table "Ingredients"
     */

    public static final String NUMBER_OFINGREDIENTS = "Number Ingredients";
    public static final String INGREDIENT_1 = "Ingredient_1";
    public static final String INGREDIENT_2 = "Ingredient_2";
    public static final String INGREDIENT_3 = "Ingredient_3";
    public static final String INGREDIENT_4 = "Ingredient_4";
    public static final String INGREDIENT_5 = "Ingredient_5";
    public static final String INGREDIENT_6 = "Ingredient_6";
    public static final String INGREDIENT_7 = "Ingredient_7";
    public static final String INGREDIENT_8 = "Ingredient_8";

    public static final String INGREDIENT_1_GERMAN = "Ingredient_1_German";
    public static final String INGREDIENT_2_GERMAN = "Ingredient_2_German";
    public static final String INGREDIENT_3_GERMAN = "Ingredient_3_German";
    public static final String INGREDIENT_4_GERMAN = "Ingredient_4_German";
    public static final String INGREDIENT_5_GERMAN = "Ingredient_5_German";
    public static final String INGREDIENT_6_GERMAN = "Ingredient_6_German";
    public static final String INGREDIENT_7_GERMAN = "Ingredient_7_German";
    public static final String INGREDIENT_8_GERMAN = "Ingredient_8_German";



    public DataBaseHelper(@Nullable Context context) {
        super(context, DATABASE, null, 1);

    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("create table " + TABLE_DRINKS + "(Name TEXT PRIMARY KEY, Name_German TEXT, Drink_Type TEXT   ) ");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_DRINKS);
        onCreate(sqLiteDatabase);
    }

    public boolean insertDataDrinksDB (String name, String nameGerman, String type) {
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(DRINK_NAME, name);
        contentValues.put(DRINK_NAME_GERMAN, nameGerman);
        contentValues.put(DRINK_TYPE, type);
        long inserted = sqLiteDatabase.insert(TABLE_DRINKS, null, contentValues);

        if (inserted==-1) {
            return false;
        }
        else
            return true;
    }


}

I implemented the methdod "public boolean insertDataDrinksDB (see at the end of the code). Then in the main activity I insert some data by using the following code:

package com.example.td.barapp;

import android.content.Intent;
import androidx.appcompat.app.AppCompatActivity;
import androidx.appcompat.widget.Toolbar;

import android.os.Bundle;

import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;

import com.example.td.barapp.databinding.ActivityMain2Binding;
import com.example.td.barapp.databinding.ActivityMainBinding;


public class MainActivity extends AppCompatActivity  {

   DataBaseHelper drinksDB;
    private ActivityMain2Binding binding;


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

        drinksDB = new DataBaseHelper (this);
        boolean insert = drinksDB.insertDataDrinksDB("Apple Juice", "Apfelsaft", "Softdrink");

        if (insert==true) {
            Toast.makeText(this, "Data inserted", Toast.LENGTH_LONG).show();
        }
        if (insert==false) {
            Toast.makeText(this, "Data not inserted", Toast.LENGTH_LONG).show();
        }



    }
    


}

When I start the app I always get the Toast message that the Data was not inserted and I do not see my mistake. Further, the database does not seem to get bigger when I insert the data so the data is seemingly not inserted. Would you mind telling me what mistake I am making? I'd appreciate every comment.

VanessaF
  • 515
  • 11
  • 36
  • First uninstall the app from the device so the db is deleted. Then change the create statement to: `sqLiteDatabase.execSQL("create table " + TABLE_DRINKS + "(" + DRINK_NAME + " TEXT PRIMARY KEY, " + DRINK_NAME_GERMAN + " TEXT, " + DRINK_TYPE + " TEXT)");` and use everywhere in your code the variables that you have defined for the table/column names. Next rerun to recreate the db and the table. Better try in a clean device. – forpas Sep 18 '20 at 15:31
  • Thanks forpas for your answer and effort. I have some questions to your comment. 1) What is the difference between your code and my original code? 2) What do you mean by "use everywhere..the variables that you have defined for the table? Why shall I do that and how can I do that? 3)How shall I return to create the db? I am using an Emulator not a real device – VanessaF Sep 18 '20 at 20:58
  • When you define a variable like: `public static final String DRINK_TYPE = "Drink_Type";` you do it because there are good reasons: to use `DRINK_TYPE` throughout your code and not `"Drink_Type"`, which (it happens) may be misspelled, or if later you want to change the value of the variable, say `"type"` instead of `"Drink_Type"` you can do only 1 change in its definition and you will not have to make find/replace in your code to replace all occurrences of `"Drink_Type"` to `"type"`. My code has the same result as yours, but it's safer and more flexible. – forpas Sep 18 '20 at 21:08
  • You can uninstall the app from the emulator just like you would in a physical device. When you uninstall the app the db is deleted. Then when you rerun the app the db and its tables are recreated by your code. – forpas Sep 18 '20 at 21:09
  • Thanks forpas for your comment and effort. How can I uninstall the app from the emulator. Basically the app gets installed everytime I start the emulator. This is a emulator from Android Studio and when I run the app the emulator starts and the app is being installed. Further, how can I check the current database and the data that is currently being in it. – VanessaF Sep 18 '20 at 21:13
  • Check this: https://stackoverflow.com/questions/2994407/how-do-i-delete-sample-app-from-an-android-emulator and this: https://aboutreact.com/see-saved-data-of-the-sqlite-database-in-device/ – forpas Sep 18 '20 at 21:19
  • Thanks forpas. Now I can see the database. So the database is persistent right? It is not deleted after you end the emulator? This was the problem. Would you advice me to delete the database after every app use in the Emulator? – VanessaF Sep 18 '20 at 21:43
  • The database is stored inside the emulator and it is not deleted when you close the emulator. It is deleted only when you uninstall the app. – forpas Sep 18 '20 at 21:45

1 Answers1

1

edit due to comments and real answer:

I've copied your code and it is working perfectly. Well, at first time, because every next insertDataDrinksDB call (next app/Activity start) tries to insert query with already existing PRIMARY_KEY value, this should be update, not insert

below previous wrong answer, which is commented under this answer:

can you confirm that this table is created properly?

sqLiteDatabase.execSQL("create table " + TABLE_DRINKS + "(Name TEXT PRIMARY KEY, Name_German TEXT, Drink_Type TEXT   ) ");

after TABLE_DRINKS there is no space, so framework may think that name of table is TABLE_DRINKS +"(Name" and following "TEXT" is column name, which is not proper... (restricted name for column type)

also check out Android-Debug-Database lib, may be useful for debug

snachmsm
  • 17,866
  • 3
  • 32
  • 74
  • There is no need for a space after the table's name. – forpas Sep 18 '20 at 14:41
  • Also TEXT is a perfectly valid (although not recommended) name for a column. – forpas Sep 18 '20 at 14:47
  • Thanks for your answer snachmsm. I inserted a spae after the table's name but this did not change anything and the problem still persists – VanessaF Sep 18 '20 at 14:49
  • Thanks forpas for your comment. Basically TEXT is not the name of the column but rather the type (INTEGER; STRING; TEXT) etc. – VanessaF Sep 18 '20 at 14:50
  • @VanessaF I know TEXT is not the name of the column. My comment was addressed to snachmsm's answer. – forpas Sep 18 '20 at 14:52
  • Thanks forpas for your comment. Do you have any idea what the problem might be. Snachmsm might be right that the database itself is not created correctly. When I put it into the SQLite Manager of Firefox I do not see anyhting. However, this might also have other reasons (as I do not know how the Firefox SQLite Manager really works and what it does). – VanessaF Sep 18 '20 at 14:57
  • Thanks snachmasm for your answer and effort. Unfortunately I do not understand your edit at all. What you do mean by "call tries to insert query with already existing PRIMARY_KEY, this should be update, not insert". Why do I need an update statement. I just want to add the data into the database first. And by the in my application the code does not work at all. I always get the error message on Toast that no data has been added (even when I start the app for the first time). – VanessaF Sep 18 '20 at 15:00
  • The database is empty at the beginning when I start the app as far as I understood. Because I have not inserted anything in the code. Further I use the name of the drink as the key meaning that I should not use duplicated of this attribute. But I do not do this. I only insert one datarow. Unfortunately I am quite confused at the moment. – VanessaF Sep 18 '20 at 15:03
  • remove your app and install/run it again, your code will work at first run and data will be inserted. you can confirm that with lib linked in answer. any next app run is trying to insert data with already existing `PRIMARY_KEY` value, which is impossible and returns `-1` as any new data isn't inserted – snachmsm Sep 18 '20 at 15:06
  • Thanks snachmsm for your answer and effort. I use the Emulator and the app does not run on a real device. How can I remove the app? – VanessaF Sep 18 '20 at 20:54
  • Shall I rather use update instead of insert when I want to add some data into the database? Further, I can still not confirm that the table is created properly as you said in the beginnig. How can I check which data is in the database currently? I tried to open the SQLite file with HeidiSQL with it did not work and I do not know hot to open the database file such that I can check, wheter the table has been created correctly? – VanessaF Sep 18 '20 at 21:05
  • once again: use library linked in question, you have to add single line to your `build.gradle` file. about inserting: before calling `sqliteDatabase.insert` check if row with same `PRIMARY_KEY` already exists with [sqliteDatabase.query](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#rawQuery(java.lang.String,%20java.lang.String[])) - if returned cursor have a row (count>0) the use `update` instead `insert` method. and rename your method to `insertOrUpdate` just for knowing in the future that this method is reliable and will always put data into db – snachmsm Sep 20 '20 at 08:00