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?