jdbc: how to find if a table exists

[dgilmore]

I'm writing a database tools application and a portion of my code depends on whether a table exists or not. Is there any test (say returning a boolean) to tell whether this table exists? I've searched the API with no result. Any help, whether a hack or not, would be much appreciated.

 

[sedj]

Which DBMS are you are using ? The best solution probably lies within the DBMS's native SQL

 

[dgilmore]

The application i'm programming will eventually have functionality to access different vendors databases, providing different drivers, commands etc which is why I want to find a solution through Java.

 

[Hattusas]

I never met such a method throughout jdbc.But Seagate Crystal Reports -a database reporting software always finds the tables and views regardless of your database kind.
You can ask them your problem.Maybe they will help you or at least show you a way to perform this as they did what you wanted.Go to Seagate Software home site maybe you can find a useful step there.

Speaking of jdbc,maybe there IS a way to do this.Also ask this question to Sun Microsystems. Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[sedj]

Hmmm...darn portability. Who thought of it anyway ?

Right - the problems as I see it then :
1)Can't use native language like Oracle PL because you want the app to talk to multiple vendors.
2) sql API contains no method for testing table existence
3) Can't rely on SQL because different vendors implement different standards of SQL - cannot rely on ANSI SQL to be implemented by all vendors with which JDBC app will interface.

So - what's left ... as far as I can see, only a ropey patch :
Write a method that executes an SQL SELECT statement from the table which may or may not exist. Depending on the DBMS, this will then either throw an exception (which you would catch with SQLException I think), or return no rows - in either case set a boolean to false, else true.

This probably would work - but bear in mind it is ropey !

Ben

 

[dgilmore]

thanks Ben,
those are exactly the problems i'm facing and it's the solution I'm currently using until I find a better one!!!

The good news is that after a hell of a lot of searching i have found a method at

http://www.cs.waikato.ac.nz/~ml/weka/doc_gui/weka.experiment.DatabaseUtils.html

it is tableExists() and returns a boolean! Oh the joy is unbounded! I have yet to see if it works ( i hope to god it's open source!!!)

David

 

[wushutwist]

This is easy to do using standard JDBC. First, make your database connection and then get the meta data from the connection. From there you check check existance of tables and tons of other things.

Get the meta data by calling Connection.getMetaData()

Check out DatabaseMetaData. One method that might interest you is DatabaseMetaData.getTables

Here is a link to the Javadocs:

http://java.sun.com/j2se/1.4/docs/api/java/sql/DatabaseMetaData.html