Page 1 of 1

Portable databases for distribution as part of an app?

Posted: Sun Jan 07, 2007 6:24 pm
by Chris Corbyn
I've never had to do this before since I've always written apps that run on a server using PgSQL or MySQL. Now I'm writing an app in Java which is client-end but I need some sort of (albeit basic) DB for storage of place names, geographical coordinates and user preferences etc.

What do you use in this situation? MySQL, PgSQL are out of the window because they need to be installed on the machine using the app, or require remote access if not. Do you use DB2/berkeley or something? What DMBS can be used and redistributed already set-up without being installed is basically what I'm asking.

I've seen Berkeley and DB2 used a lot but know little about them, and I'm also led to believe (due to the regular dependency issues on linux) that they at least require some sort of libs be installed on the client.

Posted: Sun Jan 07, 2007 6:48 pm
by Chris Corbyn
Sorry, you guys got beaten to the post by my cheeky cross-site posting ;)

http://forum.java.sun.com/thread.jspa?messageID=9428046

Posted: Sun Jan 07, 2007 7:32 pm
by Kieran Huggins
sqlite

Posted: Mon Jan 08, 2007 12:57 am
by Chris Corbyn
Hmm... doesn't SQLite require at least some sort of "client" for it? I mean, you can't just use SQLite with a Java library alone right?

Derby (from apache) was the suggested one to use over on the Java forum. I don't know much about either but I'll have a muck-about during gaps in the office at work today :)

Posted: Mon Jan 08, 2007 1:11 am
by timvw
I've written a couple of applications using (embedded) derby earlier this year.. can't say that i've had any problems untill now with it...

Posted: Mon Jan 08, 2007 3:17 am
by Chris Corbyn
timvw wrote:I've written a couple of applications using (embedded) derby earlier this year.. can't say that i've had any problems untill now with it...
I'll go with Derby, mostly because I'll probably learn more from using it, compared with SQLite :)

Thanks for the advice.

Posted: Mon Jan 08, 2007 8:02 am
by Kieran Huggins
You can use sqlite with just about everything:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Posted: Mon Jan 08, 2007 9:45 am
by Chris Corbyn
Kieran Huggins wrote:You can use sqlite with just about everything:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
I shall give that a go too. The first few (irritating) things that have struck me about Derby:

1. The readline-imitation client they provide (ij) doesn't know what I'm doing when I press UP, DOWN, LEFT and RIGHT arrows so it just prints annoying ascii control sequences.
2. Auto-increment is as painful as this:

Code: Select all

create table timezones (id int not null primary key generated always as identity, name char(3), utc_offset int);
3. I can't distinguish between signed and unsigned integer types, unless I've missed something.

Posted: Mon Jan 08, 2007 11:12 am
by Chris Corbyn
Hmm, I was just about to ask if anyone had any issues with this method of getting the size of a resultset:

Code: Select all

// snip //
	protected int getResultSetSize(ResultSet rs)
	{
		int size = -1; //If RS no worky
		try {
			int currentRow = rs.getRow();
			rs.last();
			size = rs.getRow();
			rs.relative((currentRow - size));
		} catch (SQLException e) {
			System.out.println("Error reading from internal database: ");
			e.printStackTrace();
		} finally {
			return size;
		}
	}
(I don't want to use count(*) because I'm using it to size an array and the count may change in that split second... unlikely, but possible.)

But I was stopped in my tracks by something Derby doesn't seem to want to let me do:

Code: Select all

Error reading from internal database: 
java.sql.SQLException: The 'getRow()' method is only allowed on scroll cursors.
   at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.checkScrollCursor(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.getRow(Unknown Source)
   at org.w3style.suntracker.model.Locations.getResultSetSize(Locations.java:24)
   at org.w3style.suntracker.model.Locations.getContinents(Locations.java:44)
   at org.w3style.suntracker.ui.FrontEnd.addControlFeatures(FrontEnd.java:85)
   at org.w3style.suntracker.ui.FrontEnd.addFeaturesToLayout(FrontEnd.java:56)
   at org.w3style.suntracker.ui.FrontEnd.revealGUI(FrontEnd.java:42)
   at org.w3style.suntracker.ui.FrontEnd$1.run(FrontEnd.java:26)
   at java.awt.event.InvocationEvent.dispatch(libgcj.so.70)
   at java.awt.EventQueue.dispatchEvent(libgcj.so.70)
   at java.awt.EventDispatchThread.run(libgcj.so.70)
It compiled ok, it just won't run because of this "scrolling" doo-dah... Ack.

EDIT | I just needed to explicitly ask for a scrollable ResultSet by doing this:

Code: Select all

Statement st = this.con.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = st.executeQuery(
				"SELECT id, name FROM continents ORDER BY sortorder ASC");