Java Development

[anta40]

This is the extract of my sqlite db handling routine

import java.io.IOException;
import java.io.UnsupportedEncodingException;

import javax.microedition.io.Connector;
import javax.microedition.io.file.FileConnection;

import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.DataTypeException;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseException;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.DatabaseIOException;
import net.rim.device.api.database.DatabaseSecurityOptions;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.MalformedURIException;
import net.rim.device.api.io.URI;

public class AlbumDB {

	private static AlbumDB instance = null;
	private String DB_NAME = "audio.db";
	private final String DB_PATH = "file:///SDCard/BlackBerry/documents/" + DB_NAME;
	
	public static AlbumDB getInstance(){
		if (instance == null) instance = new AlbumDB();
		return instance;
	}
	
	public void init(){
		FileConnection fConn = null;
		Database db = null;
		
		try {
			fConn = (FileConnection) Connector.open(DB_PATH);
			
			if (!fConn.exists()) {
				URI uri = URI.create(DB_PATH); 
				db = DatabaseFactory.create(uri);
				db.beginTransaction();
				Statement st;
				
				st = db.createStatement("CREATE TABLE 'tbl_audio' ( 'item_id' TEXT, 'item_name' TEXT, 'item_type' TEXT, 'item_key' TEXT )");
				st.prepare();
				st.execute();
				db.commitTransaction();
				db.close();
			}
		}
		catch (Exception e){
			System.out.println("initDatabase error: " + e.getMessage());
		}
		finally {
			try {
				if (fConn != null) fConn.close();
				if (db != null) db.close();
			}
			catch (IOException ioe){
				System.out.println("IOException: "+ioe.getMessage());
			}
			catch (DatabaseIOException dbio){
				System.out.println("initDB error: "+dbio.getMessage());
			}
		}
	}
	
	public String get_item_key(String item_name){
		String result = null;
		Statement stmt = null;
		Database db = null;
		
		try {
			URI uri = URI.create(DB_PATH);

			db = DatabaseFactory.open(uri);
			stmt = db.createStatement(query);
			
			if (stmt != null)  {
				stmt.prepare();
				
				Cursor cursor = stmt.getCursor();
				Row row = null;
				
				while (cursor.next()){
					row = cursor.getRow();
				}
				
				if (row == null) {
					result = null;
				}
				else {
					result = row.getString(0);
				}
			}
			stmt.close();
			db.commitTransaction();
			db.close();
		}
		catch (DatabaseIOException dbioe){
			System.out.println("DatabaseIOException: "+dbioe.getMessage());
		}
		catch (MalformedURIException me){
			System.out.println("MaltformedURIException: "+me.getMessage());
		}
		catch (DatabaseException dbe){
			System.out.println("DatabaseException: "+dbe.getMessage());
		}
		catch (DataTypeException dte){
			System.out.println("DataTypeException: "+dte.getMessage());
		}
		
		return result;
	}
}

Every time the application is run, then the init() method will always be called first.

The problem is every time get_item_key() called for the first time, I will get this:

DatabaseException: : SQL logic error or missing database

And if I call get_item_key() once again, I will get this:

DatabaseIOException: File system error (12)

Regarding the DatabaseException, I'm not sure what is the cause. I think the query is proper.

And regarding the DatabaseException, I'm also not sure. The database is always closed right after the transaction is commited.

Any help?

[anta40]

Ooops, I forgot to put stmt.execute() in the get_item_key method(), right after stmpt.prepare().

Doesn't matter, because those 2 exceptions still occur.

[anta40]

Well, it seems that the culprit is the missing db.beginTransaction();

D'oh. How could I miss such thing?