1. No category

SQL v aplikacijski kodi
Iztok Savnik, FAMNIT
OPB, SQL&Aplikacije
Pregled
• SQL v aplikacijski kodi
• Vgnezden SQL
• Kursorji
• Dinamični SQL
• JDBC
• SQLJ
• Shranjene procedure
OPB, SQL&Aplikacije
SQL v aplikacijski kodi
SQL ukazi se lahko prožijo iz programa napisanega v
splošnem programskem jeziku (C++ | Java).
• SQL stavki lahko uporabljajo spremenljivke jezika gostitelja.
• Vsebujejo tudi stavke za priklop na podatkovno bazo.
Obstajata dva glavna pristopa k integraciji:
• Vgnezden SQL v gostiteljskem programskem jeziku (Embedded
SQL, SQLJ)
• Kreacija posebnega API za klicanje SQL ukazov (JDBC)
OPB, SQL&Aplikacije
SQL v aplikacijski kodi (nadal.)
Problem impedance:
• SQL relacije so (multi-) množice zapisov, ki nimajo
določenega maksimalnega št. zapisov.
• Običajno nimamo takšne podatkovne strukture v
klasičnih proceduralnih programskih jezikih (imamo
sicer knjižnice, npr. STL).
• SQL podpira mehanizem kurzorjev za delo z
relacijami.
OPB, SQL&Aplikacije
Vgnezden SQL
Pristop:
Vgnezdi SQL stavke v gostiteljski jezik.
Pred-procesor prevede SQL stavke v posebne API klice.
Potem uporabimo običajen prevajalnik za prevajanje kode .
Gradniki jezika:
Priklučitev na SUPB:
EXEC SQL CONNECT
Deklaracija vrednosti:
EXEC SQL BEGIN (END) DECLARE SECTION
Stavki:
EXEC SQL Statement;
OPB, SQL&Aplikacije
Vgnezden SQL: Spremenljivke
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long c_sid;
short c_rating;
float c_age;
EXEC SQL END DECLARE SECTION
Two special “error” variables:
SQLCODE (long, is negative if an error has occurred)
SQLSTATE (char[6], predefined codes for common errors)
OPB, SQL&Aplikacije
Kurzorji
Definiramo lahko kurzor na relaciji ali poizvedbi (ki
generira relacijo).
Lahko odpremo kurzor, preberemo n-terico in potem
premaknemo kurzor naprej za eno mesto. Potem spet
preberemo n-terico...
Vrstni red n-teric v relaciji, ki jo beremo je določen z SQL
vprašanjem.
Lahko tudi spremenimo ali izbrišemo zapis na
katerega kaže kurzor.
OPB, SQL&Aplikacije
Primer: Kurzor, ki poišče imena mornarjev, ki so
rezervirali rdečo ladjo, v abecednem vrstnem
redu
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
ORDER BY S.sname
OPB, SQL&Aplikacije
SQL vgnezden v C: Primer
char SQLSTATE[6];
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20]; short c_minrating; float c_age;
EXEC SQL END DECLARE SECTION
c_minrating = random();
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname, S.age
FROM Sailors S
WHERE S.rating > :c_minrating
ORDER BY S.sname;
do {
EXEC SQL FETCH sinfo INTO :c_sname, :c_age;
printf(“%s is %d years old\n”, c_sname, c_age);
} while (SQLSTATE != ‘02000’);
EXEC SQL CLOSE sinfo;
OPB, SQL&Aplikacije
Dinamični SQL
SQL vprašanja so znana v času prevajanja.
Dinamični SQL omogoča konstrukcijo SQL stavkov
on-the-fly.
Primer:
char c_sqlstring[]=
{“DELETE FROM Sailors WHERE raiting>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;
OPB, SQL&Aplikacije
Omejitve statičnega SQL
• Delo z kurzorji je statično zakodirano v
programu
– Spremenljivke lahko uporabimo za parametre SQL
vprašanja
exec sql select name, quota, sales
from salesreps
where quota > :cutoff_amount;
exec sql update salesreps
set quota = quota + :increase
where quota > :cutoff_amount;
OPB, SQL&Aplikacije
Omejitve statičnega SQL
• Ne moremo pa spreminjati imen tabel in
stolpcev
– Poskus izvajanja vrne napako
exec sql update :which_table
set :which_column = 0;
exec sql declare cursor cursor7 for
select *
from :which_table;
• Spremeni se lahko rezultat
– Stolpci v tabeli, ki je rezultat poizvedbe se
spremenijo
OPB, SQL&Aplikacije
Osnove dinamičnega SQL
• Ne zakodiraj SQL stavka v program gostitelja
– Omogoči gradnjo SQL stavka v programu
• Nekatere enostavne ideje se zakomplicirajo
• Izvajanje SQL iz stališča prevajalnika
– Statični SQL
• V času prevajanja se določi program (opt) SQL stavka
• V času izvajanja se samo interpretira
– Dinamični SQL
• SQL stavek ni znan do izvajanja
• Program SQL stavka se določi v času izvajanja
• SQL se lahko izgradi v programu gostitelja
OPB, SQL&Aplikacije
OPB, SQL&Aplikacije
Komentarji
• SQL stavki implementirani z dinamičnim SQL se
izvajajo počasneje
• Večina programerjev ne uporablja dinamičnega
SQL ker ni potrebe
• Aplikacije klient/strežnik velikokrat zahtevajo
uporabo dinamičnega SQL
OPB, SQL&Aplikacije
1) Takojšnje izvajanje SQL
• Najenostavnejši način je takojšnje izvajanje
SQL stavka iz niza znakov
– EXECUTE IMMEDIATE
• Koraki:
– Program konstruira SQL stavek. SQL stavek ne
sme vračati vrednosti !
– Program pošlje SQL stavek z EXECUTE
IMMEDIATE SQL strežniku
– SQL stavek se izvede na SQL strežniku
OPB, SQL&Aplikacije
Zgled:
OPB, SQL&Aplikacije
Zgled:
• Izvajanje programa
Enter table name: staff
Enter search condition: quota < 20000
Delete from staff successful.
• Stavek, ki se je izvajal
delete from staff
where quota < 20000
• Stavki SQL: DELETE, INSERT, UPDATE, COMMIT
OPB, SQL&Aplikacije
2) Dinamično izvajanje
• Dinamični SQL se izvaja hitreje od EXECUTE
IMMEDIATE
• Z dinamičnim SQL izvajamo stavke, ki jih
generira uporabnik in se izvajajo zelo
velikokrat
• Koraki izvajanja
1) Program pripravi SQL stavek. Vprašaj »?« se
vstavi na mesto kjer se pričakuje spremenljivke.
Vprašaj imenujemo vrzel.
2) Stavek PREPARE zahteva od strežnika, da
razčeni, preveri in optimizira SQL stavek. Rezultat
je plan izvajanja SQL stavka. SQL strežnik postavi
SQLCODE/SQLSTATE.
OPB, SQL&Aplikacije
2) Dinamično izvajanje
• Koraki izvajanja
3) Ko program želi izvajati pripravljeni SQL stavek
naredi to ukazom EXECUTE s katerim se prenesejo
tudi parametri. SQL strežnik postavi parametre SQL
stavka in izvaja prej pripravljen plan.
4) Program lahko izvaja stavek EXECUTE večkrat s
spremenjenimi parametri.
OPB, SQL&Aplikacije
Zgled
• Splošen program za izvajanje stavka UPDATE
• Začetni stavek (niz znakov)
update table-name
set second-column-name = ?
where first-column-name = ?
• Uporabnik določi parametre pred izvajanjem
SQL stavka
OPB, SQL&Aplikacije
Zgled:
OPB, SQL&Aplikacije
Zgled:
OPB, SQL&Aplikacije
Zgled: primer izvajanja
OPB, SQL&Aplikacije
API alternativa
Namesto da bi spreminjali prevajalnik raje dodamo
knjižnico za delo z podatkovno bazo (API).
• Poseben standardiziran vmesnik: procedure/objekti.
• Podamo SQL niz iz jezika SQL stežniku in predstavi rezultate
vprašanja v obliki prijazni do jezika.
• Sun JDBC: Java API
• Gonilnik neodvisen od SUPB ujame klice in jih prevede v
DBMS-specifično kodo; podatkovna baza je lahko nekje v mreži.
OPB, SQL&Aplikacije
JDBC Arhitektura
Štiri arhitekturne komponente:
• Aplikacija: inicira in zakluči povezavo (connection), pošlje SQL
stavke stežniku.
• Upravljalnik gonilnika: nalaganje JDBC gonilnika
• Gonilnik: poveže se na podatkovni vir, prenaša zahteve ter
vrača in prevaja rezultate poizvedb ter kode napak.
• Podatkovni vir: preprocesira SQL stavke
OPB, SQL&Aplikacije
JDBC Arhitektura (nadal.)
Štiri vrste gonilnika:
Direktno prevajanje v domač API pod.vira, gonilnik ni v Javi:
Prevede SQL ukaze v domač API od podatkovnega vira. Potrebuje OS-specifičeno binarno
kodo na vsakem klientu.
Prevajanje v domač API preko Java gonilnika:
Prevede JDBC klice direktno v omrežni protokol, ki ga uporablja SUPB. Potrebuje Java
gonilnik za specifičen SUPB na vsakem klientut.
Most (Bridge):
Prevede SQL ukaze v tuj API. Primer: JDBC-ODBC most. Koda za ODBC in JDBC
gonilnik morata biti na klientu.
Omrežni most (Network bridge):
Pošilja ukaze prek omrežja vmesni opremi (middleware), ki komunicira s podatkovnim
virom. Potrebuje majhen JDBC gonilnik na vsakem klentu.
OPB, SQL&Aplikacije
JDBC razredi in vmesniki
Koraki pri pošiljanju poizvedbe nad podatkovno
bazo:
• Naloži JDBC gonilnik
• Povezovanje s podatkovnim virom
• Izvajaj SQL stavke
OPB, SQL&Aplikacije
Upravljanje JDBC gonilnika
• Vse gonilnike upravlja razred DriverManager
• Nalaganje JDBC gonilnika:
v Java kodi:
Class.forName(“oracle/jdbc.driver.Oracledriver”);
Ob startu Java aplikacije:
Djdbc.drivers=<oracle/jdbc.driver
OPB, SQL&Aplikacije
Povezave z JDBC
S podatkovnim virom delamo preko seje (session). Vsaka
povezava identificira logično sejo:
•
JDBC URL:
jdbc:<subprotocol>:<otherParameters>
Primer:
String url=“jdbc:oracle:www.bookstore.com:3083”;
Connection con;
try{
con = DriverManager.getConnection(url,usedId,password);
} catch SQLException excpt { ...}
OPB, SQL&Aplikacije
Vmesnik razreda Connection
• Postavi izolacijski nivo trenutne povazave s podatkovno bazo.
public int getTransactionIsolation()
void setTransactionIsolation(int level)
• Specificira če so transakcije v dani povezavi samo-bralne.
public boolean getReadOnly()
void setReadOnly(boolean b)
• Če je postavljen autocommit potem se vsak SQL stavek smatra
transakcija.
• Sicer se transakcija zaključi s commit(), ter prekine z rollback().
public boolean getAutoCommit() and void setAutoCommit(boolean b)
• Preveri ali je povezava še odprta.
public boolean isClosed()
OPB, SQL&Aplikacije
Izvajanje SQL stavkov
Imamo tri različne načine za izvajanje SQL stavkov:
Statement (statični in dinamični SQL stavki)
PreparedStatement (semi-statični SQL stavki)
CallableStatment (shranjene procedure)
Razred PreparedStatement:
Vnaprej prevedeni, parametrizirani SQL stavki:
Struktura je fiksna.
Vrednosti parametrov so določene v času izvajanja.
OPB, SQL&Aplikacije
Izvajanje SQL stavkov (nadal.)
String sql=“INSERT INTO Sailors VALUES(?,?,?,?)”;
PreparedStatment pstmt=con.prepareStatement(sql);
pstmt.clearParameters();
pstmt.setInt(1,sid);
pstmt.setString(2,sname);
pstmt.setInt(3, rating);
pstmt.setFloat(4,age);
// we know that no rows are returned, thus we use executeUpdate()
int numRows = pstmt.executeUpdate();
OPB, SQL&Aplikacije
ResultSets
PreparedStatement.executeUpdate vrne število
spremenjenih zapisov.
• PreparedStatement.executeQuery vrne podatke, ki
so shranjeni v objektu ResultSet (ki je kurzor)
•
ResultSet rs=pstmt.executeQuery(sql);
// rs is now a cursor
While (rs.next()) {
// process the data
}
OPB, SQL&Aplikacije
ResultSets (nadaljevanje)
ResultSet je zelo izrazen kurzor:
• previous(): premakne se eno vrstico nazaj
• absolute(int num): premakne na vrstico z
določeno številko
• relative (int num): premakne se naprej ali nazaj
• first() in last()
OPB, SQL&Aplikacije
Ujemanje Java in SQL tipov
SQL Type
Java class
ResultSet get method
BIT
CHAR
VARCHAR
DOUBLE
FLOAT
INTEGER
REAL
DATE
TIME
TIMESTAMP
Boolean
String
String
Double
Double
Integer
Double
java.sql.Date
java.sql.Time
java.sql.TimeStamp
getBoolean()
getString()
getString()
getDouble()
getDouble()
getInt()
getFloat()
getDate()
getTime()
getTimestamp()
OPB, SQL&Aplikacije
JDBC: Izjeme in opozorila
Večino java.sql vrže SQLException če se pojavi
napaka.
SQLWarning je podrazred SQLException;
ni tako resna napaka (obstoj moramo testirat
eksplicitno)
OPB, SQL&Aplikacije
Opozorila in izjeme (nadal.)
try {
stmt=con.createStatement();
warning=con.getWarnings();
while(warning != null) {
// handle SQLWarnings;
warning = warning.getNextWarning():
}
con.clearWarnings();
stmt.executeUpdate(queryString);
warning = con.getWarnings();
...
} //end try
catch( SQLException SQLe) {
// handle the exception
}
OPB, SQL&Aplikacije
Meta podatkovna baza
Objekt DatabaseMetaData vsebuje podatke o sistemu
za delo s podatkovno bazo in katalog.
DatabaseMetaData md = con.getMetaData();
// print information about the driver:
System.out.println(
“Name:” + md.getDriverName() +
“version: ” + md.getDriverVersion());
OPB, SQL&Aplikacije
Meta podatkovna baza (nadal.)
DatabaseMetaData md=con.getMetaData();
ResultSet trs=md.getTables(null,null,null,null);
String tableName;
While(trs.next()) {
tableName = trs.getString(“TABLE_NAME”);
System.out.println(“Table: “ + tableName);
//print all attributes
ResultSet crs = md.getColumns(null,null,tableName, null);
while (crs.next()) {
System.out.println(crs.getString(“COLUMN_NAME” + “, “);
}
}
OPB, SQL&Aplikacije
(ne-) Kompleten primer
Connection con = // connect
DriverManager.getConnection(url, ”login", ”pass");
Statement stmt = con.createStatement(); // set up stmt
String query = "SELECT name, rating FROM Sailors";
ResultSet rs = stmt.executeQuery(query);
try { // handle exceptions
// loop through result tuples
while (rs.next()) {
String s = rs.getString(“name");
Int n = rs.getFloat(“rating");
System.out.println(s + " " + n);
}
} catch(SQLException ex) {
System.out.println(ex.getMessage ()
+ ex.getSQLState () + ex.getErrorCode ());
}
OPB, SQL&Aplikacije
SQLJ
• Komplementira JDBC s (semi-)statičnim modelom vprašanja:
• Prevajalnik preveri sintakso, preveri tipe, konsistenco poizvedbe s
shemo, ....
• Vsi argumenti so vedno povezani z isto spremenljivko:
#sql = {
SELECT name, rating INTO :name, :rating
FROM Books WHERE sid = :sid;
• Primerjaj z JDBC:
sid=rs.getInt(1);
if (sid==1) {sname=rs.getString(2);}
else { sname2=rs.getString(2);}
• SQLJ (del SQL standarda) proti vgnezdenem SQL (specifičen)
OPB, SQL&Aplikacije
SQLJ koda
Int sid; String name; Int rating;
// named iterator
#sql iterator Sailors(Int sid, String name, Int rating);
Sailors sailors;
// assume that the application sets rating
#sailors = {
SELECT sid, sname INTO :sid, :name
FROM Sailors WHERE rating = :rating
};
// retrieve results
while (sailors.next()) {
System.out.println(sailors.sid + “ “ + sailors.sname));
}
sailors.close();
OPB, SQL&Aplikacije
SQLJ iteratorji
Dva tipa iteratorjev (“kurzorjev”):
• Imenovan iterator
Potrebuje ime in tip spemenljivke; omogoča branje stolpcev po imenu.
Poglej primer na prejšnji prosojnici.
• Pozicijski iterator
Potrebuje samo tip spremenljivke; nato uporablja konstrukt FETCH .. INTO:
#sql iterator Sailors(Int, String, Int);
Sailors sailors;
#sailors = ...
while (true) {
#sql {FETCH :sailors INTO :sid, :name} ;
if (sailors.endFetch()) { break; }
// process the sailor
}
OPB, SQL&Aplikacije
Shranjene procedure
Kaj so shranjene procedure:
Program, ki se izvaja preko enega samega SQL stavka
Izvaja se kot proces na strežniku
Prednosti:
Lahko vgradimo aplikacijsko logiko in ostanemo blizu
podatkov.
Možna ponovna uporaba aplikacijske logike, različni
uporabniki.
Izognemo se vračanju zapis-po-zapis preko kursorja.
OPB, SQL&Aplikacije
Shranjene procedure: Primeri
CREATE PROCEDURE ShowNumReservations
SELECT S.sid, S.sname, COUNT(*)
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
GROUP BY S.sid, S.sname
Shranjene procedure imajo lahko parametre:
Tri različne načini: IN, OUT, INOUT
CREATE PROCEDURE IncreaseRating(
IN sailor_sid INTEGER, IN increase INTEGER)
UPDATE Sailors
SET rating = rating + increase
WHERE sid = sailor_sid
OPB, SQL&Aplikacije
Shranjene procedure: Primeri
Shranjene procedure so lahko napisane v drugih
programskih jezikih, ne nujno v SQL:
CREATE PROCEDURE TopSailors( IN num INTEGER)
LANGUAGE JAVA
EXTERNAL NAME “file:///c:/storedProcs/rank.jar”
OPB, SQL&Aplikacije
Klicanje shranjenih procedur
EXEC SQL BEGIN DECLARE SECTION
Int sid;
Int rating;
EXEC SQL END DECLARE SECTION
// now increase the rating of this sailor
EXEC CALL IncreaseRating(:sid,:rating);
OPB, SQL&Aplikacije
Klicanje shranjenih procedur
JDBC:
SQLJ:
CallableStatement cstmt=
con.prepareCall(“{call
ShowSailors});
ResultSet rs =
cstmt.executeQuery();
while (rs.next()) {
...
}
#sql iterator
ShowSailors(...);
ShowSailors showsailors;
#sql showsailors={CALL
ShowSailors};
while (showsailors.next()) {
…
}
OPB, SQL&Aplikacije
SQL/PSM
• Večina DBMS omogoča uporabnikom pisanje shranjenih
procedur v visoko-nivojskem programskem jeziku (blizu
SQL).
• SQL/PSM - Persistent Stored Modules, SQL/Foundations
Deklariraj shranjeno proceduro:
CREATE PROCEDURE name(p1, p2, ..., pn)
local variable declarations
procedure code;
Deklariraj funkcijo:
CREATE FUNCTION name (p1, ..., pn) RETURNS
sqlDataType
local variable declarations
function code;
OPB, SQL&Aplikacije
Osnovni SQL/PSM gradniki
CREATE FUNCTION rateSailor
(IN sailorId INTEGER)
RETURNS INTEGER
DECLARE rating INTEGER
DECLARE numRes INTEGER
SET numRes = (SELECT COUNT(*)
FROM Reserves R
WHERE R.sid = sailorId)
IF (numRes > 10) THEN rating =1;
ELSE rating = 0;
END IF;
RETURN rating;
OPB, SQL&Aplikacije
Osnovni SQL/PSM gradniki
•
•
•
•
Lokalne spremenljivke (DECLARE)
RETURN vrednosti za FUNCTION
Prirejanje vrednosti spremenljivkam SET
Vejitve in zanke:
IF (condition) THEN statements;
ELSEIF (condition) statements;
... ELSE statements; END IF;
LOOP statements; END LOOP
• Poizvedbe so deli izrazov.
• Lahko uporabljamo kurzorje naravno brez “EXEC SQL”
OPB, SQL&Aplikacije
Povzetek
• Vgnezdeni SQL dovoljuje izvajanje parametričnih
statičnih poizvedb v gotiteljskem jeziku.
• Dinamični SQL dovoljuje izvajanje ad-hoc poizvedb iz
gostiteljskega jezika.
• Mehanizem kurzorjev omogoča branje enega zapisa
naenkrat in premosti impedančni problem med
gostiteljem in SQL.
• API kot npr. JDBC implementira plast abstrakcije med
aplikacijo in DBMS.
OPB, SQL&Aplikacije
Povzetek (nadal.)
• SQLJ: Statični model, poizvedbe se preverijo v
času prevajanja.
• Shranjene procedure izvajajo aplikacijsko
logiko direktno na strežniku.
• SQL/PSM standard za pisanje shranjenih
procedur.
OPB, SQL&Aplikacije