SQL-miljö

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

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

3. Pascal-program med ’inbakad’ SQL Pre- processor Ev. fel- meddelanden För- kompilering Preparerat Pascal- program Pascal- kompilator Kompile- ring Ev. fel- meddelanden Exekverbart program

4. Pascal-program EXEC SQL ; EXEC SQL ; SQL-satser EXEC SQL ;

5. 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

6. 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

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

8. 2 Felhantering SQLCA (SQLCommunication 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;

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

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

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

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

13. Ö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’;

14. 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;

15. Ö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;

16. 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.

17. 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;

18. 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.

19. 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.

20. 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; . . .

21. 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’); . . .

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

23. 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.

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

25. 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 . . . ;

26. 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.

27. 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 GetCursorName CloseCursor GetConnectAttrr Connect GetTypeInfo Disconnect Prepare Execute PutData Fetch SetCursorName

28. 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

29. 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.”

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

31. Öppna databasenjmf EXEC SQL CONNECT ’company’; Connection con = null; String dbas = ”jdbc:db2:company”; DefaultContext ctx = DefaultContext.getDefaultContext(); If (ctx == null) { try { con = DriverManager.getConnection(dbas); con.setAutoCommit(false); ctx = new DefaultContext(con); } catch (SQLException e) { System.out.println (e); System.exit (1); } DefaultContext.setDefaultContext (ctx); }

32. 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);

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

34. Samma som föregående bild men med FETCH Ex. . . . #sql iterator ptime (string pnumber, string pname, int hours); . . . ptime pt; string str1 = null; string str2 = null; int i3 = 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 :str1, str2, i3} While (pt.endFetch()){ System.out.println(str1 + str2 + i3); #sql {FETCH :pt INTO :str1, :str2, :i3}; } . . .