I have issues when trying to access and set my foreign key in my database

user_10891278 0
Viewed 156

I get the following error when running my app using android studio.

Error

==============================================

java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

My database is SQLite. My app allows a user to register and login. Sports app.

From here they can add "Players" to their team. I have two tables set up in my db one for users and one for players with the "user_id" field from users table being used as the foreign key to link both dbs. Basically only the logged in users who add a certain player can see that players information and not all others that other users created.

Originally the app was saving the players to the correct table. However the foreign key was not getting filled. I then re wrote the code to correct this. However this is when i encountered this new problem.

Any help or advice at all is much appreciated.

1) DatabaseHelper.java

    public class DatabaseHelper extends SQLiteOpenHelper {

        private static final int DATABASE_VERSION = 1;
        private static final String DATABASE_NAME = "MyDB1.db";
        private static final String TABLE_USER = "User";
        private static final String COLUMN_USER_NAME = "User_name";
        private static final String COLUMN_USER_ID = "User_id";
        private static final String COLUMN_USER_EMAIL = "User_email";
        private static final String COLUMN_USER_PASSWORD = "User_password";

        private static final String TABLE_PLAYERS = "Player";
        private static final String COLUMN_PLAYER_NAME = "Player_name";
        private static final String COLUMN_PLAYER_AGE = "Player_age";
        private static final String COLUMN_PLAYER_WEIGHT = "Player_weight";
        private static final String COLUMN_PLAYER_HEIGHT = "Player_height";
        private static final String COLUMN_PLAYER_ID = "Player_id";
        private static final String FOREIGN_PLAYER_ID = COLUMN_USER_ID;
       // private static final Image COLUMN_PLAYER_IMAGE ;

        // Table 1 : Login/Register
        private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "(" + COLUMN_USER_NAME + " TEXT,"
                + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";


        // Table 2 : Adding players
        private String CREATE_PLAYER_TABLE = "CREATE TABLE " + TABLE_PLAYERS + "(" + COLUMN_PLAYER_NAME + " TEXT,"
                + COLUMN_PLAYER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_PLAYER_AGE + " INTEGER," + COLUMN_PLAYER_WEIGHT + " INTEGER," + COLUMN_PLAYER_HEIGHT + " INTEGER, " + FOREIGN_PLAYER_ID + " INTEGER," + "FOREIGN KEY(" + FOREIGN_PLAYER_ID + ") REFERENCES " + TABLE_USER + "(User_id) " + ")";


        // Drop tables

        private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER ;
        private String DROP_PLAYER_TABLE = "DROP TABLE IF EXISTS " + TABLE_PLAYERS ;


        public DatabaseHelper(Context context){
        //String name,  SQLiteDatabase.CursorFactory factory, int version) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onOpen(SQLiteDatabase db) {
            super.onOpen(db);
            if (!db.isReadOnly()) {
                // Enable foreign key constraints
                db.execSQL("PRAGMA foreign_keys=ON;");
            }
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

                db.execSQL(CREATE_USER_TABLE);
                db.execSQL(CREATE_PLAYER_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_USER_TABLE);
        db.execSQL(DROP_PLAYER_TABLE);
        onCreate(db);
        }



        // Adding a user to Users table
        public void addUser(User user){
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(COLUMN_USER_NAME, user.getName());
            values.put(COLUMN_USER_EMAIL, user.getEmail());
            values.put(COLUMN_USER_PASSWORD, user.getPassword());
            values.put(FOREIGN_PLAYER_ID, user.getForeignID());

            db.insert(TABLE_USER, null, values);
            db.close();
        }

        // Adding a player to players table

        public void addPlayer(Player player) {

            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();


            // Table 2 : Add players info
            values.put(COLUMN_PLAYER_NAME, player.getPlayerName());
            values.put(COLUMN_PLAYER_AGE, player.getPlayerAge());
            values.put(COLUMN_PLAYER_HEIGHT, player.getPlayerHeight());
            values.put(COLUMN_PLAYER_WEIGHT, player.getPlayerWeight());
            values.put(FOREIGN_PLAYER_ID, player.getForeignKey());

            db.insert(TABLE_PLAYERS, null, values);
            db.close();

        }

        // Checking the users email
        public boolean checkUser(String email){
            String[] columns = {
                    COLUMN_USER_ID

            };
            SQLiteDatabase db = this.getWritableDatabase();
            String selection = COLUMN_USER_EMAIL + " = ?";
            String[] selectionArgs = { email };

            Cursor cursor = db.query(TABLE_USER,
                    columns,
                    selection,
                    selectionArgs,
                    null,
                    null,
                    null);
            int cursorCount = cursor.getCount();
            cursor.close();
            db.close();

            if (cursorCount > 0){
                return true;
            }
            return false;
        }

        //
        public String getColumnUserName(String email){

            String user = "";
            String[] columns = {
                    COLUMN_USER_ID

            };
            SQLiteDatabase db = this.getWritableDatabase();
            String selection = COLUMN_USER_EMAIL + " = ?";
            String[] selectionArgs = { email };

            Cursor cursor = db.query(TABLE_USER,
                    columns,
                    selection,
                    selectionArgs,
                    null,
                    null,
                    null);
            int cursorCount = cursor.getCount();


            if (cursor.moveToFirst()) // data?{
              user =  cursor.getString(cursor.getColumnIndex("EMAIL"));

            cursor.close(); // that's important too, otherwise you're gonna leak cursors
            db.close();

            if (cursorCount > 0){
                return user;
            }
            return user;
        }

        // Checking the users email and password
        public boolean checkUser(String email, String password){
            String[] columns = {
                    COLUMN_USER_ID

            };
            SQLiteDatabase db = this.getWritableDatabase();
            String selection = COLUMN_USER_EMAIL + " = ?" + " AND " + COLUMN_USER_PASSWORD + " =?";
            String[] selectionArgs = { email, password };

            Cursor cursor = db.query(TABLE_USER,
                    columns,
                    selection,
                    selectionArgs,
                    null,
                    null,
                    null);
            int cursorCount = cursor.getCount();
            cursor.close();
            db.close();

            if (cursorCount > 0){
                return true;
            }
            return false;
        }
    }

