Stored Routine per gestire facilmente l’SQL_MODE

English version

Lavorare con l’SQL_MODE può essere sconfortante, perché è una lista di parole lunghe separata da virgole. Francamente odio riscrivere i flag, cercare di leggere una lista illeggibile, etc. Certo, alcune funzioni stringa possono aiutare (Justin Swanhart consiglia di usare REPLACE() per rendere leggibili le liste separate da virgola).

Ho fatto un piccolo set di Stored Routine che mi permettono in maniera semplice di mostrare l’SQL_MODE, aggiungere un flag, togliere un flag e controllare se il flag è presente. Queste routine lavorano con l’SQL_MODE globale; se non vi va bene, sostituite “@@global.” con “@@session.” nel file SQL prima di installarlo (facile, no?).

Potete scaricare (o migliorare) queste routine usando il repo SQL_MODER su GitHub.

E ora, qualche esempio.

Mostrare i flag attivi:

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
8 rows in set (0.46 sec)

(questo è l’SQL_MODE che consiglio di usare)

Verificare se un flag è attivo:

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.38 sec)

Aggiungere uno o più flag:

MariaDB [(none)]> CALL _.sql_mode_set('NO_UNSIGNED_SUBTRACTION,HIGH_NOT_PRECEDEN
CE');
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| HIGH_NOT_PRECEDENCE        |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_UNSIGNED_SUBTRACTION    |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
10 rows in set (0.13 sec)

Query OK, 0 rows affected (2.09 sec)

Disattivare un flag:

MariaDB [(none)]> CALL _.sql_mode_unset('HIGH_NOT_PRECEDENCE');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

Se hai sbagliato a digitare il flag (o non era impostato):

MariaDB [(none)]> CALL _.sql_mode_unset('hello world');
ERROR 1644 (45000): Flag 'hello world' was not set

A presto!

MariaDB/MySQL: Procedure facili per leggere la Diagnostics Area

English version

UPDATE 2013-08-30: Ho risolto 2 bug e creato un repo su GitHub chiamato sql_diagnostix. Scusate se non l’ho fatto prima. Se io o chiunque altro troverà altri bug, aggiornerò il repo.

Il problema

Per visualizzare velocemente le informazioni sugli errori e i warning generati dall’ultima istruzione eseguita, è possibile utilizzare SHOW WARNINGS. Però i risultati di questa istruzione non possono essere lette via SQL, perciò non è possibile usarla per gestire gli errori in uno Stored Program.

A questo scopo si può usare GET DIAGNOSTICS, che ha però due problemi:

  • Richiede molto codice
  • Non esiste un modo facile per mostrare tutte le informazioni presenti nella Diagnostics Area.

Il primo problema può scoraggiare molti sviluppatori dall’usare GET DIAGNOSTICS.

Il secondo problema nella maggior parte dei casi non è così importante, perché le info mancanti sono SQLSTATE e le informazioni che possono essere impostate solo con SIGNAL e RESIGNAL, e che non possono essere lette da un programma esterno (SCHEMA_NAME, CURSOR_NAME, etc). Tuttavia leggere SQLSTATE può essere importante, e accedere a tutte le clausole di SIGNAL può aiutare nel debug e nella soluzione dei problemi… ecco perché esistono :-)

Oracle MySQL 5.7 ha due Diagnostics Area e supporta la parola chiave STACKED per accedere alla seconda DA da un error HANDLER. Questo però non aiuta molto: permette solo di eseguire un’altra istruzione che accede a una tabella, prima di leggere le informazioni sugli errori che si sono verificati. Perciò la DA STACKED non sembra in grado di risolvere problemi reali.

La soluzione

Ho creato tre Stored Procedure per risolvere questi problemi:

  • _.materialize_diagnostics_area()
  • _.show_diagnostics_area()
  • _.show_full_diagnostics_area()

Tutte le procedure creano una tabella temporanea MEMORY chiamata DIAGNOSTICS_AREA in un database chiamato _. Se la tabella esiste già, viene eliminata e ricreata. Incidentalmente viene anche cancellata la Diagnostics Area del server, ma le informazioni rimangono disponibili nella tabella finché non si chiama di nuovo una di queste procedure.

_.DIAGNOSTICS_AREA ha una colonna chiamata ID, che indica la posizione di ogni singola condizione nella Diagnostics Area. Le altre colonne sono uguali alle proprietà delle condizioni della Diagnostics Area (MYSQL_ERRNO, etc). L’unica eccezione è la colonna SQLSTATE, che usa il nome utilizzabile in SIGNAL e RESIGNAL, non quello usato nella Diagnostics Area (cioè RETURNED_SQLSTATE).

