Unable to handle exception in MYSQL Function

Question

I am not very familiar with MYSQL functions & exception handling. After all the research I could come up with below, but to no gain.

I am trying to return 0 if insert statement execution fails and 1 otherwise. Exception is getting raised instead of being handled. Where am i going wrong?

    CREATE DEFINER=`myusr`@`localhost` FUNCTION `func1`(p1 varchar(50), p2 varchar(6)) RETURNS int(1)
        READS SQL DATA
        DETERMINISTIC
    BEGIN
        DECLARE EXP DATETIME;
        DECLARE RINT INT(1);
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
           RETURN  0;
        END;
        SET exp = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
        INSERT INTO `mydb`.`my_tbl`
            (`C1`,
            `C2`,
            `C3`)
            VALUES
            (p1, p2, exp);
        SET RINT = 1;
        RETURN RINT;
    END

TABLE - my_tbl

SUCCESS CASE - func1('ABC','123456')

EXCEPTION CASE - func1('ABC','123456789')

EDIT - Added screenshots

Answer 1

It seems that mysql 8.0.19 can't catch all errors and handle them properly.

Error Code: 1406. Data too long for column 'p2' at row 1

Error Code: 1049. Unknown database 'mydb'

Are two examples that i tested and didn't work, others do so i think this is more a case for the mysql Forum.

DELIMITER $$
CREATE DEFINER=`mydb`@`localhost` FUNCTION `func1`(p1 varchar(50), p2 varchar(6)) RETURNS int
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE EXP DATETIME;
    DECLARE RINT INT(1);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
    BEGIn
       RETURN  0;
    END;
    SET exp = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
    INSERT INTO `mydb`.`func1`
        (`C1`,
        `C2`,
        `C3`)
        VALUES
        (p1, p2, exp);
    SET RINT = 1;
    RETURN RINT;
END;
DE§LIMITER ;

This Scenario works. I increased for the referenced variable p2 the size to fit the data entered, and i added a exit handler for the error 1265 , which is the error you get, when you try to insert the long text.

Error Code: 1265. Data truncated for column 'c2' at row 1

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `func1`(p1 varchar(50), p2 varchar(20)) RETURNS int
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE EXP DATETIME;
    DECLARE RINT INT(1);
    BEGIN
    DECLARE EXIT HANDLER FOR 1265 RETURN 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN 0;


    SET exp = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
    INSERT INTO `mydb`.`my_tbl`
        (`C1`,
        `C2`,
        `C3`)
        VALUES
        (p1, p2, exp);
        SET RINT = 1;
        RETURN RINT;
    END;    
END$$
DELIMITER ;

The error you got, delivers the error message before even adding a HANDLER

Answer 2

The problem here is that the exception is generated outside the exception handeler. The parameters sent to the function is verified before the exception handler is defined. To catch that exception you need an exception handler around the call to the function.

If you declared p2 varchar(20), and kept the column definition varchar(6), you could probably get the behaviour you wanted, because then the exception would trigger inside the code covered by the exception-handler.

Answer 3

The role of the TRY statement is to capture the exception. (Because this process usually comprises several statements, the term “TRY block” typically is used instead of “TRY statement.”) If an exception occurs within the TRY block, the part of the system called the exception handler delivers the exception to the other part of the program, which will handle the exception. This program part is denoted by the keyword CATCH and is therefore called the CATCH block.

NOTE

Exception handling using the TRY and CATCH statements is the common way that modern programming languages like C# and Java treat errors.

Exception handling with the TRY and CATCH blocks gives a programmer a lot of benefits, such as:

Exceptions provide a clean way to check for errors without cluttering code
Exceptions provide a mechanism to signal errors directly rather than using some side effects
Exceptions can be seen by the programmer and checked during the compilation process

SQL Server 2012 introduces the third statement in relation to handling errors: THROW. This statement allows you to throw an exception caught in the exception handling block. Simply stated, the THROW statement is another return mechanism, which behaves similarly to the already described RAISEERROR statement.

Example 1 shows how exception handling with the TRY/CATCH/THROW works. It shows how you can use exception handling to insert all statements in a batch or to roll back the entire statement group if an error occurs. The example is based on the referential integrity between the department and employee tables. For this reason, you have to create both tables using the PRIMARY KEY and FOREIGN KEY constraints.

EXAMPLE 1

0270_001

After the execution of the batch in Example 1, all three statements in the batch won’t be executed at all, and the output of this example is:

0271_001

The execution of Example 1 works as follows. The first INSERT statement is executed successfully. Then, the second statement causes the referential integrity error. Because all three statements are written inside the TRY block, the exception is “thrown” and the exception handler starts the CATCH block. CATCH rolls back all statements and prints the corresponding message. After that the THROW statement returns the execution of the batch to the caller. For this reason, the content of the employee table won’t change.

NOTE

The statements BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK are Transact-SQL statements concerning transactions. These statements start, commit, and roll back transactions, respectively. See Chapter 13 for the discussion of these statements and transactions generally.