Axel Kohnert
Lehrstuhl Mathematik 2
WS 2000/2001
Embedded SQL für
C ermöglicht die Erstellung von Datenbankapplikationen durch die Einbettung
von SQL-Anweisungen in C/C++ Programme. Eine ESQLC Applikation kommuniziert
mit dem Datenbankserver und ermöglicht so den Zugriff auf eine Datenbank.
Die Programme werden durch eine precompiler in ein C Programm umgewandelt.
Dabei werden header Dateien (.h - Dateien) und eine Bibliothek zur Verfügung
gestellt, damit kann der normale C Compiler ein lauffähiges Programm
erstellen. Vorausetzung für das Funktionieren des Programms später
ist natürlich, dass auf dem System auch ein Datenbankserver vorhanden
ist. Ferner ist es möglich Netzwerkroutinen zu linken, damit der Zugriff
über Netz auf einen Datenbankserver möglich ist. Auch die Syntax
von Embedded SQL für C ist standardisiert (ANSI,
X/Open).
Um eine oder mehrere
SQL-Anweisungen in ein C Programm einzubetten, muß die SQL-Anweisung
speziell gekennzeichnet werden. Dies geschieht durch die Schlüsselwörter
EXEC SQL, die vor den Beginn jeder SQL- Anweisung gestellt werden müssen.
Eine SQL-Anweisung muß mit einem Semikolon abgeschlossen werden.
Eine SQL-Anweisung in einem
C Programm hat dementsprechend folgende, allgemeine Syntax:
EXEC SQL SQL-Anweisung;
Eine so eingebette SQL-Anweisung kann mehrere Zeilen umfassen, wobei auch beliebig viele Leerstellen, Tabulatoren und Leerzeilen vorkommen können.
Beispiel:
EXEC SQL CREATE TABLE
base_table
(
nr INTEGER,
/* Kundennummer */
name CHAR(20)
/* Kundenname */
);
Man sieht auch die Verwendung
von Kommentaren innerhalb der EXEC SQL Anweisung. Das ist erlaubt, der
Kommentar darf nur nicht zwischen EXEC und SQL stehen. Da die EXEC SQL
Anweisungen in C Anweisungen umgewandelt werden, dürfen sie innerhalb
des C Codes nur da gesetzt werden wo auch C Anweisungen stehen dürfen.
Ein Programm kann
neben normalen C Anweisungen 2 Arten von SQL-Anweisungen enthalten:
Deklarative SQL-Anweisungen,
die in dem deklarativen Teil eines Programmes angegeben werden. Hierzu
gehören die folgenden Anweisungen:
SQL-Anweisungen, die
in dem prozedualen Teil eines Programmes vorkommen. Hierzu gehören
die folgenden Anweisungen:
EXEC SQL BEGIN DECLARE
SECTION
eingeleitet und endet mit
EXEC SQL END DECLARE SECTION.
Ein ESQLC Programm kann beliebig
viele DECLARE Abschnitte enthalten. Das folgende Beispiel zeigt einen SQL
DECLARE Abschnitt in der
Funktion main:
main(int argc, char **argv)
{
EXEC SQL BEGIN DECLARE SECTION
int i = argc;
char **args = argv;
int x, y, z;
char *dbuser = "rdb";
EXEC SQL END DECLARE SECTION
}
Die INCLUDE Anweisung
dient dazu, Header Dateien in ein ESQLC-Modul einzufügen. Die allgemeine
Form der INCLUDE Anweisung ist wie folgt:
EXEC SQL INCLUDE header-datei;
Der Inhalt der Header Datei
wird vom Precompiler gelesen und in die C Datei eingefügt. Sofern
die Header Datei SQL-Anweisungen enthält, werden diese in C Code übersetzt.
Das würde nicht mit dem normalen #include Befehl aus dem C-Präprozessor
funktionieren.
Die WHENEVER Anweisung
dient der Fehlerbehandlung in einem Embedded SQL C Programm. Über
die WHENEVER Anweisung kann das Verhalten eines Programmes beim Auftreten
eines Fehlers, der durch eine SQL-Anweisung ausgelöst wurde, festgelegt
werden. Soll z.B. ein Programm nach dem Auftreten eines Fehlers beendet
werden, kann dies durch die Formulierung folgender WHENEVER Anweisung
erreicht werden:
EXEC SQL WHENEVER SQLERROR STOP
(später genauer)
Die CONNECT Anweisung
ist eine SQL-Anweisung, die eine besondere Bedeutung hat. Eine CONNECT
Anweisung muß vor jeder anderen SQL-Anweisung mit Ausnahme eines
DECLARE Abschnitts oder der WHENEVER Anweisung ausgeführt werden.
Durch die CONNECT Anweisung wird eine Verbindung eines C Programmes (Clientprogramm)
zum SQL Serverprogramm hergestellt. Erst nach der Ausführung
der CONNECT Anweisung können weitere SQL-Anweisungen ausgeführt
werden. Das folgende Beispiel zeigt die Anmeldung des Benutzers data12
mit dem password data12 über eine CONNECT Anweisung:
EXEC SQL CONNECT USER
'data12' USING 'data12';
Zuerst ein einfaches
Beispiel um den Umgang mit Embedded SQL zu zeigen. Wir haben ein einfaches
Programm mit Namen bsp1.ec:
main()
{
EXEC SQL whenever sqlerror
stop;
EXEC SQL connect user 'axel'
using '....';
EXEC SQL create database
'beispiel';
EXEC SQL close database;
}
Mit diesen SQL Befehlen wird lediglich eine Datenbank erstellt. Passiert ein Fehler (z.B. wenn es zweimal ausgeführt wird) wird das Programm abgebrochen. Der erste Schritt ist das Programm mit dem Präprozessor zu übersetzen und dann den resultierenden C Source Code zu übersetzen und mit den Datenbank routinen zu linken, Dies geschieht mit dem Befehl
yardpc bsp1.ec
dies erstellt ein ausführbares
Programm a.out, was die gewünschten Dinge erledigt. Das zweite Beispiel
ist schon etwas umfangreicher und bedarf dann einiger Erläuterungen:
/* bsp2.ec */
#include <stdio.h>
#include <stdlib.h>
#include <math.h>
isprim(int n)
{main()
int k,s;
s = (int) sqrt((double)n) +1;
for (k=2;k<=s ;k++)
if (n % k == 0) return 0;
return 1;
}
printf("(1) anlegen der primzahl tabelle \n");switch(eingabe)
printf("(2) loeschen der primzahl tabelle \n");
printf("(3) zufaellig fuellen der primzahl tabelle \n");
printf("(4) ab startwert fuellen der primzahl tabelle \n");
printf("(5) anzeigen der primzahl tabelle \n");
printf("(6) select der primzahl tabelle \n");
printf("(7) suchen der primzahl zwillinge \n");
printf("(8) ende \n");scanf("%d",&eingabe);
EXEC SQL DATABASE 'beispiel';case 2:
EXEC SQL CREATE TABLE PRIMZAHLEN(zahl INTEGER UNIQUE,
prim INTEGER CHECK (prim = 1 or prim = 0));
EXEC SQL CLOSE DATABASE;
goto again;
EXEC SQL DATABASE 'beispiel';
EXEC SQL DROP TABLE PRIMZAHLEN;
EXEC SQL CLOSE DATABASE;
goto again;
case 8:
break;default:
goto again;}
Dies Programm soll zur Manipulation
einer Primzahltabelle dienen. Dazu gibt es eine Hilfsfunktion (isprim)
die eine int Variable auf die Primzahleigenschaft testet. Der Rückgabewert
ist 1 für Primzahl und 0 sonst. Das Hauptprogramm (main) erlaubt verschiedene
Operationen mit dieser Tabelle, die der Benutzer über ein Menu auswählt.
Punkt 1 legt die Tabelle an, Punkt 2 löscht sie wieder. Da verschiedene
Variablen zur Kommunikation zwischen C Programm und SQL Befehlen benötigt
werden gibt es eine DECLARE section, in der die entsprechenden Variablen
deklariert werden. Mit der WHENEVER Anweisung wird festgelegt, dass bei
einem SQL Fehler das Programm beendet wird. Der erste eigentliche SQL Befehl
ist das Anmelden mit CONNECT. Des weiteren ist im Menupunkt 1 die CHECK
Bedingung interessant, damit wird überwacht das nur die Werte 0 oder
1 in der Spalte eingetragen werden. Mit UNIQUE wird festgelegt, dass jede
Zahl nur einmal eingetragen wird.
Menupunkt 3 dient zum Füllen mit zufälligen Werten:
case 3:
EXEC SQL DATABASE 'beispiel';Beim Füllen mit zufälligen Werten will man natürlich weitermachen wenn ein Wert schon da ist (Verletzung der UNIQUE Anweisung) dazu der Befehl WHENEVER .... CONTINUE. Bei der INSERT Anweisung sieht man die Verwendung der sog. hostvariabeln. Es wird der normale C-Variablen Name genommen mit einem vorangestellten : . Bei den Variablen ist auf Kompatibilität zu achten, was beim C Typ int und SQL Typ INTEGER gegeben ist. Am Ende wird wieder die normale Fehlerbehandlung eingeschaltet. Der nachfolgende Menupunkt 4 ist fast identisch:
EXEC SQL WHENEVER SQLERROR CONTINUE;for (i=0;i<1000;i++)
{
kandidat = rand();
prim = isprim(kandidat);
EXEC SQL INSERT INTO PRIMZAHLEN VALUES (
:kandidat, :prim );
}
EXEC SQL CLOSE DATABASE;
EXEC SQL WHENEVER SQLERROR STOP;
goto again;
case 4:
again4:for (kandidat=eingabe+1000;kandidat>=eingabe; kandidat--)
printf("bitte startwert (>0) eingeben:");
scanf("%d",&eingabe);
if (eingabe<1) goto again4;
EXEC SQL DATABASE 'beispiel';
EXEC SQL WHENEVER SQLERROR CONTINUE;
Interessanter wird es beim Menupunkt 5, wenn es um die Verarbeitung von Ergebnismengen geht. Hierzu verwendet man sogenannte Cursor. Die Idee ist mit dem Cursor durch die Ergebnismenge zu navigieren.
case 5:
EXEC SQL DATABASE 'beispiel';stop4:
EXEC SQL DECLARE c1 SCROLL CURSOR FOR
SELECT * FROM PRIMZAHLEN;
EXEC SQL OPEN c1;
EXEC SQL WHENEVER NOT FOUND goto stop4;while(1){
EXEC SQL FETCH c1 INTO :kandidat,:prim;}
printf("%10d %1d\n",kandidat,prim);EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE c1;
EXEC SQL CLOSE DATABASE;
goto again;
Der erste Schritt ist die
Definition des Cursors, dies geschieht mit dem SQL Befehl
DECLARE ... CURSOR FOR
...;
dieser Befehl ist nur in
ESQLC sinnvoll. Der Cursor bekommt einen Namen, der innerhalb des ESQCL
Programms eindeutig ist, d.h. braucht man verschiedene Cursor, so muss
man verschiedene Namen vergeben. Der zweite Teil ist eine SELECT Anweisung,
die mittels Cursor durchlaufen werden soll. Der nächste Schritt ist
der Befehl
OPEN cursorname;
damit wird die konkrete SELECT
Anweisung ausgeführt. Nächster Schritt ist eine Schleife die
solange ausgeführt wird, bis ein Fehler auftritt. Sie wird über
den WHENEVER Befehl beendet. (Schöner programmiert noch mal später
in einem weiteren Beispiel. Innerhalb der Schleife wird ein einzelner
Datensatz aus der Cursormenge geholt, dies geschieht mit dem SQL Befehl
FETCH ... INTO ...;
Fetch holt jeweils den nächsten
Datensatz. Wenn mit FETCH ausserhalb der Ergebnismenge positioniert wird
wird das Ereignis NOT FOUND erzeugt. Dann wird durch WHENEVER zur Marke
stop4 gesprungen. Wird der Cursor als sog. SCROLL CURSOR definiert, wie
im Beispiel hat man noch weitere Möglichkeiten die Ergebnismenge zu
durchlaufen:
| Fetchrichtung | Positionierung |
| NEXT | Positioniert den Cursor auf den nächsten Satz der Ergebnismenge. |
| PREVIOUS
PREV PRIOR |
Positioniert den Cursor auf den vorhergehenden Satz der Ergebnismenge. |
| CURRENT | Positioniert den Cursor auf den aktuellen Satz der Ergebnismenge. |
| FIRST | Positioniert den Cursor auf den ersten Satz der Ergebnismenge. |
| LAST | Positioniert den Cursor auf den letzten Satz der Ergebnismenge. |
| ABSOLUTE
ABS |
Positioniert den Cursor auf den Satz, der durch cursor-position angegeben wird. Hierbei wird vom Anfang der Ergebnismenge aus positioniert. |
| RELATIVE
REL |
Positioniert den Cursor auf den Satz, der durch cursor-position angegeben wird. Hierbei wird relativ zur aktuellen Cursorposition aus positioniert. |
Der INTO Teil legt fest in
welchen Variablen das Ergebnis (eine einzelne Zeile) gespeichert wird.
Als ein weiteres Beispiel wollen wir jetzt Primzahl Zwillinge suchen. Dabei
untersuchen wir zwei verschiedene Methoden:
case 7:
zeit = time(NULL);
EXEC SQL DATABASE 'beispiel';
EXEC SQL DECLARE c2 CURSOR FOR
SELECT * INTO :kandidat,:prim
FROM PRIMZAHLEN
WHERE PRIM=1 AND ZAHL+2 IN (
SELECT * FROM PRIMZAHLEN WHERE PRIM=1);
EXEC SQL OPEN c2;
while(1){
EXEC SQL FETCH c2 ;
if (SQLCODE == 100) /* not found */ goto stop5;
printf("%10d %10d %1d\n",kandidat,kandidat+2,prim);
}
stop5:
EXEC SQL CLOSE c2;
EXEC SQL CLOSE DATABASE;
zeit = time(NULL)-zeit;printf("Dauer = %d Sekunden\n",zeit);goto again;
case 8:
zeit = time(NULL);
EXEC SQL DATABASE 'beispiel';
EXEC SQL DECLARE c3 CURSOR FOR
SELECT A.ZAHL,A.PRIM
INTO :kandidat,:prim
FROM PRIMZAHLEN A,
PRIMZAHLEN B
WHERE A.PRIM=1 AND B.ZAHL=A.ZAHL-2
AND B.PRIM=1;
EXEC SQL OPEN c3;
while(1){
EXEC SQL FETCH c3 ;
if (SQLCODE == 100) /* not found */ goto stop6;
printf("%10d %10d %1d\n",kandidat,kandidat+2,prim);
}
stop6:
EXEC SQL CLOSE c3;
EXEC SQL CLOSE DATABASE;
zeit = time(NULL)-zeit;printf("Dauer = %d Sekunden\n",zeit);goto again;
In diesen Beispielen sieht
man die Fehlerbehandlung mittels der Variable SQLCODE. Diese dient zur
Kommunikation (auch innerhalb des normalen SQL) von Ereignissen. SQLCODE
== 100 entspricht der NOT FOUND Situation, d.h. FETCH wurde ausserhalb
der Ergenismenge positioniert. Ferner wird in diesen beiden Beispielen
die Zeit gemessen (sog. wall clock time, nicht Rechenzeit). Betrachtet
man die CURSOR Definition sieht man, dass die INTO Klausel auch direkt
bei der Definition angegeben werden kann. Es ist die Reihenfolge zu beachten,
d.h. direkt vor der FROM Klausel. Der Hauptunterschied ist, dass im ersten
Fall mit subquery gearbeitet wird und im zweiten Fall mit autojoin.
Bei 40000 Einträgen benötigte Variante 1 2 Sekunden und Variante
2 128 Sekunden!
EXEC SQL DATABASE 'beispiel';
EXEC SQL WHENEVER SQLERROR CONTINUE;
nochmal:
kandidat = rand()%100000;
printf("Startwert %d\n",kandidat);
EXEC SQL SAVEPOINT CC;
for (i=0;i<100;i++)
{
prim = isprim(kandidat+i);
EXEC SQL INSERT INTO PRIMZAHLEN VALUES (
:kandidat+:i, :prim, 0 );
if (SQLCODE < 0) {
printf("Fehler, neuer Versuch\n");
EXEC SQL ROLLBACK TO SAVEPOINT CC;
goto nochmal;
}
}
EXEC SQL CLOSE DATABASE;
EXEC SQL WHENEVER SQLERROR STOP;
goto again;
Das Programm startet einen neuen Versuch wenn es bei dem Versuch Zahlen einzutragen einen Fehler hat (wahrscheinlich sollte eine Zahl zum zweiten mal eingetragen werden). Ein COMMIT am Ende ist überflüssig da dies automatisch beim Schliessen der DATABASE passiert. Ist ein Fehler aufgetretten werden auch alle bisherigen Einfügeoperationen Rückgängig gemacht. Um die Schleife herum muss WHENEVER SQLERROR auf CONTINUE geschaltet werden, da sonst keine einzelne Fehlerverarbeitung möglich ist. Die Embedded SQL Anweisung WHENEVER hat lediglich zur Folge, dass um jede einzelne Anweisung ein Abfrage nach dem SQLCODE gemacht wird. SQLCODE und die weitere Variable SQLMAINWARN sind eine vereinfachte Möglichkeit auf eine globale C-Struktur sqlca zuzugreifen, die zur Kommunikation von Fehlersituationen dient.
Folgende Möglichkeiten
hat man bei der WHENEVER Anweisung
WHENEVER <bedingung>
<aktion>
Folgende Bedingungen (condition)
können in der WHENEVER Anweisung angegeben werden:
| Bedingung | Eintreffen der Bedingung |
| SQLERROR | Fehler, d.h., SQLCODE und sqlca.sqlcode ist kleiner 0. |
| SQLWARNING | Warnung, d.h. SQLMAINWARN und sqlca.sqlwarn0 haben den Wert 'W'. In diesem Fall ist mindestens ein weiteres Element (sqlwarn1 - sqlwarn10) der sqlca Struktur auf 'W' gesetzt. |
| NOT FOUND | No data, d.h., SQLCODE und sqlca.sqlcode haben den Wert +100. |
Trifft einer dieser Bedingungen
zu, wird die durch exception-action definierte Aktion ausgeführt.
Hierbei können folgende Aktionen angegeben werden:
| Aktion | Resultat |
| CONTINUE | Die Programmausführung wird fortgesetzt. Wird keine WHENEVER Anweisung verwendet, ist dies das Defaultverhalten. |
| STOP | Das Programm wird mit dem Exitcode 1 verlassen. Zusätzlich werden alle gesetzten Elemente der sqlca Struktur auf stderr ausgegeben. |
| GO TO or GOTO host-label | Die Programmausführung wird bei host-label fortgesetzt. Hierbei muß host-label in demselben Programmblock (oder höher) wie die SQL-Anweisung, für die die Aktion ausgeführt wird, definiert sein. |
| CALL function-name | Die C-Funktion function-name wird aufgerufen. Hier können alternativ Funktionsparameter angegeben werden. Die C-Funktion muß von der Anwendung bereitgestellt werden. |
Um die Suche nach den
Primzahlzwillingen schneller zu machen kann man folgende Idee haben. Beim
Einfügen von Zahlen soll im Falle von Primzahlen p sofort überprüft
werden ob p-2 oder p+2 auch als Primzahl eingetragen ist. Dazu soll die
Tabelle um eine Spalte primzwilling erweitert werden, und dort soll
der Wert 1 stehen falls die Zahl der kleinere Partner eines Primzahlzwillingspaars
ist. Dazu muss die Routine zum Einfügen geändert werden.
case 4:
again4:
printf("bitte startwert (>0) eingeben:");
scanf("%d",&eingabe);
if (eingabe<1) goto again4;
EXEC SQL DATABASE 'beispiel';
for (kandidat=eingabe+1000;kandidat>=eingabe;
kandidat--)
{
prim = isprim(kandidat);
EXEC SQL INSERT INTO PRIMZAHLEN VALUES (
:kandidat, :prim, 0 );
if (prim == 1)
{
EXEC SQL UPDATE PRIMZAHLEN SET
PRIMZWILLING=1 WHERE
ZAHL=:kandidat-2
and PRIM=1;
EXEC SQL UPDATE PRIMZAHLEN SET
PRIMZWILLING=1 WHERE
ZAHL=:kandidat
and :kandidat+2 in
( select zahl from primzahlen where prim=1 );
}
}
EXEC SQL COMMIT;
EXEC SQL CLOSE DATABASE;
EXEC SQL WHENEVER SQLERROR STOP;
goto again;
Im Fall einer Primzahl werden
die Einträge geändert, dabei wird eine subquery gestartet um
zu schauen ob die um zwei grössere Zahl auch eine Primzahl ist. Das
geht auch anders (schneller), wenn man mit den sog. Einzelsatzzugriff arbeitet.
Dazu muss ein Index oder ein PRIMARY KEY vorhanden sein, d.h. beim Anlegen
der Tabelle wird die Spalte Zahl als PRIMARY KEY vereinbart. Hier wird
jetzt nur die Einfüge Schleife betrachtet:
prim = isprim(kandidat);
EXEC SQL INSERT INTO PRIMZAHLEN VALUES (
:kandidat, :prim, 0 );
if (prim == 1)
{
EXEC SQL UPDATE PRIMZAHLEN SET
PRIMZWILLING=1 WHERE
ZAHL=:kandidat-2 and PRIM=1;
kandidat +=2;
EXEC SQL SELECT DIRECT PRIM into :prim
FROM PRIMZAHLEN
PRIMARY KEY ZAHL=:kandidat
WHERE PRIM=1;
kandidat -= 2;
if (SQLCODE!=100) {
EXEC SQL UPDATE PRIMZAHLEN SET
PRIMZWILLING=1 WHERE
ZAHL=:kandidat and PRIM=1;
}
}
Beim Einzelsatzzugriff wird nur maximal ein Datensatz geliefert. Er kann dann in Embedded C in host Variablen gespeichert werden. Die allgemeine Syntax ist
SELECT { FIRST | DIRECT
| PREV | PREVIOUS | NEXT | LAST } .... [INTO .. ] FROM ...
{ PRIMARY KEY
| INDEX index-name }
[ column-name =
index-value [ , column-name = index-value ] ... ]
[ WHERE ..... ]
Die Suchrichtung muss angegeben werden (ähnlich dem FETCH). Der Zugriff auf die Tabelle erfolgt über den angegebenen Index oder den Primärschlüssel. Wird nicht der Primärschlüssel, sondern ein anderer Index verwendet, muss dieser Index eindeutig sein. In Abhängigkeit von der angegebenen SELECT Richtung wird der selektierte Datensatz wie folgt bestimmt:
FIRST
Der nach der logischen Reihenfolge im Index erste Datensatz wird
geliefert.
LAST
Der nach der logischen Reihenfolge im Index letzte Datensatz
wird geliefert.
Bei FIRST und LAST darf keine Indexbedingung, sondern nur ein Indexname angegeben werden, da der erste bzw. letzte Satz eines Index verwendet wird. Die anderen Positionierungen:
DIRECT
Der Datensatz, auf den über die angegebenen Indexwerte
positioniert wird, wird geliefert.
PREV
Der nach indexsequentieller Tabellenreihenfolge vorhergehende
Satz zu dem mit den Indexwerten identifizierten Satz wird
geliefert.
NEXT
Der nach indexsequentieller Tabellenreihenfolge nächste Satz zu
dem mit den Indexwerten identifizierten Satz wird geliefert.
Dabei muss ein Indexwert angegeben werden (columnname=...), der eindeutig ist und für die Positionierung verwendet wird. Wird ein Index über mehrere Spalten, muss für alle Spalten ein Wert angegeben werden.In jedem Fall kann eine WHERE Bedingung mit einer search-condition angegeben werden, über die die Suche eingeschränkt werden kann. Bei Verwendung von PREV und NEXT wird solange der vorhergehende bzw. folgende Satz gelesen, bis er der WHERE Bedingung entspricht oder das Ende der Tabelle erreicht wurde. Bei FIRST, DIRECT und LAST wird der SQLCODE +100 geliefert, wenn der Datensatz nicht der WHERE Bedingung entspricht.
Ein anderer Ansatz wäre dieses Problem mit einem Trigger zu lösen, d.h. bei jedem Einfügen soll automatisch geschaut werden, und die Spalte primzwilling aktualisiert werden. Ein entsprechender Codeabschnitt:
EXEC SQL CREATE TABLE PRIMZAHLEN
(
zahl INTEGER UNIQUE,
prim INTEGER CHECK (prim = 1 or prim = 0),
primzwilling INTEGER DEFAULT 0
CHECK (primzwilling=0 or primzwilling=1)
);
EXEC SQL CREATE TRIGGER ZWILLINGCHECK AFTER INSERT ON
PRIMZAHLEN FOR EACH ROW WHEN (PRIM=1)
CALL CHECKZWILLING(ZAHL);
EXEC SQL CLOSE DATABASE;
goto again;
Dabei wird zum erstenmal ein CALL Aufruf verwendet, dazu muss noch die SQL Funktion Checkzwilling geschrieben werden:
CREATE PROCEDURE CHECKZWILLING (wert
INTEGER)
BEGIN
UPDATE PRIMZAHLEN SET PRIMZWILLING=1 where ZAHL=wert-2 and PRIM=1;
UPDATE PRIMZAHLEN SET PRIMZWILLING=1 where ZAHL=wert and wert+2 in
( select zahl from primzahlen2 where prim=1 );
END
END PROCEDURE
Es ist leider so, dass diese
Routine fälschlicherweise bei YARD als rekursiv klassifiziert wird,
obwohl der Auslöser ein CREATE Event ist und innerhalb des Triggers
ein UPDATE gemacht wird. Rekursive Trigger sind nicht erlaubt. Da SQL Endlosschleifen
ausschliessen möchte.
Oft ist der Ablauf von
Datenbankprogrammen festgelegt. Z.b. aus dem Web wird eine ISBN Nummer
eingelesen, die Datenbank durchsucht und die Ausgabe wird abgeliefert.
Es sind aber auch Situationen denkbar, wo der Ablauf nicht klar ist. Das
einfachste Beispiel ist dem Benutzer zu erlauben selber SQL Anfragen einzugeben.Solche
Anforderungen können durch den Einsatz dynamischer SQL-Anweisungen
erfüllt werden, so daß dynamische SQL dem Entwickler mehr Flexibilität
in der Erstellung seiner Applikationen und damit für die Applikation
selbst zur Verfügung stellt. Dynamisch definierte SQL-Anweisungen
sind SQL-Anweisungen, die zur Kompilierzeit eines ESQLC- Programmes nicht
oder nur teilweise bekannt sind und somit zur Laufzeit erstellt bzw. vervollständigt
und ausgeführt werden. Dabei dürfen bis auf ein paar Ausnahmen
alle SQL Anweisungen zur Erstellung dynamischer SQL Anweisungen verwendet
werden.
2.5.1 Syntax einer dynamischen
SQL Anweisung
Die SQL-Anweisung muß
in einfachen oder doppelten Hochkommata eingeschlossen sein oder in einer
Hostvariablen vom Typ char oder varchar gespeichert sein. Das Präfix
EXEC SQL darf nicht angegeben werden.
Das folgende Beispiel zeigt
eine diesbezüglich ungültige Anweisung:
strcpy(sqlstmt,
"exec sql delete from
film where film_nr = 1");
Das Ende der SQL-Anweisung
darf nicht mit einem Semikolon abgeschlossen sein. Das folgende Beispiel
zeigt eine diesbezüglich ungültige Anweisung:
strcpy(sqlstmt, "delete
from film where film_nr = 1;");
Die SQL-Anweisung darf keine
Kommentare enthalten. Die SQL-Anweisung darf keine Hostvariablen enthalten.
Das folgende Beispiel zeigt eine diesbezüglich ungültige Anweisung:
strcpy(sqlstmt,
"delete from film where film_nr = :v_film_nr");
Sollen in der Anweisung Hostvariablen
als Parameter angegeben werden, so muß die Anweisung Fragezeichen
als Platzhalter für die bei der Ausführung (EXECUTE) der Anweisung
anzugebenden Hostvariablen enthalten. Das folgende Beispiel zeigt eine
diesbezüglich gültige Anweisung:
strcpy(sqlstmt, "delete
from film where film_nr = ?");
Handelt es sich bei der dynamisch
definierten Anweisung um ein SELECT, darf keine INTO Klausel angegeben
werden. Die INTO Klausel wird beim FETCH oder EXECUTE angegeben.
Man hat verschiedene
Methoden.
2.5.2.1 EXECUTE IMMEDIATE
(nicht bei SELECT)
Eine dynamisch definierte SQL-Anweisung kann mit EXECUTE IMMEDIATE in einem Schritt bearbeitet und ausgeführt werden. Nach Ausführung der Anweisung wird der für die Anweisung allokierte Speicher automatisch freigegeben. Mit EXECUTE IMMEDIATE können alle Anweisungen ausgeführt werden, die kein SELECT sind und keine Input oder Output Werte erwarten.
Das folgende Beispiel zeigt
gültige EXECUTE IMMEDIATE Anweisungen:
EXEC SQL BEGIN DECLARE SECTION;#define STMT_SIZE 1024
char sqlstmt[STMT_SIZE];
EXEC SQL END DECLARE SECTION;
/*
Ausführung von Anweisungen, die in einer
Stringkonstante stehen
*/
EXEC SQL EXECUTE IMMEDIATE 'lock table film in exclusive mode';EXEC SQL EXECUTE IMMEDIATE 'delete from film where film_nr = 1';
/*
Eingabe und Speicherung einer Anweisung
*/
printf("Geben Sie Ihre SQL-Anweisung ein: ");
gets(sqlstmt);/*
Ausführung der Anweisung, die in der Hostvariablen
sqlstmt gespeichert ist.
*/EXEC SQL EXECUTE IMMEDIATE :sqlstmt;
Durch die EXECUTE IMMEDIATE
Anweisung können dynamisch definierte SQL-Anweisungen ohne Parameter
auf einfache Weise ausgeführt werden.
Eine Alternative zur EXECUTE
IMMEDIATE Anweisung ist die Verwendung von PREPARE und EXECUTE zur Bearbeitung
und Ausführung von dynamisch definierten SQL-Anweisungen. Obwohl die
Verwendung von PREPARE und EXECUTE im Prinzip zum gleichen Ergebnis wie
die EXECUTE IMMEDIATE
Anweisung führt, sind
hiermit einige Vorteile gegenüber EXECUTE IMMEDIATE verbunden.
Die allgemeine Form der PREPARE
Anweisung lautet:
PREPARE statement-id FROM
sql-anweisung
Durch die PREPARE Anweisung wird die in der FROM Klausel angegebene SQL-Anweisung sql-anweisung geparst, auf syntaktische und semantische Richtigkeit überprüft und optimiert. Eine so bearbeitete Anweisung wird unter einer vom Benutzer anzugebenden statement-id durch den SQL-Server im Speicher abgelegt und zur Ausführung bereitgestellt.
Statement-Id
Eine Statement-Id ist standardmäßig
global innerhalb eines ESQLC-Programmes,
das heißt, sie kann
in einem Modul definiert (PREPAREd) und von anderen Modulen
aus ausgeführt (EXECUTE)
werden. Zudem kann eine Statement-Id als Hostvariable
definiert werden.
SQL-Anweisungstyp
Um den Typ einer dynamisch
formulierten SQL-Anweisung zu bestimmen, enthält das Element sqlca.sqlerrd[3]
der sqlca Struktur den Opcode der mit PREPARE bearbeiteten Anweisung.
Man hat die Möglichkeit,
den Typ einer dynamisch definierten SQL-Anweisung zu bestimmen, um zum
Beispiel, je nach Anweisung, bestimmte Aktionen zur Laufzeit einzuleiten.
/*
Die in der Hostvariable stmt gespeicherte Anweisung
ist nicht bekannt
*/
EXEC SQL PREPARE dynid FROM :stmt;/*
Bestimmung des Anweisungstyp falls PREPARE erfolgreich
*/
if (SQLCODE == 0)
{
switch(sqlca.sqlerrd[3])
{
case OP_DROPDB:
case OP_DROPTAB:
printf("Ungültige Anweisung\n");
break;case ...
}
}
Dynamische Parameter
Die SQL-Anweisung sql-anweisung
kann dynamische Parameter enthalten. Dynamische Parameter sind Werte, die
bei der Ausführung der SQL-Anweisung durch Hostvariablen oder Konstanten
in der USING Klausel der EXECUTE Anweisung angegeben werden müssen.
Dynamische Parameter müssen
mit einem Fragezeichen angegeben werden.
sprintf(stmt, "insert into dyn_table values (?, ?, ?)");EXEC SQL PREPARE stmtid FROM :stmt;
EXEC SQL EXECUTE stmtid USING :var1, 'STRING', 123.89;
Ressourcenkontrolle (FREE)
Bei einer PREPARE Anweisung
wird sowohl vom Laufzeitsystem als auch vom SQL-Server Speicher allokiert,
um die dynamisch definierte Anweisung und deren Spezifika zu speichern.
Dieser Speicher bleibt solange allokiert, bis das jeweilige Programm beendet
wird. Soll der Speicher vorher freigegeben werden, zum Beispiel, weil die
Anweisung nicht mehr benötigt wird, kann dies durch die FREE Anweisung
erreicht werden. Die FREE Anweisung wird hierbei auf die jeweilige Statement-Id
ausgeführt:
EXEC SQL PREPARE p_id1 FROM ...
EXEC SQL FREE STATEMENT p_id1
Ausführung über EXECUTE
Wurde die PREPARE Anweisung
ohne Fehler (SQLCODE ist 0) bearbeitet, kann die Anweisung durch EXECUTE
beliebig oft ausgeführt werden. Enthält die SQL-Anweisung dynamische
Parameter, so müssen diese durch Angabe von Werten in der USING Klausel
einer EXECUTE Anweisung ersetzt werden. Hierbei muß die Anzahl der
USING Werte gleich der Anzahl der dynamischen Parameter sein. Die allgemeine
Form der EXECUTE Anweisung lautet:
EXECUTE statement-id [
using_clause ]
Die EXECUTE Anweisung führt die durch statement-id referenzierte Anweisung aus. Die Vorteile von PREPARE und EXECUTE gegenüber EXECUTE IMMEDIATE lassen sich folgendermaßen zusammenfassen:
Das folgende Beispiel zeigt
die Verwendung von PREPARE mit dynamischen
Parametern und EXECUTE mit
USING auf eine DELETE Anweisung.
PREPARE und EXECUTE auf eine SELECT AnweisungEXEC SQL BEGIN DECLARE SECTION;
#define STMT_SIZE 512
long film_nr;
char del_stmt[STMT_SIZE];EXEC SQL END DECLARE SECTION;
/*
Anweisung in character array kopieren
*/
sprintf(del_stmt, "delete from film where film_nr = ?");/*
Anweisung mit PREPARE vorbereiten
*/
EXEC SQL PREPARE p_delete_film FROM :del_stmt;
/*
Benutzereingabe
*/
printf("Geben Sie die zu löschende Filmnummer ein: ");
scanf("%ld", &film_nr);/*
Anweisung ausführen, Hostvariable für dynamische
Parameter zur Laufzeit mit USING übergeben
*/
EXEC SQL EXECUTE p_delete_film USING :film_nr;
Soll mit PREPARE und EXECUTE eine SELECT Anweisung bearbeitet werden, so ist die EXECUTE Anweisung um eine INTO Klausel zu erweitern, um das Ergebnis der SELECT Anweisung zu speichern. Neben der INTO Klausel kann weiterhin eine USING Klausel für Input Werte angegeben werden.
Das folgende Beispiel illustriert
die Vorgehensweise von PREPARE und EXECUTE auf eine SELECT Anweisung.
EXEC SQL BEGIN DECLARE SECTION;
#define STMT_SIZE 512
#define TITLE_SIZE 81long film_nr;
char sel_stmt[STMT_SIZE];
varchar v_title[TITLE_SIZE];EXEC SQL END DECLARE SECTION;
/*
SELECT Anweisung in character array kopieren
*/
sprintf(sel_stmt,
"select titel from film where film_nr = ?");/*
Anweisung mit PREPARE vorbereiten
*/
EXEC SQL PREPARE p_select_film FROM :sel_stmt;/*
Anweisung beliebig oft ausführen
*/
for (;;){
/*
Benutzereingabe
*/
printf("Geben Sie die zu suchende Filmnummer ein oder -1 für Ende: ");
scanf("%ld", &film_nr);
if (film_nr == -1)
break;
/*
Anweisung ausführen, Hostvariable für
dynamischen Parameter zur Laufzeit mit USING
übergeben.
INTO Klausel für Ergebnis angegeben.
*/EXEC SQL EXECUTE p_select_film INTO :v_title USING :film_nr;
/*
Ergebnis anzeigen
*/
printf("Der Titel für Filmnummer %ld ist: %s\n", film_nr, v_otitle);
}
Um SELECT Anweisungen, die
mehr als eine Zeile als Ergebnismenge liefern,
dynamisch zu bearbeiten,
gilt folgende allgemeine Vorgehensweise:
SELECT Anweisung mit PREPARE bearbeiten.
Cursor mit DECLARE CURSOR auf die Statement-Id deklarieren.
Cursor mit OPEN öffnen. Enthält die SELECT Anweisung dynamische
Parameter müssen diese über USING ersetzt werden.
Ergebnismenge mit FETCH bearbeiten.
Cursor nach der Bearbeitung mit CLOSE schließen. Durch eine COMMIT
oder ROLLBACK Anweisung werden alle bis dahin offenen Cursor (außer
WITH HOLD Cursor) implizit geschlossen.
Gegebenenfalls intern allokierten Speicher mit FREE STATEMENT und
FREE CURSOR freigeben.
Das folgende Beispiel zeigt
die Bearbeitung einer dynamischen SELECT Anweisung:
EXEC SQL BEGIN DECLARE SECTION;
long v_film_nr;
varchar v_title[81];
char selstmt[512];
long nr1, nr2;EXEC SQL END DECLARE SECTION
/*
SELECT Anweisung mit dynamischen Parametern
*/
sprintf(selstmt,
"select film_nr, titel from film where film_nr \
between ? and ?");
/*
PREPARE auf die dynamische Anweisung
*/
EXEC SQL PREPARE p_sel FROM :selstmt;/*
DECLARE CURSOR auf die Statement-Id
*/
EXEC SQL DECLARE c_sel CURSOR FOR p_sel;/*
Benutzereingaben speichern
*/
printf("Geben Sie eine untere und eine obere Grenze für \
die Filmnummer ein\n");
printf("Untere Grenze: ");
scanf("%ld", &nr1);
printf("\nObere Grenze: ");
scanf("%ld", &nr2);/*
Cursor öffnen und dynamische Parameter durch Benutzereingaben ersetzen
*/EXEC SQL OPEN c_sel USING :nr1, :nr2;
/*
Ergebnismenge bearbeiten
*/
while (1)
{
EXEC SQL FETCH c_sel INTO :v_film_nr, v_title;
if (SQLCODE == SQLNOTFOUND)
break;
printf("Filmnr: %ld Filmtitel: %s\n",
v_film_nr, v_title);
}/*
Cursor schließen und Speicher freigeben
*/
EXEC SQL CLOSE c_sel;EXEC SQL FREE CURSOR c_sel;
EXEC SQL FREE STATEMENT p_sel;