Siccome sia gli Stored Program, sia i programmi esterni possono accedere a _.DIAGNOSTICS_AREA, questa permette ai programmi esterni di leggere tutte le informazioni che si possono impostare con SIGNAL.

Si può chiamare direttamente materialize_diagnostics_area() per copiare interamente la DA nella tabella, per poi leggere le informazioni con delle query.

show_full_diagnostics_area() mostra tutte le informazioni, dopo aver popolato la tabella. E’ una sorta di SHOW WARNINGS con molte colonne in più.

show_diagnostics_area() mostra solo le colonne ID, SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT.

Esempio:

MariaDB [test]> INSERT INTO `t` VALUES (1/0), (1/0), (1/0), (-1);
Query OK, 4 rows affected, 4 warnings (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 4
 
MariaDB [test]> CALL _.show_diagnostics_area();
+----+----------+-------------+--------------------------------------------+
| ID | SQLSTATE | MYSQL_ERRNO | MESSAGE_TEXT                               |
+----+----------+-------------+--------------------------------------------+
|  1 | 22012    |        1365 | Division by 0                              |
|  2 | 22012    |        1365 | Division by 0                              |
|  3 | 22012    |        1365 | Division by 0                              |
|  4 | 22003    |        1264 | Out of range value for column 'c' at row 4 |
+----+----------+-------------+--------------------------------------------+
4 rows in set (0.09 sec)

Limitazioni

Se uno Stored Program deve essere veloce, queste procedure possono essere utilizzate solo per il debug.

Come detto sopra, ogni chiamata elimina le informazioni già presenti nella tabella _.DIAGNOSTICS_AREA. E’ una scelta di progettazione.

C’è un’informazione presente nell’output di SHOW WARNING che però manca nella tabella _.DIAGNOSTICS_AREA: la colonna Level. Il motivo è che non sono riuscito a trovare nella documentazione di MySQL come distinguere un Warning da una Nota, se non con SHOW WARNINGS. Se non c’è modo, o se un modo esiste ma non è documentato, non posso aggiungere questa informazione.

La Diagnostics Area STACKED di 5.7 non viene condiderata.

Il codice

Va bene, ho scritto abbastanza. Ora, se siete interessati:

Scaricate il codice SQL

Non spiego qui il codice perché mi sembra molto semplice ed è commentato. Date un’occhiata a materialize_diagnostics_area() e capirete cosa ho fatto.

A presto!

Stoppare rapidamente MariaDB 10

English version

Nota semiseria: stavo per intitolare questo articolo “Arrestare rapidamente MariaDB”. Ma in italiano è veramente brutto…

Normalmente come si arresta un server MariaDB 5.5 o MySQL?

mysqladmin shutdown -uroot -p

Ma con MariaDB 10 (qualità alpha, mentre scrivo questo articolo) c’è un modo più rapido:

C:\Documents and Settings\utente1>mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.4-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT 'I\'m alive!';
+------------+
| I'm alive! |
+------------+
| I'm alive! |
+------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT 'I\'m gone away';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061 "Unknown
 error")
ERROR: Can't connect to the server

unknown [(none)]> \q
Bye

Non ho idea se sia sicuro, o se sarà sicuro, su un server in produzione. Ma in locale è ottimo.

Divertitevi!

JavaScript non ha gli array associativi

English version

Questo post riguarda uno dei più comuni equivoci di JavaScript: gli array associativi.

JavaScript supporta la sintassi degli array associativi, ma non ha realmente questa struttura dati. Sarebbe inutile, perché possiamo usare gli oggetti.

Ecco un semplice oggetto:

var movie = {
    title : '2001: A space Odissey',
    director : 'Stanley Kubrick',
    year : 1968
};

Abbiamo definito una variabile oggetto (movie) con 3 proprietà (title, director, year). Siccome le proprietà sono sempre pubbliche in JavaScript, possiamo leggerle:

alert('Title: ' + movie.title);

Inoltre JavaScript è molto dinamico, perciò possiamo anche modificare le proprietà o crearne di nuove:

movie.genre = 'sf';

Possiamo poi utilizzare la sintassi classica degli array associativi per accedere alle proprietà di un oggetto:

alert('Title: ' + movie['title']);

Può sembrare inutile… e in questo esempio lo è davvero. E’ meglio non usare questa sintassi se non è necessario. Ma possiamo utilizzarla per accedere ad una proprietà che non conosciamo a priori:

var prop = 'title';
alert('Title: ' + movie[prop]);

E’ particolarmente utile nei cicli for … in, che sono l’equivalente JavaScript dei foreach:

for (var i in movie) {
    alert(i + ': ' + movie[i]);
}

Divertitevi!

Il tag HTML <script>

Il codice JavaScript può trovarsi in file separati, oppure all’interno delle pagine HTML. Quest’ultima opzione non è consigliabile, però capita di usarla.

Qui spiego i modi migliori per includere del codice JavaScript, in entrambi i casi.

File esterni

Il codice da usare è il seguente:

<script src="percorso/nome_file.js"></script>

src è l’attributo che indica il percorso e il nome del file JavaScript da includere. E’ buona norma usare solo caratteri minuscoli nel percorso e terminare il nome del file con l’estensione .js.

Di solito i tag script contengono molti più attributi, ma non sono necessari. Ecco i motivi.

language è deprecato in favore di type, e comunque "javascript" è l’unico valore consentito dai browser. IE invece permette anche VBScript, ma il supporto a questo linguaggio dovrebbe essere considerato un bug.

type non ha alcuna utilità. L’unico valore sensato è "text/javascript", ma verrà comunque ignorato, perché il browser si fida delle intestazioni HTTP comunicate dal server, non del valore di questo attributo.

Esistono anche attributi specifici di IE, ma il supporto a questi attributi dovrebbe essere considerato un bug.

Codice embedded

Quando il codice JavaScript viene incluso in una pagina HTML, bisognerebbe usare la sintassi seguente:

<script>
    // <![CDATA[
    <cite>...codice...</cite>
    // ]]>
</script>

Grazie ai commenti (//), le stringhe <![CDATA e ]]> vengono ignorate dal motore JavaScript, evitando di generare errori. Servono invece al motore di rendering, perché indicano che al loro interno eventuali tag devono essere ignorati. Quindi caratteri come < e > potranno essere utilizzati senza paura.

Questo accorgimento, insieme a pochi altri, rende le pagine HTML5 dei documenti XML well-formed (da qui il termine informale XHTML5).

Anche in questo caso, gli inutili attributi elencati nella sezione precedente non devono essere utilizzati. Un tempo inoltre si usava circondare il codice JavaScript con i <!– commenti HTML –> ma questa tecnica è inutile (e potenzialmente dannosa) già dai tempi di… Netscape 2!

MariaDB: lo Storage Engine Sequence

English version

MariaDB 10.0.3 introduce un nuovo Storage Engine: Sequence. Non è in MySQL né nelle versioni più vecchie di MariaDBs. Sequence è un engine speciale, che non crea né legge alcuna tabella. Si limita a generare al volo una sequenza di interi che viene restituita alla query e poi smette di esistere. I limiti e l’incremento della sequenza dipendono dal nome della tabella.

Un rapidissimo tutorial

Per installare:

INSTALL PLUGIN sequence SONAME 'ha_sequence';

NOTA: Su MariaDB (al contrario di Oracle MySQL) non c’è bisogno di aggiungere ‘.dll’ a questa istruzione su Windows, perciò il comando è indipendente dalla piattaforma. Grazie a Vladislav Vaintroub per questa dritta!

Prima di creare una sequenza bisogna selezionare un database di default, con il comando USE. Poi si può eseguire questa query:

SELECT * FROM seq_1_to_100;

Per creare una sequenza con un incremento diverso da 1:

SELECT * FROM seq_1_to_100_step_2;

Una sequenza non può contenere numeri negativi, ma può essere discendente:

SELECT * FROM seq_10_to_1;

Notare che le sequenze discendenti generano sempre lo stesso insieme di numeri di quelle ascendenti, naturalmente in ordine inverso. Ciò implica che il numero più alto di una sequenza discendente può non essere quello specificato:

MariaDB [test]> SELECT * FROM seq_8_to_1_step_3;
+-----+
| seq |
+-----+
|   7 |
|   4 |
|   1 |
+-----+
3 rows in set (0.00 sec)

Cosa non è sequence

Lo Storage Engine Sequence non è una sequenza SEQUENCE come quelle supportate da PostgreSQL e da IBM, né un generatore di FirebirdSQL. Questo significa che una sequenza esiste solo durante l’esecuzione di una query. Non è possibile estrarre un valore da una sequenza in una query ed estrarre il valore successivo in un’altra query.

Inoltre sequence non può generate numeri negativi e non può ruotare quando raggiunge il limite massimo o minimo (come possono fare invece le sequenze di PostgreSQL).

Alcuni casi d’uso di sequence

Anche se le altre persone non ci credono, gli sviluppatori hanno moltissima fantasia. E’ assolutamente necessaria per risolvere problemi che non hanno una soluzione ovvia, o che non possono essere risolti con un algoritmo progettato da qualcun altro. Perciò sono sicuro che col tempo verranno fuori molti usi creativi dello Storage Engine sequence. Ma per ora, mi piacerebbe condividere alcune idee.

Quasi tutte le tecniche seguenti servono a riempire tabelle con dati generati automaticamente. E’ utile perché, se si può farlo utilizzando una sola query, si evitano molte comunicazioni di rete – che possono essere lente o aumentare il rischio che si verifichino errori.

Trovare i buchi in una colonna

A volte una colonna può contenere una sequenza di valori interi priva di buchi. Un esempio comune sono i campi utilizzati per ordinare gli elementi in una pagina web: mentre i buchi non dovrebbero causare problemi in fase di lettura, possono provocare problemi quando si tenta di modificare l’ordine. Nell’esempio seguente creiamo una tabella con molti buchi e poi visualizziamo una lista dei valori mancanti grazie a una sequenza:

CREATE TABLE t1 (c TINYINT UNSIGNED) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (2), (3), (5), (10);
SELECT s.seq FROM seq_1_to_10 s LEFT JOIN t1 t ON s.seq = t.c WHERE t.c IS NULL;

Costruire una combinazione di numeri

SELECT s1.seq, s2.seq FROM seq_1_to_3 s1 JOIN seq_1_to_3 s2 ORDER BY 1, 2;

Ma si può anche combinare numeri e caratteri (vedi sotto).

Trovare i multipli di 3, minori di 100

SELECT seq FROM seq_3_to_100_step_3;

Trovare i multipli di entrambi i fattori: 3 e 5

SELECT s1.seq FROM seq_5_to_100_step_5 s1 INNER JOIN seq_3_to_100_step_3 s2 ON s1.seq = s2.seq;

Ottimizzato: trovare i multipli di (2 o una delle sue potenze)

SELECT seq FROM seq_1_to_100 WHERE NOT seq & 1; -- ...of 2
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 3; -- ...of 4
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 7; -- ...of 8
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 15; -- ...of 16
...

Costruire una sequenza di stringhe di 1 carattere

La query seguente include le lettere minuscole, le maiuscole e le cifre. Ma sono sicuro che chiunque può modificarla per escludere uno di questi gruppi, o includere altri gruppi (per esempio le vocali accentate).

-- Sequence of 1-char strings
SELECT CHAR(seq) AS ch
    FROM (
                -- lowercase
                (SELECT seq FROM seq_97_to_122 l)
            UNION
                -- uppercase
                (SELECT seq FROM seq_65_to_90 u)
            UNION
                -- digits
                (SELECT seq FROM seq_48_to_57 d)
        ) ch;

Costruire una sequenza di stringhe di 2 caratteri

Ouch… questa UNION è un casino. Ma qualcuno potrebbe volerla usare.

SELECT CONCAT(ch1.ch1, ch2.ch2) AS ch
    FROM (
        (SELECT CHAR(seq) AS ch1
            FROM (
                        -- lowercase
                        (SELECT seq FROM seq_97_to_122 l1)
                    UNION
                        -- uppercase
                        (SELECT seq FROM seq_65_to_90 u1)
                    UNION
                        -- digits
                        (SELECT seq FROM seq_48_to_57 d1)
                ) s1
        )
    ) ch1
    CROSS JOIN (
        (SELECT CHAR(seq) AS ch2
            FROM (
                        -- lowercase
                        (SELECT seq FROM seq_97_to_122 l2)
                    UNION
                        -- uppercase
                        (SELECT seq FROM seq_65_to_90 u2)
                    UNION
                        -- digits
                        (SELECT seq FROM seq_48_to_57 d2)
                ) s2
        )
    ) ch2
    ORDER BY ch1, ch2;

Scusate per la confusione con gli alias (in particolare ch1 e ch2). Sono rimasto a corto di fantasia prima di terminare la query.

Sequenza di date

Ecco, questo secondo me è interessante. Molte applicazioni che gestiscono le prenotazioni al momento dell'installazione popolano una tabella con i giorni e/o le ore che potrebbero essere prenotate. Questo esempio mostra come riempire, in modo facile, una tabella con tutti i giorni di gennaio:

SELECT DATE ('2014.01.01' + INTERVAL (s.seq - 1) DAY) AS d
    FROM (SELECT seq FROM seq_1_to_30) s;

Sequenza di ore, mezz'ore, etc

-- Hours in a day
SELECT CAST('00:00:00' AS TIME) + INTERVAL (s.seq - 1) HOUR AS t
    FROM (SELECT seq FROM seq_1_to_24) s;
-- Halfes of an hour in a day
SELECT CAST('00:00:00' AS TIME) + INTERVAL (30 * s.seq) MINUTE AS t
    FROM (SELECT seq FROM seq_1_to_48) s;

Giorni lavorativi

Gli ultimi due esempi, presi da soli, non sono granché utili. Di solito non si riempe una tabella con tutti i giorni che compongono un mese o un anno, né con tutte le ore che stanno in un giorno. Si scrivono invece soltanto le ore lavorative. Per trovare i giorni lavorativi si può usare la funzione DAYOFWEEK() per escludere i sabati e le domeniche. E' chiaro che questo non esclude automaticamente il natale o altre feste - ma credo che la soluzione migliore sia cancellarle dopo aver riempito la tabella.

SELECT DATE ('2014-01-01' + INTERVAL (s.seq - 1) DAY) AS d
    FROM (SELECT seq FROM seq_1_to_30) s
    -- exclude sunday (1) and saturday (7)
    WHERE DAYOFWEEK(DATE ('2014-01-01' + INTERVAL (s.seq - 1) DAY)) BETWEEN 2 AND 6;

Solo una nota sull'implementazione. Perché iniziare le date dal giorno 1 e poi fare un - 1, invece di cominciare direttamente dal giorno 0? Per due ragioni. Primo, nell'articolo Impostare un SQL_MODE restrittivo spiego che i flag NO_ZERO_DATE e NO_ZERO_IN_DATE dell'SQL_MODE non dovrebbero mai essere impostati. Secondo: '2014-01-00' + INTERVAL 1 DAY restituisce NULL, e non è un bug ma il comportamento corretto. Se non capisci il motivo (ma anche se lo capisci), per favore non usare quei flag. Quelle opzioni fanno finta di essere tue amiche, ma un giorno di queste prenderanno il tuo scalpo mentre dormi!

Ore lavorative

SELECT {dt '2013-01-01 01:00:00'} + INTERVAL (wd.d - 1) DAY + INTERVAL (dh.h - 1) HOUR AS wh
    FROM
        (
            -- working days in a month
            SELECT seq AS d FROM seq_1_to_30
        ) wd
    CROSS JOIN
        (
            -- daily working hours
            (SELECT seq AS h FROM seq_9_to_12)
            UNION
            (SELECT seq AS h FROM seq_14_to_17)
        ) dh
    ORDER BY 1;

Qui abbiamo una subquery che restituisce i giorni lavorativi, unita a una subqueryche restituisce le ore lavorative. Notare che le ore hanno un buco (la paura pranzo dei lavoratori), per questo la seconda subquery è una UNION tra due sequenze.

E... sì, il prodotto cartesiano è malvagio, ma in questo caso è esattamente ciò che ci serve. La parola chiave CROSS serve a chiarire che se modifichi la query e commetti un errore, potresti scrivere una SELECT che restituisce un gran numero di righe.

To-Do e suggerimenti

Più avanti scriverò un articolo che mostra una Stored Function che compone dinamicamente una query per popolare una tabella con valori DATETIME, in un dato intervallo di tempo, con una data granularità.

Pensi che una di queste query non sia ottimizzata nel modo migliore? Hai idee interessanti che riguardano lo Storage Engine sequence? Per favore, lascia un commento.

Divertitevi!

I Cursori di MariaDB/MySQL: un breve Tutorial

English version

In MariaDB e MySQL, i Cursori possono essere utilizzati solo dentro gli Stored Program (Stored Procedure e Function, Trigger, Eventi), sono lenti, e hanno funzionalità molto limitate. Detto questo, in alcuni casi possono comunque essere utili. Questa pagina spiega come utilizzarli, con un esempio triviale.

Ecco una Stored Procedure che usa un Cursore:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN
    DECLARE `eof` BOOL;
    DECLARE `val` INT;
    
    DECLARE `crs` CURSOR FOR
        SELECT `c`
            FROM `test`.`tab1`;
    
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET eof = TRUE;
    
    OPEN `crs`;
    `lp`: LOOP
        FETCH `crs` INTO `val`;
        IF `eof` IS TRUE THEN
            LEAVE `lp`;
        END IF;
        
        SELECT `val`;
    END LOOP;
    CLOSE `crs`;
    
    -- workaround for server bug
    DO (SELECT `c` FROM `test`.`tab1` WHERE FALSE);
END;
    
||
DELIMITER ;


Questa Procedura sarebbe completamente inutile nel Mondo Reale, ma ci serve come esempio: restituisce un diverso ResultSet per ogni riga che si trova nella tabella in test.tab1. Ora vado a spiegare come l’ho implementata.

Prima di tutto, dichiaro (DECLARE) alcune variabili. eof è molto importante, perché indica se il Cursore ha già letto tutte le righe restituite dalla query; per ora, naturalmente, è FALSE. Lo useremo più tardi. Per inciso, il tipo BOOL non esiste in Maria e My, e viene trattato come un TINYINT, così come TRUE è sinonimo di 1 e FALSE è sinonimo di 0. Ma utilizzare le parole chiave BOOL, TRUE e FALSE è un modo per rendere il codice più leggibile. val è la variabile che contiene il valore che ci accingiamo a leggere dalla tabella.

Poi dichiaro il Cursore. In questa istruzione vediamo il nome del Cursore (crs) e la query a cui è associato. Questa query deve essere scritta per esteso in questo punto, perché non è possibile utilizzare l’SQL dinamico con i Cursori. Vi è però un workaround: vedi il mio post Cursors for Dynamic SQL (prima o poi lo tradurrò in italiano).

A questo punto dichiaro l’HANDLER per gli errori che ricadono nella classe NOT FOUND. Qui ci interessa l’errore 1329, che si verifica quando il Cursore ha già letto tutte le righe restituite dalla query e tenta di leggerne un’altra. Ma questo lo spiegherò meglio più avanti.

A questo punto apro (OPEN) il Cursore. E’ a questo punto che la query viene eseguita.

E poi c’è il ciclo LOOP sulle righe. Che cosa accade qui? Beh, prima cerchiamo di estrarre una nuova riga dal ResultSet, con FETCH. Se l’operazione ha successo, il valore dell’unico campo presente nella SELECT viene copiato nella variabile val. Controlliamo il valore di eof e, siccome è FALSE, saltiamo la IF. E produciamo un ResultSet con il valore che abbiamo letto, che viene inviato al client. Questo avviene per tre volte, perché abbiamo creato una tabella con tre righe.

La quarta volta, l’istruzione FETCH fallisce, perché non ci sono più righe da leggere. Viene allora generato un errore, e quindi viene eseguito l’HANDLER che abbiamo definito prima. Qui la variabile eof viene impostata a TRUE. Siccome è un CONTINUE HANDLER, dopo questa istruzione, l’esecuzione della Routine riprende da dov’era rimasta e troviamo la IF. Questa volta eof è TRUE, perciò entriamo nella IF. E troviamo un’istruzione LEAVE associata all’etichetta lp. lp è il LOOP, perciò a questo punto ne usciamo.

Ora possiamo chiudere (CLOSE) il Cursore.

Ma ora abbiamo un piccolo problema. Le versioni di MySQL più vecchie della 5.6, e MariaDB, hanno un bug (che però MariaDB sta risolvendo) a causa del quale l’Errore 1329 sopravvive dopo l’HANDLER.Se non facciamo niente per rimuoverlo, verrà inviato al client. Ma se eseguiamo una query su una tabella fisica, l’Errore viene dimenticato (il motivo esatto per cui questo accade va oltre gli scopi di questo post). Non vogliamo che venga restituito un altro ResultSet, perciò utilizziamo la SELECT come subquery dell’istruzione DO (che non restituisce mai nulla). Usiamo WHERE FALSE per evitare di appesantire la Procedura.

Esercizi

Questa volta vi darò qualche semplice esercizio. Se li risolvete potete postare il vostro codice nei commenti, se volete. Se non riuscite a risolverli potete postare delle domande, in modo che io possa aiutarvi. Vi suggerisco di svolgere gli esercizi, perché non si impara molto senza la pratica.

1) Modificate la Procedura demo in modo che termini se incontra più di un valore NULL.

2) Scrivete una nuova Procedura che legge le righe dalla tabella PROCESSLIST del database information_schema, e uccide (KILL) tutte le connessioni che sono in idle ed esistono da più di N secondi – dove N è un parametro della Procedura.

Divertitevi!