Il FETCH che si ferma sui NULL — SQLCODE -305 e l'array di indicatori

Il programma legge un cursore su una tabella larga — mettiamo ottantadue colonne — e per un po' funziona. Poi, su una riga a caso, si ferma:

SQL0305  Indicator variable required.
         SQLCODE -305   SQLSTATE 22002

La reazione istintiva è mettere un COALESCE su ogni colonna della SELECT. Con ottantadue colonne significa ottantadue funzioni scritte a mano, ognuna con il suo valore di sostituzione del tipo giusto, da rifare ogni volta che qualcuno aggiunge una colonna. Si può fare in due righe, e senza perdere l'informazione.

Che cosa sta dicendo davvero il messaggio

Non è un problema di RPG e non è un problema di compilazione. È una regola di SQL: un valore nullo non si può assegnare a una variabile host, perché nella variabile non c'è posto per dirlo. Un campo char(10) può contenere dieci spazi, non può contenere «niente». Serve un secondo canale, ed è la variabile indicatore: un intero corto che viaggia accanto alla variabile e dice se il valore è arrivato oppure no.

Se il valore è nullo e l'indicatore non c'è, Db2 non ha modo di consegnare il dato e solleva -305. La regola vale per FETCH, SELECT INTO, CALL, SET, VALUES INTO: ovunque un valore esca dal database ed entri in una variabile del programma.

Il messaggio nel job log è più utile di quanto sembri: il secondo livello dice quale variabile host ha bloccato l'assegnazione e la sua posizione relativa nella INTO. Con ottantadue colonne, quel numero è il modo più veloce per sapere di quale colonna si sta parlando.

Il danno che non si vede

Questa è la parte che conviene conoscere prima di scrivere la correzione, perché cambia il giudizio su quanto è grave il problema.

Quando l'assegnazione fallisce, SQL non annulla quello che ha già fatto. La documentazione è esplicita: il valore che ha causato l'errore non viene assegnato, da lì in poi non viene assegnato più niente, e i valori già messi nelle variabili restano dove sono.

Tradotto: dopo un -305 la struttura contiene le colonne che stanno prima di quella nulla, prese dalla riga corrente, e tutte quelle dopo con il contenuto della riga precedente. È una riga che non è mai esistita. Se il ciclo controlla solo sqlstate < '02000' esce e non se ne accorge nessuno; se il controllo è più permissivo — o se il -305 viene trattato come un avviso da saltare — il programma elabora quella riga inventata e la scrive da qualche parte.

La correzione: un array di indicatori

Un indicatore per colonna, tutti in un array, e l'array attaccato alla struttura nella INTO:

dcl-ds riga qualified;
  ordNum  packed(7:0);
  ordCli  char(10);
  ordDat  date;
  // … le altre colonne
end-ds;

dcl-s nullo int(5) dim(82);

exec sql
  DECLARE C1 CURSOR FOR
  SELECT * FROM ORDINI
   ORDER BY ORDNUM
   FOR READ ONLY;

exec sql OPEN C1;

dow sqlstate < '02000';
  clear riga;
  exec sql FETCH C1 INTO :riga :nullo;
  if sqlstate >= '02000';
    leave;
  endif;

  if nullo(3) < 0;
    // la data non c'è: non è il 1° gennaio 0001, non c'è
  endif;
enddo;

exec sql CLOSE C1;

Tre dettagli che fanno la differenza fra funziona e non compila:

• L'indicatore va dopo la variabile host, preceduto dai due punti e separato da uno spazio. Non da una virgola. La virgola separa due variabili host distinte: scrivendo INTO :riga, :nullo si sta chiedendo a SQL di mettere la seconda colonna dentro l'array, e il risultato è un errore di compilazione o, peggio, un programma che compila. La forma esplicita INTO :riga INDICATOR :nullo è equivalente e più leggibile.
• int(5), cioè uno smallint: è il tipo che SQL si aspetta, non è negoziabile.
• dim deve coprire tutte le colonne del risultato. Con una struttura dati l'array è posizionale: il primo elemento è la prima colonna, il secondo la seconda, e così via. Avanzare elementi non fa danno. Averne meno è responsabilità di chi scrive: la documentazione IBM chiede che l'array sia grande abbastanza e non promette nessun controllo, quindi non è il caso di contarci.

