Bases de Dados II - UBIdi.ubi.pt/~agomes/bd2/teoricas/06-applications.pdfde programação imperativa (e.g. C ou Pascal), embora a biblioteca STL da linguagem C++ já o permita. –

Bases de Dados II 6638: BSc in Information Systems and Technologies

Cap. 6 — Desenvolvimento de Aplicações em Bases de Dados

Aplicações em Bases de Dados6638 Bases de Dados II

Sumário

•  SQL em aplicações

•  Embedded SQL

•  Cursores

•  Dynamic SQL

•  JDBC

•  SQLJ

•  ODBC

•  Procedimentos (stored procedures)


Porquê aceder a bases de dados através de linguagens de programação de alto-nível?•  SQL é uma linguagem de inquirições (queries); como tal, tem limitações.

•  A utilização de linguagens de programação justifica-se por :–  às vezes, ser necessário um processamento mais complexo dos dados;

–  ser necessário construir interfaces especializadas com o utilizador;

–  ser necessário aceder a mais do que uma base de dados.


SQL em aplicações

•  As instruções SQL podem ser invocadas a partir de um programa escrito em linguagem hospedeira (e.g., C++ or Java) .

–  As instruções SQL podem mencionar host variables (incluindo variáveis especiais para devolver estado).

–  Existe uma instrução para ligar à base de dados pretendida.

•  Desajustamento de Tipos de Dados (Impedance Mismatch).

–  As relações (ou tabelas) SQL são (multi-) conjuntos de registos (records), com nenhum limite a priori relativamente ao número de registos.

–  Tradicionalmente, não existe nenhuma estrutura de dados com estas características em linguagens de programação imperativa (e.g. C ou Pascal), embora a biblioteca STL da linguagem C++ já o permita.

–  SQL suporta um mecanismo designado por cursor para manipular estas tabelas.


Características desejáveis em soluções ou aplicações de bases de dados •  Facilidade de utilização

•  Estandartização / Normalização–  Soluções desenvolvidas em conformidade com as normas existentes para linguagens de

programação, linguagens de inquirições de bases de dados, e ambientes de desenvolvimento.

•  Interoperabilidade

–  Tem que ver com a capacidade de usar uma interface comum a diversos sistemas de bases de dados em diferentes sistemas operativos.


Soluções proprietárias ��(soluções dependentes do proprietário de DBMS)•  Exemplo: Oracle PL/SQL

–  linguagem proprietária semelhante à PL/1 que suporta a execução de inquirições SQL.

•  Vantagens:–  Muitas características específicas da Oracle não são suportadas por outros sistemas de base de

dados.

–  Desempenho pode ser optimizado em sistemas baseados na Oracle.

•  Desvantagens:–  As aplicações ficam dependentes dum DBMS específico.

–  O programador de aplicações fica dependente do fornecedor proprietário do DBMS no que respeita ao ambiente de desenvolvimento de aplicações.

–  Pode não estar disponível em todas as plataformas.


Soluções não-proprietárias e baseadas em SQL

•  Há 3 estratégias básicas que podemos considerar:–  Embeber SQL num programa em linguagem hospedeira

(Embedded SQL, SQLJ)

–  SQL modules

–  SQL call level interfaces


1ª Solução não-proprietária: Embedded SQL

•  Estratégia: embeber SQL na linguagem hospedeira.–  Um preprocessador converte as instruções SQL em

chamadas API especiais.

–  Depois, um compilador regular é usado para compilar o código escrito em linguagem hospedeira.

•  Construções essenciais da linguagem:–  Ligação a uma base de dados: ��

EXEC SQL CONNECT

–  Declaração de variáveis: ��EXEC SQL BEGIN (END) DECLARE SECTION

–  Instruções: ��EXEC SQL Statement;

EXEC SQL BEGIN DECLARE SECTIONchar 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)


Embedded SQL: example

•  Estratégia: embeber SQL na linguagem hospedeira.–  Um preprocessador converte as instruções SQL em

chamadas API especiais.

–  Depois, um compilador regular é usado para compilar o código escrito em linguagem hospedeira.

•  Construções essenciais da linguagem:–  Ligação a uma base de dados: ��

