Habe mal die 4
DB-Aktionen gepostet.
Ich bin mir nicht sicher ob ich die Transaktionen richtig nutze?
Muss ich bei einem Select oder bei einer Stored Procedure die Transaktion commiten oder kann ich das DataSet einfach schließen?
Danke für die Hilfe...
Delphi-Quellcode:
function TDBFIBPlus2.GetPrimaryKey: Integer;
begin
FStoredProcPrimaryKey.Transaction.StartTransaction;
FStoredProcPrimaryKey.StoredProcName := 'GET_PRIMARYKEY';
FStoredProcPrimaryKey.ExecProc;
Result := FStoredProcPrimaryKey.Fields[0].AsInteger;
FStoredProcPrimaryKey.Commit;
end;
//------------------------------------------------------------------------------
procedure TDBFIBPlus2.Insert(DataRec: TDataRec);
begin
DataRec.Id := GetPrimaryKey;
DataRec.LastUpdate := Now;
FQueryInsert.Transaction.StartTransaction;
if (not FQueryInsert.Prepared) then begin
FQueryInsert.SQL.Text := 'INSERT INTO table_test (id, name, number, lastupdate)'+
' VALUES(:id, :name, :number, :lastupdate)';
FQueryInsert.Prepare;
end;
FQueryInsert.ParamByName('id').AsInteger := DataRec.Id;
FQueryInsert.ParamByName('name').AsString := DataRec.Name;
FQueryInsert.ParamByName('number').AsInteger := DataRec.Id;
FQueryInsert.ParamByName('lastupdate').AsDateTime := DataRec.LastUpdate;
FQueryInsert.ExecQuery;
FQueryInsert.Commit;
end;
//------------------------------------------------------------------------------
function TDBFIBPlus2.Select(Id: Integer): TDataRec;
begin
Result := nil;
FDataSetSelect.AutoCommit := True;
if (not FDataSetSelect.Prepared) then begin
FDataSetSelect.Close;
FDataSetSelect.SelectSQL.Text := 'SELECT * FROM table_test where id=:id';
FDataSetSelect.Prepare;
end;
FDataSetSelect.Close;
FDataSetSelect.ParamByName('id').AsInteger := Id;
FDataSetSelect.Open;
if (not FDataSetSelect.IsEmpty) then begin
Result := TDataRec.Create;
Result.Id := Id;
Result.Name := FDataSetSelect.FieldValues['name'];
Result.Number := FDataSetSelect.FieldValues['number'];
Result.LastUpdate := FDataSetSelect.FieldValues['lastupdate'];
end;
end;
//------------------------------------------------------------------------------
procedure TDBFIBPlus2.Update(DataRec: TDataRec);
begin
DataRec.LastUpdate := Now;
FQueryUpdate.Transaction.StartTransaction;
if (not FQueryUpdate.Prepared) then begin
FQueryUpdate.SQL.Text := Format('UPDATE table_test SET name=:name, number=:number, lastupdate=:lastupdate WHERE id=:id',[]);
FQueryUpdate.Prepare;
end;
FQueryUpdate.ParamByName('id').AsInteger := DataRec.Id;
FQueryUpdate.ParamByName('name').AsString := DataRec.Name;
FQueryUpdate.ParamByName('number').AsInteger := DataRec.Id;
FQueryUpdate.ParamByName('lastupdate').AsDateTime := DataRec.LastUpdate;
FQueryUpdate.ExecQuery;
FQueryUpdate.Commit;
end;
//------------------------------------------------------------------------------
procedure TDBFIBPlus2.Delete(DataRec: TDataRec);
begin
FQueryDelete.Transaction.StartTransaction;
if (not FQueryDelete.Prepared) then begin
FQueryDelete.SQL.Text := 'Delete FROM table_test where id=:id';
FQueryDelete.Prepare;
end;
FQueryDelete.ParamByName('id').AsInteger := DataRec.Id;
FQueryDelete.ExecQuery;
FQueryDelete.Commit;
end;
//------------------------------------------------------------------------------