Utilizzare SQL nelle applicazioni
Sistemi Informativi
Uso di SQL e nei linguaggi di programmazione
DEE - Politecnico di Bari
• Nella “vita reale” l’interazione con basi di dati utilizzando SQL non è
diretta, ma mediata attraverso l’uso di programmi applicativi. Oltre ai
tradizionali linguaggi di programmazione di alto livello procedurali ed a
oggetti (Cobol,C,PL/SQL) esistono i cosiddetti linguaggi di quarta
generazione (4GL) come Informix4GL che permettono di sviluppare
complete applicazioni per la gestione di basi di dati.
• SQL da solo non è Touring-Completo
• E’ necessario stabilire modalità di interazione tra SQL e tali linguaggi. I
problemi di interazione sono rappresentati dall’ Impedence mismatch
(“disaccoppiamento di impedenza”) fra base di dati e linguaggio di
programmazione poiché i linguaggi operano tipicamente su singole
variabili o oggetti mentre SQL opera su relazioni (insiemi di tuple)
• In alcuni approcci la soluzione ai problemi di interazione è rappresentata
dall’uso dei cursori
2
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
PRINCIPALI APPROCCI
DEE - Politecnico di Bari
• Call Level Interface (CLI)
SQL statico
• SQL embedded (SQL “integrato”)
SQL dinamico
• Stored Procedures
3
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
CLI – COME SI UTILIZZANO
E’ una Application Programming Interface (API) per accedere ai
databases, cioè un insieme di funzioni chiamabili direttamente dal
linguaggio di programmazione usato. Il database mette a disposizione
una libreria di funzioni, che seguono lo standard CLI. Generalmente
vengono usate per applicazioni basate su SQL dinamico.
API
Oracle
Prog
C
Oracle
API
Interbase
Interbase
4
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
•
•
EVOLUZIONE di CLI
Utilizza dei driver specifici per ciascun database
E’ uno strato software che si interpone tra l’applicazione e i driver specifici del
database. Consente quindi ai programmi applicativi di usare query standard
SQL, che accederanno al database, senza necessità di conoscere la particolare
interfaccia proprietaria.
Un modulo del linguaggio di programmazione fornisce una API (serie di funzioni
o classi se OO) che permette di interfacciare qualsiasi database tramite un
driver specifico per quel database (es. ODBC, JDBC)
•
Prog
JAVA
Driver
Oracle
JDBC
API
Oracle
Driver
Interbase
Interbase
5
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
Implementazioni delle CLI
ODBC (Open Database Connectivity): implementazione proprietaria (Microsoft) di
SQL/CLI (altra CLI parte di SQL:1999), per accesso a basi di dati relazionali in un
contesto eterogeneo e distribuito. Originariamente rilasciata nel 1992, consente di
accedere a database di numerosissimi costruttori, inclusi Microsoft (jet, Access,
SQLServer); Oracle, IBM, Informix e numerosi altri.
JDBC (Java Database Connectivity): è un API Java sviluppata da JavaSoft per
eseguire istruzioni SQL e consente ai programmi Java di interagire con qualunque
database. Poiché Java gira sulla maggior parte delle piattaforme, JDBC rende
possibile scrivere una singola applicazione di database che può girare
indifferentemente su piattaforme diverse e interagire con diversi database. JDBC è
simile a ODBC ma è progettato apposta per programmi Java mentre ODBC è
indipendente dal linguaggio di programmazione.
Nello scrivere programmi in Java e usando l’interfaccia JDBC si può usare un prodotto
che comprende un "bridge" program JDBC-ODBC per raggiungere database ODBCaccessible.
In generale le operazioni richieste sono: caricamento del driver, apertura della
connessione con la base di dati, richiesta di esecuzione di istruzioni SQL ed
elaborazione dei risultati delle istruzioni SQL
6
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
ODBC e ADO
•ODBC è uno strato software che si interpone tra l’applicazione e i driver specifici del
database. Consente quindi ai programmi applicativi di usare query standard SQL, che
accederanno al database, senza necessità di conoscere la particolare interfaccia
proprietaria.
•ODBC richiede una certa familiarità di programmazione con le API ed è sostanzialmente
procedurale (Scritta in C). Successivamente Microsoft ha introdotto altre due API, questa
volta orientate agli oggetti, che vanno sotto il nome di DAO
•(Data Access Object) e RDO (Remote Data Object), che sono sostanzialmente interfacce
per il motore ODBC. Esse definiscono interfacce ActiveX (originariamente COM Component
Object Model).
•Più recentemente Microsoft ha introdotto ADO (Active Data Object), basata su una nuova
tecnologia di programmazione delle interfacce per DB nota come OLE DB (Object Linking
and Embedding for DataBases).
•La differenza sostanziale con ODBC è che in questo ogni ogni tipo di DB deve avere una
DLL (Dynamic Link Library), un driver ODBC, che viene utilizzata dal motore ODBC per
accedere allo specifico DB. In OLE DB is hanno ancora dei driver, ma questi sono
implementazioni ActiveX, cioè definizioni di classi che implementano delle interfacce,
eliminando così i livelli interposti tra il programma e lo specifico DBMS che si sta utilizzando.
•Tipicamente si utilizza per applicazioni web ODBC in congiunzione con ADO.
7
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
Attivazione di una origine dati ODBC
N.B. Si suppone la presenza di un database ACCESS
•Selezionare Pannello di Controllo
•Cliccare su Origine dati ODBC
•Selezionare la scheda DSN di sistema ovvero DSN Utente
•Cliccare su aggiungi
•Selezionare il driver (Microsoft Access)
•Inserire nella scheda il nome di riferimento dell'origine dati
•Cliccare su Database: Seleziona
•Selezionarere Database
•Premere OK
8
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
Basi di ADO
ADO è composto essenzialmente da tre oggetti primari: Connection Object, Command
Object, RecordSet Object:
Connection Object è responsabile di raccogliere tutte le informazioni necessarie alla
creazione del RecordSet;
RecordSet fornisce la struttura atta a contenere i dati e i metodi necessari per
accedere ai dati presenti nel RecordSet, tutte le funzionalità dei cursori sono
rappresentati nell’interfaccia del RecordSet;
Command Object fornisce un secondo metodo per creare un RecordSet, ed è stato
esplicitamente progettato per passare i parametri alle stored procedure.
9
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
Un semplice esempio in ASP
‘ Creo un’istanza del componente e la assegno a conn
<% set conn=Server.CreateObject("ADODB.Connection") %>
‘ apro la connessione al database mediante una origine dati ODBC
<%conn.Open "northwind“, , %>
‘Definisco una query
Sqlstring="Select * from Customers"
‘Creo un recordset e gli passo la query:
<% Set rs=Server.CreateObject("ADODB.recordset")
rs.Open Sqlstring, conn %>
‘Visualizzo i dati estratti:
<%
for each x in rs.fields response.write(x.name)
response.write(" = ") response.write(x.value)
next
%>
<%conn.close%>
10
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
EMBEDDED SQL
•
•
Tecnica sviluppata sin dagli anni ’70
L’ SQL è “ospitato” in un linguaggio di programmazione, chiamato
linguaggio ospite (Java, Pascal, Cobol, C, C++, …), in quanto questi
standard hanno definito l’interfaccia con SQL: il sorgente del vostro
programma contiene sia codice nel linguaggio di programmazione che
codice sql
• un preprocessore, dipendente sia dal linguaggio ospite che dalla
piattaforma del DBMS viene usato per analizzare il codice e tradurlo
sostituendo le istruzioni SQL con chiamate alle funzioni di una API del
DBMS prima della compilazione vera e propria
Ogni programma che usa Embedded SQL deve:
1. Definire quale database usare
2. Connettersi al database
3. Effettuare le operazioni sul database (usando cursori e statementSQL)
4. Disconnettersi dal database
11
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
ESEMPIO in C di SQL statico– prima….
#include<stdlib.h>
#include<stdio.h>
#include<sqlenv.h>
main()
{
exec sql begin declare section; /*dichiarazione variabili*/
char *NomeDip = “DEE";
int Id_Dip = 3;
exec sql end declare section; /*fine della dichiarazione variabili*/
}
12
exec sql connect to esempio@azienda_esempio;
if (sqlca.sqlcode != 0) {
/*sqlca (SQL Communication Area) è una struttura di dati predefinita che mantiene le
informazioni sulla comunicazione tra SQL e programma, sqlcode=0 no errore,
altrimenti codice dell’errore*/
printf(“Errore di connessione al DB\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:Id_Dip);
/* notare l’uso del : per utilizzare le variabili del programma ospite in SQL*/
exec sql disconnect all;
}
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
ESEMPIO in C di SQL statico – prima e
dopo …
main()
{
exec sql connect to universita
user pguser identified by pguser;
exec sql create table studente (matricola integer primary key,
nome varchar(20),
annodicorso integer);
exec sql disconnect;
}
/* These include files are added by the preprocessor */
#include
#include
#include
#include
<ecpgtype.h>
<ecpglib.h>
<ecpgerrno.h>
<sqlca.h>
main()
{
ECPGconnect(__LINE__, "universita" , "pguser" , "pguser" , NULL, 0);
ECPGdo(__LINE__, NULL, "create table studente ( matricola integer primary key ,
nome varchar ( 20 ) , annodicorso integer )", ECPGt_EOIT, ECPGt_EORT);
ECPGdisconnect(__LINE__, "CURRENT");
}
13
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
ESEMPIO 3
DEE - Politecnico di Bari
…
select Nome, Cognome
into :nome_imp, :cognome_imp from Impiegato
where CF = :CF_imp;
…
Finché il risultato di una query è solo un record è tutto semplice
14
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
CURSORI
• Quando una query restituisce più tuple è necessario passarle al
programma ospite una per volta
• Si utilizza un cursore per accedere a tutte le tuple di una interrogazione,
accede a tutte le tuple di una interrogazione in modo globale (tutte
insieme o a blocchi – è il DBMS che sceglie la strategia efficiente); il
cursore passa poi una tupla per volta al programma
• Il cursore viene definito su una generica interrogazione mediante la
seguente sintassi
declare Cursor_Name [scroll ] cursor for
SelectSQL [for <read only| update [of attribute,{attribute}]>]
Dove, scroll indica che il cursore può muoversi liberamente sul risultato
della query, mentre for update indica che il cursore può essere utilizzato in
operazioni di aggiornamento permettendo di specificare eventualmente
gli attributi oggetto del comando.
Si osservi che per riga corrente si considera l’ultima riga letta.
15
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
UTILIZZO DEL CURSORE
•
Esegue la query: open Cursor_Name
•
Utilizzo dei risultati (una ennupla alla volta): fetch [position from]
Cursor_Name into Variables (accedere alla tupla corrente del risultato o a
quella indicata da position e porne i valori nelle variabili del programma ospite)
•
Position (utilizzabili se l’opzione scroll è stata imposta altrimenti è disponibile solo
next) :
next (con riferimento a current indica la prossima tupla);
prior (tupla precedente);
first (prima tupla);
last (ultima tupla);
absolute integer_value (tupla posizionata al valore ordinale espresso)
relative integer_value (tupla posizionata al valore ordinale espresso, rispetto
alla posizione corrente)
•
Accedere alla tupla corrente di un cursore per effettuare un update o un delete (si
usa solo nella clausola where e quando la query associata al cursore non esegue
un join tra diverse tabelle):
es. Delete from NomeTabella where current of Cursor_Name
•
Chiusura: close cursor Cursor_Name
16
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
ESEMPIO IN C CON UTILIZZO DI
CURSORI
……
printf(“nome della citta‘?”);
scanf(“%s”,&citta[0]);
/*creo il cursore*/
EXEC SQL DECLARE P CURSOR FOR SELECT NOME, REDDITO
FROM Impiegato
WHERE CITTA = :citta ;
EXEC SQL OPEN P ; /* apro il cursore -> eseguo la query*/
EXEC SQL FETCH P INTO :nome, :reddito ; /*prelevo i dati*/
while (sqlca.sqlcode == 0)
{
printf(“Qual è l’aumento per %s? ”, nome);
scanf(“%d”,&aumento);
EXEC SQL UPDATE Impiegato SET REDDITO = REDDITO + :aumento
WHERE CURRENT OF P; /*eseguo l’update su current*/
EXEC SQL FETCH P INTO :nome, :reddito; ; /*prelevo i dati per la tupla
immediatamente successiva*/
}
EXEC SQL CLOSE CURSOR P;
} …..
17
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
DYNAMIC SQL
•
Non sempre le istruzioni SQL sono note quando si scrive il codice ospite (si
pensi, ad esempio, ad un’applicazione che interagisce con utenti). E’ stata
introdotta una tecnica completamente diversa, chiamata SQL dinamico, che
permette di generare istruzioni SQL a tempo di esecuzione (addirittura
ricevute dal programma attraverso parametri o da input). Ovviamente, la
tecnica porta ad un degrado delle prestazioni.
•
Le operazioni in SQL dinamico possono essere eseguite immediatamente
oppure prima “preparate” e poi eseguite (anche più volte)
•
La differenza è che, in SQL statico, i nomi delle relazioni e degli attributi
coinvolte nelle istruzioni SQL sono fissate a priori; l’unica parte delle
istruzioni che può rimanere non nota a tempo di compilazione è costituita
dagli specifici valori da ricercare o da aggiornare. Nell’SQL dinamico,
invece, le istruzioni SQL sono generate a tempo di esecuzione; non è quindi
necessario specificare relazioni e attributi coinvolti in una istruzione SQL
prima della sua esecuzione.
18
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
MODALITÀ DI ESECUZIONE
Le operazioni SQL possono essere:
•
•
Esempio:
Char *
Con esecuzione immediata (in assenza di parametri)
SQL_string=“delete from Impiegato
execute immediate SQLStatement
where CF=27”;
…
exec sql execute immediate :
SQL_string;
Con esecuzione differita e ripetibile (anche con parametri):
prepare CommandName from SQLStatement
execute CommandName [into TargetList][using ParameterList]
Esempio:
Prepare :trova_nome From “select nome
from Impiegato
where CF =?”
Execute :trova_nome into:nome_imp using :cf_imp
Rilascio del comando: Deallocate prepare :trova_nome
19
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
SQL Embedded VS CLI
SQL embedded
• pemette precompilazione (e quindi efficienza e portabilità del
sorgente)
• uso di SQL completo
• Molto usato in applicazioni legacy
CLI
• indipendente dal DBMS, uso di funzioni di interazione con i DBMS
attraverso Application Programming Interfaces (API)
• permette di accedere a più basi di dati, anche eterogenee (senza
ricompilazione: portabilità dell’eseguibile): richiede un ulteriore
strato di comunicazione
 esistono drivers specifici per i vari DBMS
