- eväste (keksi, cookie)
- dynaamiset URL:t
- Suurimmat ongelmat erityisesti tietojen muuttamisen yhteydessä
- aikalukot erityisen tärkeitä
Tietokannanhallintajärjestelmiin voi tallentaa omia aliohjelmia ja funktioita joita voi käyttää avukseen SQL-lauseissa. Proseduurien syntaksi on standardoitu SQL-99:ssä mutta useimmat järjestelmät käyttävät vielä kukin omaa syntaksiaan.
DROP TABLE Testi
;
DROP TABLE Tehtava
;
CREATE TABLE Testi (
ID INTEGER NOT NULL,
Nimi VARCHAR(64) NOT NULL,
Tehtava INTEGER DEFAULT 1,
CONSTRAINT Testi_PK
PRIMARY KEY (ID)
)
;
CREATE TABLE Tehtava (
TID INTEGER NOT NULL,
Nimi VARCHAR(64) NOT NULL,
CONSTRAINT Tehtava_PK
PRIMARY KEY (TID)
)
;
DROP FUNCTION tunniste()
;
CREATE FUNCTION tunniste() RETURNS INTEGER
AS 'SELECT MAX(ID)+1 FROM Testi;'
LANGUAGE SQL;
INSERT INTO Tehtava VALUES (1, 'Ope')
;
INSERT INTO Tehtava VALUES (2, 'Oppilas')
;
INSERT INTO Testi VALUES (1, 'Tommi', 1)
;
INSERT INTO Testi VALUES (tunniste(), 'Petri', 1)
;
INSERT INTO Testi VALUES (tunniste(), 'Matti', 2)
;
INSERT INTO Testi VALUES (tunniste(), 'Maija', 2)
;
SELECT *
FROM Testi
;
CREATE TRIGGER -lauseella voidaan määritellä ns. herättimiä. Herätin on toiminta, joka tapahtuu aina, kun jokin tietty SQL-operaatio tehdään tietylle taululle. Herätin voidaan määritellä tapahtuvaksi lisäys-, poisto- tai päivitys-operaatiota ennen tai jälkeen.
--Jos Tyontekijan tunniste muuttuu niin varmistetaan, että tyontekijan lapset viittaavat oikeaan
--tyontekijaaan:
CREATE TRIGGER Paivita_Lapsi
AFTER UPDATE ON Tyontekija
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
UPDATE Lapsi SET Huoltaja = N.TyontekijaID WHERE Huoltaja = O.TyontekijaID;
-- Jos tyontekijä poistetaan tietokannasta niin poistetaan myös hänen lapsensa:
CREATE TRIGGER Poista_Lapsi
AFTER DELETE ON Tyontekija
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
DELETE FROM Lapsi WHERE Huoltaja = O.TyontekijaID;
-- Päivitettäessä lapsen tietoja varmistetaan, että lapsen huoltajaksi määritelty henkilö löytyy
-- tyontekija-taulusta. Jos ei löydy niin annetaan virheilmoitus:
CREATE TRIGGER Varmista_Lapsi_u
NO CASCADE BEFORE UPDATE ON Lapsi
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.Huoltaja NOT IN ( SELECT TyontekijaID FROM Tyontekija ) )
SIGNAL SQLSTATE '75000' ('The update value of the Huoltaja in Lapsi is no equal to any value of the parent key of the parent table');
-- Lisättäessä uutta lasta tarkistetaan, että hänelle määritelty huoltaja löytyy tyontekija-taulusta. Jos
-- ei löydy niin annetaan virheilmoitus:
CREATE TRIGGER Varmista_Lapsi_i
NO CASCADE BEFORE INSERT ON Lapsi
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.Huoltaja NOT IN ( SELECT TyontekijaID FROM Tyontekija ) )
SIGNAL SQLSTATE '75001' ('The insert value of the Huoltaja in Lapsi is not equal to any value of the parent key of the parent table');
Sekvensseillä voi tehdä helposti automaattisia tunnisteita tms. Sekvenssejä ei ole standardoitu. Seuraava esimerkki on kirjoitettu PostgreSQL:n syntaksilla:
DROP SEQUENCE sarjanumero
;
CREATE SEQUENCE sarjanumero START 10
;
INSERT INTO Testi VALUES (NEXTVAL('sarjanumero'), 'Maija', 2)
;
SELECT *
FROM Testi
;
ODBC on rajapinta SQL-sovellusten ohjelmointiin Windows-ympäristöissä
OLE DB on rajapinta mihin tahansa tietoon.
#otetaan käyttöön tarvittava kirjasto
use Win32::ODBC;
#luodaan aliohjelma sql-komentojen suorittamiseen
sub sqlkomento {
$db = $_[0];
$komento = $_[1];
if ($db->Sql($komento)){
print "SQL epäonnistui.\n";
print "\t$komento\n";
print "\tVirhe: " . $db->Error() . "\n";
$db->Close();
exit;
}
return $db;
}
# Otetaan yhteys testi-nimiseen ODBC-tietolähteeseen
$DSN = "testi";
if (!($db = new Win32::ODBC($DSN))){
print "Virhe ODBC-yhteyttä muodostettaessa $DSN\n";
print "Virhe: " . Win32::ODBC::Error() . "\n";
exit;
}
#luodaan tarvittava SQL-koodi
$sql = "\
CREATE TABLE Testi ( \
ID INTEGER NOT NULL,\
Tieto VARCHAR(50) NOT NULL,\
CONSTRAINT Testi_PK\
PRIMARY KEY (ID)\
)";
#suoritetaan SQL-koodi
sqlkomento($db, $sql);
$sql = "INSERT INTO Testi VALUES (1, 'Testidata1')";
sqlkomento($db, $sql);
$sql = "INSERT INTO Testi VALUES (2, 'Testidata2')";
sqlkomento($db, $sql);
# Määritellään lisättävä tieto ohjelmakoodin muuttujilla
$id = 3;
$teksti = "Testidata3";
$sql = "INSERT INTO Testi VALUES ($id, '$teksti')";
sqlkomento($db, $sql);
#luodaan SQL-kysely
$sql = "SELECT *\
FROM Testi";
#suoritetaan kysely ja otetaan sen palauttamat tietueet talteen
$db = sqlkomento($db, $sql);
#käydään läpi kyselyn tulos ja tulostetaan jokainen tietue
while($db->FetchRow()){
undef %tietue;
%tietue = $db->DataHash();
print $tietue{'ID'} . "\t";
print $tietue{'Tieto'} . "\n";
}
$sql = "DELETE FROM Testi";
sqlkomento($db, $sql);
$sql = "DROP TABLE Testi";
sqlkomento($db, $sql);
#suljetaan ODBC-yhteys
$db->Close();
DBI on Perl-ohjelmissa käytettävä tietokantarajapinta.
#!/usr/bin/perl -w
# Ohjelma luo SQL:n avulla tietokannan taulut.
#
# Author: Petri Heinonen & Tommi Lahtonen
# Date: 12.9.2001
# Tarvittavien kirjastojen lataaminen
use DBI;
use DBI qw(:sql_types);
#######################################################################
# Pääohjelma
$db = 'tietokanta';
$username = 'käyttäjätunnus';
$passwd = 'salasana';
# Luodaan yhteys tietokantaan
$dbh = DBI->connect("dbi:Pg:dbname=$db;user=$username; password=$passwd") or die $DBI::errstr ;
# Luodaan tietokannan taulu
#
my $SQL= <<"EOF";
CREATE TABLE Testi (
ID INTEGER NOT NULL,
Nimi VARCHAR(64) NOT NULL,
CONSTRAINT Testi_PK
PRIMARY KEY (ID)
)
EOF
# valmistellaan SQL-komento ja suoritetaan se
my $sth = $dbh->prepare($SQL);
$sth->execute;
# lisätään tietoja tietokantaan
# luodaan ensin tarvittava SQL-koodi johon sijoitetaan
# ?-merkit eli ns. placeholderit muuttuvien parametrien kohdalle
$SQL= <<"EOF";
INSERT INTO Testi VALUES (?, ?);
EOF
# valmistellaan komento
$sth = $dbh->prepare( $SQL );
# sidotaan puuttuviin parametreihin haluamamme arvot
$sth->bind_param( 1, "1", SQL_INTEGER );
$sth->bind_param( 2, "Tommi", SQL_VARCHAR );
# suoritetaan sql
$sth->execute();
$sth->bind_param( 1, "2", SQL_INTEGER );
$sth->bind_param( 2, "Petri", SQL_VARCHAR );
$sth->execute();
# luodaan SQL-kyselykoodi
$SQL= <<"EOF";
SELECT * FROM Testi
EOF
# valmistellaan kysely ja suoritetaan se
$sth = $dbh->prepare( $SQL );
$sth->execute();
# sidotaan perlin omiin muuttujiin hakutuloskohdistimen kentät
my( $id, $nimi);
$sth->bind_columns( \$id, \$nimi);
# käydään silmukassa läpi kaikki kohdistimen sisältämät tietueet ja tulostetaan ne
while( $sth->fetch() ) {
print "$id\t$nimi\n";
}
# poistetaan luotu taulu
$SQL= <<"EOF";
DROP TABLE Testi;
EOF
$sth = $dbh->prepare( $SQL );
$sth->execute();
# Suljetaan yhteys tietokantaan
$dbh->disconnect;
JDBC on Java-ohjelmien käyttöön tarkoitettu standardoitu tietokantaliittymärajapinta. JDBC on puhtaasti oliopohjainen.
import java.sql.*;
public class jdbc {
public static void main(String args[]) {
/*
* Määritellään tietokannan yhteys-url.
* Käytetään JDBC-ODBC-siltaa jolloin määritys on:
* jdbc:odbc
* Jos käytössä olisi valmis jdbc-ajuri niin tarkka määritys
* pitää tarkistaa ajurin ohjeista.
* Viimeinen sana kertoo ODBC-tietolähteen nimen eli koko url
* on:
* jdbc:odbc:testi
*/
String url = "jdbc:odbc:testi";
Connection yhteys;
String sql;
String tunnus = "";
String salasana = "";
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
/*
* Luodaan yhteys tietokantaan aiemmin määritellyn
* url:n, tunnuksen ja salasanan avulla.
*/
yhteys = DriverManager.getConnection(url, tunnus,
salasana);
/*
* Luodaan Statement-objekti, jonka avulla voidaan
* ajaa SQL-komentoja edellä määritellyn yhteyden
* ylitse
*/
stmt = yhteys.createStatement();
/*
* Suoritettava SQL-koodi
*/
sql = "CREATE TABLE Testi ( " +
"ID INTEGER NOT NULL," +
"Tieto VARCHAR(50) NOT NULL," +
"CONSTRAINT Testi_PK" +
" PRIMARY KEY (ID)" +
")";
/*
* Suoritetaan edellä määritelty SQL-koodi.
* SQL-lauseet, jotka eivät palauta mitään,
* suoritetaan
* Statement-objektin executeUpdate-metodilla.
*/
stmt.executeUpdate(sql);
sql = "INSERT INTO Testi VALUES (1, 'Testidata1')";
stmt.executeUpdate(sql);
sql = "INSERT INTO Testi VALUES (2, 'Testidata2')";
stmt.executeUpdate(sql);
/*
* Muodostetaan SQL-lause muuttujien sisällön
* perusteella.
*/
int id = 3;
String teksti = "Testidata3";
sql = "INSERT INTO Testi VALUES (" +
id +
", '" +
teksti +
"')";
stmt.executeUpdate(sql);
sql = "SELECT * " +
"FROM Testi";
/*
* Suoritetaan edellä määritelty SQL-koodi.
* SQL-lauseet, jotka palauttavat tietoa,
* suoritetaan
* Statement-objektin executeQuery-metodilla.
* Otetaan talteen kyselyn palauttamat tietueet
* ResultSet-objektiin.
*/
ResultSet rs = stmt.executeQuery(sql);
/*
* Käydään läpi kaikki Resultsetin sisältämät
* tietueet.
* Tulostetaan jokaisen tietueen sisältö.
*/
while (rs.next()) {
int i = rs.getInt("ID");
String s = rs.getString("Tieto");
System.out.println(i + "\t" + s);
}
sql = "DELETE FROM Testi";
stmt.executeUpdate(sql);
sql = "DROP TABLE Testi";
stmt.executeUpdate(sql);
/*
* Suljetaan yhteys tietokantaan
*/
stmt.close();
yhteys.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
/*
* Esimerkki sqlj:n käytöstä
* Toteutettu oraclen sqlj-paketin avulla
* ODBC-yhteys luodaan Access-tietokantaan
*
*/
import java.sql.Date;
import java.sql.SQLException;
/*
* Esitellään iteraattori.
* Iteraattorin rungossa esitellään hakutuloksessa olevia
* kenttiä vastaavat saantimetodit ja niiden tyypit.
*/
#sql iterator Testi (
int ID,
String tieto);
class malli
{
public static void main( String args[] )
{
/*
* Luodaan yhteys tietokantaan.
* Yhteyden asetukset määritellään sql.properties-tiedostossa
* jossa lukee esim. näin:
*
* sqlj.url=jdbc:odbc:testi
* sqlj.driver=sun.jdbc.odbc.JdbcOdbcDriver
*
* sqlj.password=
*
* ConnectionManager-luokka tulee oraclen sqlj-paketin mukana
*/
ConnectionManager.initContext();
try {
/*
* Suoritetaan halutut SQL-komennot
* SQL-komennot upotetaan suoraan javakoodin sekaan
* #sql-avainsanan ja kaarisulkeiden avulla
*/
#sql
{
CREATE TABLE Testi (
ID INTEGER NOT NULL,
Tieto VARCHAR(50) NOT NULL,
CONSTRAINT Testi_PK
PRIMARY KEY (ID)
)
};
#sql { INSERT INTO Testi VALUES (1, 'Testidata1') };
#sql { INSERT INTO Testi VALUES (2, 'Testidata2') };
/*
* Lisätään tietoja tietokantaan käyttäen javakoodissa esiintyviä
* muuttujia.
* Java-ohjelmakoodin muuttujia voidaan sisällyttää
* sqlj-lauseisiin
* kirjoittamalla muuttujat suoraan sql-koodin sekaan siten, että
* muuttujan nimen eteen laitetaan aina kaksoispiste.
*/
int id = 3;
String teksti = "Testidata3";
#sql { INSERT INTO Testi VALUES (:id, :teksti) };
/*
* Luodaan iteraattori.
* Tallennetaan iteraattoriin SQL-kyselyn tulos.
*/
Testi iter;
#sql iter = { SELECT * FROM Testi };
/*
* Käydään läpi iteraattorin sisältämät tietueet
*/
while( iter.next() )
{
System.out.println( iter.ID() + "\t" + iter.tieto() );
}
/*
* Suljetaan iteraattori, kun sitä ei enää tarvita.
*/
iter.close() ;
/*
* Tyhjennetään tietokanta.
*/
#sql { DELETE FROM Testi };
#sql { DROP TABLE Testi };
}
catch( SQLException exception )
{
System.err.println( "Virhe: " + exception );
}
}
}
<%
Set Command = Server.CreateObject("ADODB.Command")
Command.ActiveConnection = ConnectionString
Command.CommandText = "DELETE FROM Taulu"
RecordsAffected = 0
Command.Execute(RecordsAffected)
%>
Haetaan Recordset-objectin avulla tietoja tietokannasta
<%
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "SELECT * FROM Oppilaat ORDER BY sukunimi", _
ConnectionString, 0, 1
if (Rs.BOF AND Rs.EOF) then
Response.Write "Ei yhtään opiskelijaa<br>"
exit sub
end if
Do While Rs.EOF = false
%> <tr> <td><%=counter%></td> <td><%=Rs("sukunimi")%> </td> <td><%=Rs("etunimi")%> </td> <td><a href="mailto:<%=Rs("sähköpostiosoite")%>">
<%=Rs("sähköpostiosoite")%> </a></td>
<td><%=Rs("koulutusohjelma")%> </td> </tr> <%
Rs.MoveNext
Loop
%>
<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">
<META HTTP-EQUIV="Content-Type"
content="text/html; charset=iso-8859-1">
<TITLE>Tietokone ja perusohjelmistot</TITLE>
</HEAD>
<BODY bgcolor="#FFFFFF" text="#333333" leftmargin="0" topmargin="0">
<h1><strong>TIETOKONE JA PERUSOHJELMISTOT</strong></h1>
<%
if (Request("htyot") <> "") then
call Listaahtyot
else
call listaa
end if
%>
</BODY>
</HTML>
<%
sub listaa
%>
0 tarkoittaa mitä tahansa demoryhmää<br>
1-8 ovat demoryhmien numerot<br>
tyhjä tarkoittaa puuttuvaa demosuoritusta<br>
<br>
<a href="lista.asp?htyot=kukkuu"><strong>Hyväksytyt harjoitustyöt</strong></a>
<br>
Jos listauksen tiedoissa on puutteita tai vikoja niin lähetä sähköpostia<a
href="mailto:tlahtone@cc.hut.fi">
tlahtone@cc.hut.fi</a>
<%
ConnectionString = Session("opiskelijat_ConnectionString")
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "SELECT * FROM Oppilaat ORDER BY sukunimi", _
ConnectionString, 0, 1
if (Rs.BOF AND Rs.EOF) then
Response.Write "Ei yhtään opiskelijaa<br>"
exit sub
end if
%>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<td> </td>
<td><strong>Sukunimi</strong></td>
<td><strong>Etunimi</strong></td>
<td><strong>E-mail</strong></td>
<td><strong>KO</strong></td>
<td><strong>Demo1</strong></td>
<td><strong>Demo2</strong></td>
<td><strong>Demo3</strong></td>
<td><strong>Demo4</strong></td>
<td><strong>Demo5</strong></td>
<td><strong>Demo6</strong></td>
<td><strong>Demo7</strong></td>
<td><strong>Demo8</strong></td>
</tr>
<%
counter = 1
Do While Rs.EOF = false
%>
<tr>
<td><%=counter%></td>
<td><%=Rs("sukunimi")%> </td>
<td><%=Rs("etunimi")%> </td>
<td><a
href="mailto:<%=Rs("sähköpostiosoite")%>"><%=Rs("sähköpostiosoite")%> </a></td>
<td><%=Rs("koulutusohjelma")%> </td>
<td><%=Rs("demo1")%> </td>
<td><%=Rs("demo2")%> </td>
<td><%=Rs("demo3")%> </td>
<td><%=Rs("demo4")%> </td>
<td><%=Rs("demo5")%> </td>
<td><%=Rs("demo6")%> </td>
<td><%=Rs("demo7")%> </td>
<td><%=Rs("demo8")%> </td>
</tr>
<%
counter = counter + 1
Rs.MoveNext
Loop
%>
</table>
<%
end sub
'-------------------------------------
sub listaahtyot
%>
<strong>Hyväksytyt harjoitustyöt</strong>
<%
ConnectionString = Session("opiskelijat_ConnectionString")
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open "SELECT * FROM Oppilaat WHERE htyo_palautettu IS NOT NULL ORDER BY sukunimi", _
ConnectionString, 0, 1
if (Rs.BOF AND Rs.EOF) then
Response.Write "Ei yhtään opiskelijaa<br>"
exit sub
end if
%>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<td> </td>
<td><strong>Sukunimi</strong></td>
<td><strong>Etunimi</strong></td>
<td><strong>E-mail</strong></td>
<td><strong>KO</strong></td>
<td><strong>Harjoitustyön tyyppi</strong></td>
<td><strong>Nimi</strong></td>
<td><strong>Pvm</strong></td>
<td><strong>Bonus</strong></td>
</tr>
<%
counter = 1
Do While Rs.EOF = false
%>
<tr>
<td><%=counter%></td>
<td><%=Rs("sukunimi")%> </td>
<td><%=Rs("etunimi")%> </td>
<td><a
href="mailto:<%=Rs("sähköpostiosoite")%>"><%=Rs("sähköpostiosoite")%> </a></td>
<td><%=Rs("koulutusohjelma")%> </td>
<td><%=Rs("htyontyyppi")%> </td>
<td><%=Rs("nimi")%> </td>
<td><%=Rs("htyo_palautettu")%> </td>
<td><%=Rs("extrabonus")%> </td>
</tr>
<%
counter = counter + 1
Rs.MoveNext
Loop
%>
</table>
<%
end sub
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/2000/REC-xhtml1-20000126/DTD/xhtml1-strict.d$
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fi" lang="fi">
<head>
<link rel="StyleSheet" href="./style.css" type="text/css" media="screen" />
<title>Title-teksti</title>
</head>
<body>
<?
#tunnus- ja salasanatiedot on poistettu tästä lähdekoodista
$conn = mysql_connect($server,$user,$passwd); # Luodaan yhteys palvelimelle
$db = mysql_select_db("appro",$conn); # Luodaan yhteys tietokantaan
# Varsinainen SQL-lause
$haku1 = "select * from test";
$tulos1 = mysql_query($haku1,$conn);
$sarlkm = mysql_num_fields($tulos1);
# Luodaan haun tuloksesta taulu
print '<div class="center">';
print "<table>\n";
print "<tr>";
# Silmukka, jolla tulostetaan haluttujen kenttien nimet näkyville.
for ($i=0; $i < $sarlkm; $i++)
{
print "<th>";
print mysql_field_name($tulos1, $i);
print "</th>";
}
print "</tr>\n";
# Silmukka, jolla tulostetaan tietueet
while($tietue = mysql_fetch_array($tulos1))
{
print "<tr>";
# Käydään läpi tietueen kentät
for ($i=0; $i < $sarlkm; $i++)
{
print "<td> $tietue[$i]</td>";
}
print "</tr>\n";
}
print "</table>\n";
print "</div>\n";
?>
</body>
</html>
Perl ja WIN32::ODBC Esimerkkidata