SQLmilj Standard SQL Interaktiv Embedded statisk dynamisk moduler

SQL-miljö Standard SQL * Interaktiv * Embedded statisk dynamisk moduler CLI (Call Level Interface) JDBC, SQLJ Java Interface

Standard embedded statisk SQL Ett programmeringsspråk (värdspråk) använder sig av SQL för att kommunicera med en relationsadatabas. Värdspråket kan vara: • ADA • COBOL • FORTRAN • PASCAL • PL/1

Pascal-program med ’inbakad’ SQL Preprocessor Förkompilering Ev. felmeddelanden Preparerat Pascalprogram Pascalkompilator Kompilering Exekverbart program Ev. felmeddelanden

Pascal-program EXEC SQL ; SQL-satser

När SQL kommunicerar med ett annat språk, måste följande problem beaktas: 1 Dataöverföring mellan programvariabler i SQL och värdspråket 2 Felhantering 3 Bearbetning av en tuppel i taget

Exempeldatabas Department(dnumber, dname, dmgr) Employee(enumber, ename, dnumber) Project(pnumber, pname, dnumber) Works(enumber, pnumber, hours) Department. dmgr << Employeer. enumber Employee. dnumber << Department. dnumber Project. dnumber << Department. dnumber Works. enumber << Employee. enumber Works. pnumber << Project. pnumber

1 Dataöverföring Berörda variabler deklareras i en särskild sektion i värdspråket Ex. EXEC SQL BEGIN DECLARE SECTION; TYPE str 4=PACKED ARRAY[1. . 4] OF CHAR; str 20 = PACKED ARRAY[1. . 20] OF CHAR; emptyp = RECORD enumber: str 4; enamn: str 20; dnumber: str 4 END; VAR emprecord: emptyp; EXEC SQL END DECLARE SECTION;

2 Felhantering SQLCA (SQL Communication Area) Innehåller ett antal variabler för meddelanden och statusinformation till värdspråket. Varje databasanrop påverkar SQLCA. Skall ej deklareras! Införlivas i programmet med: EXEC SQL INCLUDE SQLCA;

SQLCA innehåller 128 tecken Pos 1 - 12 13 - 16 17 - 88 89 - 112 113 - 120 121 - 128 okänt SQLCODE SQLERRM okänt SQLWARN okänt SQLCODE = 0 <0 >0 +100 OK Fel OK med reservation Data saknas

Felhantering med hjälp av SQLCA 1 1 WHENEVER-sats 2 Test av SQLCODE EXEC SQL WHENEVER <villkor> <åtgärd>; Villkor: - SQLERROR - SQLWARNING - SQLMESSAGE - NOT FOUND Ex. Åtgärd: - CONTINUE - STOP - GO TO <satsnummer> EXEC SQL WHENEVER SQLWARNING CONTINUE;

2 Test av SQLCODE Ex. - IF SQLCODE < 0 THEN GO TO. . . - WHILE SQLCODE = 0 DO. . . - IF SQLCODE = NOT FOUND. . .

3 Bearbetning Databasen öppnas respektive stängs med: EXEC SQL CONNECT <databasnamn>; . . . EXEC SQL DISCONNECT;

Överföring av en enstaka rad från en tabell (Singleton Select) Ex. EXEC SQL SELECT enumber, enamn, dnumber INTO : enr, : ename, : dnumber FROM Employee WHERE enumber = ’ 4410’; alternativt. EXEC SQL SELECT * INTO : emprecord FROM Employee WHERE enumber = ’ 4410’;

På samma sätt kan man göra INSERT, UPDATE eller DELETE av enstaka rader. Det går även att uppdatera flera rader med UPDATE eller DELETE Ex. EXEC SQL UPDATE Works SET hours = NULL WHERE pnumber = ’ 1008’; Ex. EXEC SQL DELETE FROM Project;

