Why a Stored Procedure generates different results if run in Workbench and Java with JDBC

Question

I have a Stored Procedure in Mysql that is working normally when running from Workbench, but by running this same procedure in Java/JBDC I am getting wrong values and different from those obtained in Workbench.

My Stored Procedure counts multiple records from other tables and consolidates all results in the target table. The part of my procedure that is diverging follows below:

CREATE DEFINER=`root`@`%` PROCEDURE `processa_iap`(IN data_movimento DATE)
BEGIN
    DECLARE dia_mov INT;
    DECLARE mes_mov INT;
    DECLARE ano_mov INT;

    SET dia_mov = DAY(data_movimento); 
    SET mes_mov = MONTH(data_movimento);
    SET ano_mov = YEAR(data_movimento); 

    INSERT INTO IAP_ATINGIDO(MES,ANO,PA,DEB)
    SELECT mes_mov 'MES', ano_mov 'ANO', A.CODPAC, COUNT(A.CODPAC)
        FROM ASSOCIADOS A, (SELECT DISTINCT CC.COD_ASSOC
            FROM CONTAS_CARTAO CC
            WHERE CC.ESTADO_CARTAO='Operativo' AND CC.COD_ASSOC IS NOT NULL 
            AND CC.TITULARIDADE='TITULAR' 
            AND CC.PRODUTO NOT LIKE '%Poupança%' 
            AND CC.PRODUTO NOT LIKE '%Salário%' 
            AND CC.PRODUTO NOT LIKE '%BNDES%') C
        WHERE A.COD=C.COD_ASSOC
        GROUP BY A.CODPAC
    ON DUPLICATE KEY UPDATE DEB=VALUES(DEB);
END

Code of the Java method where I run the Stored Procedure:

public void processaIAP() throws SQLException {
    try {
        sql = "{CALL processa_iap(?)}";
        CallableStatement cstm = connection.prepareCall(sql);

        Date data = buscaMenorDataImportacoes();
        cstm.setDate(1, data);

        cstm.execute();
        cstm.close();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

I noted that the difference in the results between the two executions corresponds to the absence of the NOT LIKE in the WHERE clause of the sub-concession present in the Stored Procedure, that is, it is as if when I execute the procedure in Java it disregards the following SQL statements:

AND CC.PRODUTO NOT LIKE '%Poupança%' 
AND CC.PRODUTO NOT LIKE '%Salário%' 
AND CC.PRODUTO NOT LIKE '%BNDES%'

I tried using several versions of the Mysql connector for Java but the problem persists. Has anyone come across similar problem? Any solution?

Answer 1

I don’t know why, and if anyone has any ideas please contribute with us, but the problem was the joint use of the NOT negation command and the use of some special character in the query strings. I only found out through trial and error. The solution to my problem was the following:

AND CC.PRODUTO NOT LIKE '%Poupan_a%' 
AND CC.PRODUTO NOT LIKE '%Sal_rio%' 
AND CC.PRODUTO NOT LIKE '%BNDES%'