• After 15+ years, we've made a big change: Android Forums is now Early Bird Club. Learn more here.

[REQUEST] Simple SQLite app

A

Amanoo

Newbie
Hello. If you don't want to read useless background info, just skip to the bold letters.
After having failed countless times, I'm starting to give up. I followed this tutorial, and tried to adapt it for my own project. However, as soon as I start adding and renaming columns, things start to get wrong. I need to open a certain SQLite database and read data from it, but even creating it in this app and trying to then add entries to it won't work as soon as I start meddling around with the source code, and I can't see where I go wrong. I haven't even gotten to opening a file.

If anyone could make a very simple application for me just to help me get started and give the source code, I should be able to figure out what I'm doing wrong and how to continue. Basically a strategy that has often worked for me, if you can't figure out the answer yourself (and I tried very hard), get the right answer from somewhere else and analyse it until you understand what's going on.

So, what the app I want to request must do. First, it must be able to work for 2.1 or higher, but AFAIK, 2.1 should have all required functions already. I do want it to have Google's modern standards for interfaces, but I should be able to port it to a code that compiles against 4.1. It shouldn't be much of a big deal if it compiles against 2.1.
I want it to be able open a database that is contained in a .bin file. I attached it in the trust that it will not be abused, though there shouldn't be much that can be abused in here. It contains a few tables, the most important of which is ZDBO_VERZEN. It needs to read at least this table, being able to read, or even edit, the others as well would be very nice if you feel ambitious, but the minimum is reading the ZDBO_VERZEN table. This table contains several columns. According to the SQLite Database browser I downloaded their names and types are the following, in order:
Z_PK - - - - - - - - - - - - - - - - INTEGER PRIMARY KEY
Z_ENT - - - - - - - - - - - - - - - INTEGER (this column is, strangely, completely empty)
Z_OPT - - - - - - - - - - - - - - - INTEGER (this column is also completely empty, but both are present)
ZBNUMBER - - - - - - - - - - - - - INTEGER
ZCNUMBER - - - - - - - - - - - - - INTEGER
ZVNUMBER - - - - - - - - - - - - - INTEGER
ZID - - - - - - - - - - - - - - - - - INTEGER
ZVERZENTOBIJBELBOEKEN - - -- - INTEGER
ZVERS - - - - - - - -- - - - - - - - VARCHAR

If the app can read the data from these columns, inside of the specified table, from a certain file, I would be extremely happy. If it were to be made so that complete CRUD can be performed on the ZDBO_VERZEN and ZDBO_BIJBELBOEKEN tables and so that the Z_PRIMARYKEY table could be read, it would be perfection.
Anyone who gives me the source code of such an app will get a virtual cookie, and my most honest gratitude for helping an ignorant rookie along his way to less ignorance.
 

Attachments

