problems with my SQLiteOpenHelper

Question

package com.owen.quartergames.dao;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;

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;
import android.widget.Toast;

import com.owen.quartergames.R;
import com.owen.quartergames.domain.LogEntry;

public class SqlLiteFishLoggerDao extends SQLiteOpenHelper implements
        FishLoggerDao {

    private static final String DB_NAME = "fishingLog";

    private static final String TABLE_NAME = "LogEntries";

    private static final String DELETE_LOG_ENTRY_SQL = "DELETE FROM LogEntries WHERE _id = ?;";

    private static final String FIND_LOG_ENTRY_SQL = "SELECT _id, Longitude, Latitude FROM LogEntries WHERE _id = ?";

    private static final String FIND_ALL_ENTRIES_SQL = "SELECT * FROM LogEntries";

    private static final String[] NO_ARGS = {};

    private Context context;

    private final SQLiteDatabase db = getWritableDatabase();

    public SqlLiteFishLoggerDao(Context context) {
        super(context, DB_NAME, null, 1);
        this.context = context;
    }

    @Override
    public void deleteLogEntry(String id) {
        id = "0";

        db.execSQL(DELETE_LOG_ENTRY_SQL, new Object[] { id });
        // int deleted = db.delete(TABLE_NAME, "_id = ?",
        // new String[] { id.trim() });
        // Log.i("fishlogger", String.format("Delete %d rows", deleted));
        db.close();
    }

    @Override
    public LogEntry findEntry(String id) {
        Cursor cursor = db.rawQuery(FIND_LOG_ENTRY_SQL, new String[] { id });
        if (!cursor.moveToFirst()) {
            return null;
        }

        LogEntry entry = new LogEntry();
        entry.setId(id);
        entry.setLatitude(cursor.getDouble(cursor.getColumnIndex("Latitude")));
        entry
                .setLongitude(cursor.getDouble(cursor
                        .getColumnIndex("Longitude")));
        cursor.close();
        db.close();
        return entry;

    }

    @Override
    public void insertLogEntry(LogEntry entry) {
        ContentValues values = new ContentValues();
        values.put("Latitude", entry.getLatitude());
        values.put("Longitude", entry.getLongitude());
        values.put("PictureURL", entry.getPictureUrl());
        values.put("SizeOrWeight", entry.getSizeOrWeight());
        values.put("CreateDate", entry.getEntryDate());
        values.put("Species", entry.getSpecies());
        db.insertOrThrow("LogEntries", null, values);
        db.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String s;
        try {
            Toast.makeText(context, "1", 2000).show();
            InputStream in = context.getResources().openRawResource(R.raw.sql);
            DocumentBuilder builder = DocumentBuilderFactory.newInstance()
                    .newDocumentBuilder();
            Document doc = builder.parse(in, null);
            NodeList statements = doc.getElementsByTagName("statement");
            for (int i = 0; i < statements.getLength(); i++) {
                s = statements.item(i).getChildNodes().item(0).getNodeValue();
                db.execSQL(s);
            }
        } catch (Throwable t) {
            Toast.makeText(context, t.toString(), 50000).show();
        }
        Log.e("DB", "DB Created");

    }

    @Override
    public List<LogEntry> findAllEntries() {

        List<LogEntry> entries = new ArrayList<LogEntry>();

        Cursor cursor = db.rawQuery(FIND_ALL_ENTRIES_SQL, NO_ARGS);

        int entryDateCol = cursor.getColumnIndex("CreateDate");
        int speciesCol = cursor.getColumnIndex("Species");
        int sizeCol = cursor.getColumnIndex("SizeOrWeight");
        int latCol = cursor.getColumnIndex("Latitude");

        if (cursor.moveToFirst()) {
            do {
                LogEntry entry = new LogEntry();
                entry.setEntryDate(cursor.getString(entryDateCol));
                entry.setSpecies(cursor.getString(speciesCol));
                entry.setSizeOrWeight(cursor.getString(sizeCol));
                entry.setLatitude(cursor.getDouble(latCol));

                if (entry.getSpecies() == null) {
                    entry.setSpecies("Not Entered");
                }

                if (entry.getSizeOrWeight() == null) {
                    entry.setSizeOrWeight("Not entered");
                }

                entries.add(entry);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return entries;
    }

    @Override
    public void onUpgrade(SQLiteDatabase DB, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(getWritableDatabase());
    }
}

basically the delete doesnt work however i think the problems stems from the findAllEntries() method. the reason i say that is that i get "leak found" errors in log cat saying the db was created but never closed. i have throughly searched the internet and have asked friends who code but no luck in solving the problem. all my cursors are closed and from what i can tell this is how a sqlliteopenhelper should generally look like.

thanks for any responses

score 1 · Answer 1 · answered Apr 11 '11 at 15:33

1

Your getReadableDatabase() opens a DB for read. To avoid leaks, you need to close it after your work is done.

final db = getReadableDatabase();
//do the things
db.close();

The same with getWritableDatabase().

answered Apr 11 '11 at 15:33

ernazm

9,208
4
44
51

I strongly recommend to store the db in a member variable to prevent calling the getter every time... – WarrenFaith Apr 11 '11 at 15:36
Can you explain please? I assume we need to open-close db every time we need it. Do you propose to keep it open or did i misunderstand you? `getReadableDatabase()` isn't just a getter, it opens or creates a db. – ernazm Apr 11 '11 at 15:46
thank you that does seem to have fixed the leaks but the delete statement still doesnt work. any ideas? – owen gerig Apr 11 '11 at 15:47
ive changed the code to reflect the new changes. i wanted to add that i know the rows are there my app does successfully display the db and i know for instance that there is a table at id 0. – owen gerig Apr 11 '11 at 16:03
[link]http://stackoverflow.com/questions/4660363/android-sqlite-log-reports-and-db-close[/link] according to this though im not suppose to close the db this way. – owen gerig Apr 11 '11 at 16:16
You should either add `openWritableDatabase()` to your `deleteLogEntry` or remove `close` from it. – ernazm Apr 12 '11 at 07:30

score 0 · Answer 2 · answered Apr 11 '11 at 15:49

0

As mentionned in SQLiteOpenHelper.getWritableDatabase javadoc :

Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. (Make sure to call close() when you no longer need the database.)

answered Apr 11 '11 at 15:49

david

1,311
12
32

does calling getwritabledatabase work if it gets called again after being closed once. for instance i call it to find all entries, then close it, then make another call to the delete entry. this should reopen the db for the edit right? – owen gerig Apr 11 '11 at 15:54
well after reading that doc a few times i guess the answer to my question is no. but then how do i know where to put the close statement. sorry posted this before seeing ur update. thanks! – owen gerig Apr 11 '11 at 16:24

score 0 · Accepted Answer · answered Apr 12 '11 at 16:50

just wanted to followup. i thank you all for your help. in the end it seems there were a few issues. Theres very little chance u guys could have figured it out on ur own with what i gave u it took me forever to figure it out.
anyways heres what i did wrong plus the updated code:

I wasnt opening and closing the DB for every method (leaks found errors)
findallentries was not feeding the _id field into the object. and so when my listview was populated and u said delete, it would use the iterator id as opposed to the real database _id of the entry.
i needed to call .trim on my id field

(both 2 and 3 however produced no errors in logcat. it would just never delete. it inserted fine, select * fine, but nothing happened on delete)

package com.owen.quartergames.dao;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;

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;

import com.owen.quartergames.R;
import com.owen.quartergames.domain.LogEntry;

public class SqlLiteFishLoggerDao extends SQLiteOpenHelper implements
        FishLoggerDao {

    private static final String DB_NAME = "fishingLog";

    private static final String TABLE_NAME = "LogEntries";

    private static final String DELETE_LOG_ENTRY_SQL = "DELETE FROM LogEntries WHERE _id = ?;";

    private static final String FIND_LOG_ENTRY_SQL = "SELECT _id, Longitude, Latitude FROM LogEntries WHERE _id = ?";

    private static final String FIND_ALL_ENTRIES_SQL = "SELECT * FROM LogEntries";

    private static final String[] NO_ARGS = {};

    private Context context;

    private SQLiteDatabase DB;

    public SqlLiteFishLoggerDao(Context context) {
        super(context, DB_NAME, null, 1);
        this.context = context;
    }

    @Override
    public void deleteLogEntry(String id) {
        DB = getWritableDatabase();
        DB.execSQL(DELETE_LOG_ENTRY_SQL, new Object[] { id });
        DB.close();
    }

    @Override
    public LogEntry findEntry(String id) {
        DB = getReadableDatabase();
        Cursor cursor = DB.rawQuery(FIND_LOG_ENTRY_SQL,
                new String[] { id });
        if (!cursor.moveToFirst()) {
            return null;
        }

        LogEntry entry = new LogEntry();
        entry.setId(id);
        entry.setLatitude(cursor.getDouble(cursor.getColumnIndex("Latitude")));
        entry
                .setLongitude(cursor.getDouble(cursor
                        .getColumnIndex("Longitude")));
        cursor.close();
        DB.close();
        return entry;

    }

    @Override
    public void insertLogEntry(LogEntry entry) {
        DB = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("Latitude", entry.getLatitude());
        values.put("Longitude", entry.getLongitude());
        values.put("PictureURL", entry.getPictureUrl());
        values.put("SizeOrWeight", entry.getSizeOrWeight());
        values.put("CreateDate", entry.getEntryDate());
        values.put("Species", entry.getSpecies());
        DB.insertOrThrow("LogEntries", null, values);
        DB.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String s;
        try {
            InputStream in = context.getResources().openRawResource(R.raw.sql);
            DocumentBuilder builder = DocumentBuilderFactory.newInstance()
                    .newDocumentBuilder();
            Document doc = builder.parse(in, null);
            NodeList statements = doc.getElementsByTagName("statement");
            for (int i = 0; i < statements.getLength(); i++) {
                s = statements.item(i).getChildNodes().item(0).getNodeValue();
                db.execSQL(s);
            }
            Log.e("DB", "DB Created Successfully");
        } catch (Throwable t) {
            Log.e("DB error: ",t.toString());
        }
    }

    @Override
    public List<LogEntry> findAllEntries() {
        DB = getReadableDatabase();

        List<LogEntry> entries = new ArrayList<LogEntry>();

        Cursor cursor = DB.rawQuery(FIND_ALL_ENTRIES_SQL,
                NO_ARGS);

        int entryID = cursor.getColumnIndex("_id");
        int entryDateCol = cursor.getColumnIndex("CreateDate");
        int speciesCol = cursor.getColumnIndex("Species");
        int sizeCol = cursor.getColumnIndex("SizeOrWeight");
        int latCol = cursor.getColumnIndex("Latitude");

        if (cursor.moveToFirst()) {
            do {
                LogEntry entry = new LogEntry();
                entry.setId(cursor.getString(entryID));
                entry.setEntryDate(cursor.getString(entryDateCol));
                entry.setSpecies(cursor.getString(speciesCol));
                entry.setSizeOrWeight(cursor.getString(sizeCol));
                entry.setLatitude(cursor.getDouble(latCol));

                if (entry.getSpecies() == null) {
                    entry.setSpecies("Not Entered");
                }

                if (entry.getSizeOrWeight() == null) {
                    entry.setSizeOrWeight("Not entered");
                }

                entries.add(entry);
            } while (cursor.moveToNext());
        }
        cursor.close();
        DB.close();
        return entries;
    }

    @Override
    public void onUpgrade(SQLiteDatabase DB, int oldVersion, int newVersion) {
        DB = getWritableDatabase();
        DB.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(getWritableDatabase());
        DB.close();
    }
}

problems with my SQLiteOpenHelper

3 Answers3

Linked