Derby "create table if not exists"

A few discussions have asked how to asked how to mimic use "Create table if not exists" functionality in Derby Java DB. This is a non-standard sql extension, implemented in sqlite and MySQL among other databases. The most obvious use of this functionality is in embedded database applications where you don't want to trouble the user with db creation. (sqlite and derby both suitable for this use case.) You can create the db as part of the install process, but if you want to support a db per user of the app or similar scenario, you generally want to create the db the first time a given user runs the app.

Trying to create a table that already exists will throw a SQLException. Some authors have suggested that in the above scenario, you simply catch and ignore this exception on subsequent calls after the initial call. This is a bad idea unless you search the exception for a specific code because you will be unable to handle any problems with db creation on the initial run of the app.

A clearer way to handle this is to use java.sql.DatabaseMetaData interface. This is implemented by most (all?) db providers and allows you to check existence in about 3 lines of code. Here's an example:

Connection dbConnection
= DriverManager.getConnection("jdbc:derby:MyDb;create=true");
DatabaseMetaData dbmd = dbConnection.getMetaData();
ResultSet rs = dbmd.getTables(null, "MYSCHEMA", "MYTABLE", null);
if(!rs.next())
{
createMyTable();
}


You can find a discussion with the alternate detection method here on nabble: http://old.nabble.com/%22create-table-if-not-exists%22---td22558480.html

Subversion and netbeans on 64 bit Windows 7

I've been doing some home programming lately and needed some way to version my software. I'd already installed netbeans 6.8 for it's easy swing ui builder and it doesn't seem any worse than eclipse. Netbeans has built in support for CVS, SVN (Subversion) and Mercurial source control. SVN seemd like the obvious choice, especially when some commentators called out good SVN integration as a primary reason to use netbeans: http://rob.purplerockscissors.com/2009/08/14/eclipse-vs-netbeans-round-2/

Collabnet is the official home of SVN and I could download both a server and client from there. I wanted to run both server and client on the same system. http://subversion.open.collab.net/ Collabnet seems to offer a 1.6.6 version server, but only a 1.5.5 version of windows client. This turned into an issue later.

Installing the server was easy. Since I wasn't currently running a web server on the system, I installed the bundled apache along with the server. I'm thinking of removing that later for security purposes.

Then I created a repository:

svnadmin create \Users\bruce\baserepository

The netbeans source import option shows up when right clicking a project. Choose the Versioning->Subversion option. The first time you import, the system pops up a request to set up your integration. The two options it gives are using an auto downloaded svn client or your own svn command line version. The autodownload option specifically calls out 64 bit client as unsupported... thus the download of the client from collabnet.

I installed the collabnet 1.5.5 client on my system. The collabnet site gives some intstructions :
http://www.open.collab.net/downloads/netbeans/index.html. Unfortunately, when I restarted netbeans and tried to import files into the repository I made, it kept giving the same error:

svn: Expected FS format between '1' and '3'; found format '4'

Discussion groups revealed that this was due to the 1.5.5 and the 1.6.6 server using conflicting formats. Two options where to use a compatibility flag when creating a repository with SVNadmin or download a new windows build from sliksvn: http://www.sliksvn.com/en/download which distributes the latest builds of the client for 64 bit windows. Did the latter, installed it, restarted netbeans and voila; working import.

The other blogger was right: very slick integration between netbeans and svn.

Back to coding now, but thought that hint might help some people.