EXEC SQL CONNECT

–  Declaração de variáveis: ��EXEC SQL BEGIN (END) DECLARE SECTION

–  Instruções: ��EXEC SQL Statement;

EXEC SQL BEGIN DECLARE SECTIONchar c_sname[20];long c_sid;short c_rating;float c_age;


§ Two special “error” variables:•  SQLCODE (long, is negative if an

error has occurred)•  SQLSTATE (char[6], predefined codes

for common errors)


Embedded SQL: cursores

•  Podemos declarar um cursor sobre uma relação (tabela) ou inquirição (que produz uma relação):

•  Podemos abrir (open) um cursor e, repetidamente, procurar (fetch) um tuplo e mover (move) o cursor até que todos os tuplos tenham sido processados.

–  Podemos usar uma cláusula especial, designada por ORDER BY, em inquirições que são acedidas através de um cursor, para controlar a ordem de processamento dos tuplos.

•  campos na cláusula ORDER BY têm também de aparecer na cláusula SELECT.

–  A cláusula ORDER BY, que ordena os tuplos da tabela resultante, só é permitida no contexto do cursor.

•  Podemos modificar/eliminar o tuplo apontado pelo cursor.

•  Exemplo: Usar um cursor para obter, por ordem alfabética, os marinheiros que reservaram um barco vermelho.

•  Note-se que é ilegal substituir S.sname por, diga-se, S.sid na cláusula ORDER BY. (Porquê?)

•  Podemos adicionar S.sid à cláusula SELECT e substituir S.sname por S.sid na cláusula ORDER BY?

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


Embedded SQL: exemplo em C

char SQLSTATE[6];

EXEC SQL BEGIN DECLARE SECTIONchar c_sname[20]; short c_minrating; float c_age;


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\n”,c_sname,c_age);} while (SQLSTATE != ‘02000’);

EXEC SQL CLOSE sinfo;


Embedded SQL: exemplo completo em C#include #include #include EXEC SQL INCLUDE SQLCA; #define CHECKERR(CE_STR) if (sqlca.sqlcode != 0) {printf("%s failed. Reason %ld\n", CE_STR, sqlca.sqlcode); exit(1); } int main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; char pname[10]; short dept; char userid[9]; char passwd[19]; EXEC SQL END DECLARE SECTION; printf( "Sample C program: OPENFTCH\n" ); if (argc == 1) { EXEC SQL CONNECT TO sample; CHECKERR ("CONNECT TO SAMPLE"); } else if (argc == 3) { strcpy (userid, argv[1]); strcpy (passwd, argv[2]); EXEC SQL CONNECT TO sample USER :userid USING :passwd; CHECKERR ("CONNECT TO SAMPLE"); } else { printf ("\nUSAGE: openftch [userid passwd]\n\n"); return 1; } /* endif */ EXEC SQL DECLARE c1 CURSOR FOR (1) SELECT name, dept FROM staff WHERE job='Mgr' FOR UPDATE OF job;

EXEC SQL OPEN c1; (2) CHECKERR ("OPEN CURSOR"); do { EXEC SQL FETCH c1 INTO :pname, :dept; (3) if (SQLCODE != 0) break; if (dept > 40) { printf( "%-10.10s in dept. %2d will be demoted to Clerk\n", pname, dept ); EXEC SQL UPDATE staff SET job = 'Clerk' (4) WHERE CURRENT OF c1; CHECKERR ("UPDATE STAFF"); } else { printf ("%-10.10s in dept. %2d will be DELETED!\n", pname, dept); EXEC SQL DELETE FROM staff WHERE CURRENT OF c1; CHECKERR ("DELETE"); } /* endif */ } while ( 1 ); EXEC SQL CLOSE c1; (5) CHECKERR ("CLOSE CURSOR"); EXEC SQL ROLLBACK; CHECKERR ("ROLLBACK"); printf( "\nOn second thought -- changes rolled back.\n" ); EXEC SQL CONNECT RESET; CHECKERR ("CONNECT RESET"); return 0;}/* end of program : OPENFTCH.SQC */


2ª Solução não-proprietária: SQL modules

•  Estratégia: –  Em vez de usarmos pré-processamento nas

