Aplicações em Bases de Dados

Bases de Dados II
6638: BSc in Information Systems and Technologies
Cap. 6 — Desenvolvimento de Aplicações em Bases de Dados
6638 Bases de Dados II
Sumário
• 
SQL em aplicações
• 
Embedded SQL
• 
Cursores
• 
Dynamic SQL
• 
JDBC
• 
SQLJ
• 
ODBC
• 
Procedimentos (stored procedures)
Aplicações em Bases de Dados
6638 Bases de Dados II
Aplicações em Bases de Dados
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.
6638 Bases de Dados II
Aplicações em Bases 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.
6638 Bases de Dados II
Aplicações em Bases de Dados
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.
6638 Bases de Dados II
Aplicações em Bases de Dados
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.
6638 Bases de Dados II
Aplicações em Bases de Dados
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
6638 Bases de Dados II
Aplicações em Bases de Dados
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;
§ Two special “error” variables:
•  SQLCODE (long, is negative if an
error has occurred)
•  SQLSTATE (char[6], predefined codes
for common errors)
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long c_sid;
short c_rating;
float c_age;
EXEC SQL END DECLARE SECTION
6638 Bases de Dados II
Aplicações em Bases de Dados
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;
§ Two special “error” variables:
•  SQLCODE (long, is negative if an
error has occurred)
•  SQLSTATE (char[6], predefined codes
for common errors)
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long c_sid;
short c_rating;
float c_age;
EXEC SQL END DECLARE SECTION
6638 Bases de Dados II
Aplicações em Bases de Dados
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.
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
• 
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?
6638 Bases de Dados II
Aplicações em Bases de Dados
Embedded SQL: exemplo em C
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\n”,c_sname,c_age);
} while (SQLSTATE != ‘02000’);
EXEC SQL CLOSE sinfo;
6638 Bases de Dados II
Aplicações em Bases de Dados
Embedded SQL: exemplo completo em C
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
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
char
short
char
char
EXEC SQL
BEGIN DECLARE SECTION;
pname[10];
dept;
userid[9];
passwd[19];
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;
if (SQLCODE != 0) break;
(3)
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 */
6638 Bases de Dados II
Aplicações em Bases de Dados
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.
6638 Bases de Dados II
Aplicações em Bases de Dados
Arquitectura JDBC
Java Application
• 
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)
– 
JDBC driver manager
①
JDBC/native
bridge
Native driver
(DBMS specific)
②
JDBC/OBDC
bridge
③
JDBC driver
(DBMS specific)
④
JDBC middleware
(various DBMS)
OBDC
driver
Data source (processa instruções
SQL)
DBMS
① 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.
6638 Bases de Dados II
Aplicações em Bases de Dados
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”);
Ligação ao data source:
–  Interagimos com um data source através
de sessões, uma sessão por ligação.
–  JDBC URL:
jdbc:<subprotocol>:<otherParameters>
Exemplo:
String url=“jdbc:oracle:www.bookstore.com:
3083”;
Connection con;
try{ con =
DriverManager.getConnection(url,usedId,
password);
} catch SQLException excpt { …}
–  Quando se inicia uma aplicação Java:
Djdbc.drivers=oracle/jdbc.driver
• 
Todos os drivers são geridos pela classe
DriverManager
• 
Execução de uma instrução SQL:
–  Statement (both static and dynamic
SQL statements)
–  PreparedStatement (semi-static
SQL statements)
–  CallableStatment (stored
procedures) DriverManager
6638 Bases de Dados II
Aplicações em Bases de Dados
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();
Só devolve o nº de records afectados
pstmt.setInt(1,sid);
pstmt.setString(2,sname);
Devolve dados encapsulados num
pstmt.setInt(3, rating);
objecto ResultSet (um cursor)
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 cursor
Um ResultSet é um cursor:
while (rs.next()) {
- previous(): move para linha anterior
// process the data
- absolute(int n): move para linha n
}
- relative (int num): move para frente e para trás
- first() e last()
6638 Bases de Dados II
Aplicações em Bases de Dados
Correspondência entre tipos de dados em SQL e Java
SQL Type
Java class
ResultSet get method
BIT
Boolean
getBoolean()
CHAR
String
getString()
VARCHAR
String
getString()
DOUBLE
Double
getDouble()
FLOAT
Double
getDouble()
INTEGER
Integer
getInt()
REAL
Double
getFloat()
DATE
java.sql.Date
getDate()
TIME
java.sql.Time
getTime()
TIMESTAMP
java.sql.TimeStamp
getTimestamp()
6638 Bases de Dados II
Aplicações em Bases de Dados
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 needed
Class.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<= md.getColumnCount(); i++) {
System.out.print(rs.getString(i) + " | ");
}
}
rs.close();
} catch(Exception e) { e.printStackTrace();}
}
};
6638 Bases de Dados II
Aplicações em Bases de Dados
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.
6638 Bases de Dados II
Aplicações em Bases de Dados
Exemplo: C/MySQL
#include<windows.h>
#include <mysql.h>
#include <stdio.h>
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);
}
6638 Bases de Dados II
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);
}
Aplicações em Bases de Dados
6638 Bases de Dados II
Aplicações em Bases de Dados
Sumário:
• 
Definição de objectivos.
• 
Conceitos básicos: base de dados e DBMS. • 
Data Manipulation Language (DML).
• 
Desenho de bases de dados.
• 
Structured Query Language (SQL).
• 
Arquitectura ANSI-SPARC.
• 
Linguagens 4G.
• 
Esquemas e instâncias.
• 
Modelos de dados.
• 
Independência de dados.
• 
Funções/serviços dum DBMS.
• 
Componentes dum DBMS.
• 
Topologias da arquitectura dum DBMS
multi-utilizador.
• 
Data Definition Language (DDL).