On laravel 10 site with mysql 8 I have a stored procedure, which raise error SIGNAL in case of invalid operation
create procedure completeTask(IN in_taskId bigint unsigned,
IN completeSubtasks tinyint unsigned,
IN in_userId bigint unsigned, OUT retCount bigint unsigned)
BEGIN
DECLARE taskCreatorId bigint unsigned;
SELECT concat('in_taskId : ', in_taskId);
SELECT concat('in_userId : ', in_userId);
SELECT creator_id INTO taskCreatorId from tasks WHERE id = in_taskId;
SELECT concat('taskCreatorId : ', taskCreatorId);
IF (in_userId != taskCreatorId) THEN
SIGNAL SQLSTATE '45000' -- I RAISE ERROR
SET MESSAGE_TEXT = 'Only creator can complete task !';
SET retCount := -1; -- NOT SHUR IF THERE IS A SENSE TO SET NEGATIVE CODE - ASS ERROR OPERATION
END IF;
IF (completeSubtasks = 1) THEN
UPDATE tasks SET status = 'D', completed_at = NOW() WHERE tasks.parent_id = in_taskId;
END IF;
UPDATE tasks SET status = 'D', completed_at = NOW() WHERE tasks.id = in_taskId;
SET retCount := in_taskId;
SELECT concat('retCount is ', retCount); -- SET retCount FOR SUCCESS
END;
From laravel I call procedure:
DB::select("call completeTask('" . $id . "', " . "1, " . Auth::user()->id . ", @returnCode)");
$data = DB::select('SELECT @returnCode as returnCode');
In case of successfull operation returned data looks like :
[0] => stdClass Object
(
[returnCode] => 102
)
In case of error operation(SIGNAL triggered) I just got stdClass Object with empty returnCode.
How to catch this error on laravel side ?
Which SQLSTATE for error better to use ?
Has block with SIGNAL return some code ?
Thanks in advance!