| Subject | Re: [firebird-support] What I am missing in the transaction ? |
|---|---|
| Author | |
| Post date | 2018-09-24T14:46:44Z |
I would think that there are two ways that this issue could be easily handled...
1...
If the SQL code in question is within a stored procedure then error trapping could be implemented to throw an error back to the calling procedure. Thus, if a user attempted to update a record that no longer existed, the stored procedure could throw an error.
The following stored procedure throws an error if by accident a zero-based key is provided as the parameter value...
>>>
CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY(
PI_KEY_IN BIGINT NOT NULL)
RETURNS(
PI_KEY_OUT BIGINT,
PI_PARENT_KEY_OUT BIGINT)
AS
BEGIN
FOR
WITH RECURSIVE HIERARCHY_TABLE AS
(
SELECT RCN1.CN_KEY,
RCN1.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN1
WHERE RCN1.CN_KEY = :PI_KEY_IN
UNION ALL
SELECT RCN2.CN_KEY,
RCN2.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN2
JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY
)
SELECT CN_KEY,
CN_PARENT_KEY
FROM HIERARCHY_TABLE
INTO :PI_KEY_OUT,
:PI_PARENT_KEY_OUT
DO
BEGIN
IF (PI_KEY_IN = 0) THEN
EXCEPTION ROOT_CAT_NODE_DELETE;
SUSPEND;
END
END;
PI_KEY_IN BIGINT NOT NULL)
RETURNS(
PI_KEY_OUT BIGINT,
PI_PARENT_KEY_OUT BIGINT)
AS
BEGIN
FOR
WITH RECURSIVE HIERARCHY_TABLE AS
(
SELECT RCN1.CN_KEY,
RCN1.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN1
WHERE RCN1.CN_KEY = :PI_KEY_IN
UNION ALL
SELECT RCN2.CN_KEY,
RCN2.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN2
JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY
)
SELECT CN_KEY,
CN_PARENT_KEY
FROM HIERARCHY_TABLE
INTO :PI_KEY_OUT,
:PI_PARENT_KEY_OUT
DO
BEGIN
IF (PI_KEY_IN = 0) THEN
EXCEPTION ROOT_CAT_NODE_DELETE;
SUSPEND;
END
END;
<<<
The above code does not require any form of transactional processing since it is a retrieval routine. However, by using transactional processing, the existence of a record to be deleted can be tested for within the transaction and if not found can simply then ignore the process to delete it, returning a success result since nothing was done to incur an error.
Here is a link for the Firebird documentation on handling transactions.with the options to force a transaction to act in a single-threaded manner (WAIT option)
2...
The other option would be to do all the error trapping at the ADO.NET level, generically handling any error response a stored procedure\inline SQL code may throw.
I hope this information helps to resolve the current query on this issue...
Steve Naidamast
Sr. Software Engineer