Portable databases for distribution as part of an app?
Moderator: General Moderators
- 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?
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.
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.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Sorry, you guys got beaten to the post by my cheeky cross-site posting 
http://forum.java.sun.com/thread.jspa?messageID=9428046
http://forum.java.sun.com/thread.jspa?messageID=9428046
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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
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
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
You can use sqlite with just about everything:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
I shall give that a go too. The first few (irritating) things that have struck me about Derby:Kieran Huggins wrote:You can use sqlite with just about everything:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
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);- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Hmm, I was just about to ask if anyone had any issues with this method of getting the size of a resultset:
(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:
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
// 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;
}
}
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)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");