I do a stored procedue in my db, I have more of 1 row if i run storedd procedure on my db workbench, but if i try to debug my programm result set is empty
i tried debug, but rs is empty this is my stored procedure:
CREATE DEFINER=
root@
localhostPROCEDURE
getIntervento`(
IN in_stato TINYINT(1)
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE numero INT;
DECLARE modello VARCHAR(45);
DECLARE marca VARCHAR(45);
DECLARE costo INT;
DECLARE descrizioneComp VARCHAR(45);
DECLARE cur CURSOR FOR
SELECT i.numeroRiparazione
,i.marca
,i.modello
, i.costo
, c.descrizione
FROM intervento
i JOIN necessita
n on i.numeroRiparazione=n.codiceIntervento
JOIN componente
c ON n.codiceComponente=c.codice
WHERE pagamento=in_stato;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS `cercaIntervento`;
CREATE TEMPORARY TABLE `cercaIntervento` (
`numero` INT,
`marca`VARCHAR(45),
`modello` VARCHAR(45),
`costo` INT,
`descrizione` VARCHAR(45)
);
SET transaction isolation level read committed;
START transaction;
OPEN cur;
read_loop: LOOP
FETCH cur INTO numero, marca, modello, costo,descrizioneComp;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO `cercaIntervento` VALUES (numero, marca, modello, costo, descrizioneComp);
END LOOP;
CLOSE cur;
SELECT * FROM `cercaIntervento`;
COMMIT;
END,`
this is my code
` public List viewIntervento(int n) throws SQLException {
Intervento intervento;
List allIntervento = new ArrayList<>();
try(CallableStatement cs = connection.conn.prepareCall(“{call getIntervento(?)}”)){
cs.setInt(1,n);
boolean status=cs.execute();
if(status){
ResultSet rs=cs.getResultSet();
while(rs.next()){
int numero=rs.getInt(1);
String marca = rs.getString(2);
String modello = rs.getString(3);
int costo = rs.getInt(4);
String descrizione = rs.getString(5);
intervento = new Intervento(marca, modello, costo, descrizione, numero);
allIntervento.add(intervento);
}
}
}catch (SQLException e) {
throw new SQLException("errore durante la lettura: " + e.getMessage());
}
return allIntervento;
}
`
user26859885 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.