Perché quel clear

Non è prudenza generica, è la riga che dà il risultato che ci si aspetta.

La documentazione IBM dice che quando il database restituisce un valore nullo la variabile host può essere o non essere impostata al valore di default del tipo — zero per i numerici, spazi per gli alfanumerici. Può: non deve. In pratica, sui campi lasciati fuori, resta quello che c'era prima, cioè il valore della riga precedente.

Azzerando la struttura prima di ogni FETCH, le colonne nulle restano a spazi e zeri e quelle piene vengono sovrascritte comunque. Il costo è una riga; il risultato è identico a ottantadue COALESCE, con in più il fatto che si continua a sapere quali colonne erano nulle davvero.

Leggere gli indicatori senza contare le colonne

Scrivere nullo(47) è corretto e illeggibile. Il modo per dare un nome a ogni elemento è sovrapporre alla stessa memoria una struttura con i nomi delle colonne:

dcl-s nullo int(5) dim(82);

dcl-ds nulli based(pNulli) qualified;
  ordNum  int(5);
  ordCli  int(5);
  ordDat  int(5);
  // … uno per colonna, nello stesso ordine della SELECT
end-ds;

pNulli = %addr(nullo);

Da lì in poi si scrive nulli.ordDat < 0, che si legge. Se le colonne sono trecento e elencarle a mano non è pensabile, il numero di ogni sottocampo sta nel listato di compilazione accanto al nome — ed è lo stesso numero che compare nel secondo livello di SQL0305.

E i valori: -1, -2, e i positivi

L'errore più diffuso, dopo aver messo gli indicatori, è controllare < 0 e chiamarlo NULL. I valori sono tre cose diverse:

Il -2 è il caso che fa perdere il pomeriggio. Significa che il dato c'era, ma trasformarlo in qualcosa che stia nella variabile ha prodotto un errore: una data fuori intervallo, una divisione per zero in un'espressione della SELECT, un overflow numerico, caratteri non convertibili nel CCSID di destinazione. SQL non interrompe l'istruzione — restituisce nullo, mette -2 e va avanti. Chi tratta -2 come -1 sta scambiando un archivio sporco per un campo vuoto.

I positivi sono l'altra sorpresa: una stringa più lunga della variabile viene troncata, e senza indicatore non viene segnalato nessun errore. L'indicatore è l'unico posto dove quel fatto compare.

La lettura a blocchi

Su una tabella grande il FETCH riga per riga si sostituisce con la lettura multipla, e la mappa dei nulli diventa un array a due dimensioni: una riga di indicatori per ogni riga letta.

dcl-ds righe extname('ORDINI') qualified dim(500)
end-ds;

dcl-ds nulli qualified dim(500);
  ind int(5) dim(82);
end-ds;

dcl-s quante int(10) inz(%elem(righe));
dcl-s lette  int(10);

exec sql FETCH C1 FOR :quante ROWS INTO :righe :nulli;
exec sql GET DIAGNOSTICS :lette = ROW_COUNT;

L'indicatore della quarta colonna della seconda riga è nulli(2).ind(4). I due dim devono essere lo stesso numero: la mappa dei nulli si riempie in parallelo alle righe.

ALWNULL non c'entra — e questo va detto

È il consiglio che si trova più spesso cercando SQL0305, ed è sbagliato: compilare con ALWNULL(*INPUTONLY) non ha effetto sull'SQL incorporato.

