Leoa
Reputation: 1187
Refresh Cursor in Sqlite Database
I'm unable to get the cursor to return updated information from the SQlite database. I found that requery() is depreciated. The Android Dev site said to create a new cursor every time your need fresh information.The site also said to use a thread.
Question 1: I'm using an Async Task to get updated data from the database. The Async Task is working but It retrieves old data. Is there another approach to making the cursor return up-to-date data? Or am I missing something?
Question 2. Do I need a 'cursor adapter'? I want fresh data to return, and I'm not displaying the data in a list, so it doesn't make sense to have one. But if this is the only way to return fresh data, please let me know.
I am using cursor.close() in the Database Class.
My async task:
private class ButtonDataTask extends AsyncTask<Void,Void,Void> {
String x;
String time;
private final ProgressDialog dialog = new ProgressDialog(
AlertsDetails.this);
// can use UI thread here
protected void onPreExecute() {
this.dialog.setMessage("Selecting data...");
this.dialog.show();
}
// can use UI thread here
@SuppressWarnings("unused")
protected void onPostExecute(Void result) {
if (this.dialog.isShowing()) {
this.dialog.dismiss();
}
if (alertState == 1) {
mAlertTime.setText("ALERT OFF");
}
if (alertState == 0) {
mAlertTime.setText(x);
}
}
@Override
protected Void doInBackground(Void... params) {
DatabaseSqlite updateAlertStatus = new DatabaseSqlite(
AlertsDetails.this);
if (alertState == 0) {
alertState = 1;
updateAlertStatus.open();
updateAlertStatus.updateAlertState(id, alertState);
alertState = updateAlertStatus.getAlertState(id);
updateAlertStatus.close();
}
else {
alertState = 0;
updateAlertStatus.open();
x ="ALERT ON "
+ updateAlertStatus.getAlertTime(id)
+ " from button";
updateAlertStatus.updateAlertState(id, alertState);
alertState = updateAlertStatus.getAlertState(id);
//mAlertStateDisplay.setText(" ");
updateAlertStatus.close();
}
return null;
}
}
Database:
package com.google.android.gcm.demo.app.sqllite;
import java.util.ArrayList;
import java.util.List;
import java.util.Observable;
import org.json.JSONObject;
import com.google.android.gcm.demo.app.Alerts.AlertAdapter;
import com.google.android.gcm.demo.app.Alerts.Alerts;
import com.google.android.gcm.demo.app.EventList.Item;
import com.google.android.gcm.demo.app.EventList.NewsRowAdapter;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.ListView;
public class DatabaseSqlite extends Observable {
public static final String KEY_ROWID = "_id";
public static final String KEY_EVENTNAME = "event_name";
public static final String KEY_EVENTSTARTTIME = "event_start_time";
public static final String KEY_EVENTALERTTIME = "event_alert_time";
public static final String KEY_EVENTALERTSTATE = "event_alert_state";
public static final String KEY_EVENTSTATE = "event_state";
public static final String KEY_IDFROMREMOTESERVER = "event_id";
public static final String KEY_LOCATION = "event_location";
public static final String KEY_DATE = "date";
private static final String KEY_ORIGINALALALERTDATE = "original_alert_date";
private static final String KEY_ALERTMINUTES = "alert_in_minutes";
private static final String KEY_ALERTINMILLIS = "alert_in_millis";
// database name:
private static final String DATABASE_NAME = "Reminders_DB";
private static final String DATABASE_TABLE = "Alerts";
private static final int DATABASE_VERSION = 1;
private DBHelper dbHelper;
private final Context context;
private SQLiteDatabase database;
Cursor cursor = null;
ArrayList<String> alertList = null;
List<Alerts> alertsOfList;
ListView listView;
AlertAdapter objAdapter;
// JSONObject json;
private String name = "First time i have this Text";
/**
* @return the value
*/
public String getValue() {
return name;
}
/**
* @param value
* the value to set
*/
public void setValue(String name) {
this.name = name;
setChanged();
notifyObservers();
}
private static class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ KEY_IDFROMREMOTESERVER + " INTEGER, " + KEY_EVENTNAME
+ " TEXT, " + KEY_EVENTSTARTTIME + " TEXT, "
+ KEY_EVENTSTATE + " INTEGER," + KEY_LOCATION + " TEXT, "
+ KEY_DATE + " TEXT, " + KEY_EVENTALERTTIME + " TEXT, "
+ KEY_ALERTMINUTES + " TEXT, " + KEY_ALERTINMILLIS
+ " TEXT, " + KEY_ORIGINALALALERTDATE + " TEXT, "
+ KEY_EVENTALERTSTATE + " INTEGER);"
);
System.out.println("database created ");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
onCreate(db);
System.out.println("DROP TABLE IF EXISTS ");
}
}
public DatabaseSqlite(Context c) {
context = c;
}
public DatabaseSqlite open() throws SQLException {
dbHelper = new DBHelper(context);
database = dbHelper.getWritableDatabase();
System.out.println("database opened " + database);
return this;
}
public void close() {
try {
if (database != null && database.isOpen()) {
dbHelper.close();
System.out.println("database closed ");
// setValue("penny");
}
} catch (Exception e) {
e.printStackTrace();
}
}
public long createEntry(int idFromRemoteServer, String eventName,
String startTime, String alertTime, int alertState,
String location, int eventState, String date) {
ContentValues cv = new ContentValues();
cv.put(KEY_IDFROMREMOTESERVER, idFromRemoteServer);
cv.put(KEY_EVENTNAME, eventName);
cv.put(KEY_EVENTSTARTTIME, startTime);
cv.put(KEY_EVENTALERTTIME, alertTime);
cv.put(KEY_EVENTSTATE, eventState);
cv.put(KEY_EVENTALERTSTATE, alertState);
cv.put(KEY_LOCATION, location);
cv.put(KEY_DATE, date);
return database.insert(DATABASE_TABLE, null, cv);
}
public List<Alerts> getData() {
Cursor cursor = null;
System.out.println("get data is running");
alertsOfList = new ArrayList<Alerts>();
try {
String[] columns = new String[] { KEY_ROWID,
KEY_IDFROMREMOTESERVER, KEY_EVENTNAME, KEY_EVENTSTARTTIME,
KEY_EVENTALERTTIME, KEY_EVENTALERTSTATE, KEY_LOCATION,
KEY_EVENTSTATE, KEY_DATE };
// information from a database is read thru a cursor
cursor = database.query(DATABASE_TABLE, columns, null, null, null,
null, null);
int iRow = cursor.getColumnIndex(KEY_ROWID);
int iIdRemote = cursor.getColumnIndex(KEY_IDFROMREMOTESERVER);
int iEventState = cursor.getColumnIndex(KEY_EVENTSTATE);
int iName = cursor.getColumnIndex(KEY_EVENTNAME);
int iStartTime = cursor.getColumnIndex(KEY_EVENTSTARTTIME);
int iAlertTime = cursor.getColumnIndex(KEY_EVENTALERTTIME);
int iAlertState = cursor.getColumnIndex(KEY_EVENTALERTSTATE);
int iLocation = cursor.getColumnIndex(KEY_LOCATION);
int iDate = cursor.getColumnIndex(KEY_DATE);
if (cursor != null) {
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor
.moveToNext()) {
Alerts alertObj = new Alerts();
alertObj.setRowId(cursor.getInt(iRow));
alertObj.setRemoteServerId(cursor.getInt(iIdRemote));
alertObj.setEventState(cursor.getInt(iEventState));
alertObj.setAlertState(cursor.getInt(iAlertState));
alertObj.setEventName(cursor.getString(iName));
alertObj.setLocation(cursor.getString(iLocation));
alertObj.setStartTime(cursor.getString(iStartTime));
alertObj.setAlertTime(cursor.getString(iAlertTime));
alertObj.setDate(cursor.getString(iDate));
String result = cursor.getString(iName);
System.out.println(result);
alertsOfList.add(alertObj);
}
}
} catch (Exception e) {
e.printStackTrace();
alertsOfList = null;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.deactivate();
cursor.close();
cursor = null;
}
}
return alertsOfList;
}
public String getAlertTime(long lRow) {
String result = "";
try {
String[] columns = new String[] { KEY_ROWID + "=" + lRow, KEY_EVENTALERTTIME };
cursor = database.query(DATABASE_TABLE, columns, null, null, null,
null, null);
int iAlertTime = cursor.getColumnIndex(KEY_EVENTALERTTIME);
if (cursor != null) {
cursor.moveToFirst();
result = cursor.getString(iAlertTime);
// System.out.println(" Alert time pulled from server is "
// + result);
}
} catch (Exception e) {
e.printStackTrace();
// alertsOfList = null;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.deactivate();
cursor.close();
cursor = null;
}
}
System.out.println("---------get alert Time is " + result );
System.out.println("row from time is " + lRow);
return result;
}
public int getAlertState(long lRow) {
Cursor cursor = null;
int result = 0;
try {
String[] columns = new String[] { KEY_ROWID + "=" + lRow , KEY_EVENTALERTSTATE };
// information from a database is read thru a cursor
cursor = database.query(DATABASE_TABLE, columns, null, null, null,
null, null);
int iAlertState = cursor.getColumnIndex(KEY_EVENTALERTSTATE);
if (cursor != null) {
cursor.moveToFirst();
result = cursor.getInt(iAlertState);
}
} catch (Exception e) {
e.printStackTrace();
// alertsOfList = null;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.deactivate();
cursor.close();
cursor = null;
}
}
System.out.println("---------get alert state is " + result );
System.out.println("row from state is " + lRow);
return result;
}
// ////////////
public List<Alerts> getAlertsforService() {
Cursor cursor = null;
System.out.println("get data is running");
alertsOfList = new ArrayList<Alerts>();
try {
String[] columns = new String[] { KEY_ROWID, KEY_EVENTNAME,
KEY_EVENTSTARTTIME, KEY_EVENTALERTTIME,
KEY_EVENTALERTSTATE, KEY_LOCATION, KEY_EVENTSTATE,
KEY_DATE, KEY_ORIGINALALALERTDATE, KEY_ALERTMINUTES,
KEY_ALERTINMILLIS };
// information from a database is read thru a cursor
cursor = database.query(DATABASE_TABLE, columns, null, null, null,
null, null);
int iRow = cursor.getColumnIndex(KEY_ROWID);
// int iIdRemote = cursor.getColumnIndex(KEY_IDFROMREMOTESERVER);
int iEventState = cursor.getColumnIndex(KEY_EVENTSTATE);
int iName = cursor.getColumnIndex(KEY_EVENTNAME);
int iStartTime = cursor.getColumnIndex(KEY_EVENTSTARTTIME);
int iAlertTime = cursor.getColumnIndex(KEY_EVENTALERTTIME);
int iAlertState = cursor.getColumnIndex(KEY_EVENTALERTSTATE);
int iLocation = cursor.getColumnIndex(KEY_LOCATION);
int iDate = cursor.getColumnIndex(KEY_DATE);
int iOrigAlertDate = cursor.getColumnIndex(KEY_ORIGINALALALERTDATE);
int iMinutes = cursor.getColumnIndex(KEY_ALERTMINUTES);
int iAlertInMills = cursor.getColumnIndex(KEY_ALERTINMILLIS);
if (cursor != null) {
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor
.moveToNext()) {
if (cursor.getInt(iAlertState) == 0
&& cursor.getString(iAlertInMills) != "") {
Alerts alertObj = new Alerts();
// iOrigAlertDate
//
alertObj.setOrgAlertDate(cursor
.getString(iOrigAlertDate));
alertObj.setAlertMinutes(cursor.getString(iMinutes));
alertObj.setEventState(cursor.getInt(iEventState));
alertObj.setRowId(cursor.getInt(iRow));
alertObj.setAlertInMillis(cursor
.getString(iAlertInMills));
alertObj.setEventState(cursor.getInt(iEventState));
alertObj.setAlertState(cursor.getInt(iAlertState));
alertObj.setEventName(cursor.getString(iName));
alertObj.setLocation(cursor.getString(iLocation));
alertObj.setStartTime(cursor.getString(iStartTime));
alertObj.setAlertTime(cursor.getString(iAlertTime));
alertObj.setDate(cursor.getString(iDate));
alertsOfList.add(alertObj);
}
}
}
} catch (Exception e) {
e.printStackTrace();
alertsOfList = null;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.deactivate();
cursor.close();
cursor = null;
}
}
return alertsOfList;
}
// //////////
public void updateEventState(long lRow, int eventState) {
// is Event running or canceled
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_EVENTSTATE, eventState);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void updateEventStartTime(long lRow, int eventStartTime) {
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_EVENTSTARTTIME, eventStartTime);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void updateAlertState(long lRow, int alertState) {
System.out.println(lRow+"---------update alert state is " + alertState);
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_EVENTALERTSTATE, alertState);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
// public void updateEventAlertTime(long lRow, String updateAlertTime) {
// // time update from notification
// System.out.println("----updateEventAlert time is "+updateAlertTime);
// ContentValues updateCV = new ContentValues();
// updateCV.put(KEY_EVENTALERTTIME, updateAlertTime);
// database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
//
//
// }
public void updateAlertTime(long lRow, String updateAlertTime) {
// time update from notification
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_EVENTALERTTIME, updateAlertTime);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void updateEventLocation(long lRow, String location) {
// time update from notification
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_LOCATION, location);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void updateEventState(long lRow, String state) {
// time update from notification
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_EVENTSTATE, state);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void updateDate(long lRow, String state) {
// time update from notification
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_DATE, state);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void updateAlertInDB(long lRow, String date, String alertInMillis,
String minutes, String alertTime) {
ContentValues updateCV = new ContentValues();
updateCV.put(KEY_ORIGINALALALERTDATE, date);
updateCV.put(KEY_EVENTALERTTIME, alertTime);
updateCV.put(KEY_ALERTMINUTES, minutes);
updateCV.put(KEY_ALERTINMILLIS, alertInMillis);
System.out.println("------------------"+alertInMillis + " " + date + " " + minutes);
database.update(DATABASE_TABLE, updateCV, KEY_ROWID + "=" + lRow, null);
// setValue("penny");
}
public void deleteEntry(long lRow1) {
// TODO Auto-generated method stub
database.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);
// setValue("penny");
}
}
Update
The cursor updates when the activity is destroyed. Is there another way to get up-to-date data from the sqlite database?
Upvotes: 0
Views: 2165
Answers (1)
Leoa
Reputation: 1187
I needed to specify the row in the getAlertState function, in the Sql database.
cursor = database.query(DATABASE_TABLE, columns, _id, null, null,
null, null);
Upvotes: 1
Related Questions
- Android - Can you update a Cursor for SQLite results?
- Unable to show updated values from Cursor when live updating SQLite database
- Getting java.lang.IllegalStateException: attempt to re-open an already-closed object: when using cursor in Android
- Android re-using a cursor
- Refresh SQLite data on ListView with CursorAdapter
- Android Cursor in SQLite
- Android db.update SQLite
- Updating Cursor on SQLite database change
- android sqlite database cursor
- NullPointerException with AsyncTask and SQLite cursor