Best way to figure out if a table is existing

[joseph...@gmail.com]

Hi

I would like to figure out if a table is existing.

I haven't found a cross database way to do it up to now.

As such I'm seriously thinking of throwing some stupid select at it and see whether it fails. I'm a bit annoyed by the extra exception (and stack compilation), for the performance cost, but I haven't found any better way.

Am I missing some better way?

Thanks again for jOOQ!

NB: I know of createTableIfNotExists, but this isn't on all DB...

cheers
joseph

[Samir Faci]

That's fairly DB specific.  

For postgres for example you can do something along these lines:

source: http://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema  

SELECT EXISTS (
   SELECT 1
   FROM   information_schema.tables 
   WHERE  table_schema = 'ui'
   AND    table_name = 'users'
);

which returns a boolean of true if it exists.  Each DB will have a at least one way of doing this but it's hardly consistent.

MySQL has a different set of tables to check etc...You can find the correct tables to query and invoke it via Jooq if you'd like.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

[Lukas Eder]

What Samir suggested is implemented (more or less) in jOOQ-meta. You could use that, but it has quite an overhead: That of caching the entire schema in memory. And the cache is not thread safe.

Another option would be to use DSLContext.meta() to query the dictionary views via JDBC's DatabaseMetaData (or use the latter directly).

By the way

I'm a bit annoyed by the extra exception (and stack compilation), for the performance cost, but I haven't found any better way.

I wouldn't worry about the stack compilation too much in the event of a database query. Even the latency produced by the TCP/IP connection is very likely to outshine any exception cost in Java :)

Besides that, jOOQ-meta also does this internally, e.g. to see if it runs on Oracle 12, 11, or 10. It just checks if a column is available from a dictionary view by selecting it. On error: Not available.

Hope this helps,
Lukas

[joseph...@gmail.com]

Hi Samir and Lukas

Samir's proposal is fairly DB specific, which I would like to avoid. Using jOOQ-meta would make it a bit more cross DB, but then table creation/deletion will be done at runtime, so I can't cache the outcome and so the overhead would be over the top.

Another option would be to use DSLContext.meta() to query the dictionary views via JDBC's DatabaseMetaData (or use the latter directly).

I'll look into this one, thx :)
 

By the way

I'm a bit annoyed by the extra exception (and stack compilation), for the performance cost, but I haven't found any better way.

I wouldn't worry about the stack compilation too much in the event of a database query. Even the latency produced by the TCP/IP connection is very likely to outshine any exception cost in Java :)

Indeed, you're deeply right.
 

Besides that, jOOQ-meta also does this internally, e.g. to see if it runs on Oracle 12, 11, or 10. It just checks if a column is available from a dictionary view by selecting it. On error: Not available.

Ok, good to know: it's not too crazy to proceed this way.

In the end, would you advise using DatabaseMetaData or seeing if an exception comes back ?

Thanks again all :)

 

[Lukas Eder]

2016-05-27 15:06 GMT+02:00 <joseph...@gmail.com>:

In the end, would you advise using DatabaseMetaData or seeing if an exception comes back ?

I think the exception solution is the most quick and dirty, but still somewhat clean approach. DatabaseMetaData isn't implemented optimally in all JDBC drivers.

If you're using jOOQ for DDL (or if at least you contain your DDL in a specific application location only), then you can still cache the results of your "meta queries" until the next DDL statement is executed.

Hope this helps,

Lukas