I want to move specific lines (some fields) from a tableA to a tableB by deleting these rows from tableA
.
I use Delphi version Rio with FireDAC with Firebird RDMBS.
- I use a procedure to move and delete a single line
- I can not use this cursor to move multiple lines
I use procedure 1 to move and delete a single line. The problem is that when I use the procedure 2 (EXECUTE PROCEDURE "PRST_RetransFert";
) I get an error message:
Engine Error (code = 335544336): deadlock.
update conflicts with concurrent update.
concurrent transaction number is 41556.
At procedure 'PRST_INSERTMEMODELETTRANSF' line: 36, col: 3
At procedure 'PRST_RetransFert' line: 13, col: 15.
SQL Error (code = -913): deadlock.
This procedure alone works fine.
RECREATE PROCEDURE PRST_INSERTMEMODELETTRANSF (
IDTRANSFMEMO INTEGER)
AS
declare variable numero varchar(10);
declare variable sommetransf decimal(15,2);
declare variable crdtransf smallint;
declare variable marge decimal(15,2);
declare variable idut integer;
declare variable idcl integer;
declare variable optionenvoie integer;
declare variable idop integer;
declare variable idoptionoperat integer;
declare variable naturetransf varchar(25);
declare variable codetransfert varchar(25);
declare variable coderapel varchar(25);
declare variable codepin varchar(25);
declare variable avantconnect smallint;
declare variable etattransf varchar(255);
begin
/* Procedure Text */
:etattransf = '....NON TRANSMIS RETRANSFERE EN ATTENTE.....' ;
:AVANTCONNECT = 0 ;
select numero ,somme ,credit_cl ,marge,id_ut ,
id_cl , option_envoie ,id_op ,id_option_operat
,nature_transf ,code_transfert , code_rapel ,Code_pin
from v_nontransmi where id_transfmemo = :idtransfmemo
into :numero,:sommetransf , :CRDTRANSF , :marge , :idut
, :idcl , :optionenvoie , :idop , idoptionoperat , :naturetransf
, :codetransfert , :coderapel , :codepin ;
insert into transfertsmemoire (numero, somme_transf, crd_transf, marge, id_ut, etat_transf, id_cl, avant_connect,
option_envoie, id_op, id_option_operat, nature_transf, code_transfert, code_rapel, code_pin)
Values (:Numero,:sommetransf, :crdtransf,:marge,:idut,:etattransf,:idcl, :avantconnect,:optionenvoie,:idop,
:idoptionoperat,:naturetransf,:codetransfert,:coderapel,:codepin);
delete from transferts where id_transfmemo = :idtransfmemo ;
-- suspend;
end
I can not use this SP to move multiple lines
RECREATE PROCEDURE "PRST_RetransFert"
AS
declare variable id_trsMem integer ;
declare curIdMemoTran Cursor for
(select id_transfmemo from v_nontransmi);
begin
open curIdMemoTran;
while (row_count > 0) do
begin
fetch curIdMemoTran into :id_trsMem ;
execute procedure prst_insertmemodelettransf (:id_trsMem);
if (row_count = 0) then leave;
suspend;
end
close curIdMemoTran ;
end