Recursive call error in nested procedures

11

I have some MySQL procedures to do the following:

  • The main code will always call the procedure CALL sp_syncTabela .

  • The sp_syncTabela will check if there is another specific procedure for tableName passed as argument. If it exists, it will be called; if not, the default procedure " sp_syncExecuteQuery " will be called instead.

  • For example, I have sp_syncTabela_caixa in the database. So when I run CALL sp_syncTabela (..."caixa"...) , it goes internally to call CALL sp_syncTabela_caixa . But a call to CALL sp_syncTabela (..."outra"...) will only use the default procedure sp_syncExecuteQuery .

  • All specific procedures have the format sp_syncTabela_??? , as the "box" has. These procedures are for performing other custom tasks in addition to calling CALL sp_syncExecuteQuery .

  • The logic is: Does the table referenced by the tableName argument have a specific procedure? If so, do some custom tasks on the table and then call sp_syncExecuteQuery ; but if it does not, just run sp_syncExecuteQuery with no extra tasks.

I do not see any recursive calls, but I'm getting the error

  

Error Code: 1444. The prepared statement contains a stored routine   call that refers to that same statement. It's not allowed to execute a   prepared statement in such a recursive manner.

This is happening after running CALL sp_syncTabela ("I", 33, "caixa", "id='20',fundo='11.11',abertura_user_id='2',abertura_data='2014-11-11 18:24:33',fechamento_user_id=NULL,fechamento_data=NULL,", "id=20") .

Now, the codes:

CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_syncTabela'(IN action VARCHAR(10), IN tableName VARCHAR(50), IN columnData VARCHAR(4000))
BEGIN
    DECLARE res Int;

    SELECT Count(*) INTO res FROM information_schema.Routines WHERE routine_name = Concat("sp_syncTabela_", tableName);

    CASE res
        WHEN 0 THEN CALL sp_syncExecuteQuery (action, tableName, columnData);
        ELSE 
        BEGIN
            SET @querySync = Concat("CALL sp_syncTabela_", tableName, " (\"", action, "\", \"", tableName, "\", \"", columnData, "\");");
            PREPARE tablePreviewQuery FROM @querySync;
            EXECUTE tablePreviewQuery;  
        END;
    END CASE;
END


CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_syncTabela_caixa'(IN action VARCHAR(10), IN tableName VARCHAR(50), IN columnData VARCHAR(4000))
BEGIN
    /* Apenas uma procedure específica de exemplo, com uma tarefa adicional antes de chamar sp_syncExecuteQuery */

    INSERT INTO configuration (name, value) VALUES ("Última chamada à sp_syncTabela_caixa", Cast(Now() as Char)) 
        ON DUPLICATE KEY UPDATE value = Cast(Now() as Char);
    CALL sp_syncExecuteQuery (action, tableName, columnData);

END


CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_syncExecuteQuery'(IN action VARCHAR(10), IN tableName VARCHAR(50), IN columnData VARCHAR(4000))
BEGIN

    CASE action
        WHEN "I" then CALL sp_generateInsertQuery (tableName, columnData, @queryToExecute);
        WHEN "U" then CALL sp_generateUpdateQuery (tableName, columnData, @queryToExecute);
        WHEN "D" then CALL sp_generateDeleteQuery (tableName, columnData, @queryToExecute);
        ELSE Begin End;
    END CASE;

    PREPARE tablePreviewQuery FROM @queryToExecute;
    EXECUTE tablePreviewQuery;  
END

I have already looked for help on various websites, in Portuguese and English, for keywords like 'nested procedures', 'recursive errors', and the like. And, furthermore, I do not see where there is recursion in this process. When I directly execute sp_syncTabela_caixa , no error occurs.

    
asked by anonymous 17.11.2014 / 21:46

0 answers