chamadas à SQL, usamos bibliotecas de procedimentos.

•  Interface estandartizada especial: procedimentos/objectos

•  Passa-se strings SQL a partir da linguagem, resultando daí result sets apresentados numa forma amigável à linguagem de alto nível

•  É suposto ser neutral em relação ao DBMS–  um “driver” intercepta as chamadas SQL e

traduz as respectivas instruções em código específico do DBMS

–  A base de dados pode estar situada numa rede de computadores

•  Example:–  Sun’s JDBC: Java API

–  Parte do java.sql package

•  Vantagens sobre o Embedded SQL:–  Clara separação entre o código SQL e a

linguagem hospedeira.

–  Debugging (depuração) é muito mais fácil visto não haver qualquer pré-processador envolvido.

•  Desvantagens:–  As bibliotecas de módulos são específicas

da linguagem de alto-nível e do IDE. Portanto, a portabilidade está, à partida, comprometida.


Arquitectura JDBC

•  Tem 4 componentes:–  Aplicação (inicializa e termina

ligações; submete instruções SQL)

–  Gestor de drivers (carrega JDBC driver)

–  Driver (permite a ligação ao data source; transmite pedidos e devolve/traduz resultados e códigos de erro)

–  Data source (processa instruções SQL)

Java Application

JDBC driver manager

JDBC/native bridge

JDBC/OBDC bridge

JDBC driver (DBMS specific)

JDBC middleware (various DBMS)

DBMS

Native driver (DBMS specific)

OBDC driver

①

① Translates JDBC function calls (with SQL commands) to native API of data source. Need OS-specific binary on each client.② Translates JDBC calls (with SQL commands) into non-native API. Example: JDBC-ODBC bridge. Code for ODBC and JDBC driver needs to be available on each client.③ Converts JDBC calls directly to network protocol used by DBMS. Needs DBMS-specific Java driver at each client.④The JDBC driver talks over a network to a middleware server that translates JDBC requests into DBMS-specific method invocations, i.e. the middleware server talks to the data source. Needs only small JDBC driver at each client.

② ③ ④


Submissão duma inquirição SQL via JDBC

•  Tem 3 passos essenciais:–  Carregamento do driver JDBC

–  Ligação ao data source

–  Execução de instrução SQL

•  Carregamento dum driver JDBC:–  Em código Java: ��

Class.forName(“oracle/jdbc.driver.Oracledriver”);

–  Quando se inicia uma aplicação Java: ��Djdbc.drivers=oracle/jdbc.driver

•  Todos os drivers são geridos pela classe DriverManager

•  Ligação ao data source:–  Interagimos com um data source através

de sessões, uma sessão por ligação.

–  JDBC URL: ��jdbc::

Exemplo:

String url=“jdbc:oracle:www.bookstore.com:3083”;

Connection con;

try{ con = DriverManager.getConnection(url,usedId, password);

} catch SQLException excpt { …}

•  Execução de uma instrução SQL:–  Statement (both static and dynamic

SQL statements)

–  PreparedStatement (semi-static SQL statements)

–  CallableStatment (stored procedures) DriverManager


Execução duma instrução SQL: PreparedStatement

•  PreparedStatement–  Instruções SQL pré-compiladas e parametrizadas:

•  Estrutura fixa•  Valores dos parâmetros são determinados em run-time

•  Exemplo:String sql =“INSERT INTO Sailors VALUES(?,?,?,?)”;PreparedStatement pstmt = con.prepareStatement(sql);pstmt.clearParameters();pstmt.setInt(1,sid);pstmt.setString(2,sname);pstmt.setInt(3, rating);pstmt.setFloat(4,age);//we know no. rows are returned, thus we use executeUpdate()// int numRows = pstmt.executeUpdate();ResultSet rs=pstmt.executeQuery(sql); // rs is now a cursorwhile (rs.next()) { // process the data}

Só devolve o nº de records afectados

Devolve dados encapsulados num objecto ResultSet (um cursor)

Um ResultSet é um cursor: - previous(): move para linha anterior - absolute(int n): move para linha n - relative (int num): move para frente e para trás - first() e last()


Correspondência entre tipos de dados em SQL e Java

