Regarding the SQL Select and "try...catch" statement

[LianaN]

Hi!

I have a question regarding the SQL Select and "try...catch". The problem is that my SQL Select statement could sometimes return empty sets. For this reason I'm using "try...catch" statement. However, it doesn't work - the error message is generated for the empty set. So, how could I solve this problem? Thanks!

cmbFormType.addActionListener(new java.awt.event.ActionListener() {
          public void actionPerformed(java.awt.event.ActionEvent evt) {
              try {
                tableModel.setQuery("select F.at_code, F.at_size, F.at_visible from F inner join FT"
                + " on F.ft_code = FT.ft_code and F.ft_code = " + formTypeCodesArray[cmbFormType.getSelectedIndex()].toString());
              } catch (Exception ex) {

              }
          }
        });

[Ezzaral]

> However, it doesn't work - the error message is generated for the empty set.

I assume you meant the error is not generated. An empty result doesn't throw an exception because it's a completely valid result - there simply are no records that match the query.

If your code needs to know that, you could either throw your own exception from the setQuery() method when the result is empty, or you could return a value from that method. It could return an int count of the records found or boolean to indicate at least one record was returned from that method.

[Ezzaral]

Re-reading your post: Do you mean that the error is getting past your catch? I may have misunderstood you with the previous comment.

[LianaN]

Hi!

Well, I'm using SQL SELECT statement in order to fill JTable with some data. The JComboBox is used to specify the selection conditions: "select ... F.ft_code = " + formTypeCodesArray[cmbFormType.getSelectedIndex()].toString()".

If the database doesn't contain any entries that satisfy this condition, then the empty set is returned. In this case the JTable cannot be filled with the data from database and, therefore, the "catch statement" should be executed. But it does not happen. For example:

try {
  tableModel.setQuery("select F.at_code, F.at_size, F.at_visible from F inner join FT"
  + " on F.ft_code = FT.ft_code and F.ft_code = " + formTypeCodesArray[cmbFormType.getSelectedIndex()].toString());
} catch (Exception ex) { 
  System.out.println("Empty set.") 
}

...returns the message:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TT' in 'on clause'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.Util.getInstance(Util.java:384)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1476)
        at SystClasses.Folder$QTableModel.setQuery(Folder.java:333)
        at SystClasses.Folder$4.actionPerformed(Folder.java:146)
        at javax.swing.JComboBox.fireActionEvent(JComboBox.java:1240)
        at javax.swing.JComboBox.setSelectedItem(JComboBox.java:567)
        at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:603)
        at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(BasicComboPopup.java:817)
        at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:273)
        at java.awt.Component.processMouseEvent(Component.java:6267)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
        at javax.swing.plaf.basic.BasicComboPopup$1.processMouseEvent(BasicComboPopup.java:481)
        at java.awt.Component.processEvent(Component.java:6032)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
        at java.awt.Container.dispatchEventImpl(Container.java:2085)
        at java.awt.Window.dispatchEventImpl(Window.java:2478)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

...instead of the message "Empty set".

However, if I execute the same SQL Select statement in MySQL Command Line Client, then I just receive the message about the empty set (so, the SQL statement is correct).

If this code is executed for the non-empty set, then everything is ok.

[LianaN]

Perhaps, I must use SQLException:

try {
                 tableModel.setQuery("select FormAttr.at_code, FormAttr.at_size, FormAttr.at_visible from FormAttr inner join FormTypes"
                 + " on FormAttr.ft_code = FormTypes.ft_code and FormAttr.ft_code = " + formTypeCodesArray[cmbFormType.getSelectedIndex()].toString());
              } catch (SQLException sqlException) {
                 System.out.println(sqlException.getMessage());
              }

However, I've got compilation error "exception java.sql.SQLException is never thrown in body of corresponding try statement"...

[LianaN]

Well, finally I understand the problem. I just had to change the setQuery procedure as follows (see the "catch" statement):

public void setQuery(String q) {
    cache = new Vector();
    try {
      ResultSet rs = statement.executeQuery(q);
      ResultSetMetaData meta = (ResultSetMetaData) rs.getMetaData();
      colCount = meta.getColumnCount();

      headers = new String[colCount];
      for (int h = 1; h <= colCount; h++) {
        headers[h - 1] = meta.getColumnName(h);
      }

      while (rs.next()) {
        Object[] record = new Object[colCount];
        for (int i = 0; i < colCount; i++) {
          record[i] = rs.getObject(i + 1);
        }
        cache.addElement(record);
      }
      fireTableChanged(null);
    } catch (Exception e) {
      cache = new Vector();
      attrTable.removeAll(); // I HAD TO PUT THIS 2 LINES OF CODE
      attrTable.updateUI();  // INSTEAD OF e.printStackTrace();
      //e.printStackTrace();
    }
  }