Pushpam Kumar Pushpam Kumar - 6 months ago 10
Android Question

how can retrieve data from sqlite?

i am trying to to retrive data from SQLite databse but this code is not working .i do not understand why?? i am new in android help!!

this code for retrieving data!!

data.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {

String dbString="";

TextView tName=(TextView)findViewById(R.id.textView);

DataBase2n6 objOfDataBase2n6=new DataBase2n6(getBaseContext());

Cursor getData = objOfDataBase2n6.showData();

getData.moveToFirst();
do{
dbString+=getData.getString(getData.getColumnIndex("name"));

dbString+="\n";
tName.setText(dbString);

}while(getData.moveToNext());

Toast.makeText(getBaseContext(), "YOUR DATA", Toast.LENGTH_LONG).show();
}
});


this method use under database file!!

public Cursor showData(){

db=this.getWritableDatabase();

Cursor res = db.rawQuery("SELECT * FROM "+TABLE_NAME,null);
return res;

}

Answer

i recommend a different approach: create a class that will hold your sqlite data structure:

public class SQLiteAdapterPrefs {

public static final String MYDATABASE_NAME = "AssetTrackerPrefs";
public static final String MYDATABASE_TABLE = "systemPrefs";
public static final int MYDATABASE_VERSION = 1;
public static final String KEY_ID = "_id";
public static final String KEY_IMEI = "Nr_Target_Imei"; 
public static final String KEY_LAT = "Nr_Track_Target_Latitude";
public static final String KEY_LON = "Nr_Track_Target_Longitude";   
public static final String KEY_TRACKTIME = "Dt_Target_Track_Point"; 
public static final String KEY_FREQUENCY = "Nr_Frequency";  // upload frequency
public static final String KEY_USER = "Ds_Target_Email";
public static final String KEY_IP = "Ds_IP";
public static final String KEY_PORT = "Nr_IP_Port"; 
public static final String KEY_STICKY = "Ic_Autostart";

public static String imeiFromDevice = "INVALID_IMEI_I9100_sqlliteadapter01";
private boolean flagSticky = true;
private int frequency = 1;
private String emailId = "";

//create table MY_DATABASE (ID integer primary key, Content text not null);
private static final String SCRIPT_CREATE_DATABASE =
        "create table " + MYDATABASE_TABLE + " ("
        + KEY_ID + " integer default 1, " // primary key autoincrement, "
        + KEY_IMEI + " text not null, " 
        + KEY_LAT + " float default -91.0, "
        + KEY_LON + " float default -181.0, " 
        + KEY_TRACKTIME + " long NOT NULL, "
        + KEY_FREQUENCY + " integer default 1, "
        + KEY_USER + " text not null, " 
        + KEY_IP + " text not null, " 
        + KEY_PORT + " integer default 9099, "
        + KEY_STICKY + " integer default 1"
        + ");";

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;

private String TAG = "TGPrefs";
private Context context;


public SQLiteAdapterPrefs(Context c){
    context = c;
}


public SQLiteAdapterPrefs openToRead() throws android.database.SQLException {
    sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
    sqLiteDatabase = sqLiteHelper.getReadableDatabase();
    return this;    
}

public SQLiteAdapterPrefs openToWrite() throws android.database.SQLException {
    sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
    sqLiteDatabase = sqLiteHelper.getWritableDatabase();
    return this;    
}

public void close(){
    sqLiteHelper.close();
}
public Cursor queueAll(){
    //String[] columns = new String[]{KEY_ID, KEY_IMEI, KEY_LAT, KEY_LON, KEY_TRACKTIME, KEY_USER, KEY_IP, KEY_STICKY};
    String[] columns = new String[]{KEY_ID, KEY_IMEI, KEY_LAT, KEY_LON, KEY_TRACKTIME, KEY_FREQUENCY, KEY_USER, KEY_IP, KEY_PORT, KEY_STICKY};
    Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
            null, null, null, null, null);
    return cursor;
}
public Cursor getFirst(){
    //String[] columns = new String[]{KEY_ID, KEY_IMEI, KEY_LAT, KEY_LON, KEY_TRACKTIME, KEY_USER, KEY_IP, KEY_STICKY};
    String[] columns = new String[]{KEY_ID, KEY_IMEI, KEY_LAT, KEY_LON, KEY_TRACKTIME, KEY_FREQUENCY, KEY_USER, KEY_IP, KEY_PORT, KEY_STICKY};


    String whereCondition = KEY_ID+"=1";
    Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
            whereCondition, null, null, null, null);
    return cursor;
}

then i call this class from within my application:

    // open sysPrefs
    SQLiteAdapterPrefs sysPrefs = new SQLiteAdapterPrefs(this);
    sysPrefs.openToRead();
    Cursor cursor = sysPrefs.getFirst();
    int index_IMEI = cursor.getColumnIndex(SQLiteAdapterPrefs.KEY_IMEI);
    int counterRows = cursor.getCount();
    Log.v(TAG, "TENTANDO ABRIR PREFS registros no cursor= "+counterRows+"  numColumns="+cursor.getColumnCount());
    cursor.moveToFirst();
    if ( !(cursor.isAfterLast()) ) {
        imeiFromDevice =  cursor.getString(index_IMEI);
    }
    Log.v(TAG, "from sysprefs imeiFromDevice: "+imeiFromDevice);
    // set preferences and get emailId
    try {
        boolean prefsOK = sysPrefs.setPrefs();