I have a simple SQLite class that can create tables, read data, write data, update data, getHighestId etc. Would you like to have them?(:
Also, I will provide usage on how to use them, and I promise it won't be hard for you to learn.
 
It is very much different from what I had in mind, but if you can help me to adapt your project to finally work with my tables, I'd be overjoyed. Would finally enable me to do it more often, too. My current attempts to do it have been utter failures. I can download sources that look very obvious, but when I try adapting it for different tables, it goes all wrong.
 
What I meant to request was a program that was already completely compatible with my database. Nontheless, I will see what I can do with the example code you provided.
 
What I meant is: adapted for my database. Meaning that the java code tries to read a table with the same columns as mine. This database as columns such as "name", a column not present in mine. So far I haven't been able to adapt any other example code yet. Haven't tried anything with this example code yet.

EDIT: your example code is even filled with references to nonexistent constants and variables. It is full of errors. This makes adapting it even harder for me, having to fix the code before I can even change it to use my table structure.
 
Amanoo said:
What I meant is: adapted for my database. Meaning that the java code tries to read a table with the same columns as mine. This database as columns such as "name", a column not present in mine. So far I haven't been able to adapt any other example code yet. Haven't tried anything with this example code yet.

EDIT: your example code is even filled with references to nonexistent constants and variables. It is full of errors. This makes adapting it even harder for me, having to fix the code before I can even change it to use my table structure.
Click to expand...
There's no error in it. I'm using it for many of my project. It's a CLASS. That means you have to change the variables yourself to your own database.

I hope someone else will be able to help you out. Cheers(:
 
With a lot of trouble I managed to make the following three classes:

Code: 
package nl.psssoftware.mannashaker;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.TextView;

public class MainActivity extends Activity {
	/** Called when the activity is first created. */
	TextView demot;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		demot = (TextView) findViewById(R.id.demot);
		BibleDAO bibleDAO = new BibleDAO(this);
		bibleDAO.createVerse(1, 1, 1, 1, 1, "Hallo");
		bibleDAO.createVerse(2, 2, 3, 4, 2, "Hfvw efgveallo");
		bibleDAO.createVerse(6, 3, 5, 6, 6, "WRGrwHallo");
		String[] cols = new String[] { BibleDAO.Z_PK, BibleDAO.Z_ENT,
				BibleDAO.Z_OPT, BibleDAO.ZBNUMBER, BibleDAO.ZCNUMBER,
				BibleDAO.ZVNUMBER, BibleDAO.ZID,
				BibleDAO.ZVERZENTOBIJBELBOEKEN, BibleDAO.ZVERS };
		Cursor mCursor = bibleDAO.database.query(true,
				BibleDAO.TABLE_ZDBO_VERZEN, cols, null, null, null, null, null,
				null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		demot.setText(mCursor.getString(0) + "" + mCursor.getString(1) + ""
				+ mCursor.getString(2) + "" + mCursor.getString(3) + ""
				+ mCursor.getString(4) + "" + mCursor.getString(5) + ""
				+ mCursor.getString(6) + "" + mCursor.getString(7) + ""
				+ mCursor.getString(8));
	}

}
Code: 
package nl.psssoftware.mannashaker;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * A subclass of SQLiteOpenHelper used to get readable or writable database of
 * our app
 * 
 * @author 712806
 * 
 */
public class DatabaseHelper extends SQLiteOpenHelper {

	
	
	
	private static final String DATABASE_NAME = "b_EN";

	private static final int DATABASE_VERSION = 2;

	// Database creation sql statement
	private static final String DATABASE_CREATE = "CREATE TABLE ZDBO_VERZEN ("
			+ BibleDAO.Z_PK + " INTEGER PRIMARY KEY, " + BibleDAO.Z_ENT
			+ " INTEGER, " + BibleDAO.Z_OPT + " INTEGER, " + BibleDAO.ZBNUMBER
			+ " INTEGER, " + BibleDAO.ZCNUMBER + " INTEGER, "
			+ BibleDAO.ZVNUMBER + " INTEGER, " + BibleDAO.ZID + " INTEGER, "
			+ BibleDAO.ZVERZENTOBIJBELBOEKEN + " INTEGER, " + BibleDAO.ZVERS
			+ " VARCHAR )";

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Method is called during creation of the database
	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(DATABASE_CREATE);
	}

	// Method is called during an upgrade of the database,
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion,
			int newVersion) {
		Log.w(DatabaseHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS  "+BibleDAO.TABLE_ZDBO_VERZEN);
		onCreate(database);
	}
}
Code: 
package nl.psssoftware.mannashaker;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

public class BibleDAO {

	private DatabaseHelper dbHelper;

	SQLiteDatabase database;
	/**
	 * Bible table related constants.
	 */
	public final static String TABLE_ZDBO_VERZEN = "ZDBO_VERZEN";

	public final static String Z_PK = "Z_PK";
	public final static String Z_ENT = "Z_ENT";
	public final static String Z_OPT = "Z_OPT";
	public final static String ZBNUMBER = "ZBNUMBER";
	public final static String ZCNUMBER = "ZCNUMBER";
	public final static String ZVNUMBER = "ZVNUMBER";
	public final static String ZID = "ZID";
	public final static String ZVERZENTOBIJBELBOEKEN = "ZVERZENTOBIJBELBOEKEN";
	public final static String ZVERS = "ZVERS";

	/**
	 * 
	 * @param context
	 */
	public BibleDAO(Context context) {
		dbHelper = new DatabaseHelper(context);
		database = dbHelper.getWritableDatabase();
	}

	public BibleDAO(Context context, SQLiteDatabase db) {
		dbHelper = new DatabaseHelper(context);
		database = db;
	}

	public long createVerse(int zbnumber, int zcnumber, int zvnumber, int zid,
			int zverzentobijbelboeken, String zvers) {
		ContentValues values = new ContentValues();
		values.put(ZBNUMBER, zbnumber);
		values.put(ZCNUMBER, zcnumber);
		values.put(ZVNUMBER, zvnumber);
		values.put(ZID, zid);
		values.put(ZVERZENTOBIJBELBOEKEN, zverzentobijbelboeken);
		values.put(ZVERS, zvers);
		return database.insert(TABLE_ZDBO_VERZEN, null, values);
	}

	public int updateVerse(int zbnumber, int zcnumber, int zvnumber, int zid,
			int zverzentobijbelboeken, String zvers, String id) {
		ContentValues values = new ContentValues();
		values.put(ZBNUMBER, zbnumber);
		values.put(ZCNUMBER, zcnumber);
		values.put(ZVNUMBER, zvnumber);
		values.put(ZID, zid);
		values.put(ZVERZENTOBIJBELBOEKEN, zverzentobijbelboeken);
		values.put(ZVERS, zvers);
		return database.update(TABLE_ZDBO_VERZEN, values, Z_PK + "=?",
				new String[] { id });
	}

}

It creates a database, puts some stuff in it, and reads it. The created database should have the exact same structure as the one I want to read. Only problem is that I'm still failing at the part where I need to open the file. Currently this version has no code for it. On eof the databse files that need to be opened has already been included in the first post. Can anyone help me with this?
 
The code posted here does not work properly: Using your own SQLite database in Android applications | ReignDesign

These are my classes:
Code: 
package nl.psssoftware.mannashaker;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper{
 
    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/nl.psssoftware.mannashaker/databases/";
 
    private static String DB_NAME = "b_EN.bin";
 
    private SQLiteDatabase myDataBase; 
 
    private final Context myContext;
 
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) {
 
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
    }	
 
  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){
    		//do nothing - database already exist
    	}else{
 
    		//By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
        	this.getReadableDatabase();
 
        	try {
 
    			copyDataBase();
 
    		} catch (IOException e) {
 
        		throw new Error("Error copying database");
 
        	}
    	}
 
    }
 
    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    	}catch(android.database.sqlite.SQLiteException e){
 
    		//database does't exist yet.
 
    	}
 
    	if(checkDB != null){
 
    		checkDB.close();
 
    	}
 
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{
 
    	//Open your local db as the input stream
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
 
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
 
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
 
    public void openDataBase() throws SQLException{
 
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    }
 
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();
 
    	    super.close();
 
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
 
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
 
        // Add your public helper methods to access and get content from the database.
       // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
       // to you to create adapters for your views.
 
}