20
Utilizzare SQL nelle applicazioni
E. Di Sciascio
Sistemi Informativi
DEE - Politecnico di Bari
STORED PROCEDURES
A partire da SQL-2 è possibile definire procedure dette stored procedures per il fatto che
vengono memorizzate all’interno della base di dati come parti dello schema.
Le procedure permettono di associare un nome ad una o più istruzioni SQL, con la possibilità di
specificare dei parametri da utilizzare per lo scambio di informazioni con la procedura. I vantaggi
sono: minor quantità di dati trasferita tra client e server, una più facile manutenibiltà, favorisce il
riutilizzo della logica dell’applicazione da parte di utenti diversi e la possibilità di ottenere in diversi
casi un sensibile incremento delle prestazioni. In tal modo però la logica di business risulta in
gran parte inglobata nella base di dati.
Una volta che la procedura è definita, essa è utilizzabile come se facesse parte dell’insieme dei
comandi SQL predefiniti. La procedura può essere invocata avendo cura di associare un valore ai
Parametri.
procedure Update_address(:cod_dip int, :Addr varchar(60))
Begin;
update Department set Address =:Addr where ID_dip =:cod_dip
End;
exec sql Update_address(:codice,:indirizzo);
Definizione
Chiamata
Molti sistemi, e.g Oracle PL/SQL, presentano estensioni ricche e più complesse (ad es. costrutti
if – else, cicli while) che rendono SQL un linguaggio computazionalmente completo ossia con lo
stesso potere espressivo di un normale linguaggio di programmazione.
21
Utilizzare SQL nelle applicazioni
E. Di Sciascio