StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORecords of empty data being returned from Android SQLite db for one or more tables
text
Body
copied!<p>I've been laboring over this problem for the past two days. I am attempting to create a list view that is populated from a database query (seems simple enough). As I will be managing multiple tables, I have created a database helper class to manage insertions and deletions. But it does not work consistently (or at all of late).</p> <p>When I attempt to query a table, using one of the defined functions, the db return cursors with XX number of records, but null column data. In effect, multiple rows ( I see the row separators), but each row is blank.</p> <p>I'm a bit stumped on this problem, and would appreciate any assistance. Below is the DB helper class.</p> <p>Database Helper Class</p> <pre><code>package org.data.agroassistant; import static android.provider.BaseColumns._ID; import static org.data.agroassistant.Constants.*; import java.net.URL; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class AgroAssistantDB extends SQLiteOpenHelper { private static final String CREATE_TABLE_FARMERS = "create table " + FARMERS_TABLE + " ( " + _ID + " integer primary key autoincrement, " + FARMER_ID + " int not null, " + FARMER_FNAME + " text not null, " + FARMER_LNAME + " text not null, " + FARMER_SIZE + " text not null);"; private static final String CREATE_TABLE_FARMS = "create table " + FARMS_TABLE + " ( " + _ID + " integer primary key autoincrement, " + FARM_ID + " integer not null, " + FARM_FARMER_ID + " integer not null, " + FARM_SIZE + " text not null, " + FARM_PARISH + " text not null, " + FARM_EXTENSION + " text not null, " + FARM_DISTRICT + " text not null, " + FARM_LAT + " long not null, " + FARM_LONG + " long not null);"; private static final int DATABASE_VERSION = 1; private SQLiteDatabase db; public AgroAssistantDB(Context ctx) { super(ctx, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase dbl) { try { dbl.execSQL(CREATE_TABLE_FARMERS); Log.d("AgroAssistant", "Create Farmers table: " + CREATE_TABLE_FARMERS); //dbl.execSQL(CREATE_TABLE_FARMS); //Log.d("AgroAssistant", "Create Farms table: " + CREATE_TABLE_FARMS); } catch (RuntimeException e) { Log.d("AgroAssistant", "Unable to create tables: " + CREATE_TABLE_FARMERS + CREATE_TABLE_FARMS); } } @Override public void onUpgrade(SQLiteDatabase dbl, int oldVersion, int newVersion) { Log.w("AgroAssistant", "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); dbl.execSQL("DROP TABLE IF EXISTS " + FARMERS_TABLE); dbl.execSQL("DROP TABLE IF EXISTS " + FARMS_TABLE); Log.d("AgroAssistant", "Upgrade step: " + "DROP TABLE IF EXISTS " + FARMERS_TABLE + FARMS_TABLE); onCreate(dbl); } /* * Userdefined function used to run rawQueries again specific tables */ public Cursor rawQuery(String tableName, String tableColumns, String queryParams) { db = this.getReadableDatabase(); Log.d("AgroAssistant", "Raw Query Insertion: SELECT "+ tableColumns + " FROM " + tableName + " WHERE " + queryParams); Cursor cursor = db.rawQuery("SELECT "+ tableColumns + " FROM " + tableName +" WHERE " + queryParams, null); Log.d("AgroAssistant", "Raw Query Result: Returned " + cursor.getCount() + " record(s)"); return cursor; } public Cursor getFarmers() { db = this.getReadableDatabase(); //Cursor cursor = db.query(FARMERS_TABLE, FROM_FARMERS, null, null, null, null, null); Cursor cursor = db.query(FARMERS_TABLE, new String[] {"*"}, null, null, null, null, null); return cursor; } public boolean insertFarmer(int id, String firstname, String lastname, String farmersize) { db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(FARMER_ID, id); values.put(FARMER_FNAME, firstname); values.put(FARMER_LNAME, lastname); values.put(FARMER_SIZE, farmersize); try { db.insertOrThrow(FARMERS_TABLE, null, values); Log.d("AgroAssistant", "Insert Farmer: " + id + " " + firstname + " " + lastname + " " + farmersize); //db.close(); return true; } catch (RuntimeException e) { //db.close(); Log.e("AgroAssistant","Farmer Insertion Exception: "+e.toString()); return false; } } public boolean deleteFarmer(Long farmerId) { db = this.getWritableDatabase(); if (db.delete(FARMERS_TABLE, _ID + '=' + farmerId.toString(), null) > 0) { db.close(); return true; } else { db.close(); return false; } } public boolean insertFarm(int fid, int pid, int p_size, int latitude, int longtitude, String p_parish, String p_extension, String p_district) { db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(FARM_ID, pid); values.put(FARM_FARMER_ID, fid); values.put(FARM_SIZE, p_size); values.put(FARM_LAT, latitude); values.put(FARM_LONG, longtitude); values.put(FARM_PARISH, p_parish); values.put(FARM_EXTENSION, p_extension); values.put(FARM_DISTRICT, p_district); try { db.insertOrThrow(FARMS_TABLE, null, values); Log.d("AgroAssistant", "Insert Farm: " + fid + " " + pid + " " + p_size + " " + latitude + " " + longtitude + " " + p_extension + " " + p_district); db.close(); return true; } catch (RuntimeException e) { db.close(); Log.e("AgroAssistant","Farm Insertion Exception: "+e.toString()); return false; } } public Cursor getFarms() { db = this.getReadableDatabase(); Cursor cursor = db.query(FARMS_TABLE, FROM_FARMS, null, null, null, null, null); return cursor; } public boolean deleteFarm(Long farmId) { db = this.getWritableDatabase(); if (db.delete(FARMS_TABLE, _ID + '=' + farmId.toString(), null) > 0) { db.close(); return true; } else { db.close(); return false; } } } </code></pre> <p>All of the constants I use in my DB class are defined here:</p> <pre><code>package org.data.agroassistant; import android.provider.BaseColumns; public interface Constants extends BaseColumns { public static final int FARMER_SEARCH = 0; public static final int FARM_SEARCH = 1; public static final int CROP_SEARCH = 2; public static final int PRICE_SEARCH = 3; /*====DATABASE CONSTANTS====================== * Constants used by the database adapter class */ public static final String DATABASE_NAME = "agroassistant"; public static final String FARMERS_TABLE= "farmers"; public static final String FARMS_TABLE= "farms"; //Columns in the Farmers table public static final String FARMER_ID = "farmerid"; public static final String FARMER_FNAME = "firstname"; public static final String FARMER_LNAME = "lastname"; public static final String FARMER_SIZE = "farmersize"; //Columns in the Farms table public static final String FARM_ID = "farmid"; public static final String FARM_FARMER_ID = "farmerid"; public static final String FARM_SIZE = "propertysize"; public static final String FARM_PARISH = "parish"; public static final String FARM_EXTENSION = "extension"; public static final String FARM_DISTRICT = "district"; public static final String FARM_LAT = "xcoord"; public static final String FARM_LONG = "ycoord"; public static final String[] FROM_FARMERS = {_ID, FARMER_ID, FARMER_FNAME, FARMER_LNAME, FARMER_SIZE}; public static final String[] FROM_FARMS = {_ID, FARM_ID, FARM_FARMER_ID, FARM_SIZE, FARM_PARISH, FARM_EXTENSION, FARM_DISTRICT, FARM_LAT, FARM_LONG}; public static final String FROM_S_FARMERS = _ID + ", " + FARMER_ID + ", " + FARMER_FNAME + ", " + FARMER_LNAME + ", " + FARMER_SIZE; public static final String FROM_S_FARMS = _ID + ", " + FARM_ID + ", " + FARM_FARMER_ID + ", " + FARM_SIZE + ", " + FARM_PARISH + ", " + FARM_EXTENSION + ", " + FARM_DISTRICT + ", " + FARM_LAT + ", " + FARM_LONG; } </code></pre> <p>Here is the listactivity that attemps to use the db helper object.</p> <pre><code>package org.data.agroassistant; import static org.data.agroassistant.Constants.*; import java.util.Arrays; import android.app.ListActivity; import android.database.Cursor; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.ListView; import android.widget.SimpleCursorAdapter; public class ResultView extends ListActivity { private AgroAssistantDB agroDB; private Cursor resultsCursor; private int searchType; private String searchParams; private static final int[] FARMER_TO = {R.id.txt_farmer_id, R.id.txt_farmer_fname, R.id.txt_farmer_lname}; /* * @see android.app.Activity#onCreate(android.os.Bundle) * @Intent Params: * (int) searchType Type of search performed {Farmer,Farm,Crop,Price} * (String) searchParams String of WHERE clause for query */ @Override protected void onCreate(Bundle savedInstanceState) { // TODO Auto-generated method stub super.onCreate(savedInstanceState); setContentView(R.layout.result_search); Bundle b = getIntent().getExtras(); agroDB = new AgroAssistantDB(this); //searchType = b.getInt("searchType"); //final String searchResponse = b.getString("searchResponse"); //final String searchParams = b.getString("searchParams"); /* * Need * Cursor * CursorAdapter * SQL query (params) * SQL (from) */ searchType = FARMER_SEARCH; searchParams = FARMER_ID + "=1102018039"; try { switch(searchType) { case(FARMER_SEARCH): //resultsCursor = agroDB.rawQuery(FARMERS_TABLE, FROM_S_FARMERS, searchParams); break; case (FARM_SEARCH): resultsCursor = agroDB.rawQuery(FARMS_TABLE, FROM_S_FARMS, searchParams); break; case (CROP_SEARCH): resultsCursor = agroDB.rawQuery(CROPS_TABLE, "FROM_S_CROPS", searchParams); break; case (PRICE_SEARCH): resultsCursor = agroDB.rawQuery(PRICES_TABLE, "FROM_PRICES", searchParams); break; default: break; } resultsCursor = agroDB.getFarmers(); startManagingCursor(resultsCursor); showResults(resultsCursor); } finally { //agroDB.close(); } } private void showResults(Cursor cursor){ SimpleCursorAdapter results = null; switch(searchType) { case(FARMER_SEARCH): ListView list = getListView(); Cursor cursorSelect = (Cursor) list.getItemAtPosition(0); cursor.getColumnNames(); Log.d("AgroAssistant", "showResults: Cusor contains " + cursor.getCount() + " record(s)"); Log.d("AgroAssistant", "showResults: Cusor contains " + cursor.getColumnCount() + " column(s)"); Log.d("AgroAssistant", "showResults: Cusor contains " + Arrays.toString(cursor.getColumnNames())); /* Log.d("AgroAssistant", "ID " + cursorSelect.getString(cursor.getColumnIndex(_ID))); Log.d("AgroAssistant", "FID " + cursorSelect.getString(cursor.getColumnIndex("farmerid"))); Log.d("AgroAssistant", "FNAME " + cursorSelect.getString(cursor.getColumnIndex("fname"))); Log.d("AgroAssistant", "LNAME " + cursorSelect.getString(cursor.getColumnIndex(FARMER_LNAME))); */ results = new SimpleCursorAdapter(this, R.layout.farmer_row, cursor, new String[] {FARMER_ID, FARMER_FNAME, FARMER_LNAME}, FARMER_TO); break; case (FARM_SEARCH): //results = new SimpleCursorAdapter(this, R.layout.farm_row, cursor, new String[] {FEED_TITLE}, TO); break; case (CROP_SEARCH): //results = new SimpleCursorAdapter(this, R.layout.crop_row, cursor, new String[] {FEED_TITLE}, TO); break; case (PRICE_SEARCH): //results = new SimpleCursorAdapter(this, R.layout.price_row, cursor, new String[] {FEED_TITLE}, TO); break; default: break; } setListAdapter(results); } </code></pre> <p>Any thoughts or assistance would be GREATLY appreciated.</p> <p>*EDIT: I pulled the sqlite db off the phone and opened it in a sqlite browser. All of the records entered were there. Moreover, the queries worked when written in the browser view.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload