"table already exists" error message....
Hi
Please could someone explain something basic to me...
I have just started creating a table and inserting values, but when I run the program I get a message saying my table already exists (as it does once it's been complied and run once) , but I can't find any information anywhere that says how to deal with this - are there any methods for sorting this, or what should I do?!
I'm new to Java programming and would really appreciate an explanation!
Thanks!
[529 byte] By [
kkkkaren] at [2007-9-26 2:53:41]
In a database, once you have created a table you cannot create it again without dropping (deleting) the original table. If your purpose is to have a table that is created each time the app runs and then have it dissapear on its own when your app closes the database connection, make sure you create the table as a GLOBAL TEMPORARY TABLE
dubwai at 2007-6-29 10:42:36 >
I'm not sure if I understand your question, but here's a potential solution to the table exists problem - from your Connection instance you should be able to getMetaData about the database you're connecting to. With the DatabaseMetaData, use getTables() which returns a ResultSet of data on the tables in the database. If your table is already there, don't create it. You can look up the API calls here: http://java.sun.com/j2se/1.3/docs/api/index.html
I haven't done alot of work with DatabaseMetaData apart from Oracle, and am not sure how useful it is across platforms, e.g. you might get different results from different db vendors
You could also, just catch and ignore that particular exception. That would be a nice quick'n'dirty solution.
Check to see if the table exists yet. If it does not exist then create it, otherwise just continue.
Use the databaseMetaData to see if the tables exist:
DatabaseMetaData dmd = sconn.getMetaData();
ResultSet r = dmd.getTables("CATALOG_NAME","SCHEMA_NAME","TABLENAME",null);
//check for table
if (!(r.next()))
{//table does not exist
Statement s = sconn.createStatement();
s.executeUpdate("CREATE TABLE ...");
}
Statement stat = sconn.createStatement();
ResultSet resultset = stat.executeUpdate("INSERT INTO ...");
more on databaseMetaData can be found at:
http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
Jamie
It this is intended to be a large scale, long term application then you shouldn't be creating tables in the application. All that does is intefer with the job of a dba.
If that isn't the intent then just catch the SQLException and parse it for the specific error message. If it says 'already created' then ignore it. Otherwise just rethrow it.
