Portable databases for distribution as part of an app?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Portable databases for distribution as part of an app?

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

sqlite
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

You can use sqlite with just about everything:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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");
Post Reply