Basi di Dati e Sistemi Informativi

Basi di Dati e SistemiInformativi SQL per Applicazioni Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/

SQL per Applicazioni • L’accessotipico ad una base di datiavvienemedianteapplicazioni integrate nelsistemainformativo. • Integrazione di SQL in linguaggi di programmazione ad alto livello. • Integrazione di SQL in applicazioni/interfacce Web (Web Information System, WIS)

SQL per Applicazioni • L’integrazione del linguaggio SQL con inormalilinguaggi di programmazione di alto livello(C, C++, Java, etc) presentaalcuniostacoli. • Differenze di sintassitrailinguaggi. • Differenzenelmeccanismo di gestionedeidati (SQL e’ set-oriented, mentreilinguaggi di programmazionesono per lo piu’ tuple-oriented).

SQL per Applicazioni • Due soluzioniper consentirel’uso di SQL all’interno di un programmascritto in un linguaggio di programmazione: • SQL Embedded  ilprogrammasorgentecontienecodicemisto(es. SQL e Java) • Call Level Interface (CLI)  ilprogrammasorgentecontienechiamate ad unalibreria per la gestionedeidati(es. JDBC).

SQL per Applicazioni • SQL Embedded Il programmacontieneistruzioni SQL, distintedalleistruzioni del linguaggiosorgenteattraversol’uso di separatori (exec sqle ;) • In fase di compilazione, ilpreprocessorericonosce le istruzioni SQL e le sostituisce con opportune chiamate di libreria del DBMS.

SQL per Applicazioni main() { inti; … exec sql insert into Impiegati values(“Marco”,20); … } PREPROCESSORE CHIAMATA LIBRERIA LIBRERIA ACCESSO DBMS DBMS

SQL per Applicazioni • Il preprocessoreesegueiseguentitask: • Verifica la sintassidel codice SQL • Sostituisceilcodice SQL con le rispettivechiamate di libreria del DBMS. • Necessario un preprocessorespecificoper unaspecificacombinazioneDMBS-linguaggio-piattaforma(es. Oracle/C/Linux).

SQL per Applicazioni #include<stdlib.h> main(){ execsqlbegindeclaresection; char *NomeDip = "Manutenzione"; char *CittaDip = "Pisa"; intNumeroDip = 20; execsql end declaresection; execsqlconnect to utente@librobd; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; } } C con SQL Embedded

SQL per Applicazioni #include<stdlib.h> main(){ execsqlbegindeclaresection; char *NomeDip = "Manutenzione"; char *CittaDip = "Pisa"; intNumeroDip = 20; execsql end declaresection;execsqlconnect to utente@librobd; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; } } C con SQL Embedded Variabili C condivise

SQL per Applicazioni #include<stdlib.h> main(){ execsqlbegindeclaresection; char *NomeDip = "Manutenzione"; char *CittaDip = "Pisa"; intNumeroDip = 20; execsql end declaresection; execsqlconnect to utente@librobd; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; } } C con SQL Embedded Strutturadaticondivisa

SQL per Applicazioni #include<stdlib.h> main(){ execsqlbegindeclaresection; char *NomeDip = "Manutenzione"; char *CittaDip = "Pisa"; intNumeroDip = 20; execsql end declaresection; execsqlconnect to utente@librobd; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; } } C con SQL Embedded Esecuzionedella query

SQL per Applicazioni CODICE SORGENTE intmain() { execsqlconnect to universita userpguseridentified by pguser; execsql create table studente (matricola integerprimarykey, nome varchar(20), annodicorsointeger); execsqldisconnect; return 0; }

SQL per Applicazioni #include <ecpgtype.h> #include <ecpglib.h> #include <ecpgerrno.h> #include <sqlca.h> intmain() { ECPGconnect(__LINE__, "universita" , "pguser" , "pguser" , NULL, 0); ECPGdo(__LINE__, NULL, "create table studente ( matricola integerprimarykey , nome varchar ( 20 ) , annodicorsointeger )", ECPGt_EOIT, ECPGt_EORT); ECPGdisconnect(__LINE__, "CURRENT"); return 0; } CODICE PRE-COMPILATO

