If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Determine if table exists

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-28-04, 02:50
gandalf_me gandalf_me is offline
Registered User
 
Join Date: Mar 2004
Posts: 8
Determine if table exists

What's the best way to determine, through JDBC, if a table exists in the database?

One way is to run a query on that table and catch the exception. (E.g. "SELECT count(*) from table_name"). If no exception is thrown, table exists, otherwise not.

There has got to be a better way than this. Does standard SQL provide any such mechanism? A quick search didn't reveal anything.

Not to mention, DB independent mechanism would be preferred.
Reply With Quote
  #2 (permalink)  
Old 05-28-04, 03:03
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
Definitely do not use:
SELECT count(*) from table_name

That will take too long if the table has lots of rows. You could try:
SELECT 1 from table_name

You could also query the SYSCAT.TABLES catalog view, which has a list of all the tables in the database. Besure to check on TYPE because not everything in there is actually a base table (some are views, etc.).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old 05-28-04, 10:12
n_i n_i is offline
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
I don't believe there would be a DBMS-independent way of doing this. Even if you attempt to analyze an exception thrown during the query, it will still be database-dependent. That is, it will of course be a SQLException; however, you'll need to analyze the returned error code to make sure it's thrown because of a missing table definition and not because of some communication problem or insufficient privileges...

JDBC provides a standard interface to the database catalog, Connection.getMetaData(), while the driver takes care of the DBMS specifics. Have a look at the DatabaseMetaData.getTables() definition in the JDBC API.
Reply With Quote
  #4 (permalink)  
Old 05-28-04, 12:29
J Petruk J Petruk is offline
Registered User
 
Join Date: Mar 2004
Location: Toronto, ON, Canada
Posts: 513
Quote:
Originally Posted by n_i
JDBC provides a standard interface to the database catalog, Connection.getMetaData(), while the driver takes care of the DBMS specifics. Have a look at the DatabaseMetaData.getTables() definition in the JDBC API.
That's a good one... Very close to being vendor-independent, although the "catalog" and possibly "schema Pattern" might change. According to the docs, you can throw nulls into those, which makes it reasonably vendor-independent.
__________________
--
Jonathan Petruk
DB2 Database Consultant
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On