2) Players.java

    public class Players extends AppCompatActivity {

        private Button insert;
        private static final int PICK_IMAGE=100;
        private String nameFromIntent = "";

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

            //Open add players section
            insert = (Button) findViewById(R.id.addPlayer);
            insert.setOnClickListener(new View.OnClickListener()

            {

                @Override
                public void onClick (View v)

                {
                    openaAddPlayersActivity();       
                }
            });
            nameFromIntent = getIntent().getStringExtra("EMAIL");        
        }

        private void openaAddPlayersActivity() {

            Intent intent = new Intent(this, addPlayers.class );
            String nameFromIntent = getIntent().getStringExtra("EMAIL");
            intent.putExtra(("EMAIL") ,nameFroenter code heremIntent);
            startActivity(intent);
        }        
    }

3)addPlayers.java

public class addPlayers extends AppCompatActivity implements View.OnClickListener{

    private Button insert;
    private static final int PICK_IMAGE=100;

    private final AppCompatActivity activity = addPlayers.this;    
    private EditText editTextPlayerName;
    private EditText editTextPlayerAge;
    private EditText editTextPlayerWeight;
    private EditText editTextPlayerHeight;  
    private TextInputEditText textInputEditTextEmail;
    private Inputvalidation inputvalidation;
    private DatabaseHelper databaseHelper;
    private Player player;
    private Button appCompatButtonRegister;
    private User user;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_add_players);
       // insert = (Button) findViewById(R.id.profilePicture);
       // insert.setOnClickListener(new View.OnClickListener()

        getSupportActionBar().hide();

        initViews();
        initListeners();
        initObjects();

    }
    private void initViews() {


        editTextPlayerName = (EditText) findViewById(R.id.playerName);
        editTextPlayerAge = (EditText) findViewById(R.id.playerAge);
        editTextPlayerHeight = (EditText) findViewById(R.id.playerHeight);
        editTextPlayerWeight = (EditText) findViewById(R.id.playerWeight);
        textInputEditTextEmail = (TextInputEditText) findViewById(R.id.enterEmail);
        appCompatButtonRegister = (Button) findViewById(R.id.savePlayer);

    }

    private void initListeners() {

        appCompatButtonRegister.setOnClickListener(this);   
    }

    private void initObjects() {   
        inputvalidation = new Inputvalidation(activity);
        databaseHelper = new DatabaseHelper(activity);
        player = new Player ();    
    }

             // Table 2 : Add players info

            @Override
            public void onClick(View v) {
               // Intent intent = new Intent(Intent.ACTION_PICK, Uri.parse("content://media/internal/images/media"));
                //startActivityForResult(intent, PICK_IMAGE);

                switch (v.getId()){
                    case R.id.savePlayer:
                        postDataToSQLite();
                        break;
            }
    }

    private void postDataToSQLite() {


        if(!databaseHelper.checkUser(editTextPlayerName.getText().toString().trim()))
        //textInputEditTextPassword.getText().toString().trim()))
        {
            Bundle email= getIntent().getExtras();
            String a = databaseHelper.getColumnUserName(email.getString("EMAIL"));

            player.setPlayerName(editTextPlayerName.getText().toString().trim());
            player.setPlayerAge(Integer.parseInt(editTextPlayerAge.getText().toString().trim()));
            player.setPlayerHeight(Integer.parseInt(editTextPlayerHeight.getText().toString().trim()));
            player.setPlayerWeight(Integer.parseInt(editTextPlayerWeight.getText().toString().trim()));
            player.setForeignKey(Integer.parseInt(a));

            //Integer.parseInt(databaseHelper.getColumnUserName(ContactsContract.CommonDataKinds.Email.getString("EMAIL"))));
            databaseHelper.addPlayer(player);

            Snackbar.make(findViewById(R.id.addPlayer), R.string.success_player_message,Snackbar.LENGTH_LONG).show();
           // emptyEditText();    
            Intent accountIntent = new Intent(activity, Players.class);
            accountIntent.putExtra("EMAIL", textInputEditTextEmail.getText().toString().trim());
            //emptyInputEditText();
            startActivity(accountIntent);    
        }    
        //else {
            // Snack Bar to show error message that record already exists
          //  Snackbar.make(findViewById(R.id.Register), getString(R.string.error_email_exists), Snackbar.LENGTH_LONG).show();
       // }    
    }

    /*protected void onActivityResult(int requestCode, int resultCode, Intent data){
        super.onActivityResult(requestCode, resultCode, data);
        if(resultCode==RESULT_OK && requestCode==PICK_IMAGE){
            Uri uri = data.getData();
            String x = getPath(uri);
            Toast.makeText(getApplicationContext(), x, Toast.LENGTH_LONG).show();
        }
    }

    private String getPath(Uri uri) {
        if(uri==null)return null;
        String [] projection = {MediaStore.Images.Media.DATA};
        Cursor cursor = managedQuery(uri, projection, null, null, null);
        if(cursor!=null){
            int column_index = cursor.getColumnIndexOrThrow(MediaStore.Images.Media.DATA);
            cursor.moveToFirst();
            return cursor.getString(column_index);
        }
        return uri.getPath();
    }
*/

}

