This discussion is archived
11 Replies Latest reply: Jan 5, 2013 12:51 PM by 982114 RSS

Gather table names from array of database names

982114 Newbie
Currently Being Moderated
Hey guys,

I'm creating an application using JDBC and have several databases within my MySQL Server, however am only concerned with two.

I've used DatabaseMetaData method "getCatalogs" and an if statement to gather only the database names I wish, passing these to a String array.

I want to now use this String array in the "getTables" method, however only the tables from the first database are accessed.
rs = dbMetaData.getTables(dNames[0].toString(), null, null, null);
Is there any way to gather all the tables within the two databaes in this one result set?

Sorry if it's glaringly obvious!

Thanks

Edited by: 979111 on Dec 29, 2012 8:01 AM
  • 1. Re: Gather table names from array of database names
    rp0428 Guru
    Currently Being Moderated
    >
    I've used DatabaseMetaData method "getCatalogs" and an if statement to gather only the database names I wish, passing these to a String array.

    I want to now use this String array in the "getTables" method, however only the tables from the first database are accessed.

    rs = dbMetaData.getTables(dNames[0].toString(), null, null, null);

    Is there any way to gather all the tables within the two databaes in this one result set?
    >
    No - you will have to combine the two arrays (e.g. adding each to an ArrayList) yourself.
  • 2. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    Thanks for the reply.

    Do you mean carrying out two "getTable" methods for each database and then combining the two arrays from the results of the tables?

    This is my code below:

    DatabaseMetaData dbMetaData = conn.getMetaData();
                   rs = dbMetaData.getCatalogs();
                   System.out.println("Databases:");
                   while (rs.next()) {
                        if(rs.getString(1).equals("d1") || rs.getString(1).equals("d2"))
                        {
                             strDNames = rs.getString(1);
                             dNames = new String[] {strDNames};
                             System.out.println(dNames[0].toString());
                        }
                   }
    
                   rs = dbMetaData.getTables(dNames[0].toString(), null, null, null);
                   System.out.println("Tables:");
                   while (rs.next()) {
                        String tableName = rs.getString("TABLE_NAME");
                        System.out.println(tableName);
                   }
    Been staring at code for hours and it's all a blur, I know there's probably some (somewhat) obvious solution, just can't see it right now!

    Thanks.

    Edited by: 979111 on Dec 28, 2012 3:35 PM

    Edited by: 979111 on Dec 29, 2012 8:00 AM
  • 3. Re: Gather table names from array of database names
    Joe Weinstein Expert
    Currently Being Moderated
    By spec, if you give null as the catalog parameter, the result should include tables from all catalogs/databases,
    and the catalog name is returned in each row, so you will have all you want in the one result set, but also
    more, which you can ignore by checking the catalog value...
  • 4. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    Thanks for the reply.

    I've tried as you have said with the following code:
    rs = dbMetaData.getTables(null, null, null, 
                             null);
                   System.out.println("List of tables: "); 
                   while (rs.next()) {
                        System.out.println(
                                  "   "+rs.getString("TABLE_CAT") 
                                  + ", "+rs.getString("TABLE_NAME")); 
                   }
    However, the print statement shows nothing whatsoever. Any idea why that may be? If I put a database name in then results show up, bit confused!

    Thanks.

    Edited by: 979111 on Dec 29, 2012 8:00 AM
  • 5. Re: Gather table names from array of database names
    Joe Weinstein Expert
    Currently Being Moderated
    I would consider that to be a driver bug, and contact the vendor, pointing them to the specification for the method.
  • 6. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    Hmm, how would I go about that?

    The setup I currently have is my MySQL Server setup, with my JDBC set using the following DRIVER and URL:
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
    static final String DB_URL = "jdbc:mysql://localhost/";
    What I am intending to do is as follows:

    Gathering metadata for all tables from two databases within my server, including:

    - Database names
    - Table names for each database
    - Field name, type and size
    - Check for each field to see whether PK or FK
    - If yes to above, reference to FKTable and FKField

    And putting all of this metadata within a new database and respective table.

    Should quickly say, I'm not asking for some definitive answer on the above, just more of an approval of whether I'm going the right way about this.

    Many thanks for all the help so far!

    Edited by: 979111 on Dec 29, 2012 2:20 PM
  • 7. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    In addition to my previous post, I've checked by adding a database name in my URL connection and in that case, table names are shown for the catalog value being null.

    That being the case, does anybody think using an Array, ArrayList or Vector to hold my database names, then scanning through these for this statement:
    rs = dbMetaData.getTables(null, null, null, types);
    Then passing in my array for the catalog value, would be suitable in relation to my question above?

    Thanks.
  • 8. Re: Gather table names from array of database names
    EJP Guru
    Currently Being Moderated
    Define 'suitable'. Does it work?
  • 9. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    I'm in the process of trying this out currently - so I'll get back to you soon either elated or even more bewildered on the outcome.

    I guess I mean 'suitable' in the way of my application being reusable and having as little redundant code as possible.

    I don't want to create a result set for each database and each table individually as I know that would be poor coding - I'd like it so if someone reused my code, they'd only have to change a value or two for the program to work as expected.

    What I'm thinking is creating an Array (or similar) for each database, using this array to create another Array for all table names...then using this final array for the rest of the metadata.

    I know how to do each element, it's just visualising it into correct code that is confusing me.

    Thanks.
  • 10. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    Right, I have managed to gather a list of table names from the array, so got that part out the way!

    What I went onto next, was trying to gather the metadata (field name, type name and size) for each attribute within each table.

    I went about this using an SQL statement, passing in the database name array and table name array, creating a nested loop to scan through these arrays.

    What I wish to do next is to check every attribute from all tables and retrieving either a 'yes' or 'no' value linking to whether the attribute is a primary key or not.

    Now I know this is the typical code to carry out gathering the primary key from a table:
    rs = meta.getPrimaryKeys(null, null, "table");
    
        while (rs.next()) 
        {
          String columnName = rs.getString("COLUMN_NAME");
          System.out.println("Primary key: " + columnName);
        }
    Looking at it, it seems to make sense to use an IF statement to compare whether rs.getString("COLUMN_NAME") equals the field name from the metadata I've gathered previously, then setting a boolean ArrayList to either true or false.

    Would people say this is a good way forward? As in, good coding for what I want to do (see below)

    Gather metadata for all tables from all databases within my server, including:

    - Database names
    - Table names for each database
    - Field name, type and size
    - Check for each field to see whether PK or FK
    - If yes to above, reference to FKTable and FKField

    Thanks.
  • 11. Re: Gather table names from array of database names
    982114 Newbie
    Currently Being Moderated
    Another quick question too:

    Is there any negative in using 'getImportedKeys' DatabaseMetaData to gather primary keys, as opposed to using 'getPrimaryKeys' from the ResultSetMetaData?

    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points