Appeler une procédure stockée en PHP

Une application appelle une procédure stockée comme n'importe quelle requête : elle envoie CALL nom(?, ?) par une requête préparée, lit les lignes renvoyées, puis récupère les paramètres OUT et intercepte les erreurs levées par SIGNAL. Chaque pilote a ses particularités : PDO doit libérer les résultats avec closeCursor(), MySqlConnector sait lire les paramètres OUT directement, mysql2 renvoie un tableau de jeux de résultats. Ce chapitre donne le code qui marche pour les trois, testé sur la base parc avec les procédures des chapitres précédents.

Les connexions suivent les règles du chapitre SQL dans une application du cours SQL : un compte dédié, les paramètres de connexion dans des variables d'environnement, charset=utf8mb4. Les résultats indiqués en commentaire supposent la base remise à zéro au début du chapitre, avec tes routines recréées (voir L'essentiel).

En PHP avec PDO

php

$pdo = new PDO(
    sprintf('mysql:host=%s;dbname=parc;charset=utf8mb4', getenv('DB_HOST')),
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]
);

Une procédure qui renvoie des lignes.

php

$stmt = $pdo->prepare('CALL tickets_du_demandeur(?)');
$stmt->execute([$idEmploye]);
$tickets = $stmt->fetchAll();
$stmt->closeCursor();   // indispensable avant la requête suivante

foreach ($tickets as $ticket) {
    echo htmlspecialchars($ticket['titre']) . PHP_EOL;
}

Un CALL renvoie, en plus des lignes, un résultat d'état final. Tant qu'il n'est pas lu, la connexion est occupée : la requête suivante échoue avec SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets. closeCursor() lit et jette ce qui reste. Prends l'habitude de l'appeler après chaque CALL.

Des paramètres OUT. PDO ne sait pas lire les paramètres OUT de MySQL. On passe des variables de session dans le CALL, puis on les lit avec un SELECT, sur la même connexion :

php

$stmt = $pdo->prepare('CALL charge_technicien(?, @nb, @minutes)');
$stmt->execute([11]);
$stmt->closeCursor();

$charge = $pdo->query('SELECT @nb AS nb, @minutes AS minutes')->fetch();
// ['nb' => 6, 'minutes' => 295]

Plusieurs jeux de résultats. nextRowset() passe au jeu suivant :

php

$stmt = $pdo->prepare('CALL bilan_ticket(?)');
$stmt->execute([9]);
$ticket = $stmt->fetch();              // premier jeu : le ticket
$stmt->nextRowset();
$interventions = $stmt->fetchAll();    // second jeu : ses 3 interventions
$stmt->closeCursor();

Les erreurs. Une erreur levée par SIGNAL arrive comme une PDOException. errorInfo contient le SQLSTATE, le numéro MySQL et le message :

php

try {
    $stmt = $pdo->prepare('CALL installer_logiciel(?, ?)');
    $stmt->execute([$idPoste, $idLogiciel]);
    $stmt->closeCursor();
    $message = 'Logiciel installé.';
} catch (PDOException $e) {
    [$etat, $numero, $texte] = $e->errorInfo;   // ['45000', 1644, 'Plus de licence Odoo : …']
    $message = match ($numero) {
        1644    => $texte,                                     // erreur métier : message prévu pour l'utilisateur
        1062    => 'Ce logiciel est déjà installé sur ce poste.',
        default => 'Installation impossible, réessaie plus tard.',
    };
    if ($numero !== 1644) {
        error_log($e->getMessage());                           // le détail technique va dans le journal
    }
}

Le message d'un SIGNAL est écrit pour l'utilisateur : on peut l'afficher (échappé avec htmlspecialchars). Les autres messages de MySQL révèlent les noms des tables et des contraintes : ils vont dans le journal, jamais à l'écran (erreurs côté code). $e->getCode() renvoie le SQLSTATE ('45000'), une chaîne, pas le numéro MySQL. Le chapitre PDO : fetch, erreurs et transactions du cours PHP détaille la gestion des exceptions.

