why do I get this exception

[ben riches]

Hello All,
When I click on a table name called 00_BPDCON I get the Exception:
SQL Exception: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrent Syntax near '00' .
I don't know why this is happerning?

import java.awt.*;
 import java.awt.event.*;
 import javax.swing.*;
 import java.sql.*;
 public class ComboBoxDemo extends JPanel {
   static Connection conn;
   DatabaseMetaData DMD;
   JComboBox group = new JComboBox();
    JComboBox groupList = new JComboBox();
    public ComboBoxDemo()  {
try  { 
     // Load the database driver
   Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
    conn = DriverManager.getConnection( "jdbc :-o	 dbc:myProject" ) ;
   DMD=conn .getMetaData();
   ResultSet rs=DMD.getTables(null,null,null,new String[]{"TABLE"});
   while(rs.next()) {

group.addItem((String)rs.getString(3));

   }
   rs.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)  {
String tbl = (String)group.getSelectedItem();
System.out.println(tbl.toString());
 try {
      Statement st=conn.createStatement();
    st.execute("SELECT * FROM "+tbl+" WHERE 1=0");
    ResultSet rs=st.getResultSet();
    ResultSetMetaData md=rs.getMetaData();
    groupList.removeAllItems();
   for (int i=1;i<=md.getColumnCount();i++) {
groupList.addItem(md.getColumnName(i));
    }
   st.close() ;
 } catch (SQLException se ) {
   System.out.println( "SQL Exception: " + se ) ;
 } catch (Exception ex ) {
   System.out.println( ex ) ;
 }
      }
});
 // Layout the demo
setLayout(new BorderLayout());
 add(group, BorderLayout.NORTH);
 add(groupList, BorderLayout.SOUTH);
 setBorder(BorderFactory.createEmptyBorder(200,20,20,20));
    }
   public static void main(String s[])  {
JFrame frame = new JFrame("ComboBoxDemo");
 frame.addWindowListener(new WindowAdapter()  {
   public void windowClosing(WindowEvent e)  {
try { 
     conn.close() ;
 } catch (Throwable t) {
}
System.exit(0);
   }
});
 frame.setContentPane(new ComboBoxDemo());
 frame.pack(); frame.setVisible(true);
    }
}

Rgds
Ben

[Chris Hall]

Since its a SQLException, the problem lies in the sql statement you are executing, which in this case is select * from {tableName] where 1=0 . Assuming the table name is correct, the 'where 1=0' means you have a field called 1 and you are looking for the records of that field with a value of 0. This really doesn't make sense, and I would guess that is the root of your problem. Make sure you know the structure of the database, and you can try executing the sql statement directly against the database to make sure it works.

[Wayne L Johnson]

Chris' reply is correct in that you should print out the query as it is being executing, and try executing it directly against the database to verify it.
However the query, "Select * from <tablename> where 1=0" is a perfectly valid query in most databases (I've used it against Oracle, Sybase and MySql) and is a great way to get the table's column definition. It executes very fast and returns no rows, but you still get the MetaData so you can pull out the number of columns, and column names and types.
The error message seems to indicate it doesn't like the table name. However test the SQL directly against your SQL-Server database to make sure that it supports the query.
Finally, and this is a minor issue, but you should probably close your resultset explicitly. Some database don't care, but with some databases you need to close both the resultset and statement. And that should be done in a "finally" clause to ensure they get closed even if an exception gets thrown.

Statement st = null;
ResultSet rs = null;
try {
   st = conn.createStatement();
   st.execute("...");
   rs = st.getResultSet();
   // do whatever processing
} catch (SQLException se ) {
   System.out.println("SQL Exception: " + se);
} catch (Exception ex) {
   System.out.println(ex);
} finally {
   try { if (rs != null) rs.close(); } catch (Exception ex2) { }
   try { if (st != null) st.close(); } catch (Exception ex2) { }
}

[mani pallapu]

Originally posted by Chris Hall:
Since its a SQLException, the problem lies in the sql statement you are executing, which in this case is select * from {tableName] where 1=0 . Assuming the table name is correct, the 'where 1=0' means you have a field called 1 and you are looking for the records of that field with a value of 0. This really doesn't make sense, and I would guess that is the root of your problem. Make sure you know the structure of the database, and you can try executing the sql statement directly against the database to make sure it works.

[ben riches]

So how can I pull all the tablenames out from the database without looking for 1?
Ben

[Dana Hanna]

DatabaseMetadata has a method for getting column names!
And the 1=0 trick on DB2 for OS/390 takes FOREVER to execute - Don't ask me why!