How can I know if sqlite is working on my app?

Question

I´m currently doing a tic tac toe with sqlite, to allow sqlite to save a username with the name and lastname of the person playing but if the username already exists the app will start the tic tac toe game, the thing is, that when I register a username, if I exit the game and try to enter with the same username it send me again to the register form (to write my name and lastname again). My project is being made with API 19 in android studio (KitKat 4.4) I dont know if that could be the reason for sqlite to avoid inserting data. Here is the code for sqlite:

DBHelper.java:

public class DbHelper extends SQLiteOpenHelper {

    private static final String DB_NAME="GatoBD.sqlite";
    private static final int DB_SCHEME_VERSION=1;
    public DbHelper(Context context) { 
        super(context, DB_NAME, null, DB_SCHEME_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DataBaseManager.CREATE_1_TABLE);
      // db.execSQL(DataBaseManager.CREATE_2_TABLE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

DataBaseManager.java:

public class DataBaseManager {
    public static String TABLE_1_NAME ="Users";
    public static String TABLE_2_NAME="Points";


    public static final String CN_ID = "_id";
    public static final String CN_NAME = "name";
    public static final String CN_1_LASTNAME = "last name";
    public static final String CN_1_USER = "user";
    public static final String CN_2_SCORE = "points";
    public static final String CN_2_TIME = "time";

    public static final String CREATE_1_TABLE = "create table "+TABLE_1_NAME+ "("//Tabla de usuarios
            + CN_ID + " integer primary key autoincrement,"
            +CN_NAME + " text,"
            +CN_1_LASTNAME + "text,"
            +CN_1_USER +" text);";

    public static final String CREATE_2_TABLE = "create table " + TABLE_2_NAME + " ("//Tabla de puntajes
            + CN_ID + " integer primary key autoincrement,"
           +CN_1_USER + " text not null,"
            +CN_2_SCORE +" integer);"
            +CN_2_TIME +" text);";

    private DbHelper helper;
    private SQLiteDatabase db1;

    public DataBaseManager(Context context) {
        helper=new DbHelper(context);
            db1=helper.getWritableDatabase(); 
        }



    public boolean ItsEmpty() 
    {
        String columns[]={CN_ID ,CN_NAME ,CN_1_LASTNAME ,CN_1_USER }; 
        Cursor cursor = db1.query(TABLE_1_NAME, columns, null, null, null, null,null);
        if(cursor.getCount()>0) 
        {

            return false;   // database not empty
        } else {

            return true;  // database empty
        }

    }






public ContentValues generateContentValues(String name, String lastname, String user) //
{
    ContentValues values=new ContentValues();
    valores.put("name",name);
    valores.put("last name",lastname);
    valores.put("user",user);
    return values;
}

public void InsertNewUser(String name, String lastname, String user)
{

    db1.insert("Users",null, generateContentValues(name,lastname,user) );

    db1.close();
}

public boolean SearchUser(String user)
{
    String countQuery = "SELECT  * FROM " + TABLE_1_NAME;

    Cursor c = db1.rawQuery(countQuery, null);
    if (!ItsEmpty())
    {

        c.moveToFirst();
        do {

            if (user.equals(c.getString(c.getColumnIndex("user"))))
            {
                c.close();
                return true;
            }



        }
        while (c.moveToNext());



    }
    c.close();
    return false;
}

Have a look here: https://stackoverflow.com/questions/1951805/how-to-access-an-existing-sqlite-database-in-android this will allow you to see the content of the DB and so check if data are saved of not. — Peter, Apr 14 '18 at 00:15

score 1 · Accepted Answer · answered Apr 14 '18 at 00:32

You have a couple of mistakes in the Users CREATE TABLE, that will make the inserts be ignored (along with other potential issues/crashes)

The first is that you are using a column name last name, it not being enclosed will result in the column name being called last and ensuing syntax errors when resultant SQL comes accross last name comma not between the two (as far as SQL is concerned) column names.

I'd suggest amending :-

public static final String CN_1_LASTNAME = "last name";

to :-

public static final String CN_1_LASTNAME = "lastname";

If you insist upon the space then one of the following :-

public static final String CN_1_LASTNAME = "`last name`";
public static final String CN_1_LASTNAME = "[last name]";
public static final String CN_1_LASTNAME = "'last name'";
public static final String CN_1_LASTNAME = "\"last name\"";

Ensuring that you always refer to the column name using CN_1_LASTNAME.

The second issue is again with the last name column in that there is no space between it an the column type TEXT. So if the above fix is applied the column will be name lastnameTEXT again causing issues.

The fix for this is to change :-

+CN_1_LASTNAME + "text,"

to be :-

+CN_1_LASTNAME + " text," //<<<< ADDED SPACE before text.

Once you have amneded the code you will need to delete the database (assuming you have no important data in the database) (deleting database is the easiest way to rebuild it).

You can do this by either :- - Deleting the App's data or - Uninstalling the App. And then rerunning the App.

With regards to knowing what's in the database you may find the following useful :- Are there any methods that assist with resolving common SQLite issues?

Thank you so much, you are great for taking the time to answer, it worked perfectly :D — Sebas Silva, Apr 14 '18 at 04:49

How can I know if sqlite is working on my app?

1 Answers1