En C# avec MySqlConnector

MySqlConnector, le pilote recommandé dans le chapitre Connexion C# à MySQL, appelle une procédure avec CommandType.StoredProcedure et lit les paramètres OUT directement :

csharp

using System.Data;
using MySqlConnector;

await using var connexion = new MySqlConnection(Environment.GetEnvironmentVariable("PARC_CONNECTION_STRING"));
await connexion.OpenAsync();

// Paramètres OUT
await using (var cmd = new MySqlCommand("charge_technicien", connexion))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("p_id_technicien", 11);
    cmd.Parameters.Add(new MySqlParameter("p_nb_interventions", MySqlDbType.Int32)
        { Direction = ParameterDirection.Output });
    cmd.Parameters.Add(new MySqlParameter("p_minutes", MySqlDbType.Int32)
        { Direction = ParameterDirection.Output });

    await cmd.ExecuteNonQueryAsync();
    Console.WriteLine($"{cmd.Parameters["p_nb_interventions"].Value} interventions, "
                    + $"{cmd.Parameters["p_minutes"].Value} minutes");   // 6 interventions, 295 minutes
}

// Lignes
await using (var cmd = new MySqlCommand("tickets_du_demandeur", connexion))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("p_id_employe", 3);
    await using var lecteur = await cmd.ExecuteReaderAsync();
    while (await lecteur.ReadAsync())
    {
        Console.WriteLine($"{lecteur.GetInt32("id_ticket")} {lecteur.GetString("titre")}");
    }
}

Les noms des paramètres sont ceux de la procédure (p_id_technicien) : MySqlConnector lit la définition de la procédure pour les associer. Cela fonctionne avec un compte qui n'a que le droit EXECUTE.
Plusieurs jeux de résultats se lisent avec await lecteur.NextResultAsync() entre les deux boucles de lecture.

Les erreurs arrivent en MySqlException, dont Number est le numéro MySQL :

csharp

try
{
    await using var cmd = new MySqlCommand("installer_logiciel", connexion);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("p_id_poste", idPoste);
    cmd.Parameters.AddWithValue("p_id_logiciel", idLogiciel);
    await cmd.ExecuteNonQueryAsync();
}
catch (MySqlException ex) when (ex.Number == 1644)        // erreur levée par SIGNAL
{
    MessageBox.Show(ex.Message, "Installation refusée", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}

ex.SqlState donne 45000. La clause when filtre l'exception : les autres erreurs remontent vers le gestionnaire général de l'application. C'est le code de l'épreuve blanche n° 2 de l'E6, où la règle des licences est placée dans la base.

En Node.js avec mysql2

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: 'parc',
  connectionLimit: 10,
});

// Lignes : un CALL renvoie un tableau de jeux de résultats, suivi d'un ResultSetHeader
const [resultats] = await pool.execute('CALL tickets_du_demandeur(?)', [3]);
const tickets = resultats[0];

// Plusieurs jeux de résultats
const [bilan] = await pool.execute('CALL bilan_ticket(?)', [9]);
const [ticket] = bilan[0];          // le ticket
const interventions = bilan[1];     // ses interventions

Avec une requête ordinaire, execute renvoie directement les lignes ; avec un CALL, il renvoie un tableau de jeux de résultats (resultats[0] pour le premier), terminé par un objet ResultSetHeader. C'est la source d'erreur la plus fréquente : resultats.length vaut 2 pour une procédure qui renvoie une seule liste.

Des paramètres OUT. Comme avec PDO, on passe par des variables de session. Mais un pool peut envoyer le CALL et le SELECT sur deux connexions différentes, et la seconde ne connaît pas les variables de la première. Il faut emprunter une connexion pour les deux :

