I use this code for executing a MySQL Stored Procedure but this gives me unexpected result: 0 results. If I execute the procedure with same parameter from my MySQL client this give me 2 results.
I use this PHP snippet:
$_config_db_servername = "#";
$_config_db_username = "#";
$_config_db_password = "#";
$_config_db_name = "#";
$conn = new mysqli($_config_db_servername, $_config_db_username, $_config_db_password, $_config_db_name);
if ($conn->connect_error) {
die("Connessione al database fallita.");
}
$stmt = $conn->prepare("CALL aw_spZAAAOttieniDataTables(?);");
$stmt->bind_param("s", $id_datatable);
if($stmt->execute()) {
$result = $stmt->get_result();
if($result && $result->num_rows > 0) {
$row = $result->fetch_assoc();
$dati[] = $row;
$resp = new stdClass();
$resp->esito = 1;
$resp->dati = $dati;
} else {
$resp = new stdClass();
$resp->esito = 20;
$resp->dati = $dati;
}
} else {
$resp = new stdClass();
$resp->esito = 30;
$resp->dati = $dati;
}
$stmt->close();
$conn->close();
Result is:
{"esito":20,"dati":[]}
From the client the result is:
Dump of the result is:
object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(3) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(1) }
Stored Procedure:
CREATE DEFINER = 'c4_awuser'@'%'
PROCEDURE c4_awdb.aw_spZAAAOttieniDataTables(IN _identificativoTabella VARCHAR(255))
BEGIN
DECLARE finito INT DEFAULT 0;
DECLARE nomeColonna VARCHAR(200) DEFAULT '';
DECLARE titoloColonna VARCHAR(200) DEFAULT '';
DECLARE queryColonne LONGTEXT DEFAULT 'SELECT ';
DECLARE _nomeVista VARCHAR(255);
DECLARE curColonne CURSOR FOR
SELECT
COLUMN_NAME,
COALESCE(nome_campo, COLUMN_NAME) AS nome_campo
FROM information_schema.columns
LEFT JOIN aw_tbZAAEGestioneTabelleDati
ON ZAAEcvVistaAssociata = _nomeVista
LEFT JOIN aw_tbZAACCampi
ON nome_campo_db = COLUMN_NAME
AND tabella = ZAAEcvNomeTabella
WHERE table_name = _nomeVista;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finito = 1;
SELECT
ZAAEcvVistaAssociata INTO _nomeVista
FROM aw_tbZAAEGestioneTabelleDati
WHERE ZAAEcvIdentificativoTabella = _identificativoTabella;
IF _nomeVista IS NOT NULL AND EXISTS (SELECT
DISTINCT 1
FROM information_schema.columns
WHERE table_name = _nomeVista) THEN
OPEN curColonne;
ciclaColonne: LOOP
FETCH curColonne INTO nomeColonna,titoloColonna;
IF finito = 1 THEN
LEAVE ciclaColonne;
END IF;
SET queryColonne = CONCAT(queryColonne,nomeColonna,' AS \'',titoloColonna,'\',');
END LOOP ciclaColonne;
CLOSE curColonne;
IF RIGHT(queryColonne,1) = ',' THEN
SET queryColonne = LEFT(queryColonne,LENGTH(queryColonne)-1);
END IF;
SET queryColonne = CONCAT(queryColonne,' FROM ',_nomeVista,';');
SET @s = queryColonne;
PREPARE stmt FROM @s;
EXECUTE stmt;
END IF;
END
I pass as parameter an id such as 'elenco_corsi'. I search in aw_tbZAAEGestioneTabelleDati what is his view, then I use system table to get view's field, the i build a query and execute it via statement.
