pulling the table names from a Database

[ben riches]

Hi All,
The code below displays 2 comboBoxes and puts the data from each table into the pull downs. How can I get it so that the first comboBox displays all the tables in my dataBase and the second comboBox dispalys the column names from the table the user has selected from the first comboBox?
Thanks
ben

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class ComboBoxDemo extends JPanel {
public ComboBoxDemo() {
JComboBox group = new JComboBox();
JComboBox groupList = new JComboBox();
try
{
// Load the database driver
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
Connection conn = DriverManager.getConnection( "jdbc :-o	 dbc:myProject" ) ;
Statement stmt = conn.createStatement() ;
// Execute the query
ResultSet rs = stmt.executeQuery( "SELECT * FROM ben" ) ;
while( rs.next() )
{
group.addItem(rs.getString(1));
}
rs.close() ;
stmt.close() ;
conn.close() ;
}
catch( SQLException se )
{
System.out.println( "SQL Exception: " + se ) ;
}
catch( Exception e )
{
System.out.println( e ) ;
}
group.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
JComboBox cb = (JComboBox)e.getSource();
String ryhma = (String)cb.getSelectedItem();
}
});
try
{
// Load the database driver
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
Connection conn2 = DriverManager.getConnection( "jdbc :-o	 dbc:myProject" ) ;
Statement stmt2 = conn2.createStatement() ;
// Execute the query
ResultSet rs2 = stmt2.executeQuery( "SELECT ID FROM ben" ) ;
while( rs2.next() )
{
groupList.addItem(rs2.getString(1));
}
rs2.close() ;
stmt2.close() ;
conn2.close() ;
}
catch( SQLException se )
{
System.out.println( "SQL Exception: " + se ) ;
}
catch( Exception e )
{
System.out.println( e ) ;
}
// Layout the demo
setLayout(new BorderLayout());
add(group, BorderLayout.NORTH);
add(groupList, BorderLayout.SOUTH);
setBorder(BorderFactory.createEmptyBorder(20,20,20,20));
}
public static void main(String s[]) {
JFrame frame = new JFrame("ComboBoxDemo");
frame.addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent e) {System.exit(0);}
});
frame.setContentPane(new ComboBoxDemo());
frame.pack();
frame.setVisible(true);
}
}

[Tom Blough]

Have a look at DatabaseMetaData. The method getTables will return the information for your first listbox. The method getColumns will return the information for your second listbox based on the table selected in the first list.
Tom Blough

[ben riches]

Originally posted by Tom Blough:
Have a look at DatabaseMetaData. The method getTables will return the information for your first listbox. The method getColumns will return the information for your second listbox based on the table selected in the first list.
Tom Blough

Hi Tom,
I have been able to get the table names fro the Database with this code

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTables(null,null,null,new String[]{"TABLE"});
while( rs.next() ){
  //System.out.println( rs.getString( 3 ) );
group.addItem(rs.getString(3));
}

But when I am having trouble to get the Column names from the table the user has selected, could you help me with this part please.
Thanks
ben

[Tom Blough]

Ben,
See if this helps:

import java.sql.*;
public class DumpSchema
{
  // JDBC Stuff
  private static final String ODBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
  private static final String ODBC_SOURCE = "jdbc:odbc:POData";
  public static void main(String[] args) 
  {
    new DumpSchema();
  }
  public DumpSchema()
  {
    try
    {
      Class.forName( ODBC_DRIVER);
      Connection ODBCConnection = DriverManager.getConnection( ODBC_SOURCE);
      DatabaseMetaData metadata = ODBCConnection.getMetaData();
      try
      {
        ResultSet tableNames = metadata.getTables( null, null, null, new String[] {"TABLE"});
        while( tableNames.next())
        {
          String table = tableNames.getString( 3);
          try
          {
            ResultSet columnNames = metadata.getColumns( null, null, table, null);
            System.out.println( "TABLE: " + table);
            while( columnNames.next())
            {
              System.out.println( "\t" + columnNames.getString(4));  //Field name
              System.out.println( "\t\tDescription: " + columnNames.getString(12));
              System.out.println( "\t\tType: " + columnNames.getString(6));
              System.out.println( "\t\tSize: " + columnNames.getString(7));
              System.out.println( "\t\tDecimal Digits: " + columnNames.getString(9));
              System.out.println( "\t\tAllow Nulls? " + columnNames.getString(18));
              System.out.println( "\t\tDefault Value: " + columnNames.getString(13));
            }
            columnNames.close();
          }
          catch( Exception e)
          {
            System.out.println( "Unable to get column information for table '"
                + tableNames.getString(3) + "'\n" + e);
          }
        }
        tableNames.close();
      }
      catch( Exception e)
      {
        System.out.println( "Unable to get table information\n" + e);
      }
      ODBCConnection.close();
    }
    catch( Exception e)
    {
      System.out.println( "Error connecting to PO database\n" + e);
    }
  }
}

[ March 10, 2004: Message edited by: Tom Blough ]