How to handle database exceptions

[Hosh Nasi]

I have a gui program that displays a table and allows users to click on diffrent fields and change the data. Almost all these fields are unique to one record though. If the use input a duplicate unique value and tries to comit the changes the database throws an error that basically does nothing but say "Dupplicate key" What I want to do is put the cursor in the second duped field keeping the first one.

It sounds like I am not sure on the exception scheme because I am not. I made this in JBuilder, and I am not very proficiant in Java GUI developement.

Thank you!

**Looks like this is main source of exception catching.**

catch(Exception e) {
e.printStackTrace();
}
[ October 29, 2004: Message edited by: Hosh Nasi ]

[Jeff Bosch]

In your catch statement, you could add another try-catch block and invoke the JTable's editCellAt method with the cell's row and column indexes. (The second try-catch is to catch the IllegalArgumentException if either of the two indexes is out of bounds of the available JTable dimensions.)

[David Harkness]

This has always been a difficult problem to address due to the nature of the typical application architecture:

Read data from database

User changes some data

Write all changes to database

Error handling is trivial if the user modifies (or adds or deletes) only one row, but this becomes difficult with more. Even when the client layer tracks all of the changes, writing code to determine which cell caused an error can be tiresome.

One method I've used is to parse the exception message for the integrity constraint that was violated. You can use the Strategy pattern to supply an exception translator to your DAOs and implement one for each database dialect (MySQL, Oracle, etc) to which you deploy your application.

Even that doesn't solve the entire problem. For example, you determine that the USER_EMAIL_UK constraint was violated, telling you the user has given two people the same email address. But the constraint name doesn't tell you which row caused the problem, so you still must search the table for duplicates.

One easy out is to tell the user as much information as you can easily get and leave the rest to them: "You have specified a duplicate email address." Let the user sort it out.

You can create a constraint name to error message (key) table for each window to allow for a generic framework.
[ October 30, 2004: Message edited by: David Harkness ]

[Hosh Nasi]

Thanks guys for the replies. Upon further inspection I have learned that when the program does in fact throw the JDataStore dupplicate error it automatically selects the row with the dupe data. I can attribute this to only two things.

1. JDataStore inputs data row by row from the resultset.
2. There is some kind of internal handle that is using the JDataStore error to figure out what record is duped. If that is the case I just need to plug into that..

Part of the TraceStack says

 See com.borland.dx.sql.dataset.ResolutionException error code:  BASE+3
 
com.borland.dx.sql.dataset.ResolutionException: An error occurred saving a change to the "FIELD" table
 

[David Harkness]

Originally posted by Hosh Nasi:
JDataStore inputs data row by row from the resultset.

That would be my guess. Back when I wrote PowerBuilder GUI code, all data modifications (insert/update/delete) were handled row-by-row. Thus any SQL errors were raised for a single row, making placement of the cursor easy.

[Hosh Nasi]

I am very perplexed.. I am not sure where the error handleing is happeing within my program. I have put in many break points and added diffrent dialog boxes to test which exception path is being followed but nothing works I am confused..

As you can see the blanket exception catch has a diaglo message before the printstack. However this is never displayed.

package test2;
 
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import com.borland.dx.sql.dataset.*;
import com.borland.dx.dataset.*;
import com.borland.dbswing.*;
 
/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
 
public class TestFrame1 extends JFrame {
  JPanel contentPane;
  JMenuBar jMenuBar1 = new JMenuBar();
  JMenu jMenuFile = new JMenu();
  JMenuItem jMenuFileExit = new JMenuItem();
  JMenu jMenuHelp = new JMenu();
  JMenuItem jMenuHelpAbout = new JMenuItem();
  ImageIcon image1;
  ImageIcon image2;
  ImageIcon image3;
  BorderLayout borderLayout1 = new BorderLayout();
  Column column2 = new Column();
  QueryDataSet queryDataSet1 = new QueryDataSet();
  Database database1 = new Database();
  DBDisposeMonitor dBDisposeMonitor1 = new DBDisposeMonitor();
  TableScrollPane tableScrollPane1 = new TableScrollPane();
  JdbTable jdbTable1 = new JdbTable();
  JdbNavToolBar jdbNavToolBar1 = new JdbNavToolBar();
  JButton jButton1 = new JButton();
 
  //Construct the frame
  public TestFrame1() {
    enableEvents(AWTEvent.WINDOW_EVENT_MASK);
   [B] try {
      jbInit();
    }
    catch(com.borland.dx.sql.dataset.ResolutionException e){
      JOptionPane.showMessageDialog(null, "Test database explosion.", "unique key dupe",
                 JOptionPane.INFORMATION_MESSAGE);
    }
    catch(Exception e) {
      JOptionPane.showMessageDialog(null, "Test database explosion.", "unique key dupe",
                 JOptionPane.INFORMATION_MESSAGE);
      e.printStackTrace();[/B]
    }
  }
 