Överföring av mer än en rad löses med cursor, en markör som pekar ut och behandlar en rad i taget. Ex. Hämta alla projekt och skriv ut nummer, namn och totalt antal timmar för varje projekt. I deklarationsdelen av Pascalprogrammet skrivs: EXEC SQL INCLUDE SQLCA; Pascalvariabler som har kontakt med SQLvariabler deklareras i DECLARE SECTION. EXEC SQL BEGIN DECLARE SECTION; VAR pno: PACKED ARRAY[1. . 4] OF CHAR; project: PACKED ARRAY[1. . 20] OF CHAR; hours: SMALLINT; EXEC SQL END DECLARE SECTION;

Ex forts. Begin EXEC SQL DECLARE ptime CURSOR FOR SELECT pnumber, pname, SUM(hours) FROM Project, Works WHERE Project. pnumber = Works. pnumber GROUP BY Project. pnumber, pname; . . . EXEC SQL OPEN ptime; . . . EXEC SQL FETCH ptime INTO : pno, : project, : hours; While SQLCODE = 0 Do Begin Writeln(pno, project, hours: 5); EXEC SQL FETCH ptime INTO : pno, : project, : hours; End; . . . EXEC SQL CLOSE ptime; . . . End.

Vid uppdatering med cursor lägger man till FOR UPDATE i cursor-deklarationen Ex. EXEC SQL DECLARE w CURSOR FOR SELECT enumber, pnumber, hours FROM Works WHERE pnumber = : pnum FOR UDATE OF hours;

Exempel med två cursor Hämta nummer och namn på varje avdelning samt nummer och namn på de anställda inom varje avdelning och skriv ut dessa uppgifter. Begin … EXEC SQL DECLARE d CURSOR FOR SELECT dnumber, dname FROM Department; EXEC SQL DECLARE e CURSOR FOR SELECT enumber, enam FROM Employee WHERE dnumber = : depno; EXEC SQL WHENEVER SQLERROR GO TO 99; EXEC SQL CONNECT ”company”; EXEC SQL OPEN d; . . . EXEC SQL FETCH d INTO : depno, : depname; While SQLCODE = 0 Do forts.

Ex forts. Begin Writeln(”Department: ”, depno, ” ”, depname); Writeln(”Empno”, ” Empname”; EXEC SQL OPEN e; EXEC SQL FETCH e INTO : eno, : ename; While SQLCODE = 0 Do Begin Writeln(eno, ” ”, ename); EXEC SQL FETCH e INTO : eno, : ename; End; EXEC SQL CLOSE e; Writeln; EXEC SQL FETCH d INTO : depno, depname; End; . . . EXEC SQL CLOSE d; EXEC SQL DISCONNECT; . . . End.

Embedded dynamisk SQL Tre steg: • Generera SQL-sats • Kompilera (preparera) • Exekvera } ev. i ett steg Ex. . VAR inmatning : CHAR(1000); . . . inmatning : = ’DELETE FROM Employee WHERE enumber = ’ 0025’; EXEC SQL PREPARE SQLPREPPED FROM : inmatning; EXEC SQL EXECUTE SQLPREPPED; . . .

Alternativt ex. . VAR inmatning : Char(1000); . . . Writeln(’Ange din SQL-sats: ’); Readln(inmatning); EXEC SQL EXECUTE IMMEDIATE : inmatning; If SQLCODE = 0 Then Writeln (’Rätt’) Else Writeln (’Fel’); . . .

Placeholders Ex. . Inmatning: = ’SELECT enumber FROM Works WHERE hours < ? ’; EXEC SQL PREPARE SQLPREPPED FROM : inmatning; . . . hmin: = 5; EXEC SQL EXECUTE SQLPREPPED USING : hmin; . . .

SQLDA En datastruktur som används för att transportera data mellan applikationsprogrammet och databasen. SQLDA innehåller en räknare och ett antal variabler med information om bl a kolumner, datatyper och värden. SQLDA är inte officiell standard, men stöds av de flesta SQL-produkter.

Jämförelse mellan statisk och dynamisk SQL Embedded statisk SQL + Färdigkompilerad programkod med optimerad accessväg ger snabb exekvering - SQL-satserna måste vara definierade från början Embedded dynamisk SQL + Mer flexibel än statisk SQL Databasobjekten behöver inte existera före exekveringen - Tar vanligtvis längre tid att exekvera, eftersom frågorna optimeras vid körningen

