CODEWITHSUNDEEP
androidsqlite
Azmat Inayat
Azmat Inayat

Reputation: 55

unable to retrieve data of column from database

I am having a table named keywords in database.I want to retrieve data of alarm and location columns from this table and unable to retrieve them except for contact number.For now I am showing their values in a Toast but every time I run any query to show my alarm or location in Toast its empty.But my contact_number is always shown.Don't understand the cause of this problem .I have also checked my tables view and it is showing the values of alarm ,location in them.

Create Table keywords( contact_number text primary key  , alarm text  , location text )

and my insert function is 

  public boolean insertkeys (String alarm ,String location ,String contact){

        SQLiteDatabase db = this.getWritableDatabase();
        //ContentValues is a name value pair, used to insert or update values into database tables.
        // ContentValues object will be passed to SQLiteDataBase objects insert() and update() functions.
        //  ContentValues contentValues = new ContentValues();
        ContentValues contentValues = new ContentValues();


        contentValues.put("alarm",alarm);
        contentValues.put("location",location);
        contentValues.put("contact_number",contact);
        long ins = db.insert("keywords",null,contentValues);
        long upd = db.update("keywords",contentValues,"contact_number = ?",new String[]{contact});
        //  db.close();
        if(ins == -1 && upd == -1)
            return  false;
        else
            return true;
    }

I am inserting plus updating my data every single time my save button is clicked.Can anyone here tell how can I write a query to retrieve data of these fields and set it to Toast or Edit text. I am new to Database and stuck here for about a week. Thanks in advance for help :)

Upvotes: 1

Views: 1236

Answers (1)

MikeT
MikeT

Reputation: 57063

You extract data via a SELECT query which is returned as a Cursor when using the Android SDK.

The Cursor is similar to a table in that it has a number of rows, each with a set number of columns as determined by what you select.

To get all rows the SELECT query would be along the lines of :-

`SELECT * FROM keywords`

To do this using the Android SDK you could use the SQLiteDatabase query convenience method e.g. for the above you could use :-

Cursor cursor = db.query("keywords",null,null,null,null,null,null);
  • check the links above for the values/parameters that can be passed and how they correlate with the SELECT statement.

You then traverse the returned cursor extracting the data, typically using the Cursor's move??? methods. Noting that most will return false if the move could not be made and also noting that the original position in the Cursor is before the first row

As such you could have a method that returns a Cursor as per :-

public Cursor getAllKeys(){

    SQLiteDatabase db = this.getWritableDatabase();
    return db.query("keywords",null,null,null,null,null,null);
}

You could then process all the rows using :-

Cursor csr = yourDBHelper.getAllKeys();
while (csr.moveToNext()) {
    String current_contact_number = csr.getString(csr.getColumnIndex("contact_number");
    String current_alarm = csr.getString(csr.getColumnIndex("alarm");
    String current_location = csr.getString(csr.getColumnIndex("location"));
    ...... your code to Toast or use the retrieved values       
}
csr.close(); //<<<<<<<<<< you should always close a Cursor when finished with it.

Additional

In regard to the comment :-

Cursor query which you have suggested I tried to make changes in it like putting column and where clause but after that it returns me nothing when I execute it.Could you tell me that query too.

The following could be a method to retrieve just the alarm according to a contact number.

public String getAlarmByContactNumber(String contactNumber){

    String rv = "";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor csr = db.query(
        "keywords", //<<<<<<<<<<<< the FROM clause (less the FROM keyword) typically the name of the table from which the data is to be extracted (but can include JOINS for example, for more complex queries)
        new String[]{"alarm"}, //<<<<<<<<<< A String[] of the column names to be extracted (null equates to * which means all columns) (note can be more complex and include functions/sub queries)
        "contact_number=?", //<<<<<<<<<< WHERE clause (less the WHERE keyword) note **?** are place holders for parameters passed as 4th argument
        new String[]{contactNumber},
        null, //<<<<<<<<<< GROUP BY clause (less the GROUP BY keywords)
        null, //<<<<<<<<<< HAVING clause (less the HAVING keyword)
        null  //<<<<<<<<<< ORDER BY clause (less the ORDER BY keywords)
    );
    if (csr.moveToFirst()) {
        rv = csr.getString(csr.getColumnIndex("alarm"));
    }
    csr.close();
    return rv;
}
  • The above assumes that you would only have/want one alarm per contact number.
  • The above is in-principle code, it has not been run or tested and may therefore contain some minor errors.

Upvotes: 1

Related Questions