Devart Forums

I'm using the latest version of IBDAC. With Delphi XE7 an windows 7.
Firebird 2.5.3

I am obviously misunderstanding how Transactions work using IBDAC.
I have a Connection with AutoCommit = FALSE
a query = qryPatient with AutoCommit = FALSE
I have an update Transaction = trnWrite in qryPatient.
trnWrite default action on close set to Commit.

When I run the following method - I expect the changes to be rolled back but they are always committed. What am I doing wrong?
Method is still under construction so it's programming layout will be improved

function TdmEndoPix.SavePatient(const AStudy: IStudy): Boolean;
begin
{TODO -oPeter : check on how to use transactions}
trnWrite.StartTransaction;
try
qryPatient.SQL.Clear;
qryPatient.SQL.Add('UPDATE OR INSERT INTO PATIENT');
qryPatient.SQL.Add('(WHO, FULLNAME, SEX, DOB, WHO_SHADOW)');
qryPatient.SQL.Add('VALUES (:WHO, :FULLNAME, :SEX, :DOB, :WHO_SHADOW)');
qryPatient.SQL.Add('MATCHING (WHO_SHADOW, FULLNAME)');
qryPatient.SQL.Add('RETURNING ID');

if not (qryPatient.Prepared) then
begin
qryPatient.Prepare;
end; {if}

SetPatientParams(AStudy); <--- sets all the above parameters

qryPatient.ExecSQL;
// trnWrite.Commit; <---- commented out to test transaction
trnWrite.Rollback; <---- I expect this to cause the values inserted to be rolled back
Result := TRUE;
except
on E:Exception do
begin
ShowMessage('Debug: failed to Insert patient ' + E.Message);
Result := FALSE;
trnWrite.Rollback;
raise;
end;
end; {try/except}
end;

Why does the function always result in the values being committed even when rollback called.
Has it got something to do with the SQL statement I've constructed?

It doesn't seem to matter how I set the autocommit properties in the connection and query components the sql statement always succeeds in inserting the patient values into the DB

Found the answer,
The sql statement shown runs under the Transaction Property and not under the UpdateTransaction property as I assumed.
If I set the Connection.Default transaction property to Transaction it works as advertised.
I was therefore starting the wrong transaction

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about IBDAC.