Checking the existence of temp tables?
Hello All,
I'm trying to come up with a way to check for the existence of two temp tables and if they are there, drop them. I tried this, but temp tables don't work the same with db metadata I think, so I get an exception trying to query the resultset:
DatabaseMetaData dbm = conn.getMetaData();
ResultSet tempA = dbm.getTables(null, null, "#tempA", null);
ResultSet tempB = dbm.getTables(null, null, "#tempB", null);
if(tempA.next() || tempB.next()){
dropResult = stmt.executeUpdate(DROP_TEMP_TABLES);
}
We are using Sybase 4.2 and jdbc 2
Any help is appreciated.
Thanks,
James
# 1
Take a look at this code:
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTables {
private static boolean tableExists(String tableName, DatabaseMetaData dbmd) throws Exception{
// Specify the type of object; in this case we want tables
String[] types = {"TABLE"};
ResultSet resultSet = dbmd.getTables(null, null, tableName, types);
// Get the table names
while (resultSet.next()) {
return true;
}
return false;
}
private static int dropTable(String tableName, Statement stmt) throws Exception{
return stmt.executeUpdate("DROP TABLE "+tableName);
}
public static void main(String[] args) {
String tempATable = "tempA";
String tempBTable = "tempB";
Connection connection = null;/////////////Create your connection here
Statement stmt = null;
boolean tempAExists = false;
boolean tempBExists = false;
try {
tempAExists = tableExists(tempATable, connection.getMetaData());
tempBExists = tableExists(tempBTable, connection.getMetaData());
stmt = connection.createStatement();
if(tempAExists){
dropTable(tempATable, stmt);
}
if(tempBExists){
dropTable(tempBTable, stmt);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Hope that Helps
# 3
> Hello All,
> I'm trying to come up with a way to check for the
> existence of two temp tables and if they are there,
> drop them. I tried this, but temp tables don't work
> the same with db metadata I think, so I get an
> exception trying to query the resultset:
Why do you think that they might exist such that you must delete them?
What database are you using?
In SQL Server, temp tables, by definition have a limited life span. Certainly no more than that of the connection. And probably within the transaction.
