You are not logged in.
- Topics: Active | Unanswered
Pages: 1
#1 2015-05-10 14:28:55
- Celso
- Member
- Registered: 2013-05-14
- Posts: 55
Transactio inside thread
I have a client/server application.
On the server side, I have a thread that inserts records in the database. But I'm not getting the transaction to be respected. When a rollback is performed, the permance record in the database. My code looks like this:
TLongWorkServiceThread = class(TThread)
protected
fCallback: ILongWorkCallback;
fWorkType: Integer;
ffile : TStringList;
fuser : Integer;
ftix : Int64;
fConn : TSQLDBConnection;
procedure Execute; override;
public
constructor Create(const worktype: Integer;
const callback: ILongWorkCallback;
const Afile : TStringList;
const Auser : Integer);
....
{ TLongWorkServiceThread }
constructor TLongWorkServiceThread.Create(const worktype: Integer;
const callback: ILongWorkCallback;
const Afile : TStringList;
const Auser : Integer);
begin
inherited Create(false);
fCallback := Callback;
fWorkType := worktype;
fuser := Auser;
ftix := GetTickCount64;
ffile := TStringList.Create;
ffile.Assign(Afile);
FreeOnTerminate := true;
end;
procedure TLongWorkServiceThread.Execute;
var
v_count,
v_ID : Integer;
v_cpuibpt : TSQLCPU_IBPT;
v_cpuibptdet: TSQLCPU_IBPTDET;
v_cputipi : TSQLCPU_TIPI;
begin
inherited;
case fWorkType of
0: //Processar arquivo IBPT
begin
try
fConn := frmDMServer.fProps.NewConnection;
fConn.Connect;
fConn.StartTransaction;
for v_count := 0 to ffile.Count - 1 do
begin
case v_count of
0: ;
1: begin
v_cpuibpt := TSQLCPU_IBPT.Create;
v_cpuibptdet := TSQLCPU_IBPTDET.Create;
v_cpuibpt.IBPTVERSION := fn_getposstring(ffile[v_count],';',12);
v_cpuibpt.IBPT_START := StrToDate(fn_getposstring(ffile[v_count],';',9));
v_cpuibpt.IBPT_FINISH := StrToDate(fn_getposstring(ffile[v_count],';',10));
v_cpuibpt.IBPTSOURCE := fn_getposstring(ffile[v_count],';',13);
v_cpuibpt.IBPTKEY := fn_getposstring(ffile[v_count],';',11);
v_cpuibpt.INC_DATE := now;
v_cpuibpt.INC_USER := fuser;
v_ID := frmDMServer.aServerDB.Add(v_cpuibpt,True,False);
......
if (condiction) then
begin
fConn.Rollback;
v_cpuibpt.Free;
v_cpuibptdet.Free;
fCallback.WorkFailed(fWorkType,
frmServAppRM.OLANG.fn_lerini('Mens','cpuMMensNotInsert','cpuMMensNotInsert'));
exit;
end
else
fConn.Commit;
end;
........
What am I doing wrong?
Offline
#2 2015-05-10 14:35:47
Re: Transactio inside thread
You are mixing things up here.
You transaction is at TSQLDBConnection level (i.e. external DB), whereas the operation is at TSQLRestServer level.
So the add statements are not at the same level.
All the actions you take at fConn level are not connected at all with the aServer.Add().
To make such process, and in an efficient way, use a BATCH command, with its own internal transaction, via the AutomaticTransactionPerRow parameter.
It would be faster and safer.
See http://synopse.info/files/html/Synopse% … ml#TITL_28
Offline
#3 2015-05-12 17:26:43
- Celso
- Member
- Registered: 2013-05-14
- Posts: 55
Re: Transactio inside thread
Thanks for the feedback.
I changed my code looked like this:
procedure TLongWorkServiceThread.Execute;
var
v_count,
v_ID : Integer;
v_batchibpt,
v_batchibptdet: TSQLRestBatch;
v_cpuibpt : TSQLCPU_IBPT;
v_cpuibptdet : TSQLCPU_IBPTDET;
v_cputipi : TSQLCPU_TIPI;
begin
inherited;
try
for v_count := 0 to ffile.Count - 1 do
begin
case v_count of
0: ;
1: begin
v_cpuibpt := TSQLCPU_IBPT.Create;
v_batchibpt := TSQLRestBatch.Create(frmDMServer.aServerDB,TSQLCPU_IBPT);
v_cpuibptdet := TSQLCPU_IBPTDET.Create;
v_batchibptdet := TSQLRestBatch.Create(frmDMServer.aServerDB,TSQLCPU_IBPTDET);
v_cpuibpt.IBPTVERSION := fn_getposstring(ffile[v_count],';',12);
v_cpuibpt.IBPT_START := StrToDate(fn_getposstring(ffile[v_count],';',9));
v_cpuibpt.IBPT_FINISH := StrToDate(fn_getposstring(ffile[v_count],';',10));
v_cpuibpt.IBPTSOURCE := fn_getposstring(ffile[v_count],';',13);
v_cpuibpt.IBPTKEY := fn_getposstring(ffile[v_count],';',11);
v_cpuibpt.INC_DATE := now;
v_cpuibpt.INC_USER := fuser;
v_ID := v_batchibpt.Add(v_cpuibpt,True,False);
if v_ID = 0 then
begin
v_batchibpt.Free;
v_batchibptdet.Free;
v_cpuibpt.Free;
v_cpuibptdet.Free;
fCallback.WorkFailed(fWorkType,
frmServAppRM.OLANG.fn_lerini('Mens','cpuMMensNotInsert','cpuMMensNotInsert'));
exit;
end;
end;
else
begin
v_cputipi := TSQLCPU_TIPI.Create(frmDMServer.aServerDB,
'TIPINCM=? AND TIPIEXT=?',
[fn_getposstring(ffile[v_count],';',1),
fn_getposstring(ffile[v_count],';',2)]);
if (v_cputipi.TIPINCM = fn_getposstring(ffile[v_count],';',1)) and
(v_cputipi.TIPIEXT = fn_getposstring(ffile[v_count],';',2)) then
begin
v_cpuibptdet.IDIBPT := v_ID;
v_cpuibptdet.IDTIPI := v_cputipi.ID;
v_cpuibptdet.IBPTALIQ_NATION := StrToFloat(fn_getposstring(ffile[v_count],';',5));
v_cpuibptdet.IBPTALIQ_INTERN := StrToFloat(fn_getposstring(ffile[v_count],';',6));
v_cpuibptdet.IBPTALIQ_STATE := StrToFloat(fn_getposstring(ffile[v_count],';',7));
v_cpuibptdet.IBPTALIQ_CITY := StrToFloat(fn_getposstring(ffile[v_count],';',8));
v_cpuibptdet.INC_DATE := now;
v_cpuibptdet.INC_USER := fuser;
v_cpuibptdet.MOD_USER := fuser;
if v_batchibptdet.Add(v_cpuibptdet,True,False) = 0 then
begin
v_batchibpt.Free;
v_batchibptdet.Free;
v_cpuibpt.Free;
v_cpuibptdet.Free;
fCallback.WorkFailed(fWorkType,
frmServAppRM.OLANG.fn_lerini('Mens','cpuMMensNotInsert','cpuMMensNotInsert'));
exit;
end;
end;
v_cputipi.Free;
end;
end;
end;
frmDMServer.aServerDB.BatchSend(v_batchibpt);
frmDMServer.aServerDB.BatchSend(v_batchibptdet);
v_batchibpt.Free;
v_batchibptdet.Free;
v_cpuibpt.Free;
v_cpuibptdet.Free;
fCallback.WorkFinished(fWorktype,GetTickCount64 - ftix)
except
on E: Exception do
begin
if not (E is EAbort) then
begin
v_batchibpt.Free;
v_batchibptdet.Free;
fCallback.WorkFailed(fWorkType,E.Message);
end;
end;
end;
end;
end;
But it is not working. The add the line "v_ID := v_batchibpt.Add(v_cpuibpt,True,False);" always returns zero.
What could be wrong?
Offline
#4 2015-05-12 19:10:50
Re: Transactio inside thread
Your code is difficult to follow.
The "case" within the loop is just aweful.
The way you are handling instance lifetime, especially the batches, is difficult to debug, and certainty defectivve.
You should better create a single batch outside the loop.
Please RTFM about TSqlRestBatch.Add: it returns the index of the command, starting from 0, which means it is correct.
Offline
#5 2015-05-12 19:52:29
- Celso
- Member
- Registered: 2013-05-14
- Posts: 55
Re: Transactio inside thread
I'll try to explain.
I have to read a csv file.
In the first line of this file I have to get some information and write to a master table. I next lines of this file, I have to write to a detail table. This is why I need the ID of the master table.
But I need a transaction of all, because it fails at some point, have to give RollBak at all.
We clear?
Offline
Pages: 1