SQLite - select * from table returns 0 rows

Question

I am inserting dummy data into the table and then retrieving it.

Here is my code:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_record);

    mDbAdapter = DatabaseHelper.get(getApplicationContext()).getDbAdapter();

    insertDummyData();

    recentPostsArrayList = mDbAdapter
            .getRecords(Const.RECENT_POSTS_TABLE_NAME);

    listViewRecentPosts = (ListView) findViewById(R.id.record_listView_recent_posts);
    listViewRecentPosts.setAdapter(new RecentPostsAdapter());
}

insertDummyData():

private void insertDummyData() {

    Item item;

    mDbAdapter.beginTransaction();
    mDbAdapter.clearTable(Const.RECENT_POSTS_TABLE_NAME);

    item = new Item();
    item.setAuthor("Priyanka");
    item.setTitle("Priyanka");
    item.setComments("Awesome");
    item.setDate("11-12-2014");
    item.setId("1");
    item.setImagepath("image1");
    item.setLink("");
    item.setRatings("5");
    mDbAdapter.insertRow(Const.RECENT_POSTS_TABLE_NAME, item);

    item = new Item();
    item.setAuthor("Jackson");
    item.setTitle("Jackson");
    item.setComments("Super");
    item.setDate("11-12-2014");
    item.setId("2");
    item.setImagepath("image2");
    item.setLink("");
    item.setRatings("4");
    mDbAdapter.insertRow(Const.RECENT_POSTS_TABLE_NAME, item);

    item = new Item();
    item.setAuthor("Sami");
    item.setTitle("Sami");
    item.setComments("Good");
    item.setDate("11-12-2014");
    item.setId("3");
    item.setImagepath("image3");
    item.setLink("");
    item.setRatings("3");
    mDbAdapter.insertRow(Const.RECENT_POSTS_TABLE_NAME, item);


    mDbAdapter.endTransaction();

}

getRecords():

public List<Item> getRecords(String tableName) {

    Item item;
    List<Item> items = new ArrayList<Item>();

    String query = "SELECT * FROM " + tableName;
    c = super.query(query);

    c.moveToFirst();

    do {

        item = new Item();
        item.setAuthor(c.getString(c
                .getColumnIndex(Const.TableColumns.AUTHOR_KEY)));
        item.setComments(c.getString(c
                .getColumnIndex(Const.TableColumns.COMMENTS_KEY)));
        item.setDate(c.getString(c
                .getColumnIndex(Const.TableColumns.DATE_KEY)));
        item.setId(c.getString(c.getColumnIndex(Const.TableColumns.ID_KEY)));
        item.setImagepath(c.getString(c
                .getColumnIndex(Const.TableColumns.IMAGEPATH_KEY)));
        item.setLink(c.getString(c
                .getColumnIndex(Const.TableColumns.LINK_KEY)));
        item.setRatings(c.getString(c
                .getColumnIndex(Const.TableColumns.RATINGS_KEY)));
        item.setTitle(c.getString(c
                .getColumnIndex(Const.TableColumns.TITLE_KEY)));

        items.add(item);

    } while (c.moveToNext());

    return items;

}

Here is my Logcat:

12-11 12:49:29.687: D/xx(23290): Table recentposts cleared
12-11 12:49:29.688: D/xx(23290): Row inserted into table recentposts
12-11 12:49:29.689: D/xx(23290): Row inserted into table recentposts
12-11 12:49:29.689: D/xx(23290): Row inserted into table recentposts
12-11 12:49:29.690: D/xx(23290): End Transaction
12-11 12:49:29.695: D/xx(23290): Query 'SELECT * FROM recentposts' returned 0 rows

What am i doing wrong?

Answer 1

Is it possible that your forgot the ;?

I see in log:

  Query 'SELECT * FROM recentposts'

and your code is:

String query = "SELECT * FROM " + tableName;

You can try:

String query = "SELECT * FROM " + tableName + ";";

I hope that it helps.

Answer 2

You are using a transaction and do not call setTransactionSuccessful()

private void insertDummyData() {

    Item item;

    mDbAdapter.beginTransaction();
    mDbAdapter.clearTable(Const.RECENT_POSTS_TABLE_NAME);

    item = new Item();
    item.setAuthor("Priyanka");
    item.setTitle("Priyanka");
    item.setComments("Awesome");
    item.setDate("11-12-2014");
    item.setId("1");
    item.setImagepath("image1");
    item.setLink("");
    item.setRatings("5");
    mDbAdapter.insertRow(Const.RECENT_POSTS_TABLE_NAME, item);

    item = new Item();
    item.setAuthor("Jackson");
    item.setTitle("Jackson");
    item.setComments("Super");
    item.setDate("11-12-2014");
    item.setId("2");
    item.setImagepath("image2");
    item.setLink("");
    item.setRatings("4");
    mDbAdapter.insertRow(Const.RECENT_POSTS_TABLE_NAME, item);

    item = new Item();
    item.setAuthor("Sami");
    item.setTitle("Sami");
    item.setComments("Good");
    item.setDate("11-12-2014");
    item.setId("3");
    item.setImagepath("image3");
    item.setLink("");
    item.setRatings("3");
    mDbAdapter.insertRow(Const.RECENT_POSTS_TABLE_NAME, item);

    mDBAdapter.setTransactionSuccessfull();  //Mark the transaction as successfull 
    mDbAdapter.endTransaction();

}

Answer 3

Add this mDbAdapter.setTransactionSuccessful(); before mDbAdapter.endTransaction();