Thursday, October 04, 2007

Where are my database tables in NetBeans?

All right, another common question, and this one (understandably) can cause a lot of frustration: where the heck are my tables?!

So I think it's time to record some common causes of this. I'm actually finding these 'tips and tricks' blogs very helpful because when the question comes up again I can just point to my blog and save my fingers some typing...

A lot of users create tables in their database, and then are flummoxed when they can't find them in NetBeans.

Here are some possible reasons why this can happen:

Bad Metadata

Older JDBC drivers, particularly Oracle drivers, are notorious for not handling metadata correctly. NetBeans reports exceptions in the log when it can't get metadata, but you may not have noticed that. The visible result is you see no tables when you open the connection, even though you know you created thoses tables. Head banging ensues.

So, make sure you upgrade to the latest JDBC drivers and register them with NetBeans, and see if that helps.

Two Different Schemas

Make sure you're in the right schema. If you create a schema and then create the tables under that schema, they may not show up by default in NetBeans. To fix this, in the connection dialog for NetBeans, choose [Advanced] and then select the correct schema.

Two Different Databases with the Same Name

This is possible particularly with Java DB. Java DB will create databases directories in the location where you start the VM which hosts the database. If you connect to Java DB using the embedded driver, that's the VM of your tool.

So if you use one tool to create your tables, and then use NetBeans to look at the tables, you may actually be looking at two different databases, and so in NetBeans the tables won't be there.

Let's follow this scenario more closely so you can see what I mean:

- You use a tool to create tables. You use a URL like "jdbc:derby:mydb;create=true". Note that if the database does not exist, it is created automatically. The database is created in the directory where your tool was started.

- Then you open up NetBeans, and you connect to your database using the same URL. But NetBeans is started in a different directory, and a different database is created

- You browse the connection in NetBeans, and your tables aren't there! Bang bang goes the head.

The best way to solve this is use an absolute path to the database when you specify the database name in the URL. So instead of "jdbc:derby:mydb" use "jdbc:derby:/the/path/to/my/db/mydb" or "jdbc:derby:C:/the/path/to/my/db" (note that you use forward slashes even for Windows)

Update: Some great comments from my Dear Readers, who are respectfully correcting my proposed solution. Take a look at them if you know what's good for you :) But to summarize:

  • an alternate to absolute path is setting derby.system.home (or, in NetBeans, setting the database directory, see my previous post on how to do this). Absolute path doesn't always work, and particularly is a problem in production deployments where you don't control your environment
  • don't use "create=true" after you've created your database, to avoid creating databases willy nilly (although this approach eliminates some usability where in embedded deployments you don't have to worry about the database not existing)
  • if you don't know where your database is, search for it
Anyway, my readers say it better than me, so read their comments.


Anonymous said...

Actually David, I disagree respectfully.

Better, ASSUMING you are running the NetWork server is NEVER to have the path in the JDBC name, and instead set the derby.system.home property first before starting network server.

IMO, it's better to abstract the detail this way.

And it avoids some issues on uncommon NOS's like NetWare, where Derby JDBC URL parsing will choke (Netware has paths like SERVER\VOLUME:PATH1\PATH2 ... trust me this will get misinterpreted!)

John said...

I'm not so sure that the "best way" solution to the "Two different databases" problem is to use an absolute path.

It may be the easiest way during development, but for reasons mentioned by the first anonymous commenter, it is probably not the best way when you are deploying your application, for instance.

The best way, I would say, is to make an effort trying to understand these path issues, by reading docs and (above all) experimenting. Then implement the solution that works best for you. This could be using an absolute path, or it could be setting derby.system.home, or something else.

My number one tip for avoiding these issues is:

Don't include ;create=true in more than one of your connection URLs.

Actually, include it only the first time you connect to the database, i.e. when you create it. Then remove that attribute from your URL. This way, you will avoid having databases with the same name being created all over the place depending on how many different tools you use. You will get an error message instead, if your URL doesn't point to the actual database.

So, if you created your database using ij, but want to browse the data using Netbeans, then don't include ;create=true in the Netbeans URL.

Also, my #1 tip for detecting whether or not you have this issue:
Do a file search for the database name. For example, if you have a URL like 'jdbc:derby:mydb', or 'jdbc:derby://localhost:1527/myRelativeDir/mydb', then search for directories called 'mydb'. If you end up with more than one match, you know where to start further investigations...