Bases de Dados II 6638: BSc in Information Systems and Technologies. Cap. 2 Desenvolvimento de Aplicações em Bases de Dados. Module Introduction

(1)

Module Introduction

6638: BSc in Information Systems and Technologies

(2)

Sumário

•

• SQL em aplicaç

SQL em aplicaç

ões

•

• Embedded

Embedded

SQL

•

• Cursores

Cursores

•

• Dynamic

Dynamic

SQL

•

• JDBC

JDBC

•

• SQLJ

SQLJ

•

• ODBC

ODBC

•

(3)

Porquê aceder a bases de dados através de linguagens de

programação de alto-nível?

•

• SQL

SQL

é uma linguagem

de inquiriç

ões (

queries

)

; como tal, tem limitaç

ões

.

•

• A utilizaç

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.

(4)

SQL em aplicações

•

• As instruç

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 (

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.

(5)

Características desejáveis em soluções ou aplicações de bases

de dados

•

• Facilidade de utilizaç

Facilidade de utilizaç

ão

•

• Estandartizaç

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

Interoperabilidade

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

(6)

Soluções proprietárias

(soluções dependentes do proprietário de DBMS)

•

• Exemplo:

Exemplo:

Oracle PL/SQL

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

•

• Vantagens:

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:

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.

(7)

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

•

• H

H

á 3 estratégias básicas que podemos considerar:

– Embeber SQL num programa em linguagem hospedeira (Embedded SQL, SQLJ)

– SQL modules

(8)

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

•

• Estrat

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ç

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 SECTION char 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)

(9)

Embedded SQL: example

•

• Estrat

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ç

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;

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)

(10)

Embedded SQL:

cursores

•

• Podemos declarar um cursor sobre uma

Podemos declarar um cursor sobre uma

relaç

ão (tabela) ou inquirição (que produz

uma relação)

:

•

• Podemos abrir (

Podemos abrir (

open) um cursor e,

open

) um cursor e,

repetidamente, procurar (

fetch) um tuplo e

fetch

) um tuplo e

mover (

move) o cursor at

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

Podemos modificar/eliminar o tuplo

apontado pelo cursor.

•

• Exemplo

Exemplo

:

Usar um cursor para obter,

por ordem alfab

por ordem alfabética,

ética, os marinheiros que

os marinheiros que

reservaram um barco vermelho

.

•

• Note-se que

Note-se que

é ilegal substituir

S.sname

por, diga-se,

S.sid

na

cl

áusula

ORDER BY. (Porqu

ê

?)

•

• Podemos adicionar

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

(11)

Embedded SQL:

exemplo em C

char SQLSTATE[6];

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’);

(12)

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 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;

}

(13)

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

•

• Estrat

Estrat

égia:

– Em vez de usarmos pré-processamento nas chamadas à SQL, usamos bibliotecas de procedimentos.

•

• Interface estandartizada especial:

Interface estandartizada especial:

procedimentos/objectos

•

• Passa-se

Passa-se

strings

SQL a partir da linguagem,

resultando da

í

result sets

apresentados

numa forma amig

ável à linguagem de alto

nível

•

• É suposto ser

É 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

Example

:

– Sun’s JDBC: Java API

– Parte do java.sql package

•

• Vantagens

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

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.

(14)

Arquitectura JDBC

•

• Tem 4 componentes:

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.

(15)

Submissão duma inquirição SQL via JDBC

•

• Tem 3 passos essenciais:

Tem 3 passos essenciais:

– Carregamento do driver JDBC – Ligação ao data source

– Execução de instrução SQL

•

• Carregamento dum driver JDBC

Carregamento dum driver JDBC

:

– Em código Java:

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

– Quando se inicia uma aplicação Java:

Djdbc.drivers=oracle/jdbc.driver

•

• Todos os drivers s

Todos os drivers s

ão geridos pela classe

DriverManager

•

• Ligaç

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: Exemplo: String url=“jdbc:oracle:www.bookstore.com:3083”; Connection con; try{ con = DriverManager.getConnection(url,usedId, password);

} catch SQLException excpt { …}

•

• Execuç

Execuç

ão de uma instrução SQL

