How to detect if an execute failed?

[docH]

Using Delphi I run MyStoredProc.execute to do an insert and return the last insert ID.

How should I correctly code, in Delphi, the situation where the execute might fail, eg if MyStoredProc was unable to do an insert (and so the last ID is invalid)?

Is there a MyStoredProc event I can use or do I just have to put it in a try - except block and let it fail?

(I realise I could put the insert and the select inside a transaction so the select doesn't run if the insert fails but I still need to know that the returned ID isn't valid.)

In essence the stored proc is below (real code has more parameters)

Code: Select all

DELIMITER $$
CREATE    PROCEDURE InsertNewArea(new_area VARCHAR(45), area_code VARCHAR(5), 
                                  OUT area_id INTEGER)
DETERMINISTIC

BEGIN
    INSERT INTO Areas (AreaName, AreaCode) 
    VALUES  (new_area, area_code) ; 
    SET area_id = (SELECT LAST_INSERT_ID() FROM Areas);
END$$

DELIMITER ;

[AlexP]

Hello,

Yes, for this, you should use a Try...Except block, in which your procedure will be executed, and if no errors occured during execution, then the correct ID of the inserted record is returned. If there are errors, then handle them in the except block:

Code: Select all

  try
    MyStoredProc1.Execute;
    ShowMessage('OK, LastInsert id = ' + IntToStr(MyStoredProc1.Params[2].AsInteger));
  Except
    on E: EMyError do
      case e.ErrorCode of
        xxxy: ;//do anything;
        yyyy: ;//do anything;
        Else  ;// do anything;
      end;
    end;
  end;