Code: 
package nl.psssoftware.mannashaker;

import java.io.IOException;

import android.app.Activity;
import android.database.Cursor;
import android.database.SQLException;
import android.os.Bundle;
import android.widget.TextView;

public class MainActivity extends Activity {
	/** Called when the activity is first created. */
	TextView demot;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		demot = (TextView) findViewById(R.id.demot);

		DataBaseHelper myDbHelper = new DataBaseHelper(this);
		myDbHelper = new DataBaseHelper(this);

		try {

			myDbHelper.createDataBase();

		} catch (IOException ioe) {

			throw new Error("Unable to create database");

		}

		try {

			myDbHelper.openDataBase();

		} catch (SQLException sqle) {

			throw sqle;

		}
	}

}

I stubbornly tried this method, although it said the database should include an android_metadata table. I get the following error: sqlite returned: error code = 1, msg = no such table: android_metadata, db=/data/data/nl_psssoftware_mannashaker/databases/b_EN_bin
I guess I was just being stubborn. Obviously, my database HAS NO android_metadata, and it is never going to get one either. Including such such a table is an impossibility. I don't really understand why this code is still trying to read it. I only want it to read the tables I tell it to read, not some random tables it thinks should be present. Can't I just open it somehow without having the bloody system trying to read it?
I repeat, I CANNOT add ANY tables. What is being read are databases from an already existent system and we cannot completely rebuild this system just to include Android.
 
It didn't change a single thing.

