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

0

I have a Stored Procedure in MySQL that is working normally when I run from the Workbench, but when I execute this same procedure in Java / JBDC I am getting erroneous values different from those obtained in the Workbench.

My Stored Procedure counts multiple records from other tables and consolidates all results into the target table. The part of my procedure that is exhibiting divergence 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

Java method code 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 noticed that the difference presented in the results between the two executions corresponds to the absence of the NOT LIKE in the WHERE clause of the subquery present in the Stored Procedure, ie it is as if when I executed the procedure in Java it disregarded 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 ever encountered a similar problem? Any solution?

    
asked by anonymous 07.10.2015 / 21:07

1 answer

0

I do not know why, and if anyone has any ideas please contribute to us, but the problem was in using together the NOT negation command and the use of some special character in the query strings. I only discovered it through trial and error. The solution to my problem was this:

AND CC.PRODUTO NOT LIKE '%Poupan_a%' 
AND CC.PRODUTO NOT LIKE '%Sal_rio%' 
AND CC.PRODUTO NOT LIKE '%BNDES%'
    
08.10.2015 / 15:28