Im letzten Artikel zu diesem Thema wurden die einzelnen Schritte zur Prüfung einer E-Mail-Adresse entwickelt und erläutert. Diese Einzelteile werden nun zusammengefasst. Dies geschieht zunächst in Form eines Compound Statements, dieses wird dann in eine Prüffunktion überführt, die dann für den Einsatz eines Triggers angepasst wird.
Was ist für die Prüfung zu beachten?
Um zu ermitteln, ob eine E-Mail-Adresse gültig ist, sind folgende Schritte erforderlich:
- Ist der Inhalt des Feldes E-Mail leer oder NULL? Sollen diese Werte zulässig ein?
- Ist das ‘@‘-Zeichen vorhanden?
- Überprüfen der Syntax
- Aufbau der E-Mail-Adresse nach RFC 5321 und RFC 5322
- Der lokale Teil der Adresse darf aus bis zu 64 Teichen bestehen (vor ‘@‘)
- Der Domänen-Teil darf aus bis zu 253 Zeichen bestehen (nach ‘@‘)
- Der erste Teil darf aus lateinischen Buchstaben, Zahlen und einigen Sonderzeichen bestehen
- Überprüfen der Domain
- Beim Domänen-Teil muss es sich um eine gültige Domäne handeln
Für die einzelnen Prüfungen und der zum Abschluss zu erstellenden Funktion soll nur SQL verwendet werden.
Für die Erstellung der Funktion werden mehrere eingebaute SQL-Funktionen wie LENGTH, POSITION, SUBSTRING, Prüfung auf gültigen Ausdruck mit REGEXP_LIKE und Konstrukte wie die CASE-Anweisung, SQL-Variable, das COMPOUND STATEMENT und die Tabellenfunktion DNS_LOOKUP verwendet.
Hinweis: Die hier verwendeten Prüfungen erheben keinen Anspruch auf Vollständigkeit und Korrektheit. Anpassungen können von Ihnen selbst durchgeführt werden.
Hier folgen nun die weiteren Schritte zur Prüfung der E-Mail auf Korrektheit.
Meine Testumgebung
Zum Testen habe ich mir folgende E-Mail-Adressen in SQL-Variablen abgespeichert, um die Beispiele übersichtlich zu halten:
create or replace variable qgpl.email varchar(400) default(‚info@edv-baer.com‘); — gültige E-Mail
create or replace variable qgpl.email2 varchar(400) default(‚info&edv-baer.com‘); — ungültige E-Mail, @ fehlt
create or replace variable qgpl.email3 varchar(400) default(‚123456789012345678901234567890123456789012345678901234567890123456789@edv-baer.com‘); — ungültige E-Mail, 1.Teil > 64t
Für die einzelnen Prüfungen verwende ich die folgenden Stati:
— Bedeutung der Prüf-Stati:
— 0 = kein Fehler
— 1 = Länge Email > 318
— 2 = kein @-Zeichen vorhanden
— 3 = Länge 1.Teil > 64
— 4 = Länge 2.Teil > 253
— 5 = ungültige Syntax
— 6 = Domain existiert nicht
Für die Prüfungen mit dem Compound Statement und der Funktion habe ich eine Tabelle EMAILS erstellt.
/* Tabelle EMAILS zum Testen erstellen */
Create or Replace Table tb012.emails
(email varchar(318) ;
insert into tb012.emails values(‚info@edv-baer.com‘);
insert into tb012.emails values(‚info@edv-baer.Xom‘);
insert into tb012.emails values(‚info&edv-baer.Xom‘);
insert into tb012.emails values(‚123456789012345678901234567890123456789012345678901234567890123456789@edv-baer.com‘);
insert into tb012.emails values(‚ ‚);
insert into tb012.emails values(‚x–…333((( ‚);
insert into tb012.emails values(‚xxx@–pp893—8))))) ‚);
insert into tb012.emails values(‚ @!“§$%&/‘);
Compound Statement mit den einzelnen Prüfschritten
Die einzelnen Prüfungen werden nun in einem Compound Statement zusammengefasst. Dieses kann kann dann komplett zum Testen ausgeführt werden. Beim Prüfen der Domain mit der Funktion QSYS2.DNS_LOOKUP muss sichergestellt sein, dass die darin verwendeten Zeichen gültig sind (Prüfung mit REGEXP_like).
/*————————————————————*/
/* alle Prüfungen mit einem Compound-Statement */
/*———————————————————–*/
Begin
declare status int;
declare v_Nachricht varchar(100);
SET status = 0;
— 0. Prüfen, ob EMAIL = Blank oder IS NULL –> zulässig
IF qgpl.email = ‚ ‚ or qgpl.email IS NULL
THEN SET status = 0;
ELSE — nur dann sollen auch die folgenden Prüfungen durchgeführt werden
— — 1. Prüfen max. Länge <=318 — kann entfallen, da Feld für EMAIL Varchar (318)
— set status = (select case
— when length(qgpl.email) <= 318
— Then 0
— else 1
— end
— from sysibm.sysdummy1);
— 2. Prüfen, ob @-Zeichen vorhanden
IF (SELECT
CASE
WHEN position(‚@‘, qgpl.email) > 0
THEN 0
ELSE 2
END
from sysibm.sysdummy1) = 2
THEN SET status = 2;
ELSE
— nur falls @-Zeichen vorhanden, dann folgende Prüfungen ausführen
— 3. Prüfen, Länge 1. Teil > 64
SET status = status +
(SELECT
CASE
WHEN length(substring(qgpl.email, 1, position(‚@‘, qgpl.email )-1)) <= 64 — mit email3 –> Fehler
THEN 0
ELSE 3
END
from sysibm.sysdummy1 ) ;
— 4. Prüfen, Länge 2. Teil > 253
SET status = status +
(SELECT
CASE
WHEN length(trim(substring(qgpl.email, position(‚@‘, qgpl.email )+1))) <= 253
THEN 0
ELSE 4
END
from sysibm.sysdummy1);
— 5. Prüfen gültige Syntax
set status = status +
(SELECT
CASE
WHEN REGEXP_like(qgpl.email, ‚^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$‘) — mit email5 Fehler
THEN 0
ELSE 5
END
FROM sysibm.sysdummy1);
— 6. Prüfen, ob Domain vorhanden
— Domain muss gültige Zeichen enthalten!
if status = 0 — M5
Then
IF — M6
(SELECT 1
FROM TABLE(QSYS2.DNS_LOOKUP(
rtrim(substr(qgpl.email, position(‚@‘, qgpl.email) + 1)))) — mit email6 Fehler
LIMIT 1 ) IS NULL
THEN SET status = status +6;
END IF ; — M6
END IF; — M5
end if;
end if;
/* Ende der Prüfungen */
/* Abbruchnachricht, falls Fehler */
— status > 0 — Fehler
if status >0
THEN
call qsys2.qcmdexc(‚SNDBRKMSG MSG(FEHLER) TOMSGQ(E550AB)‘); — nur zum Testen
Set v_Nachricht = ‚***Fehler ist aufgetreten*** Status = ‚ || Status;
SIGNAL SQLSTATE ‚75002‘ SET MESSAGE_TEXT = v_Nachricht;
else
call qsys2.qcmdexc(‚SNDBRKMSG MSG(“Kein Fehler“) TOMSGQ(E550AB)‘); — nur zum Testen
SIGNAL SQLSTATE ‚01002‘ SET MESSAGE_TEXT =’***KEIN Fehler ist aufgetreten***‘;
end if;
— return Status; — erst aktivieren, wenn als Funktion realisiert
End;
/*————————————————————————*/
Die Funktion checkEMail()
Dieses Compound Statement verwende ich jetzt für das Erstellen der Funktion checkEMail() wie folgt:
/*————————————————————–*/
/* CheckEmail – Funktion zum Prüfen E-Mail-Adresse */
/* 2023-06-07 */
/* endgültige Version */
/*————————————————*/
— Rückgabewerte der skalaren Funktion:
— 0 = gültig, > 0 = ungültig
Create Or Replace Function Tb012.checkEmail (
Email Varchar(318)
)
Returns Int
LANGUAGE SQL
DETERMINISTIC
Begin
…
Hier wird nun das komplette Compound Statement bis zum Kommentar /* Ende der Prüfungen */
eingefügt und danach:
….
return Status; — erst aktivieren, wenn als Funktion realisiert
End;
/* Ende der Funktion */
Die Funktion kann nun zum Prüfen der E-Mail-Adressen in der Tabelle EMAILS verwendet werden:
— Test
select * from tb012.emails;
select Email, tb012.checkEmail(Email) as „Status: 0=OK“ from tb012.emails;
values tb012.checkEMail(‚info@edv-baer.com‘);
values tb012.checkEMail(‚info@edv-baer.Xom‘);
values tb012.checkEMail(‚ ‚);
values tb012.checkEMail(‚info&edv-baer.Xom‘);
— alle ungültigen EMails ernitteln
select Email, tb012.checkEmail(Email) as „ungültig > 0“ from tb012.emails
where tb012.checkEmail(Email) <> 0;
STOP;
Quelle: BärErgebnis der Prüfung mit checkEMail()
Alle ungültigen Emails erhalten Sie mit:
— alle ungültigen EMails ernitteln
select Email, tb012.checkEmail(Email) as „ungültig > 0“ from tb012.emails
where tb012.checkEmail(Email) <> 0;
Quelle: BärAlle ungültigen Emails
Funktion checkEMail() als Prüfintegrität oder Trigger?
Nun hätte ich diese Funktion gerne als Prüfintegrität in die Tabelle EMALS wie folgt eingefügt:
/* Tabelle EMAILS zum Testen erstellen */
Create or Replace Table tb012.emails
(email varchar(318) CHECK(tb012.checkEmail(email) =0)); — Prüfintegrität mit UDF geht leider nicht
Der Versuch scheiterte, da die Verwendung von eigenen Funktionen (UDFs) für Prüfintegritäten nicht erlaubt ist. Schade!
Die Lösung:
Die Funktion wird beim Implementieren eines Triggers verwendet:
/*Trigger erstellen */
Create Or Replace Trigger tb012.checkEmailTrg
After insert or Update On TB012.EMAILS
Referencing New Row As N Old Row As O
FOR EACH ROW
MODE DB2SQL
Begin Atomic
If tb012.checkemail5(N.email) > 0 THEN — EMail ungültig
SIGNAL SQLSTATE ‚75001‘ (
‚EMail ungültig‘
);
end if;
End;
Dazu habe ich die Funktion checkEMail5() erstellt, die identisch ist mit checkEMail() mit dem Zusatz:
— status > 0 — Fehler
if status >0
THEN
Set v_Nachricht = ‚***Fehler ist aufgetreten*** Status = ‚ || Status;
SIGNAL SQLSTATE ‚75002‘ SET MESSAGE_TEXT = v_Nachricht;
else
SIGNAL SQLSTATE ‚01002‘ SET MESSAGE_TEXT =’***KEIN Fehler ist aufgetreten***‘;
end if;
return Status; — erst aktivieren, wenn als Funktion realisiert
End;
Quelle: BärTest der Triggerfunktion mit Fehler
Nun wünsche ich Ihnen viel Spaß beim Programmieren und Implementieren mit SQL.
Vorschau:
Im nächsten Artikel werden die hier behandelten einzelnen Prüfschritte zu einer Prüffunktion (UDF) zusammengeführt.
Bis dahin wünsche ich Ihnen weiterhin viel Spaß beim Vermehren Ihrer Fertigkeiten.
Der Autor Theo Bär schreibt regelmäßig für den TechKnowLetter.
Sie erreichen ihn unter
EDV-Beratung Theo Bär
Ringmauerweg 1
69250 Schönau
Tel.: (+49) 6228 912 630
E-Mail: [info@edv-baer.com]
Sechs Ausgaben des TechKnowLetters erhalten Sie hier für 88 Euro.
