lundi 2 mars 2015

sqlite for android, how to update table

I found a number of links on stack overflow dealing with this but none of the solutions have worked for me so here goes:


I am trying to add a column to hold a date (and may need to add a time column in future) but am having some trouble since the table had already been created. Here is the database code:



public class MyDatabase {

public static final String KEY_ROWID = "_id";
public static final String KEY_DATE = "Date";
public static final String KEY_SYS = "Systolic";
public static final String KEY_DIA = "Diastolic";

private static final String DATABASE_NAME = "Blood Pressures";//Used to reference database
private static final String DATABASE_TABLE = "bloodPressureTable";//Tables can be stored in database
// this will be used to store ID, date, systolic and diastolic
private static final int DATABASE_VERSION = 9;

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

public String getData() {
String[] columns = new String[]{KEY_ROWID, KEY_SYS, KEY_DIA};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
String result = "";

int iRow = c.getColumnIndex(KEY_ROWID);
int iSys = c.getColumnIndex(KEY_SYS);
int iDia = c.getColumnIndex(KEY_DIA);

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) { //if cursor is after position of our last entry, then stop
result = result + c.getString(iRow) + //get ROW_ID column, get name of first row and hotness, create new string
" " + c.getString(iSys) + " " +
c.getString(iDia) + "\n";
}

return result;
}

private static class DbHelper extends SQLiteOpenHelper {

public DbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
//TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) { //This is only called when we first create a database
// when then we will just cycle through onUpgrade, passes a database in
db.execSQL(
"CREATE TABLE " + DATABASE_TABLE + " (" + //create a table called table name
//adds columns inside parenthesise
KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + //integer increment automatically, referenced by keyID
KEY_DATE + "TEXT NOT NULL, " + //SQL uses the word text rather than string
KEY_SYS + " INTEGER, " +
KEY_DIA + " INTEGER);"
);
}

@Override //called if oncreate has already been called
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


//db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE); //If table name exists it is going
// to drop it and then all we have to do is call our onCreate method
//Removes table added by create table statement
//onCreate(db);

if (newVersion > oldVersion) {
db.execSQL("ALTER TABLE " + DATABASE_TABLE +
" ADD COLUMN " + KEY_DATE + " TEXT NOT NULL, ");
}

}
}

//Constructor below
public MyDatabase(Context c) {
ourContext = c;
}
//open() allows us to open and write to database

public MyDatabase open() {
ourHelper = new DbHelper(ourContext); // this is a new instance of that object passing in the context
ourDatabase = ourHelper.getWritableDatabase();//here we set up our database,
// getting the writeable database. This will open up our database through our helper
return this;
}

public void close() {
ourHelper.close(); //close our SQLiteOpenHelper

}

public long createEntry(int systolic, int diastolic) {
ContentValues cv = new ContentValues();
Calendar c = Calendar.getInstance();
String s = Integer.toString(c.get(Calendar.DAY_OF_MONTH)) + "/" + Integer.toString(c.get(Calendar.MONTH)) +
"/" + Integer.toString(c.get(Calendar.YEAR));

cv.put(KEY_DATE, s);
cv.put(KEY_SYS, systolic);
cv.put(KEY_DIA, diastolic);//Put the string in KEY_NAME column?
return ourDatabase.insert(DATABASE_TABLE, null, cv); //we want this method to return this line
}
}


Can someone advise me of a solution please? As you can see I have tried two different methods (one is commented out in onUpgrade). As you can see I am on version 10 from playing about with it already!


Here is the logcat:



03-02 12:48:18.771 1695-1695/com.dissertation.michael.biolog E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.dissertation.michael.biolog, PID: 1695
java.lang.RuntimeException: Failure delivering result ResultInfo{who=null, request=1111, result=-1, data=Intent { (has extras) }} to activity {com.dissertation.michael.biolog/com.dissertation.michael.biolog.MainActivity}: java.lang.NumberFormatException: Invalid int: "300-400"
at android.app.ActivityThread.deliverResults(ActivityThread.java:3391)
at android.app.ActivityThread.handleSendResult(ActivityThread.java:3434)
at android.app.ActivityThread.access$1300(ActivityThread.java:138)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1284)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:149)
at android.app.ActivityThread.main(ActivityThread.java:5045)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:602)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.NumberFormatException: Invalid int: "300-400"
at java.lang.Integer.invalidInt(Integer.java:137)
at java.lang.Integer.parse(Integer.java:374)
at java.lang.Integer.parseInt(Integer.java:365)
at java.lang.Integer.parseInt(Integer.java:331)
at com.dissertation.michael.biolog.MainActivity.onActivityResult(MainActivity.java:153)
at android.app.Activity.dispatchActivityResult(Activity.java:5430)
at android.app.ActivityThread.deliverResults(ActivityThread.java:3387)
at android.app.ActivityThread.handleSendResult(ActivityThread.java:3434)
at android.app.ActivityThread.access$1300(ActivityThread.java:138)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1284)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:149)
at android.app.ActivityThread.main(ActivityThread.java:5045)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:602)
at dalvik.system.NativeStart.main(Native Method)


Alternatively (and preferably if it is simple) a method to delete my table entirely so that version 1 is created again would be ideal. (No useful data has been entered into the database at this point)... Cheers!


Aucun commentaire:

Enregistrer un commentaire