Delphi 2009, How can I detect if a MySQL transaction was rolled back?

Question

Adapting the code from mysql transaction - roll back on any exception I am making two inserts into a table via a transaction that rolls back if either insert fails. I would like to use Delphi to detect if a rollback has happened or not.

I already use a try - except block to check for errors in executing the whole transaction but presumably a rollback is treated by Delphi as a correct execution.

Is the only way to do this to use a SELECT afterward to see if the data is there?

That could be tricky as the live data that is inserted could possibly be duplicated if two transactions happened for the same person, same amount and on the same day. (The uniqueness of each row is implemented via an autoincrement column (subs_paid_id) that doesn't appear in the inserts)

At the risk of being verbose I have below shown the DLL for the table and my delphi code for doing the inserts.

(ToSQL is just a class that converts various passed parameters into a format suitable for a SQL string. MyConnection1 is a Devart MyDAC TMyConnection conected to a remote database)

/*DDL Information*/
-------------------

CREATE TABLE subscriptions_paid 
(
  subscription_year varchar(4) NOT NULL DEFAULT '',
  member_id int(11) NOT NULL DEFAULT '0',
  individual_subs_due float DEFAULT NULL,
  individual_subs_paid float DEFAULT NULL,
  payment_date date DEFAULT NULL,
  import_date date DEFAULT NULL,
  user_comment varchar(100),
  subs_paid_id int(11) NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (subscription_year,member_id,subs_paid_id),
  KEY subs_paid_id (subs_paid_id)
) 
ENGINE=InnoDB AUTO_INCREMENT=538 DEFAULT CHARSET=utf8

and the Delphi code, including generating the SQL is

procedure TFrm_EditSubsPaid.btnConfirmTransferToSelectedClick(Sender: TObject);

//make sql to add a new payment for FirstMemberID, FirstSubsDue, FirstSubsYear using
//dtpNewPaymentDate, edtNewPaymentComment,  edtNewPayment and
//a new negative 'payment' for   SecondMemberID,  SecondSubsDue, 
//SecondSubsYear, using  edtAmountToTransferFrom , dtpTransferFrom and  
//edtNewTransferFromComment

var overpaid : single;
begin
if TransferAmountValid then
   begin
   SQL := ''
   +'DELIMITER $$  '
   +'CREATE PROCEDURE transfer()  '
   +'BEGIN '
   //next two lines allow for all the transaction to be rolled back if any insert fails
   +'   DECLARE `_rollback` BOOL DEFAULT 0; '
   +'   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;  '

   + 'START TRANSACTION '
   +'INSERT INTO subscriptions_paid '   //the positive payment into account
   +'       (                            '
   +'              subscription_year   , '
   +'              member_id           , '
   +'              individual_subs_due , '
   +'              individual_subs_paid, '
   +'              payment_date        , '
   +'              import_date         , '
   +'              user_comment          '
   +'       ) '
   +'       VALUES '
   +'       (                                 '
   +'         ToSQL.Text(FirstSubsYear) + ', '
   +          FirstMemberID + ', '
   +          ToSQL.Float(StrToFloat(FirstSubsDue)) +', '
   +          ToSQL.Float(StrToFloat(edtAmountToTransferFrom.Text)) +', '
   +          ToSQL.Date(DateOf(dtpTransferFrom.date)) + ', '
   +          'NULL' + ', '
   +          ToSQL.Text('(Tfr From ' + SecondMemberID +') ' + edtNewTransferFromComment.text) +' '
   +          ');'

   +'INSERT INTO subscriptions_paid '     //the negative payment out of account
   +'       (                            '
   +'              subscription_year   , '
   +'              member_id           , '
   +'              individual_subs_due , '
   +'              individual_subs_paid, '
   +'              payment_date        , '
   +'              import_date         , '
   +'              user_comment          '
   +'       ) '
   +'       VALUES '
   +'       ( '
   +        ToSQL.Text(FirstSubsYear) + ', '
   +        SecondMemberID + ', '
   +        ToSQL.Float(StrToFloat(SecondSubsDue)) +', '
   +        ToSQL.Float(StrToFloat('-' + edtAmountToTransferFrom.Text)) +', '
   +        ToSQL.Date(DateOf(dtpTransferFrom.date)) + ', '
   +        'NULL' + ', '
   +        ToSQL.Text('(Tfr To ' + FirstMemberID +') ' + edtNewTransferFromComment.text) +' '
   +        ');'

   //next five lines allow for all the transaction to be rolled back if any insert fails
   +'IF `_rollback` THEN '
   +'        ROLLBACK;   '
   +'ELSE                '
   +'        COMMIT;     '
   +'END IF; ' 

   +'END$$'
   +'DELIMITER ;'   ;


  try
   begin
     dMod.MyConnection1.ExecSQL(sql);
     dMod.MyConnection1.ExecSQL('CALL transfer;');


   // ???? now check if the inserts went OK ???

     end;
 except
 on E : Exception do
     begin
       showmessage (
                        'Exception class name = '+E.ClassName+ slinebreak
                +  'Exception message = '+E.Message);
      end  //on E
 end;//try
 end; //if


end;