SQL per Applicazioni • Conflittod’impedenzasui dati: • SQL e’ un linguaggioset-oriented, un’interrogazionepuo’ restituire come risultatoun’interatabella(es. SELECT). • I linguaggi di programmazioneaccedonoaglielementi di unastrutturadatiscandendo le righeuna per volta. • SOLUZIONE: Usareicursori …

SQL per Applicazioni Cursore Iteratore per accedereallarighe di unatabellaunaallavolta. BUFFER del PROGRAMMA CODICE SORGENTE CURSORE DBMS SELECT Il cursorepuo’ muoversiavanti/indietrosullerighedellatabella.

SQL per Applicazioni • Definizione del cursore • declareNomeCursore [scroll] cursor for SelectSQLCode • declare NomeCursore scroll cursor for SELECT Cognome, Stipendio • FROM Impiegati • WHERE (Stipendio > 1000)

SQL per Applicazioni • Esecuzionedelleinterogazione • openNomeCursore • Deallocazionedelleinterogazione • closeNomeCursore • Recuperodeirisultati • fetchNomeCursoreinto ListaVariabili

SQL per Applicazioni voidVisualizzaStipendiDipart(charNomeDip[]) { char Nome[20], Cognome[20]; long int Stipendio; $ declareImpDipcursor for select Nome, Cognome, Stipendio from Impiegato whereDipart = :NomeDip; printf("Dipartimento %s\n",NomeDip); $ open ImpDip; $ fetchImpDipinto :Nome, :Cognome, :Stipendio; while (sqlcode == 0) { printf("Attuale stipendio: %d\n",Stipendio); $ fetchImpDipinto :Nome, :Cognome, :Stipendio; } $ closecursorImpDip; }

