U0400017:GaussDB stored procedures do not support CONTINUE HANDLER

Description

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: all GaussDB versions

Syntax Example

This error is reported because UGO cannot process CONTINUE HANDLER of MySQL stored procedures.

The following MySQL stored procedure is used to insert employee records into the employees table. If the inserted record already exists (that is, the unique key constraint is violated), a log is generated and the stored procedures continue to be executed. GaussDB stored procedures do not support CONTINUE HANDLER. After an exception is handled, the program will terminate. Therefore, SQL statements in the stored procedures cannot be automatically converted.

CREATE PROCEDURE insert_employee(IN p_first_name VARCHAR(50), IN p_last_name VARCHAR(50), IN p_email VARCHAR(100))
BEGIN
    DECLARE CONTINUE HANDLER FOR 1062 -- 1062 is the error code for duplicate entry
    BEGIN
        INSERT INTO error_log (message) VALUES (CONCAT('Duplicate email: ', p_email));
    END;

    INSERT INTO employees (first_name, last_name, email) VALUES (p_first_name, p_last_name, p_email);
END;

Suggestion

Modify the logic or use application code of stored procedures of the source database.

In the preceding example, there is no other SQL statement for the exception. That is, CONTINUE HANDLER and EXIT HANDLER have the same effect. You can change CONTINUE HANDLER to an exception handling statement supported by GaussDB. For details about how to handle error codes returned by GaussDB, see GaussDB Developer Guide. In the following example, error 1062 is reported because some SQL statements are not converted even after manual conversion of all statements:

CREATE PROCEDURE insert_employee(IN p_first_name VARCHAR(50), IN p_last_name VARCHAR(50), IN p_email VARCHAR(100))
AS
BEGIN
    INSERT INTO employees (first_name, last_name, email) VALUES (p_first_name, p_last_name, p_email);

    EXCEPTION WHEN 1062 THEN
    BEGIN
        INSERT INTO error_log (message) VALUES (CONCAT('Duplicate email: ', p_email));
    END;
    RETURN;
END ;

Ensure that no SQL statement in the source stored procedure needs to be processed after CONTINUE HANDLER is executed.