How to Update Sqlite database method

Question

Trying to update a column in a table which happens to be a foreign key.

I know its entering the database as I login with these details but it just doesn't seem to work when updating.

Here is my update method with a method to turn the id to an email:

    private int getIdFromName(String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        ArrayList<Integer> refs = new ArrayList<>();
        Cursor cursor;

        cursor = db.rawQuery("select " + REFEREE_EMAIL_COL + " from " +
                REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

        while (cursor.moveToNext()){
            refs.add(cursor.getInt(0));
        }

        return refs.get(0);
    }

     public boolean updateRef(String email)
   {
       SQLiteDatabase db = this.getWritableDatabase();
       ContentValues values = new ContentValues();

       int ref_id = getIdFromName(email);

       values.put(REFEREE_ID_COL, ref_id);

       db.update(MATCH_TABLE, values, "REFEREEID = ?", new String[]{email});
       return true;
   }


private Button btnSave,btnDelete;
private EditText homeTeamEdit, awayTeamEdit, typeOfMatchEdit, redCardsEdit, bookingsEdit, groundEdit, refEdit, dateEdit, timeEdit,
        awayScoreEdit, homeScoreEdit;
DBHandler mDatabaseHelper;

private String homeTeam, awayTeam, homeScore, awayScore;
private String typeOfMatch;
private String ref;
private String redCards, bookings;
private String date, time;
private String ground;
private int selectedID;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_update_match);
        btnSave = (Button) findViewById(R.id.UpdateMatchButton);
        homeTeamEdit = (EditText) findViewById(R.id.HomeTeamUpdate);
        homeScoreEdit = (EditText) findViewById(R.id.updateHomeScore);
        awayTeamEdit = (EditText) findViewById(R.id.AwayTeamUpdate);
        awayScoreEdit = (EditText) findViewById(R.id.updateAwayScore);
        typeOfMatchEdit = (EditText) findViewById(R.id.updateTypeOfMatch);
        refEdit = (EditText) findViewById(R.id.updateRef);
        groundEdit = (EditText) findViewById(R.id.updateGround);
        refEdit = (EditText) findViewById(R.id.updateRef);

        final String referee = refEdit.getText().toString();

        mDatabaseHelper = new DBHandler(this);


        //get the intent extra from the ListDataActivity
        Intent receivedIntent = getIntent();

        //now get the itemID we passed as an extra
        selectedID = receivedIntent.getIntExtra("MatchId", -1); //NOTE: -1 is just the default value

        //now get the name we passed as an extra
        homeTeam = receivedIntent.getStringExtra("homeTeam");

        homeScore = receivedIntent.getStringExtra("homeScore");

        awayTeam = receivedIntent.getStringExtra("awayTeam");

        awayScore = receivedIntent.getStringExtra("awayScore");

        ground = receivedIntent.getStringExtra("ground");

        typeOfMatch = receivedIntent.getStringExtra("typeOfMatch");


        //set the text to show the current selected name
        homeTeamEdit.setText(homeTeam);
        awayTeamEdit.setText(awayTeam);
        typeOfMatchEdit.setText(typeOfMatch);
        groundEdit.setText(ground);
        homeScoreEdit.setText(homeScore);
        awayScoreEdit.setText(awayScore);

        btnSave.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String item = refEdit.getText().toString();
                if(mDatabaseHelper.checkIfRefExists(referee)) {
                    if (!item.equals("")) {
//                    mDatabaseHelper.updateName(referee, selectedID);
                        mDatabaseHelper.updateRef(referee);
                        toastMessage("Ref Updated");
                    } else {
                        toastMessage("You must enter a name");
                    }
                }
                else
                {
                    toastMessage("No ref with that email");
                }
            }
        });
    }

    /**
     * customizable toast
     * @param message
     */
    private void toastMessage(String message){
        Toast.makeText(this,message, Toast.LENGTH_SHORT).show();
    }
}

When I try to update the match with ref, it always hits the toast message of no refs with that email.

Is there something I'm doing wrong with the update method or am I totally off?

UPDATE Here is my table creation with column names:

    private static final String TAG = "DBHandler";

public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "RefereeDB";
public static final String REF_TABLE = "referee_table";
public static final String MATCH_TABLE = "match_table";
public static final String ADMIN_TABLE = "admin_table";

//REF Tables
public static final String REFEREE_ID_COL = "REFEREEID";
public static final String REFEREE_NAME_COL = "NAME";
public static final String REFEREE_AGE_COL = "AGE";
public static final String REFEREE_PHONENUM_COL = "PHONENUMBER";
public static final String REFEREE_STATUS_COL = "REFEREESTATUS";
public static final String REFEREE_CLUB_COL = "REFCLUB";
public static final String REFEREE_EMAIL_COL = "REFEMAIL";
public static final String REFEREE_PASSWORD_COL = "REFEREEPASSWORD";