const connexion = await pool.getConnection();
try {
  await connexion.execute('CALL charge_technicien(?, @nb, @minutes)', [11]);
  const [[charge]] = await connexion.query('SELECT @nb AS nb, @minutes AS minutes');
  console.log(charge);   // { nb: 6, minutes: 295 }
} finally {
  connexion.release();   // toujours rendre la connexion au pool
}

Les erreurs. L'erreur porte errno (le numéro MySQL), sqlState et sqlMessage :

try {
  await pool.execute('CALL installer_logiciel(?, ?)', [idPoste, idLogiciel]);
} catch (err) {
  if (err.errno === 1644) {
    return res.status(409).json({ erreur: err.sqlMessage });   // règle métier : 409 Conflict
  }
  throw err;   // le reste : erreur 500, détail dans les journaux
}

Le code HTTP 409 (Conflict) dit au client que la requête est correcte mais contredit l'état des données ; le chapitre Node.js et MySQL avec mysql2 montre comment l'intégrer à une API Express.

Ce qui est commun aux trois

Besoin	PDO	MySqlConnector	mysql2
Appeler	`prepare('CALL p(?)')`, `execute([…])`	`CommandType.StoredProcedure`	`execute('CALL p(?)', […])`
Lire les lignes	`fetchAll()`, puis `closeCursor()`	`ExecuteReaderAsync()`	`resultats[0]`
Jeu suivant	`nextRowset()`	`NextResultAsync()`	`resultats[1]`
Paramètre `OUT`	`@variable`, puis `SELECT @variable`	`ParameterDirection.Output`	`@variable`, puis `SELECT`, sur la même connexion
Numéro de l'erreur	`$e->errorInfo[1]`	`ex.Number`	`err.errno`
Erreur métier (`SIGNAL`)	1644	1644	1644

Qui gère la transaction ?

Le chapitre 7 l'a montré : une procédure qui exécute START TRANSACTION valide toute transaction déjà ouverte sur la connexion. Côté application, cela donne une règle simple :

une procédure qui gère sa transaction (cloturer_ticket, installer_logiciel) s'appelle hors de toute transaction de l'application : pas de beginTransaction() autour ;
une procédure sans transaction peut s'appeler entre beginTransaction() et commit(), avec d'autres requêtes : c'est alors l'application qui annule tout en cas d'erreur.

Documente le choix dans le commentaire de la procédure (COMMENT 'Gère sa propre transaction') : c'est la première chose que le développeur de l'application doit savoir.

Et les ORM ?

Un ORM (Entity Framework Core, Prisma, Doctrine, Eloquent) génère lui-même son SQL et ne connaît pas tes procédures. Tous savent exécuter du SQL brut paramétré : context.Database.ExecuteSql($"CALL cloturer_ticket({id}, {technicien}, {duree}, {compteRendu})") en EF Core, prisma.$executeRaw en Prisma. Les valeurs interpolées y deviennent des paramètres, pas du texte collé. Les triggers, eux, n'ont besoin de rien : ils s'exécutent quand l'ORM écrit dans la table.

Exercices

Exercice 1 sur 3 Clôturer un ticket depuis PHP Réussi À revoir

Écris la fonction PHP cloturerTicket(PDO $pdo, int $idTicket, int $idTechnicien, int $duree, string $compteRendu): string qui appelle la procédure cloturer_ticket du chapitre 7 et renvoie un message pour l'utilisateur : « Ticket clôturé. », le message de la procédure pour une erreur levée par SIGNAL, « La durée doit être positive. » pour l'erreur 3819, et un message générique sinon.

Voir la réponseMasquer la réponse

php

function cloturerTicket(PDO $pdo, int $idTicket, int $idTechnicien, int $duree, string $compteRendu): string
{
    try {
        $stmt = $pdo->prepare('CALL cloturer_ticket(?, ?, ?, ?)');
        $stmt->execute([$idTicket, $idTechnicien, $duree, $compteRendu]);
        $stmt->closeCursor();
        return 'Ticket clôturé.';
    } catch (PDOException $e) {
        $numero = $e->errorInfo[1] ?? null;
        if ($numero === 1644) {
            return $e->errorInfo[2];            // message écrit par la procédure (SIGNAL)
        }
        if ($numero === 3819) {
            return 'La durée doit être positive.';
        }
        error_log($e->getMessage());
        return 'Clôture impossible, réessaie plus tard.';
    }
}