SQL per Applicazioni SQLJ Tecnica per includerecodice SQL all’interno di programmi Java. import java.sql.*; public class SimpleDemoSQLJ{ public Address getEmployeeAddress(intempno) throws SQLException { Address addr; #sql { SELECT office_addr INTO :addr FROM employees WHERE empnumber = :empno }; return addr; } }

SQL per Applicazioni • Tecnica vista fin qui: Static SQL Embedded. • PROBLEMA: Non sempre le istruzioni SQL da eseguiresono note quandosiscriveilprogramma … • Dynamic SQL Embedded  Tecnicache permette di eseguire istruzioni SQL costruite dal programma (o addirittura ricevute dal programma attraverso parametri o da input).

SQL per Applicazioni • Due soluzioniper consentirel’uso di SQL all’interno di un programmascritto in un linguaggio di programmazione: • SQL Embedded  ilprogrammasorgentecontienecodicemisto(es. SQL e Java) • Call Level Interface (CLI)  ilprogrammasorgentecontienechiamate ad unalibreria per la gestionedeidati(es. JDBC).

SQL per Applicazioni • Nellamodalita’ CLI, illinguaggiosorgenteoffreunalibreriaspecifica per interagire con un DBMS (indipendente dal DBMS). • Si creaunaconnessione con il DBMS. • Si invia un comando SQL con la richiesta. • Si riceve la risposta dal DBMS, e la sigestisce per mezzo di cursori. • Si chiude la connessione con il DBMS. INTERAZIONE

SQL per Applicazioni Sonodisponibilidiverse tecnologie di CLI per piattaforme/linguaggidifferenti…

SQL per Applicazioni • Java Database Connectivity (JDBC)  insieme di librerie Java per accessoaidati di un database relazionale, indipendentidallospecifico DBMS in uso. • JDBC denotaun’interfaccia standard, implementata da classidettedriver. • Un driver e’ specifico di un certo DBMS (es. MySQL Connector/J per MySQL)

SQL per Applicazioni JDBC puo’ essereusato in 4 architetture: Bridge JDBC-ODBC: sirichiamaun driver ODBC, chedeveesseredisponibilesulclient. Driver nativosul client: sirichiamaun componenteproprietario(non necessariamente Java) sulclient. Driver puro Java con server intermedio("middleware server"): sicomunicavia protocollo di rete con il server intermedio, che non deverisiederesulclient. Driver puro Java, con connessione al DBMS: siinteragiscedirettamente con ilDBMS.

SQL per Applicazioni

SQL per Applicazioni • Vantaggi di SQLJ su JDBC: • SQLJ richiedemenocodice. • La sintassidelleistruzioni SQL puo’ esserecontrollatadurante la compilazione • Vantaggi di JDBC su SQLJ: • SQLJ richiede un preprocessore, non sempredisponibilesututte le piattaforme/DBMS.

SQL per Applicazioni • Le API di JDBC (java.sql) consentono di: • Creare la connessionecon un DBMS (supponendo di aver gia’ installatoi driver). • Eseguirequery SQL sul DBMS. • Processareilrisultatodella query SQL. • Gestiretransazioniedoperazioni batch.

SQL per Applicazioni • Per creareunaconnessione, sonodisponibili le seguenticlassi: • Driver interfaccia di collegamento verso il DBMS (implementato da ogni DBMS). • DriverManager servizio di base per la gestionedei drivers attualmentepresenti. • Connection  sessione di connessione ad unospecifico DBMS, il cui driver e’ gestito dal DriverManager.

SQL per Applicazioni • Supponendo di avereidriver del DBMS (es. MySQL) installatisulproprio host, ilpassosuccessivo e’ creare la connessione dal DriverManager. • static Connection getConnection(String url, String user, String password) • url URL del DB, tipo e nome. • user, password  credenzialid’accesso.

SQL per Applicazioni Supponendo di avereidriver del DBMS (es. MySQL) installatisulproprio host, ilpassosuccessivo e’ creare la connessione dal DriverManager. String url=“jdbc:mysql://localhost:3306/provadb”; String user=“root”; String password=“root”; Connection con=DriverManager.getConnection(url, user, password);

SQL per Applicazioni • Per eseguireuna query e processarneilrisultato, sonodisponibili le seguenticlassi: • Connection  sessione di connessione ad unospecifico DBMS, il cui driver e’ gestito dal DriverManager. • Statement  componenteusata per eseguireuna query SQL suunaConnection. • ResultSet  tabella di datichecontieneilrisultato di esecuzione di unoStatement.

SQL per Applicazioni Unavoltacreata la Connection, e’ possibilecreareunoStatementassociato, e quindieseguireuna querysul DBMS. Connection con=DriverManager.getConnection(url, user, password); Statement state= con.createStatement(); String sql=“ SELECT * FROM IMPIEGATI”; ResultSetrs=state.executeQuery(sql);

SQL per Applicazioni • Per gestireilrisultato di una query, siutilizza la classeResultSet tabella di datigeneratadall’esecuzione di una query SQL + cursore. • ResultSetmantiene un puntatoreallarigacorrentedellatabella. • Tramiteilmetodonext() e’ possibilespostarsiallarigasuccessiva; next()ritornafalsoquando non ci sonopiu’ righenelResultSet.

SQL per Applicazioni • Data unariga del ResultSet, sipossonoutilizzaremetoditipizzatiper accedere ad unaspecificacolonna, conoscendonel’indice: • String getString(intcolumnIndex) • booleangetBoolean(intcolumnIndex) • intgetInt(intcolumnIndex) • float getFloat(intcolumnIndex) • Date getDate(intcolumnIndex) • …

SQL per Applicazioni Data unariga del ResultSet, sipossonoutilizzaremetoditipizzatiper accedere ad unaspecificacolonna, conoscendonel’indice: Statement st=con.createStatement(); ResultSetrs=st.executeQuery(“SELECT NOME, STIPENDIO FROM IMPIEGATI”); while(rs.next) { System.out.println(rs.getString(1)); System.out.println(rs.getInt()); }

SQL per Applicazioni • Eseguireuna query senza un controllopreventivo sui parametrifornitidall’utentepuo’ esserepericoloso dal punto di vista dellasicurezza (es. SQL Injection); per questo, sipreferisce: • Preparare lo Statementinserendodeiplaceholder al postodeiparametridella query. • Fornireivalorideiparametri, edeseguire la query (in manierasicura).

SQL per Applicazioni Esempio di esecuzionedi una query SQL mediantePreparedStatement (anziche’ Statement). PreparedStatementpst; String author=“Michele”; pst=con.PreparedStatement(“INSERT INTO IMPIEGATI(Nome) VALUES(?)”); pst.setString(1, author); pst.executeUpdate();

SQL per Applicazioni • In Java JDBC, unatransazioneindica un insieme di comandi SQL cui puo’ essereassociataunamodalita’ di esecuzione del “tutto o niente”, ossia: • Tuttiicomandidellatransazionesonostatieseguiticorrettamente, OPPURE: • In caso di errori, e’ possibile fare rollbackdell’esecuzione di tutticomandidellatransazione.

SQL per Applicazioni • Per costruireunatransazione in JDBC: • Si costruiscono le query, e sieseguonomedianteexecuteUpdate() o executeQuery(). • Si utilizzailmetodocommit() del Connection per eseguire le operazionisul DBMS in manierapersistente. • Si utilizzailmetodorollback() del Connectionper annullarele operazionisulDBMSeseguitedopo la precedente commit.

SQL per Applicazioni try { st.executeupdate(“UPDATE IMPIEGATO SET Name=“Marco” WHERE Name=“Michele”); st.executeupdate(“UPDATE IMPIEGATO SET Name=“Mario” WHERE Name=“Giovanni”); con.commit(); } catch(SQLException) { con.rollback(); }

SQL per Applicazioni • Per poterlavorare con le transazioni in JDBC, e’ necessarioimpostare a false la modalita’ di autocommitdeicomandi SQL. • Connection con=DriverManager(url. user, password); • con.setAutoCommit(false); • Se siutilizzal’autocommit, non e’ possibile fare rollback delletransazioni …

SQL per Applicazioni • Nelcasosidebbanoeseguireoperazioni di DELETE, UPDATE, INSERT in serie, e’ possibileusareilmeccanismodeibatch update in JDBC: • Le query sonoraggruppate in un unica query, chevieneinviata al DMBS (vantaggi in terminimi di efficienza di esecuzione). • Il batch restituisce un array con ilnumero di righeaffette da ciascuna query.

SQL per Applicazioni • Nelcasosidebbanoeseguireoperazioni di DELETE, UPDATE, INSERT in serie, e’ possibileusareilmeccanismodeibatch update in JDBC: • addBatch(String sql)  aggiungeuna query SQL all’insieme batch. • executeBatch() esegueil batch update, ritorna un array di dimensioneparialle query SQL, con ilnumero di righeaffette da ciascuna.

SQL per Applicazioni st=con.createStatement(); st.addBatch(“DELETE FROM Impiegati”); st.addBatch(“INSERT INTO IMPIEGATI(Nome) VALUES(‘Marco’”); st.addBatch(“INSERT INTO IMPIEGATI(Nome) VALUES(‘Michele’”); int counts=st.executeBatch(); con.commit();

SQL per Applicazioni st=con.createStatement(); st.addBatch(“DELETE FROM Impiegati”); st.addBatch(“INSERT INTO IMPIEGATI(Nome) VALUES(‘Marco’”); st.addBatch(“INSERT INTO IMPIEGATI(Nome) VALUES(‘Michele’”); intcounts=st.executeBatch(); con.commit();

SQL per Applicazioni • In un DBMS, le informazionisulmodellologico di rappresentazione di unatabellasidiconometadati, e sonorappresentati a suavoltatramitetabelle. • Nomi delletabelle • Nomi degliattributi di unatabella • Tipi degliattributi • Vincolirelazionali • … ESEMPI DI METADATI

SQL per Applicazioni In JDBC, e’ possibileaccedere al campo metadati di un ResultSettramiteilmetodogetMetaData(). PreparedStatementpst; pst=con.PreparedStatement(“SELECT Nome, Cognome FROM IMPIEGATI”); ResultSetrs=pst.executeQuery(); ResultSetMetaData meta=pst.getMetaData(); String column1=meta.getColumnName(1); String column2=meta.getColumnName(2);

Basi di Dati e Sistemi Informativi