4) Login.java

public class LoginActivity extends AppCompatActivity implements View.OnClickListener {


   private final AppCompatActivity activity = LoginActivity.this;

    private NestedScrollView nestedScrollView;

    private TextInputLayout textInputLayoutEmail;
    private TextInputLayout textInputLayoutPassword;

    private TextInputEditText textInputEditTextEmail;
    private TextInputEditText textInputEditTextPassword;

    private AppCompatButton appCompatButtonLogin;
    private AppCompatTextView textViewLinkRegister;

    private Inputvalidation inputvalidation;
    private DatabaseHelper databaseHelper;

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

        initViews();
        initListeners();
        initObjects();    
    }

    private void initViews() {

       textInputLayoutEmail = findViewById(R.id.textInputLayoutEmail);
       textInputLayoutPassword = findViewById(R.id.textInputLayoutPassword);

       textInputEditTextEmail = findViewById(R.id.enterEmail);
       textInputEditTextPassword = findViewById(R.id.enterPassword);

       appCompatButtonLogin = findViewById(R.id.Login);
       textViewLinkRegister = findViewById(R.id.textViewLinkRegister);

    }

    private void initListeners() {

        appCompatButtonLogin.setOnClickListener(this);
        textViewLinkRegister.setOnClickListener(this);
    }

    private void initObjects() {

        databaseHelper = new DatabaseHelper(activity);
        inputvalidation = new Inputvalidation(activity);
    }

    @Override
    public void onClick(View v){

        switch (v.getId()){

            case R.id.Login:
            verifyFromSQLite();
            break;

            case R.id.textViewLinkRegister:
                Intent intentRegister = new Intent(getApplicationContext(), Register.class);
                startActivity(intentRegister);
                break;   
        }
    }

    private void verifyFromSQLite() {

       if (!inputvalidation.isInputEditTextFilled(textInputEditTextEmail, textInputLayoutEmail, getString(R.string.error_message_email))){
                return;
            }

        if (!inputvalidation.isInputEditTextEmail(textInputEditTextEmail, textInputLayoutEmail, getString(R.string.error_message_email))){
            return;
        }

        if (!inputvalidation.isInputEditTextFilled(textInputEditTextPassword, textInputLayoutPassword, getString(R.string.error_message_password))){
            return;
        }

        if(databaseHelper.checkUser(textInputEditTextEmail.getText().toString().trim(), textInputEditTextPassword.getText().toString().trim()))
        {

            Intent accountIntent = new Intent(activity, LoggedIn.class);
            accountIntent.putExtra("EMAIL", textInputEditTextEmail.getText().toString().trim());
            emptyInputEditText();
            startActivity(accountIntent);    
        }else {
            Snackbar.make(findViewById(R.id.Login), R.string.error_valid_email_password,Snackbar.LENGTH_LONG).show();
                    //nestedScrollView, getString(R.string.error_valid_email_password), Snackbar.LENGTH_LONG).show();
            }
        }

    private void emptyInputEditText() {

        textInputEditTextEmail.setText(null);
        textInputEditTextPassword.setText(null);

    }
}
sqlite android sql javascript java
2 Answers