//Match Tables
public static final String MATCH_ID_COL = "MATCHID";
public static final String MATCH_HOME_TEAM_COL = "HOMETEAM";
public static final String MATCH_AWAY_TEAM_COL = "AWAYTEAM";
public static final String MATCH_TIME_COL = "MATCHTIME";
public static final String MATCH_DATE_COL = "MATCHDATE";
public static final String MATCH_HOMESCORE_COL = "MATCHHOMESCORE";
public static final String MATCH_AWAYSCORE_COL = "MATCHAWAYSCORE";
public static final String MATCH_TYPEOFMATCH_COL = "TYPEOFMATCH";
public static final String MATCH_GROUND_NAME_COL = "GROUNDNAME";
public static final String MATCH_GROUND_LAT_COL = "GROUNDLAT";
public static final String MATCH_GROUND_LONGITUDE_COL = "GROUNDLONGITUDE";

//Admin Tables
public static final String ADMIN_ID_COL = "ADMINID";
public static final String ADMIN_EMAIL = "ADMINEMAIL";
public static final String ADMIN_PASSWORD = "ADMINPASSWORD";


//Create Tables
public static final String CREATE_TABLE_REF = "CREATE TABLE "
        + REF_TABLE + "(" + REFEREE_ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + REFEREE_NAME_COL + " TEXT," +
        REFEREE_AGE_COL + " INTEGER," + REFEREE_PHONENUM_COL + " TEXT," + REFEREE_STATUS_COL +
        " TEXT," + REFEREE_CLUB_COL + " TEXT," + REFEREE_EMAIL_COL + " TEXT," +
        REFEREE_PASSWORD_COL + " TEXT" + ")";


public static final String CREATE_TABLE_MATCH = "CREATE TABLE "
        + MATCH_TABLE + "(" + MATCH_ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + MATCH_HOME_TEAM_COL +
        " TEXT," + MATCH_AWAY_TEAM_COL + " TEXT," + MATCH_TIME_COL + " TEXT," + MATCH_DATE_COL +
        " DATETIME," + MATCH_HOMESCORE_COL + " TEXT," + MATCH_AWAYSCORE_COL + " TEXT," + MATCH_TYPEOFMATCH_COL +
        " TEXT," + MATCH_GROUND_NAME_COL + " TEXT," + MATCH_GROUND_LONGITUDE_COL + " TEXT," + MATCH_GROUND_LAT_COL + " TEXT, " + REFEREE_ID_COL + " INTEGER," + " FOREIGN KEY (REFEREEID) REFERENCES " + REF_TABLE + " (REFEREEID)" + ")";

public static final String CREATE_TABLE_ADMIN = "CREATE TABLE "
        + ADMIN_TABLE + "(" + ADMIN_ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + ADMIN_EMAIL +
        " TEXT," + ADMIN_PASSWORD + " TEXT" + ")";