Moduler En modul är ett separatkomplierat program som innehåller en eller flera procedurer. Varje procedur innehåller en enda SQL-sats. Ex. MODULE projectmodule; LANGUAGE Pascal; AUTHORIZATION Stina; PROCEDURE find_project (SQLCODE, : pno Character(4), : pname Character(20)) SELECT pname INTO : pname FROM Project WHERE pnumber = : pno; . . . PROCEDURE del_project. . . ; PROCEDURE new_project. . . ;

Ex. forts. I Pascal. . . VAR number, pno: Packed Array[1. . 4] Of Char; pname : Packed Array[1. . 20] Of Char; returncode: Integer; . . . Begin. . . Write(’Ange projektnummer: ’); Readln(number); pno : = number; Call find_project(returncode, pno, pname); If returncode = 0 Then Writeln(’Projektets namn är: ’, pname) Else. . . End.

Call Level Interface Ett applikationsprogram använder sig av CLI-rutiner för att genomföra SQL-operationer. Vilka rutiner som ska användas bestäms dynamiskt. CLI innefattar en mängd rutiner (routins). En rutin är antingen en funktion eller en procedur. Exempel på rutiner: Cancel Close. Cursor Connect Disconnect Execute Fetch Get. Cursor. Name Get. Connect. Attrr Get. Type. Info Prepare Put. Data Set. Cursor. Name

CLI Descriptor Areas Har samma funktion som SCLDA i dynamisk SQL. Skillnad: SQLDA innehåller aktuella datavärden CLIDA innehåller pekare till variabler som innehåller sådana värden

Många anser att CLI utgör ett ”renare” gränssnitt mot SQL. Följande är hämtat ur The SQL Standard av C J Date / H Darwen, 1997: ”CLI actually represents a better approach to the problem than dynamic SQL does. For this reason it is probably true to say that dynamic SQL would never have been included in the standard at all if CLI had been defined first. ”

Embedded SQL med Java DB 2 JDBC hanterar dynamisk SQL ingen förkompilering, API calls DB 2 SQLJ hanterar statisk SQL förkompilering med SQLJ Translator JDBC i botten för koppling till databasen och felhantering SQLJ Program SQLJ Routine Classes JDBC Interface DB 2 Database

Öppna databasen jmf EXEC SQL CONNECT ’company’; Connection con = null; String dbas = ”jdbc: db 2: company”; Default. Context ctx = Default. Context. get. Default. Context(); If (ctx == null) { try { con = Driver. Manager. get. Connection(dbas); con. set. Auto. Commit(false); ctx = new Default. Context(con); } catch (SQLException e) { System. out. println (e); System. exit (1); } Default. Context. set. Default. Context (ctx); }

Singleton select Ex. #sql {DELETE FROM project}; Ex. string ename = null; #sql {SELECT ename INTO : ename FROM Employee WHERE enumber = ” 4410”}; System. out. println(”Namn = ” + ename);

Cursor Ex. (= ex. i standard emb. SQL) #sql iterator ptime (string pnumber, string pname, int hours); . . . ptime pt; (jmf EXEC SQL OPEN ptime; ) string str 1 = null; string str 2 = null; int i 3 = 0; #sql pt = {SELECT pnumber, pname, SUM(hours) FROM project, Works WHERE Project. pnumber = Works. pnumber GROUP BY Project. pnumber, pname}; While (pt. next()) { str 1 = pt. pnumber; str 2 = pt. pname; i 3 = pt. hours; System. out. println(str 1 + str 2 + i 3); System. out. println(); } pt. close (); (jmf EXEC SQL CLOSE ptime; )

Samma som föregående bild men med FETCH Ex. . #sql iterator ptime (string pnumber, string pname, int hours); . . . ptime pt; string str 1 = null; string str 2 = null; int i 3 = 0; #sql pt = {SELECT pnumber, pname, SUM(hours) FROM project, Works WHERE Project. pnumber = Works. pnumber GROUP BY Project. pnumber, pname}; #sql {FETCH : pt INTO : str 1, str 2, i 3} While (pt. end. Fetch()){ System. out. println(str 1 + str 2 + i 3); #sql {FETCH : pt INTO : str 1, : str 2, : i 3}; }. . .