I believe that your issue may be that your foreign key is acting as it should and that a conflict is being raised because the the player passed to the addPlayer method doesn't appear to have the user set (unless dome within the construction of the Player object).

That is user may well be null or some other value that is not a value that is stored in the User_id column in the User table.

That is you only appear to pass the email from activity to activity and then only use the checkUser (only returns a boolean) and the getColumnsUserName (only returns a String that appears to be the email that was found according to the email used as the search argument).

The User_id however MUST be an integer value for it to be a valid User_id column in the User table.

  • That is as the User_id column being defined using INTEGER PRIMARY KEY (with or without AUTOINCREMENT) is an alias of the hidden rowid column and MUST be an INTEGER value (SQLite Autoincrement P.S. additionally note the summary in regards to using AUTOINCREMENT).

Determining the issue

I would suggest placing a breakpoint on the line db.insert(TABLE_PLAYERS, null, values); in the DatabaseHelper and running in Debug, when the breakpoint is reached to then inspect the value of foreignId in the player object.

You may wish to read Debug your app

However, if the cause is as suspected I also believe that the Stack-trace should also indicate the CONFLICT.

Personally rather than passing the email from activity to activity I'd suggest passing the User_id value as this will be UNIQUE and is also the most efficient means to locate the respective row as per :-

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. ROWIDs and the INTEGER PRIMARY KEY

Additonal

There are two issues that will each result in the index -1 issue.

The -1 issue itself is because the Cursor getColumnIndex method returns -1 if the column passed to the method does not exist in the Cursor (note a Cursor only has the columns that have been specified).

The first issue is with the line :-

user =  cursor.getString(cursor.getColumnIndex("EMAIL"));

There is no such column in the user table so this would always fail. Changing this to :- user = cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL));

will specify a column that is in the table.

The second issue is that although COLUMN_USER_EMAIL is a valid column in the table, that column is not included in the Cursor.

To include that column in the Cursor then change :-

        String[] columns = {
                COLUMN_USER_ID

        };

to :-

    String[] columns = {
            COLUMN_USER_ID,
            COLUMN_USER_EMAIL

    };

or to :-

    String[] columns = {"*" }; //<<<<<<<<<< * means ALL columns

or instead change :-

    Cursor cursor = db.query(TABLE_USER,
            columns,
            selection,
            selectionArgs,
            null,
            null,
            null);
    int cursorCount = cursor.getCount();

to 

    Cursor cursor = db.query(TABLE_USER,
            null, //<<<<<<<<<< null equates to * and thus ALL columns
            selection,
            selectionArgs,
            null,
            null,
            null);
user_47445140

So the app is now working correctly after a rebuild. Pretty strange behavior but i wont complain. Thanks for you help.

Eamon

user_108912780
Related