2

I have a table named test2 in my database. now i want to check if it exists in java code. i have written these lines of code to check if the table exists or not.

DatabaseMetaData dbm = conn.getMetaData();
             ResultSet rs = dbm.getTables(null, "APP", "test2", null);
             if (!rs.next()) {
                 PreparedStatement create = conn.prepareStatement("create table test2(name2 varchar(33))");
                 create.executeUpdate();
             }else{
                 System.out.println("already exists");
             }

As test2 exists in APP schema, my else should get executed. but in my case if is getting executed.

user2864740
  • 60,010
  • 15
  • 145
  • 220
Usman Riaz
  • 2,920
  • 10
  • 43
  • 66
  • 2
    Why are you querying against a table that you're not sure exists in the first place? That alone seems very, very unusual. – Makoto Sep 27 '14 at 04:40
  • Who said i am not sure that it exists ? it does exists , i want to check in the code that it exists or not. please read question carefully . – Usman Riaz Sep 27 '14 at 04:48
  • @Makoto - This makes sense to me. If we have a critical table which might get moved or renamed, then you want your code to mention that. Eg. Expecting TBL_USERS, but not found. You might find that the table was renamed to t_customers. – Erran Morad Sep 27 '14 at 04:52
  • @BoratSagdiyev: At that point, the developer did not do their job properly; the renaming refactor *must* include adjusting/updating any existing queries that the table relies on. – Makoto Sep 27 '14 at 04:52
  • @UsmanRiaz - Please tell us why you want to check for the existence of a table in the code. – Erran Morad Sep 27 '14 at 04:54
  • Do anyone knows why am i getting in IfBlock and not ElseBlock :-/ – Usman Riaz Sep 27 '14 at 04:54
  • @BoratSagdiyev because i want to create database tables at runtime. for that purpose i am testing the code. i delebrately put a table named test2 to check if it exists in database or not from my java code. – Usman Riaz Sep 27 '14 at 04:56
  • [possible duplicate ](http://stackoverflow.com/questions/2942788/check-if-table-exists) – Madhawa Priyashantha Sep 27 '14 at 04:58
  • @UsmanRiaz - Okay. Its been a while since i did jdbc. So it looks like your result set might have a row all the time. When a table exists, there will be two rows. Try printing the Result set and see what you get. Simplest way to debug ! – Erran Morad Sep 27 '14 at 05:00
  • @BoratSagdiyev yeah. i did tried printing . it showed me nothing . – Usman Riaz Sep 27 '14 at 05:01
  • Finally, there is the answer @a_horse_with_no_name . Thanks. Problem Solved. . . . – Usman Riaz Sep 27 '14 at 07:45

1 Answers1

1

Derby stores table names in upper case (as required by the SQL standard and done by many other DBMS as well)

You need to pass the table name in upper case as well

ResultSet rs = dbm.getTables(null, "APP", "TEST2", null);

The driver (all drivers that I know of) runs something along the lines:

where table_name like 'TEST2'

to retrieve the list of tables. Where TEST2 is the unaltered value from the call to getTables(). If you have a DBMS that does case-sensitive string comparison (which almost all DBMS do - I think MySQL and SQL server are the exception) then obviously the table name must be passed in the same case as it was stored.

However you can create mixed case table names by quoting them: create table "FooBar" (...) In that case you would need to pass "FooBar" to getTables().