CODEWITHSUNDEEP
sql-serverdelphierror-handling
Niyoko
Niyoko

Reputation: 7672

Database constraint violation error handling

I still confused how to handle database violation error. Is this code give best possible error handling? Or any other way to handle error, which also make user clear about why error happen?

procedure TForm2.cxButton1Click(Sender: TObject);
var
  sp:TADOStoredProc;
  errorMsg : string;
begin
  //
  sp := TADOStoredProc.Create(nil);
  try
    sp.Connection := FOrm1.ADOConnection1;
    sp.ProcedureName := 'cfg.AddConfiguration';
    sp.Parameters.CreateParameter('@RETURN_VALUE', TFieldType.ftInteger, pdReturnValue, 0, 0);
    sp.Parameters.CreateParameter('@key', ftString, pdInput, 50, cxTextEdit1.Text);
    sp.Parameters.CreateParameter('@caption', ftString, pdInput, 50, cxTextEdit2.Text);
    sp.Parameters.CreateParameter('@datatype', ftString, pdInput, 50, cxComboBox1.Text);
    sp.Parameters.CreateParameter('@description', ftString, pdInput, 4000, cxMemo1.Text);
    try
      sp.ExecProc;
    except
      on e:EOleException do
      begin
        errorMsg := 'Failed to add new configuration.';
        if e.ErrorCode = 2601 then
        begin
          errorMsg := errorMsg + sLineBreak + 'Possible duplicate in key!';
        end;
        MessageDlg(errorMsg, mtError, [mbOK], 0);
      end;
    end;
  finally
    sp.Free;
  end;
end;

Upvotes: 3

Views: 1525

Answers (2)

mjn
mjn

Reputation: 36654

I recommend to move the procedure call and error processing away from the user interface (form "Form2") to a separate method in the data module. I would also make sure that no message boxes / dialogs will be shown from within this method, to support usage in server-side (middle-tier, application server etc.) or non GUI type apps too.

Then I would use either a return code or application specific exceptions to report errors to the calling GUI method.

Upvotes: 2

itadapter DKh
itadapter DKh

Reputation: 590

The code that you have is very database-dependent. Every RDBMS reports those constraint violations differently. If you only use particular database (judging by the usage of ADO objects) then yes, errorCode can be inspected, however I would have encapsulated these RDBMS-specific errorCode checks in the stored procedure itself and then returned my app-specific error-reason code ans showed user a message thus shielding Delphi layer from ugly db exceptions altogether.

Upvotes: 4

Related Questions