ALWNULL è una parola chiave della control specification RPG, e la documentazione dice esattamente su che cosa agisce: sui record che arrivano da file descritti esternamente, cioè dalla lettura nativa — CHAIN, READ, SETLL. Riguarda %NULLIND, che senza ALWNULL(*USRCTL) non si può nemmeno usare. Non tocca il percorso EXEC SQL, dove il comportamento è deciso dalla presenza o meno dell'indicatore e da nient'altro.

Due conseguenze pratiche:

• CRTSQLRPGI non ha un parametro ALWNULL. Chi lo cerca sul comando non lo trova, e non è una svista: si scrive in ctl-opt o si passa al compilatore con COMPILEOPT('ALWNULL(*USRCTL)').
• Il valore predefinito è *NO, e cambiarlo può servire per altri motivi — ma non farà sparire un -305.

Quando il COALESCE va bene lo stesso

Non è una funzione da evitare: è una scelta diversa, che va fatta sapendo che cosa si perde.

COALESCE(ORDIMP, 0) dice al database di consegnare zero al posto del nullo. Da quel momento il programma non ha più modo di distinguere «importo zero» da «importo non compilato», e su un totale le due cose coincidono, mentre su una media no: AVG salta i nulli e conta gli zeri. Se la distinzione non serve — un contatore, una somma, una stampa — il COALESCE è più corto ed è la scelta giusta. Se serve, l'indicatore è l'unico modo per conservarla.

Su Db2 for i esiste anche IFNULL(colonna, valore), che fa la stessa cosa con due argomenti soli. COALESCE è quella standard e ne accetta quanti se ne vuole: a parità di risultato, tanto vale scrivere quella che si porta altrove.

«Ma le mie colonne sono tutte NOT NULL»

Capita, ed è il momento in cui si comincia a dubitare del sistema. La tabella non è l'unica sorgente di nulli: il risultato della query lo è.

• Un LEFT OUTER JOIN o un EXCEPTION JOIN produce nulli su tutte le colonne della tabella che non ha trovato corrispondenza, comprese quelle dichiarate NOT NULL.
• Un aggregato su un insieme vuoto restituisce nullo: MAX, MIN, SUM, AVG. L'unico che non lo fa è COUNT, che risponde zero — ed è il motivo per cui l'errore sorprende.
• Una sottoquery scalare che non trova righe vale nullo.
• Un CASE senza ELSE vale nullo quando nessun ramo corrisponde.
• NULLIF restituisce nullo per definizione.
• Un errore di conversione dà nullo con indicatore -2, come sopra.

Per sapere in fretta quali colonne di una tabella ammettono nulli:

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
  FROM QSYS2.SYSCOLUMNS
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND TABLE_NAME   = 'ORDINI'
   AND IS_NULLABLE  = 'Y'
 ORDER BY ORDINAL_POSITION

ORDINAL_POSITION è la stessa posizione che serve per indicizzare l'array di indicatori, ed è lo stesso numero che il job log ha già scritto nel secondo livello del messaggio.

La correzione a monte, quando si può

Se la tabella è vostra e quelle colonne non dovrebbero mai essere vuote, il posto giusto per sistemare non è il programma:

ALTER TABLE PRODLIB/ORDINI
  ALTER COLUMN ORDIMP SET NOT NULL

Funziona solo se non ci sono già righe nulle, e questo è un pregio: il comando che fallisce sta dicendo quante righe di dati sporchi ci sono in produzione. Chiudere il buco nella definizione toglie il problema a tutti i programmi che leggono quella tabella, presenti e futuri, invece che a uno solo.

Rilasci e verifica. Le regole sugli indicatori valgono su tutti i rilasci supportati; gli esempi sono in RPG completamente libero e sono stati controllati sulla documentazione IBM i 7.5. Il comportamento sulle assegnazioni parziali, i valori -1 / -2 / positivi e il campo d'azione di ALWNULL vengono dalla documentazione IBM citata in fondo, non dalla memoria.

Fonti. Indicator variables in applications that use SQL · Indicator variables used with host structures · FETCH · References to host variables · CRTRPGMOD — ALWNULL · SYSCOLUMNS