Saving all tablenames into a string

[Markus Reis]

Hi all,

I'm trying to save all names of all tables in my database into a String. I currently have the following:

	public int getMaxID(int tabellenAnzahl) {  //returns the highest IDs of all tables
		int ID = -1;
		int maxID = -1;
		String[] tabellenName = new String[tabellenAnzahl];
		try {
			for(int j = 0; j < tabellenAnzahl; j++){	
				ResultSet rs = null;
				statement = connection.createStatement();
				rs = rs.getTables("Rechner");
				while(rs.next())	//save all tablenames in tabellenName[]
				{
					tabellenName[j] = rs.getString("TABLE_NAME");
				}
                            rs.close();
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		try {
			for(int i = 1; i <= tabellenAnzahl; i++){
				ResultSet rs = null;
				statement = connection.createStatement();	//go through all tables and select each highest ID
				rs = statement.executeQuery("SELECT ID FROM '" +tabellenName[i]+ "' WHERE ID = ( SELECT max(ID) FROM '" + tabellenName[i]+ "' )");
				while(rs.next()) {	//save the highest ID
					ID = rs.getInt("ID");
					if(ID > maxID){
						maxID = ID;
					}
				}
				rs.close();
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return maxID;
	}
}

rs = rs.getTables("Rechner");

is the problem
I found this solution on another board, but this doesnt work.

btw - is there maybe a better way or even a SQL-Statement to get the highest ID among several tables? I couldnt find one so I tried like this.

Thanks

[chris webster]

Do you need to include more information in your call to getTables()?

ResultSet getTables(String catalog,
                    String schemaPattern,
                    String tableNamePattern,
                    String[] types)
                    throws SQLException

Retrieves a description of the tables available in the given catalog. Only table descriptions matching the catalog, schema, table name and type criteria are returned. They are ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM and TABLE_NAME.
...
Parameters:
catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
tableNamePattern - a table name pattern; must match the table name as it is stored in the database
types - a list of table types, which must be from the list of table types returned from getTableTypes(),to include; null returns all types

Also, even if you get a list of all the tables in your database schema, are you sure that every table has an ID column? If not, your query will fail. It might be better to use a SQL query that will look in your database catalogue to find only those tables which have an ID column.

And why are you doing this? If you're doing this in order to determine the next ID to use for a record, it would be much better and safer to use your database's built-in mechanisms for this (e.g. auto-increment or sequence). Your query will be out of date as soon as a new record gets created somewhere with a new ID anyway. And what happens if somebody deletes the latest record - do you want to re-use the ID? Also, if you have a surrogate key (an arbitrary numeric ID as your primary key) then it's best not to worry about the actual value in the ID, so long as it's guaranteed to be unique (via sequence for example). If you're using sequences (e.g. on Oracle) to populate IDs, then you can use a single sequence as the source of your ID values and simply check the value in the sequence instead of having to check all your tables. Or just trust the sequence to do its job and don't worry about the actual value.