Pas de beginTransaction() autour de l'appel : la procédure gère sa transaction. Sur MariaDB, la violation de la contrainte CHECK porte le numéro 4025 : si l'application doit tourner sur les deux SGBD, teste in_array($numero, [3819, 4025], true). Côté affichage, le message renvoyé passe par htmlspecialchars(). On peut aussi contrôler la durée dans le formulaire, mais la base reste la gardienne de la règle.

Tu avais trouvé ?

Exercice 2 sur 3 Le pool qui perd les variables Réussi À revoir

Une développeuse écrit ce code Node.js. Le nombre affiché vaut parfois null. Explique pourquoi et corrige.

await pool.execute('CALL charge_technicien(?, @nb, @minutes)', [12]);
const [[charge]] = await pool.query('SELECT @nb AS nb');
console.log(charge.nb);

Voir la réponseMasquer la réponse

Les variables @nb et @minutes appartiennent à la connexion qui a exécuté le CALL. Le pool prête une connexion à chaque appel : quand le SELECT part sur une autre connexion, @nb n'y existe pas et vaut NULL. Le bogue est intermittent, ce qui le rend difficile à trouver. Correction : emprunter une connexion pour les deux requêtes.

const connexion = await pool.getConnection();
try {
  await connexion.execute('CALL charge_technicien(?, @nb, @minutes)', [12]);
  const [[charge]] = await connexion.query('SELECT @nb AS nb, @minutes AS minutes');
  console.log(charge.nb);   // 7
} finally {
  connexion.release();
}

Tu avais trouvé ?

Exercice 3 sur 3 Afficher le bilan d'un ticket en C# Réussi À revoir

Écris la méthode C# async Task<(string Titre, int NbInterventions, int Minutes)> BilanAsync(MySqlConnection connexion, int idTicket) qui appelle la procédure bilan_ticket et renvoie le titre du ticket, le nombre de ses interventions et le total des minutes.

Voir la réponseMasquer la réponse

csharp

async Task<(string Titre, int NbInterventions, int Minutes)> BilanAsync(MySqlConnection connexion, int idTicket)
{
    await using var cmd = new MySqlCommand("bilan_ticket", connexion);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("p_id_ticket", idTicket);

    await using var lecteur = await cmd.ExecuteReaderAsync();
    if (!await lecteur.ReadAsync())
    {
        throw new InvalidOperationException($"Ticket {idTicket} introuvable");
    }
    var titre = lecteur.GetString("titre");

    await lecteur.NextResultAsync();       // second jeu : les interventions
    int nb = 0, minutes = 0;
    while (await lecteur.ReadAsync())
    {
        nb++;
        minutes += lecteur.GetInt32("duree_min");
    }
    return (titre, nb, minutes);
}

Pour le ticket 9 : ("Rançongiciel suspecté", 3, 570). Si le total des minutes est la seule chose utile, la fonction duree_totale_ticket du chapitre 4, appelée dans un SELECT, est plus simple.

Tu avais trouvé ?

Appeler une procédure stockée en PHP (PDO), C# et Node.js

À la fin de ce chapitre, tu sauras

En PHP avec PDO

En C# avec MySqlConnector

En Node.js avec mysql2

Ce qui est commun aux trois

Qui gère la transaction ?

Et les ORM ?

Exercices

Chapitre terminé !

À la fin de ce chapitre, tu sauras

En PHP avec PDO

En C# avec MySqlConnector

En Node.js avec mysql2

Ce qui est commun aux trois

Qui gère la transaction ?

Et les ORM ?

Exercices

Chapitre terminé !

Qui gère la transaction ?

Et les ORM ?

Chapitre terminé !