getTimestamp()java.sql.TimeStampTIMESTAMP

getTime()java.sql.TimeTIME

getDate()java.sql.DateDATE

getFloat()DoubleREAL

getInt()IntegerINTEGER

getDouble()DoubleFLOAT

getDouble()DoubleDOUBLE

getString()StringVARCHAR

getString()StringCHAR

getBoolean()BooleanBIT

ResultSet get methodJava classSQL Type


Exemplo (semi-completo) em Java/SQL

import java.sql.*;/* This is a sample program with jdbc odbc Driver */

public class localdemo { public static void main(String[] args) {

try { // Register JDBC/ODBC Driver in jdbc DriverManager

// On some platforms with some java VMs, newInstance() is neededClass.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

// Test with MS Access database (sailors ODBC data source) String url = "jdbc:odbc:mysailors";

java.sql.Connection c = DriverManager.getConnection(url);java.sql.Statement st = c.createStatement();

java.sql.ResultSet rs = st.executeQuery("select * from Sailors");java.sql.ResultSetMetaData md = rs.getMetaData();

while(rs.next()) { System.out.print("\nTUPLE: | ");

for(int i=1; i


3ª Solução não-proprietária: SQL call-level interfaces

•  Estratégia: Uma call-level interface fornece uma biblioteca de funções para aceder a vários DBMS.

•  Os drivers dos DBMS são armazenados separadamente; portanto, a biblioteca usada pela linguagem de programação é independente do DBMS.

•  As funções da linguagem de programação fornecem somente uma interface para os drivers dos DBMS.

•  Vantagens:–  O ambiente de desenvolvimento não está

dependente dum DBMS particular, nem do sistema operativo.

•  Desvantagens:–  Algumas optimizações de baixo-nível

podem ser mais difíceis ou mesmo impossíveis de fazer.

•  ODBC (Open Database Connectivity):–  É um método estandartizado de acesso a

bases de dados.

•  Objectivo: tornar possível o acesso a quaisquer dados a partir de qualquer aplicação, independentemente do DBMS.

•  ODBC consegue este objectivo pela inserção duma camada intermédia, designada por database driver, entre a aplicação e o DBMS.

•  O propósito desta camada é traduzir inquirições lançadas pela aplicação em comandos que o DBMS entende.

•  Para isso, quer a aplicação quer o DBMS têm de ser both the application and the DBMS must be ODBC-compatíveis, i.e. a aplicação tem de ser capaz de emitir comandos ODBC e o DBMS tem de ser capaz de responder-lhes.


Exemplo: C/MySQL

#include#include #include

int main() { MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row;

//char *server = "mysql-server.ucl.ac.uk"; char *server = "localhost"; char *user = "root"; char *password = "gggag"; char *database = "sakila";

conn = mysql_init(NULL);

/* Connect to database */ if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) { fprintf(stderr, "%s\n", mysql_error(conn)); return(0); }

/* send SQL query */ if (mysql_query(conn, "SELECT * FROM people WHERE age > 30")) { fprintf(stderr, "%s\n", mysql_error(conn)); return(0); }


Exemplo: C/MySQL��(cont.) res = mysql_use_result(conn); /* output fields 1 and 2 of each row */ while ((row = mysql_fetch_row(res)) != NULL) printf("%s %s\n", row[1], row[2]);

/* Release memory used to store results and close connection */ mysql_free_result(res); mysql_close(conn);}


Sumário:

•  Definição de objectivos.

•  Conceitos básicos: base de dados e DBMS.

•  Desenho de bases de dados.

•  Arquitectura ANSI-SPARC.

•  Esquemas e instâncias.

•  Independência de dados.

•  Data Definition Language (DDL).

•  Data Manipulation Language (DML).

•  Structured Query Language (SQL).

•  Linguagens 4G.

•  Modelos de dados.

•  Funções/serviços dum DBMS.

•  Componentes dum DBMS.

•  Topologias da arquitectura dum DBMS multi-utilizador.

Documents

Bases de Dados II - UBIdi.ubi.pt/~agomes/bd2/teoricas/06-applications.pdfde programação imperativa (e.g. C ou Pascal), embora a biblioteca STL da linguagem C++ já o permita. –