Accessing values from SQlite

Question

i am trying to access all of the values from my SQlite table and store them as integer values, but everytime i run the program it crashes

When i click the button (either on the emulator or running on my phone), it just crashes while i am expecting to see the textview change text (depending on which button i click )

here is the code:

(main_activity)

int x = 0;
int y =0;
int[] stats = new int[]{};
@Override
public void onClick(View arg0) {
    switch(arg0.getId()){
    case R.id.button1: 
        x= 1;
        Database db = new Database(this);
        db.open();
        db.createEntry(x, y);
        stats = db.getStatistics();
        db.close();
        x= 0;       
        txt_view.setText("");
        txt_view.setText(Integer.toString(stats[0]));
        break;
    case R.id.button2:
        y=1;
        Database db2 = new Database(this);
        db2.open();
        db2.createEntry(x, y);
         stats = db2.getStatistics();
        db2.close();
        cravings = 0;
        db2.getStatistics();
        txt_view2.setText("");
        txt_view2.setText(Integer.toString(stats[1]));
        break;

    }
}

and here is my database class

public class Database  extends Activity{
private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;
public static final String DATABASE_NAME = "DB_NAME";
public static final String TABLE_NAME = "DB_TABLE";
public static final String NUM_SMOKES  = "NUM_X";
private static final int DATABASE_VERSION = 1;
public static final String NUM_CRAVINGS = "NUM_Y";
public static final String CreateMyDatabase = "create table "+ TABLE_NAME +" ("+NUM_SMOKES+" integer default 0,"+NUM_CRAVINGS+" integer default 0)";


private static class DbHelper extends SQLiteOpenHelper{

public DbHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }
@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(CreateMyDatabase);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("drop table if exists "+ TABLE_NAME);
    onCreate(db);
}}
public Database(Context c ){
ourContext = c;}    
public Database open() throws SQLException{
ourHelper = new DbHelper(ourContext, DATABASE_NAME, null, DATABASE_VERSION);
ourDatabase = ourHelper.getWritableDatabase();
return this;    
}
public void close(){
ourHelper.close();
}
public long createEntry(int x, int y){
ContentValues cv = new ContentValues();
cv.put(NUM_X, x);
cv.put(NUM_Y, y);
return ourDatabase.insert(DATABASE_NAME, null, cv);
}
public int[] getStatistics(){
String[] column = new String[]{NUM_X,NUM_Y};
Cursor c = ourDatabase.query(DATABASE_NAME, column, null, null, null,
        null, null);    
int x=0;
int y= 0;
int i_X= c.getColumnIndex(NUM_X);
int i_Y= c.getColumnIndex(NUM_Y);
for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
    y+= c.getInt(i_Y);
    x+= c.getInt(i_X);
}
int[] X&Y= new int[]{x, y};
return X&Y;
}

Thank you for your help!

it was no such table exists, i looked through it previously and completely missed it, and fixed the code. now works. The problem was i was trying to insert through db.insert(DATABASE_NAME) instead of what it should of being db.insert(TABLE_NAME) — 7888, Mar 18 '14 at 04:35

score 0 · Accepted Answer · answered Mar 18 '14 at 08:07

0

Try to insert values like this :

//In your model define method that returns content values for that table like     
public class SomeModel{
private int field1;
public ContentValues getContentValues(){
        ContentValues localContentValues = new ContentValues();
        localContentValues.put("id", getId());
        //Other values...
        return localContentValues;
    }

}
//And in sqliteopen handler do insert like this
//Pass variables to constructor
SomeModel model=new SomeModel(...);
ContentValues modelValues = model.getContentValues();
this.db.insert("SomeModel", null, modelValues );

answered Mar 18 '14 at 08:07

Kenan Begić

1,228
11
21

Thanks, i used the code you provided and it helped clean out my code. – 7888 Mar 20 '14 at 20:26
1

You should version your DB on every change it should save you time and errors. How to do that you can find may questions/answers here on stack like this one : http://stackoverflow.com/questions/3424156/upgrade-sqlite-database-from-one-version-to-another – Kenan Begić Mar 21 '14 at 09:00

score 0 · Answer 2 · answered Mar 19 '14 at 05:01

the error was 'no such table exists', i looked through it previously and completely missed it, and fixed the code. now it works. The problem was i was trying to insert through db.insert(DATABASE_NAME); instead of what it should of being db.insert(TABLE_NAME);

Accessing values from SQlite

2 Answers2