| Subject | Re: [firebird-support] What I am missing in the transaction ? |
|---|---|
| Author | |
| Post date | 2018-09-24T17:09:08Z |
Thanks Steve and Mark for these usefull informations about transactions
And the other way from is the client (delphi) :
I start a read transaction T1 where I check with a SELECT to see if the record exists and commit the T1
If the record doesn't exist the client show a message to the user and ignore the T2
if record exists I start the second write transaction T2 to update the record just after the T1 in the same code and commit T2. I don't know if it is correct. It seems to me so heavy.
As I have code in the database and in the client I have two ways for checking the deleted before updating record in a stored procedure like this:
CREATE OR ALTER PROCEDURE ASCENDANT_UI (
EMPLOYEUR_ID INTEGER,
EMPLOYE_ID INTEGER,
ASCENDANT_ID INTEGER,
NOM VARCHAR(30),
PRENOMS VARCHAR(30),
DATE_NAIS DATE
)
RETURNS (
ID INTEGER)
AS
DECLARE VARIABLE RES INTEGER;
DECLARE VARIABLE MES VARCHAR(100);
BEGIN
IF (COALESCE(:ASCENDANT_ID,0)<>0) THEN
BEGIN
SELECT 1 FROM ASCENDANT WHERE ASCENDANT_ID=:ASCENDANT_ID INTO :RES;
RES=COALESCE(:RES,0);
IF (:RES=0) THEN
BEGIN
MES ='[RECORD HAS BEEN DELETED]';
EXCEPTION ALREADY_EXISTS MES;
END
END
UPDATE OR INSERT INTO ASCENDANT (EMPLOYEUR_ID, EMPLOYE_ID, ASCENDANT_ID,
NOM, PRENOMS, DATE_NAIS)
VALUES
(:EMPLOYEUR_ID, :EMPLOYE_ID, :ASCENDANT_ID, :NOM, :PRENOMS, :DATE_NAIS)
MATCHING (EMPLOYEUR_ID, EMPLOYE_ID, ASCENDANT_ID)
RETURNING ASCENDANT_ID INTO :ID;
SUSPEND;
END
And the other way from is the client (delphi) :
I start a read transaction T1 where I check with a SELECT to see if the record exists and commit the T1
If the record doesn't exist the client show a message to the user and ignore the T2
if record exists I start the second write transaction T2 to update the record just after the T1 in the same code and commit T2. I don't know if it is correct. It seems to me so heavy.