Possible duplicate of [sqlite db update](https://stackoverflow.com/questions/10978136/sqlite-db-update) — InsaneCat, Dec 13 '17 at 07:17

score 1 · Answer 1 · answered Dec 12 '17 at 22:06

I thing you just made a typo. You are selecting REFEREE_EMAIL_COL:

   cursor = db.rawQuery("select " + REFEREE_EMAIL_COL + " from " +
            REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

Instead you must provide some variable like REFEREE_ID_COL with name ID of the column and use it.

cursor = db.rawQuery("select " + REFEREE_ID_COL + " from " +
            REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

this doesn't work my friend. It has to be something to do with my update method! — Padraig McMahon, Dec 12 '17 at 22:09

MikeT · Answer 2 · 2017-12-13T19:48:28.683

There appears to be an issue with the getIdFromName method as it appears to be extracting an integer from the string passed to the method.

That is, assuming the email is fred@email.com and is a valid email stored in the REFEREE_EMAIL_COL, then :-

The query is saying create a cursor with a single column, the REFEREE_EMAIL_COL where the REFEREE_EMAIL_COL is fred@email.com.

So the resultant Cursor will contain a single row with a single column that has the value fred@email.com.

So the line refs.add(cursor.getInt(0)); will try to get an integer based upon the contents of the cursor column offset 0 (the only column), which contains a value of fred@email.com. This, due to the getInt method being friendly/kind, will return 0 (see the example - For String myTEXT = "The Lazy Quick Brown Fox Jumped Over the Fence or something like that."; here).

So unless email passed to the method, is actually the id and is an integer converted to a string and also the id wanted, the getIdFromName method will return 0. Otherwise you would be getting the id of the id passed, which would be a waste of time.

Note! fred@email.com has only been used hypothetically.

Debugging you code to check if the above is the case.

The following code could be used to ascertain if the above is the case :-

private int getIdFromName(String email) {
    SQLiteDatabase db = this.getWritableDatabase();
    ArrayList<Integer> refs = new ArrayList<>();
    Cursor cursor;

    cursor = db.rawQuery("select " + REFEREE_EMAIL_COL + " from " +
            REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

    Log.d("CURSOR COUNT","The number of rows in the Cursor is " + Integer.toString(ccursor.getCount()));
    while (cursor.moveToNext()){
        Log.d("ID EXTRACTED","The extracted ID is " + Integer.toString(cursor.getInt(0))); // <<<<<<<< added 
        refs.add(cursor.getInt(0));
    }
    return refs.get(0);
}

The log would contain, - a) a line with the number of rows that met the selection criteria.

b) if a row contains the String, as per the variable email, in the REFEREE_EMAIL_COL column, a line (lines if multiple rows) with the extracted ID. If 0 then the above is likely the reason.

A Potential Fix :-

Assuming that the table is not created with WITHOUT ROWID, then the following may work :-

cursor = db.rawQuery("select " + rowid + " from " +
                REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);

A little about rowid here

In which case the rowid will be the only column in the cursor, it will be converted to an integer (really getLong should be used as it's a long and then long returned and subsequently used).

Note! There is information not available in your question, such as the actual column names, that limit the answer that can be given.

Regarding your comment

this doesn't work my friend. It has to be something to do with my update method!

The update method is dependent upon the value returned from the getIdFromName method. 0, assuming you are using a standard id column e.g. ?? INTEGER PRIMARY KEY or ?? INTEGER PRIMARY KEY AUTOINCREMENT, where ?? is the column name (alias of rowid), will not be a row. Id's start at 1 (unless you force it). If you do have a column that is an alias of the rowid, then you can use that column name instead of rowid in the fix above.

Edit after table structure

The previous answer applies, it only partly resolves the issues, so the getIdFromName could be :-

private long getIdFromName(String email) {
    long rv = -1; //default return value -1 indicates no referee
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("select " + REFEREE_ID_COL + " from " +
            REF_TABLE + " where " + REFEREE_EMAIL_COL + "='" + email + "'", null);
    if (cursor.moveToFirst()) {
        rv = cursor.getLong(csr.getColumnIndex(REFEREE_ID_COL));
    }
    cursor.close();
    return rv;
}

**`getIdFromName`** will now return a **long** rather than an **int**, there is no need for the Integer Array as you only return a long (was int). If the email passed does not result in a referee being found then the value returned will be -1. The Cursor is also closed to reduce the potential for problems if there are too many open Cursors. You will note that the hard coded offset of 0, has been replaced with csr.getColumnIndex(column_name), this gets the offset based upon the column name it is more felxible and less prone to errors.

There is also an issue with the updateRef method. This looks as if it's purpose is to update the current match with the id of the referee.

However, what it is doing is trying to update the referee id of the match who's referee id is the same as the referee id. The end result would be that nothing ever gets updated.

e.g. say you have Matches id of 10, it current has Ref with an email of fred@email.com and an id of 200.

getIdFromName correctly returns 200.

Your code says :-

Update the match table to change the referee id to 200 where the referee id is 200. What it should be saying is update the match table to change the referee id to 200 where the match id is 10.

Yours would effectively do nothing to all matches that have referee 200.

What is needed is the current match so that that specific match can be updated with the referee, so two parameters need to be passed to the updateRef method e.g. :-

    public boolean updateRef(String email, long matchid) {
       long ref_id = -1;
       int updates_done = 0;
       SQLiteDatabase db = this.getWritableDatabase();
       ContentValues values = new ContentValues();

       // Get the referee's id
       ref_id = getIdFromName(email);
       // if the referee exists then try to update the match
       if (ref_id > 0) {
           values.put(REFEREE_ID_COL,ref_id);
           updates_done = db.update(
               MATCH_TABLE, 
               values, 
               MATCH_ID_COL + "=?",
               new String[]{Long.toString(matchid)}
           );
           return updates_done > 0;
       }
       return false;
   }

A minor change would then be required to pass the match id e.g. :-

        mDatabaseHelper.updateRef(referee);

Should be changed to :-

        mDatabaseHelper.updateRef(referee, (long) selectedID);

Ideally you should always use long for id's as an id can be up to 9223372036854775807.

Note! again, the code hasn't been checked so may have some errors.

@PadraigMcMahon answer updated, noting that 2nd issue is now apparent. — MikeT, Dec 13 '17 at 19:49

How to Update Sqlite database method

2 Answers2

Debugging you code to check if the above is the case.

A Potential Fix :-

Regarding your comment

Edit after table structure