  //Component initialization
  private void jbInit() throws Exception  {
    image1 = new ImageIcon(test2.TestFrame1.class.getResource("openFile.png"));
    image2 = new ImageIcon(test2.TestFrame1.class.getResource("closeFile.png"));
    image3 = new ImageIcon(test2.TestFrame1.class.getResource("help.png"));
    contentPane = (JPanel) this.getContentPane();
    contentPane.setLayout(borderLayout1);
    this.setResizable(false);
    this.setSize(new Dimension(718, 252));
    this.setTitle("Frame Title");
    jMenuFile.setText("File");
    jMenuFileExit.setText("Exit");
    jMenuFileExit.addActionListener(new TestFrame1_jMenuFileExit_ActionAdapter(this));
    jMenuHelp.setText("Help");
    jMenuHelpAbout.setText("About");
    jMenuHelpAbout.addActionListener(new TestFrame1_jMenuHelpAbout_ActionAdapter(this));
    column2.setColumnName("NewColumn2");
    column2.setDataType(com.borland.dx.dataset.Variant.STRING);
    column2.setPreferredOrdinal(1);
    column2.setServerColumnName("NewColumn2");
    column2.setSqlType(0);
    queryDataSet1.setAccumulateResults(false);
       dBDisposeMonitor1.setDataAwareComponentContainer(this);
    jdbTable1.setDataSet(queryDataSet1);
    tableScrollPane1.setMaximumSize(new Dimension(32767, 32767));
    tableScrollPane1.setMinimumSize(new Dimension(24, 24));
    jButton1.setText("Do Something");
    jButton1.setVerticalAlignment(javax.swing.SwingConstants.CENTER);
    jButton1.addActionListener(new TestFrame1_jButton1_actionAdapter(this));
    jMenuFile.add(jMenuFileExit);
    jMenuHelp.add(jMenuHelpAbout);
    jMenuBar1.add(jMenuFile);
    jMenuBar1.add(jMenuHelp);
    contentPane.add(jdbNavToolBar1, BorderLayout.NORTH);
    jdbNavToolBar1.add(jButton1, null);
    contentPane.add(tableScrollPane1, BorderLayout.CENTER);
    tableScrollPane1.getViewport().add(jdbTable1, null);
    this.setJMenuBar(jMenuBar1);
  }
 
  //File | Exit action performed
  public void jMenuFileExit_actionPerformed(ActionEvent e) {
    System.exit(0);
  }
 
  //Help | About action performed
  public void jMenuHelpAbout_actionPerformed(ActionEvent e) {
    TestFrame1_AboutBox dlg = new TestFrame1_AboutBox(this);
    Dimension dlgSize = dlg.getPreferredSize();
    Dimension frmSize = getSize();
    Point loc = getLocation();
    dlg.setLocation((frmSize.width - dlgSize.width) / 2 + loc.x, (frmSize.height - dlgSize.height) / 2 + loc.y);
    dlg.setModal(true);
    dlg.pack();
    dlg.show();
  }
 
  //Overridden so we can exit when window is closed
  protected void processWindowEvent(WindowEvent e) {
    super.processWindowEvent(e);
    if (e.getID() == WindowEvent.WINDOW_CLOSING) {
      jMenuFileExit_actionPerformed(null);
    }
  }
 
  void jButton1_actionPerformed(ActionEvent e) {
    JOptionPane.showMessageDialog(null, "Test database explosion.", "unique key dupe",
                 JOptionPane.INFORMATION_MESSAGE);
  }
}
 
class TestFrame1_jMenuFileExit_ActionAdapter implements ActionListener {
  TestFrame1 adaptee;
 
  TestFrame1_jMenuFileExit_ActionAdapter(TestFrame1 adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jMenuFileExit_actionPerformed(e);
  }
}
 
class TestFrame1_jMenuHelpAbout_ActionAdapter implements ActionListener {
  TestFrame1 adaptee;
 
  TestFrame1_jMenuHelpAbout_ActionAdapter(TestFrame1 adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jMenuHelpAbout_actionPerformed(e);
  }
}
 
class TestFrame1_jButton1_actionAdapter implements java.awt.event.ActionListener {
  TestFrame1 adaptee;
 
  TestFrame1_jButton1_actionAdapter(TestFrame1 adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jButton1_actionPerformed(e);
  }
}

[ November 02, 2004: Message edited by: Hosh Nasi ]

[ November 02, 2004: Message edited by: Hosh Nasi ]
[ November 02, 2004: Message edited by: Hosh Nasi ]