Standard embedded statisk SQL

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
•C
• 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
;
EXEC SQL
;
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 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;
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
CloseCursor
Connect
Disconnect
Execute
Fetch
GetCursorName
GetConnectAttrr
GetTypeInfo
Prepare
PutData
SetCursorName
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
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 Program
SQLJ
Routine Classes
JDBC
Interface
DB2
Database
Öppna databasen
jmf 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);
}
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 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;)
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};
}
...