DB: how could call a DB function?

REST API
11

Hey Ben,

Thanks for getting back to me quickly.
I figured out the reason. I have it working perfectly now.

For others who might find it useful.
Detailed description:
In MySQL, there is no return value when calling a stored procedure with the sole purpose of doing an insert or update. So hence, the error was occurring b/c there was no return value coming back. My guess would be that your code on your end is looking for either a result or an output return variable.
And since I was not using an output parameter in my stored procedure… the error was happening.
I don’t know what the correct solution for this would be. Although, I think you guys probably handled it correctly… in stating that it is indeed a technical error since DSP was expecting something that was not right (you can say). And it got me to tweak my stored procedure accordingly.

2 possible Solutions:
(1) Use an output parameter which indicates the status of your stored procedure.
(2) I created my own return value by using logic to capture the SQLState of the event by creating a handler for the SQLException and getting the DIAGNOSTICS CONDITION (in mysql).

CODE for (2) in your Stored Procedure:


CREATE STORED PROC spName (
  -- your parameters here
)
BEGIN
	-- Declare variables to hold diagnostics area information
	DECLARE code CHAR(5) DEFAULT '00000';
	DECLARE rows INT;
	DECLARE result INT;
	-- Declare exception handler for failed insert
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE;
    END;

    -- perform the insert. Put your insert statement here

	-- Check whether the insert was successful
	IF code = '00000' THEN
		GET DIAGNOSTICS rows = ROW_COUNT;
		SET result = CAST(rows AS UNSIGNED);
	ELSE
		SET result = CAST(code AS UNSIGNED);
        END IF;
	-- Say what happened
	SELECT result;
END

HTH,
Harry

2 Likes