:

– Statement (both static and dynamic SQL statements)

– PreparedStatement (semi-static SQL statements)

– CallableStatment (stored procedures) DriverManager

(16)

Execução duma instrução SQL:

PreparedStatement

•

• PreparedStatement

PreparedStatement

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

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

•

• Exemplo:

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 cursor

while (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()

(17)

Correspondência entre tipos de dados em SQL e Java

getTimestamp() java.sql.TimeStamp TIMESTAMP getTime() java.sql.Time TIME getDate() java.sql.Date DATE getFloat() Double REAL getInt() Integer INTEGER getDouble() Double FLOAT getDouble() Double DOUBLE getString() String VARCHAR getString() String CHAR getBoolean() Boolean BIT

ResultSet get method Java class

(18)

Exemplo (semi-completo) em Java/SQL

import java.

import java.sqlsql.*;.*;

/* This is a sample program with

/* This is a sample program with jdbc odbc jdbc odbc Driver */Driver */ public class

public class localdemo localdemo {{

public static void main(String[]

public static void main(String[] argsargs) {) {

try {

// Register JDBC/ODBC Driver in // Register JDBC/ODBC Driver in jdbc DriverManagerjdbc DriverManager

// On some platforms with some java // On some platforms with some java VMsVMs, , newInstancenewInstance() is needed() is needed Class.

Class.forNameforName("sun.("sun.jdbcjdbc..odbcodbc..JdbcOdbcDriver"JdbcOdbcDriver").).newInstancenewInstance();(); // Test with MS Access database (sailors ODBC data source)

// Test with MS Access database (sailors ODBC data source)

String

String url url = = "jdbc"jdbc::odbcodbc::mysailors"mysailors";; java.

java.sqlsql.Connection c = .Connection c = DriverManagerDriverManager..getConnectiongetConnection((urlurl););

java.

java.sqlsql.Statement .Statement st st = c.= c.createStatementcreateStatement();(); java.

java.sqlsql..ResultSet rs ResultSet rs = = stst..executeQueryexecuteQuery("select * from Sailors");("select * from Sailors"); java.

java.sqlsql..ResultSetMetaData md ResultSetMetaData md = = rsrs..getMetaDatagetMetaData();(); while(rs.next()) {

while(rs.next()) {

System.out.print("\nTUPLE: | ");

for(int i=1; i<= md.getColumnCount(); i++) {

System.out.print(rs.getString(i) + " | "); System.out.print(rs.getString(i) + " | "); } } } } rs.close(); rs.close(); } catch(Exception e) { e.printStackTrace();} } catch(Exception e) { e.printStackTrace();} } } }; };

(19)

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

•

• Estrat

Estrat

égia:

Uma

call-level interface

fornece

uma biblioteca de funç

ões para aceder a

vários DBMS

.

•

• Os drivers dos DBMS s

Os drivers dos DBMS s

ão armazenados

separadamente; portanto, a biblioteca

usada pela linguagem de programação é

independente do DBMS

.

•

• As funç

As funç

ões da linguagem de programação

fornecem somente uma interface para os

drivers dos DBMS.

•

• Vantagens

Vantagens

:

– O ambiente de desenvolvimento não está dependente dum DBMS particular, nem do sistema operativo.

•

• Desvantagens:

Desvantagens

:

– Algumas optimizações de baixo-nível podem ser mais difíceis ou mesmo impossíveis de fazer.

•

• ODBC (

ODBC (

Open Database Connectivity)

Open Database Connectivity

)

:

– É um método estandartizado de acesso a bases de dados.

•

• Objectivo: tornar poss

Objectivo: tornar poss

ível o acesso a

quaisquer dados a partir de qualquer

aplicação, independentemente do DBMS

.

•

• ODBC consegue este objectivo pela

ODBC consegue este objectivo pela

inserç

ão duma camada intermédia,

designada por

database

driver,

entre a

aplicaç

ão e o DBMS

.

•

• O prop

O prop

ósito desta camada é traduzir

inquirições lançadas pela aplicação em

comandos que o DBMS entende

.

•

• Para isso, quer a aplicaç

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.

(20)

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); }

(21)

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);