Hi all!
I have several tables in an MsAccess db and I would like to read all data of a certain column to a Hashmap. With 3 tables and using static array, it' working but i have about 300 tables.
With the static array, I used sg like this:

id=1;
int array[] = {206,260,91}; //names of 3 tables
    for (int i=0; i<3; i++) 
    {
      int tables = array[i];
      String sql = "SELECT * FROM "+tables+"";
      try
     {
           PreparedStatement stat = con.prepareStatement (sql);
           ResultSet rs = stat.executeQuery (); 
           Statement stmt2 = con.createStatement();

       if (rs != null) 
       {                
           while (rs.next())
           {
              station = rs.getString("STATIONS");
               boolean blnExists = hMap.containsValue(station);
              
               if (blnExists == true) 
               {
                  System.out.println("Already exists " + station + ": " + blnExists);     
               }
               else
               {
                  hMap.put(id, station);
                  String sql_out = "INSERT INTO Stations_All(ID_STATION, STATION) VALUES(" + id + ", '" + station + "')";
                  stmt2.executeUpdate(sql_out);
                  id++;
                  System.out.println(hMap.values());
               }
            }
          } 
       }
   }

So this puts the stations of the 3 tables into both a hashmap and a new database(Stations_All). But what happens if I have several databases especially when the number of tables occasionally change? I haven't found a solution. I believe with the method number of tables should be determined and then somehow read all tables.
Any help is appreciated.

You can use the following query to get a list of the non-system tables

select name from MSysObjects where type=1 and flags=0

How to use this? I set everything in access to have all the rights on sysobjects.

String sql_count = "select count(name) from MSysObjects where type=1 and flags=0";
     try {
         PreparedStatement st = con.prepareStatement (sql_count);
         ResultSet rsst = st.executeQuery();
         Statement stst = con.createStatement();
         if (rsst != null) {
             while (rsst.next()) {
                 stst.executeUpdate(sql_count);
             }
         }

This gives this msg: No row count was produced
What is wrong with my code? And i'd like to have the number of tables written out...

Don't do it that way. That is Database specific. Read the API docs for DatabaseMetaData.

commented: Good point. +19

Connection c;
ResultSet rs = c.getMetaData().getTables(catalog, schemaPattern, tableNamePattern, types);
while(rs.next())
{
//rs.getString("<column for table names>");
}

This looks like similar to what i found:

DatabaseMetaData dbmd = con.getMetaData();
      String[] types = {"TABLE"};   // Specify the type of object; in this case we want tables
      ResultSet resultSet = dbmd.getTables(null, null, "%", types);
     // Get the table names
        while (resultSet.next()) 
      {  
          String tableName = resultSet.getString(3);   // Get the table name
          String tableCatalog = resultSet.getString(1);  // Get the table's catalog and schema names (if any)
          String tableSchema = resultSet.getString(2);
}

Thank u all for the help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.