EDIT: Never mind, the example had to places where it tried to open the database. I only changed it in one place, so the second still bugged. Changing both fixed it...

EDIT: 2, I can now read a row from my database, so far so good, this is finally getting somewhere. I am still getting an Exception, though.

Code: 
07-24 19:52:25.705: W/CursorWindow(17109): Window is full: requested allocation 164 bytes, free space 24 bytes, window size 2097152 bytes
07-24 19:52:25.965: D/CLIPBOARD(17109): Hide Clipboard dialog at Starting input: finished by someone else... !
07-24 19:52:25.995: D/dalvikvm(17109): GC_CONCURRENT freed 232K, 10% free 15029K/16647K, paused 1ms+2ms
07-24 19:52:25.995: E/SQLiteDatabase(17109): close() was never explicitly called on database '/data/data/nl.psssoftware.mannashaker/databases/b_EN.bin' 
07-24 19:52:25.995: E/SQLiteDatabase(17109): android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:2064)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1118)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1075)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1051)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at nl.psssoftware.mannashaker.DataBaseCreator.openDataBase(DataBaseCreator.java:149)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at nl.psssoftware.mannashaker.DataBaseCreator.DataBaseCreateSequence(DataBaseCreator.java:49)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at nl.psssoftware.mannashaker.MainActivity.onCreate(MainActivity.java:17)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.Activity.performCreate(Activity.java:4562)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1053)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1934)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1995)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.ActivityThread.access$600(ActivityThread.java:128)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1161)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.os.Handler.dispatchMessage(Handler.java:99)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.os.Looper.loop(Looper.java:137)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at android.app.ActivityThread.main(ActivityThread.java:4514)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at java.lang.reflect.Method.invokeNative(Native Method)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at java.lang.reflect.Method.invoke(Method.java:511)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:993)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:760)
07-24 19:52:25.995: E/SQLiteDatabase(17109): 	at dalvik.system.NativeStart.main(Native Method)
07-24 19:52:26.000: E/System(17109): Uncaught exception thrown by finalizer
07-24 19:52:26.000: E/System(17109): java.lang.IllegalStateException: Don't have database lock!
07-24 19:52:26.000: E/System(17109): 	at android.database.sqlite.SQLiteDatabase.verifyLockOwner(SQLiteDatabase.java:2222)
07-24 19:52:26.000: E/System(17109): 	at android.database.sqlite.SQLiteDatabase$1.entryRemoved(SQLiteDatabase.java:2314)
07-24 19:52:26.000: E/System(17109): 	at android.database.sqlite.SQLiteDatabase$1.entryRemoved(SQLiteDatabase.java:2310)
07-24 19:52:26.000: E/System(17109): 	at android.util.LruCache.trimToSize(LruCache.java:197)
07-24 19:52:26.000: E/System(17109): 	at android.util.LruCache.evictAll(LruCache.java:285)
07-24 19:52:26.000: E/System(17109): 	at android.database.sqlite.SQLiteDatabase.deallocCachedSqlStatements(SQLiteDatabase.java:2275)
07-24 19:52:26.000: E/System(17109): 	at android.database.sqlite.SQLiteDatabase.closeClosable(SQLiteDatabase.java:1247)
07-24 19:52:26.000: E/System(17109): 	at android.database.sqlite.SQLiteDatabase.finalize(SQLiteDatabase.java:2035)
07-24 19:52:26.000: E/System(17109): 	at java.lang.Daemons$FinalizerDaemon.doFinalize(Daemons.java:185)
07-24 19:52:26.000: E/System(17109): 	at java.lang.Daemons$FinalizerDaemon.run(Daemons.java:168)
07-24 19:52:26.000: E/System(17109): 	at java.lang.Thread.run(Thread.java:856)

What is this? I don't want to close the database, do I? Do you want to close it when you're supposed to use it?
BTW, DataBaseCreator is what was originally DataBaseHelper. I already had another DatabaseHelper. I probably have to merge the two.
 
The cursor error wouldn't cause a crash. What is probably causing the crash is the database lock error.

Try removing the second line of this code since line two is duplicating what line one is doing:

DataBaseHelper myDbHelper = new DataBaseHelper(this);
myDbHelper = new DataBaseHelper(this);
 
You must log in or register to reply here.
Back
Top Bottom