Execução de Instruções SQL - DI PUC-Rio

Bancos de Dados III
Acesso a SGBDs
via Aplicação
Rogério Costa
[email protected]
1
Execução de Instruções SQL
Instruções SQL dentro de um programa
aplicativo, em conjunto com seu código. SQL
Embutido
Comandos podem ser estáticos (todo o
comando está escrito e não é modificado em
tempo de execução) ou dinâmicos (partes do
comando SQL podem ser alteradas em tempo
de execução).
2
1
SQL Embutido (Embedded SQL)
Permite acesso a bases e dados SQL, via outra linguagens de
programação.
Código embutido
Acesso e manipulação da base de dados
sistema de bases de dados.
Processamento associado
A linguagem host recebe os resultados e manipula-os.
O código tem que ser pré-processado. A parte SQL é
transformada em código da linguagem host, mais chamadas
a run-time do servidor.
3
SQL Embutido (Embedded SQL)
Código tem que ser pré-processado. A parte
SQL é transformada em código da linguagem
host, mais chamadas a run-time do servidor.
Exemplo: Pro*C
4
2
SQL Embutido (Embedded SQL)
A expressão EXEC SQL é usado para
identificar código SQL embutido
EXEC SQL <embedded SQL statement >
END-EXEC
Nota: Este formato varia de linguagem para
linguagem. E.g. em C usa-se ‘;’ em vez do
END-EXEC. Em Java usa-se # SQL { …. } ;
5
SQL Embutido (Embedded SQL)
Para executar um comando SQL numa linguagem host é
necessário começar por declarar um cursor para esse
comando.
O comando pode conter variáveis da linguagem host,
precedidas de :
E.g. Encontrar os nome e cidades de clientes cujo saldo seja
superior a amount
EXEC SQL
declare c cursor for
select customer-name, customer-city
from account natural inner join depositor
natural inner join customer
where account.balance > :amount
END-EXEC
6
3
Programas com SQL
Embutidos
É preciso que o programa possa:
invocar operações SQL passando
parâmetros;
recuperar os resultados de consultas em
variáveis do programa;
detectar exceções produzidas pela
avaliação remota dos comandos SQL
7
Programas com SQL Embutidos
O comando open inicia a avaliação da consulta no cursor
EXEC SQL open c END-EXEC
O comando fetch coloca o valor de um tuplo em variáveis
da linguagem host.
EXEC SQL fetch c into :cn, :cc END-EXEC
Chamadas sucessivas a fetch obtêm tuplos sucessivos
O comando close apaga a relação temporária, criada pelo
open, que contem os resultados da avaliação do SQL.
EXEC SQL close c END-EXEC
8
4
Programas com SQL Embutidos
Em Java -> Exemplo: SQLJ: Embedded SQL
for Java
String dBuser;
#sql { select user into :dBuser from dual };
System.out.println("Database username is " +
dBuser);
9
Acesso via interfaces
padrão
10
5
SGBD Relacional
Os dados ficam armazenados em SGBDs
segundo alguma organização ou modelo.
Modelo mais utilizado => relacional.
Uma das vantagens dos SGBDs relacionais
foi a utilização da linguagem SQL.
Padronização no acesso aos dados.
Mas os fornecedores implementaram variações
da linguagem, da forma de acesso ao SGBD...
11
Interface Padronizada para Acesso
a Banco de Dados
1989
Fornecedores de SGBDs associaram-se através
da SAG ( SQL Access Group) para produzir
especificações de uma interface para uso comum
no acesso aos dados gerenciados pelos diferentes
produtos baseados em SQL.
12
6
Interface Padronizada para Acesso
a Banco de Dados
A SGA se concentrou na especificação de uma
interface para SQL de chamada de função (SQL
Call-Level Interface – SQL CLI)
Objetivo SQL CLI
Simplificar o uso da linguagem SQL para processamento
dos comandos embutidos
Evitar o uso de pré-compiladores para processar os comandos
embutidos e
Viabilizar a emissão de comandos diretamente pelos programas
de aplicação
13
Interface Padronizada para
Acesso a Banco de Dados
Padrão atual da SQL/Cli
ISO/IEC 9075-3:2003
Implementações podem ter diferentes níveis de
conformidade com o padrão
Objetivo de uma interface genérica de programação
(API) padronizada
Desenvolvimento de uma aplicação não precisa estar
orientado a uma linguagem de um SGBD específico
Deve estar de acordo com nível de conformidade (SQL)
14
7
Interface Padronizada para Acesso
a Banco de Dados
Nesta abordagem, a aplicação, em tempo de
execução:
Especifica qual a fonte de dados a que deseja
ter acesso.
Faz a vinculação entre a aplicação e a fonte de
dados, através de um módulo (driver).
O driver converte os formatos de dados e os
comandos padronizados para os formatos
compreendidos pelo SGBD-alvo.
15
Interface Padronizada para Acesso
a Banco de Dados
Não é necessário utilizar várias linguagens para
acesso a dados de diferentes SGBDs
Menor exigência de treinamento dos desenvolvedores
Menos erros de programação
Mais rápido desenvolvimento
Os desenvolvedores não precisam se preocupar com as
particularidades dos bancos de dados que irão acessar e
trabalhar.
16
8
Interface Padronizada para Acesso
a Banco de Dados
Principais implementações de APIs no
mercado
ODBC
JDBC
17
ODBC
ODBC (Open Database Connectivity)
Gerenciador desenvolvido pela Microsoft
Diversos fornecedores de drivers
18
9
Aplicativo
API ODBC
Gerenciador de Drivers ODBC
API do Driver ODBC
Driver ODBC suprido pelo fornecedor
Jet SQL
Oracle
Access SQL
Oracle
19
Aplicativo
API ODBC
Gerenciador de Drivers ODBC
API do Driver ODBC
Driver ODBC suprido pelo fornecedor
Jet SQL
Aplicativo
Access
SQL
Oracle
Oracle
Programa desenvolvido pelo usuário ou pode ser um produto para recuperação,
apresentação e manipulação de dados pelo usuário final, como planilhas por
exemplo.
Interação entre aplicação e fonte de dados:
• Estabelecimento de conexão
• Texto de cada comando colocado em buffer e submetido para execução
• No caso de retorno de resultados, a aplicação deve ter uma área para
armazenamento dos resultados
• No caso de retorno de erros, a aplicação recupera o erro e adota uma ação
adequada
• Fechamento de transação (commit e rollback)
• Finalização da conexão
20
10
Aplicativo
API ODBC
Gerenciador de Drivers ODBC
API do Driver ODBC
Driver ODBC suprido pelo fornecedor
Jet SQL
Access
Oracle
SQL
Oracle
API ODBC: Interface entre o aplicativo e o gerenciador
de drivers.
21
Aplicativo
API ODBC
Gerenciador de Drivers ODBC
API do Driver ODBC
Driver ODBC suprido pelo fornecedor
Jet SQL
Oracle
Access SQL
Oracle
Gerenciador de Drivers
Objetivo:
Efetuar a carga dos drivers solicitados pelos aplicativos,
Inicialização – alocação e desalocação de recursos e criação e finalização de
conexões com os BDs
Passagem de chamadas de função da aplicação para os drivers
Log: registro das chamadas de função efetuadas pela aplicação
Gerencia os diversos pedidos associados a diferentes conexões da aplicação com o
SGBD
22
11
Aplicativo
API ODBC
Gerenciador de Drivers ODBC
API do Driver ODBC
Driver ODBC suprido pelo fornecedor
Jet SQL
Access
Oracle
SQL
Oracle
API do Driver ODBC:
Encapsulamento das regras para escrever drivers de
dispositivo – interessa aos fornecedores de bancos de
dados.
23
Aplicativo
API ODBC
Gerenciador de Drivers ODBC
API do Driver ODBC
Driver ODBC suprido pelo fornecedor
Jet SQL
Access
SQL
Oracle
Oracle
Drivers
É o componente específico de cada SGBD
Driver é o mediador entre a aplicação e a fonte de dados.
Implementa as chamadas remotas ao SGBD
Gerencia a obtenção dos resultados de comandos SQL (como a manipulação de
cursores);
Aloca área para dados e mensagens de erro;
Em casos em que a fonte de dados não implementa todo o potencial ODBC, o driver
implementa a funcionalidade;
Cada driver, em princípio, corresponde a uma biblioteca e corresponde a apenas
uma fonte de dados. Mas existem drivers que podem se comunicar com mais de
uma fonte de dados.
24
12
ODBC
25
ODBC
Camada de middleware
Maior consumo de recursos... Exemplo:
Driver Oracle, em algumas versões, 3% mais
lento do que conexão direta à API do SGBD
26
13
ODBC
Segurança:
Sniffers podem descobrir tudo que é trafegado
Alguns drivers fornecem criptografia
27
ODBC
É possível criar um Data Source Name
(DSN), apontando para uma instância de um
SGBD, utilizando ODBC
28
14
ODBC – Criando DSN
Para executar o ODBC siga os passos:
Menu Start (Iniciar) do Windows, selecionar
Settings (Configurações).
Escolha a opção Control Panel (Painel de
Controle), e na janela que será aberta dê um
duplo clique no ícone do ODBC.
29
Tela Principal do Gerenciador
ODBC
30
15
Origem de Dados ODBC
Para acessar um banco de dados (BD) através
do ODBC é necessário registrar este banco
como uma origem de dados ODBC. Fazendo
isto a aplicação precisa apenas saber o nome
desta origem de dados. A localização e o tipo
do BD não fazem diferença.
31
Origem de Dados ODBC
Origem de Dados System DSN
ao definir a origem de dados como System DSN,
o BD será aberto para qualquer usuário do
sistema.
32
16
Criando um System DSN
(1)
Na tela inicial do ODBC siga os passos descritos a seguir:
Selecione a pasta
System DSN.
Para criar um novo
System DSN, clique
no botão Add.
33
Criando um System DSN
(2)
Depois de pressionado o botão Add, será mostrada a tela a
seguir, onde deve ser especificado o driver ODBC a ser
utilizado para acessar seus dados
Selecione um
driver na lista
apresentada.
Depois de
selecionar, clique
em Finish.
34
17
Criando um System DSN
(3)
Depois de especificar o driver ODBC a ser utilizado, é preciso
selecionar o arquivo que contém os dados.
Indique o nome da origem de
dados (não precisa ser o nome
do banco de dados).
Dê uma descrição
para a origem de
dados.
Nesta parte estão disponíveis as
opções para seleção, criação ou
reparo e compactação do arquivo
de banco de dados. Para
selecionar um arquivo clique no
botão Select.
Feito isso foi criado uma origem de dados para seu banco de dados. Quando for
preciso acessar um BD, será preciso fazer referência apenas a origem de dados.35
Conectando com o SGBD
Utilizando o DSN
' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Dsn=MeuDSN;" & _
"Uid=UsuarioDoBD;" & _
"Pwd=SenhaDoUsuario"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
36
18
Conectando com o SGBD
Sem o DSN
' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver={SQL Server};" & _
"Server=NomeDoServidor;" & _
"Database=DatabaseASeConectar;" & _
"Uid=UsuarioDoBd;" & _
"Pwd=SenhaDoUsuario"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
37
Conectando com o SGBD
Sem o DSN
' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver={SQL Server};" & _
"Server=NomeDoServidor;" & _
"Database=DatabaseASeConectar;" & _
"Uid=UsuarioDoBd;" & _
"Pwd=SenhaDoUsuario"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
38
19
Conectando com o SGBD
Sem o DSN – Outras strings de conexão
-- Oracle
Dim sConnString As String = _
"Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=UsuarioDoBd;" & _
"Pwd=SenhaDoUsuario"
-- Access
Dim sConnString As String = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;"
39
Executando Comandos
Exemplo:
Dim commandString As String = "DELETE FROM Sessions
WHERE Expires < ?"
Dim conn As OdbcConnection = new
OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand(commandString,
conn)
cmd.Parameters.Add("@Expires", OdbcType.DateTime).Value
= DateTime.Now
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
40
20
Executando Comandos
Public Sub LeDistinct(ByVal connectionString As String, ByVal
strColuna As String, ByVal strTabela As String)
Dim queryString As String = "SELECT DISTINCT " & strColuna & _
" FROM " & strTabela
Using connection As New OdbcConnection(connectionString)
Dim command As New OdbcCommand(queryString, connection)
connection.Open()
Dim reader As OdbcDataReader = command.ExecuteReader()
While reader.Read()
Console.WriteLine("Valor lido={0}", reader(0).ToString)
End While
reader.Close()
End Using
End Sub
41
JDBC
Java Database Connectivity
Permite aos programadores Java conectar-se
a bancos de dados e a acessá-lo e manipulálo utilizando SQL.
Vantagem: Programas escritos em Java e
JDBC são independentes de plataforma e
independentes de fornecedor.
42
21
Aplicativo Java
API JDBC
Gerenciador de Drivers JDBC
API do Driver JDBC
Ponte JDBC/ODBC
Driver JDBC suprido
pelo fornecedor
Driver ODBC suprido
pelo fornecedor
Jet SQL
Oracle
Access SQL
Oracle
Jet
SQL
Oracle
Jet SQL
Oracle
Access SQL
Oracle
43
JDBC vs ODBC
No final das contas, o conceito por trás de
JDBC é o mesmo que o de ODBC:
programas escritos usando a API JDBC se
comunicariam com o gerenciador de driver
JDBC, que por sua vez usaria os drivers que
estivessem plugados nele naquele momento
para comunicações com o banco de dados
real.
44
22
JDBC vs ODBC
Por que a Sun não utilizou o modelo ODBC?
Motivos alegados por ela:
1. ODBC é difícil de ser aprendida (?)
2. ODBC tem poucos comandos com complexas
opções.
3. Dificuldade de mapear ODBC para Java por causa
de ponteiros e ponteiros para ponteiros, comuns em
C.
45
JDBC
Utilizando JDBC
Carregando drivers com a ponte JDBC-ODBC
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Ou direto via JDBC (exemplo Oracle)
Class.forName("oracle.jdbc.OracleDriver");
46
23
JDBC
Conexão com BD
Estabelecendo conexão
Connection con = DriverManager.getConnection
(url,"myLogin", "myPassword");
Exemplo:
String url = "jdbc:odbc:Fred";
Connection con = DriverManager.getConnection(url,
"Fernanda", "J8");
String url =
"jdbc:oracle:thin:@200.20.235.31:1521:desenv01";
Connection con = DriverManager.getConnection(url,
"Fernanda", "J8");
47
Conexão com BD
Fechando Conexão
con.close();
48
24
JDBC
Criando Comandos em JDBC
Statement
Objeto que envia o comando SQL ao SGBD.
Criamos o objeto comando e o executamos
através de um método apropriado com o tipo do
comando
Métodos de execução de comandos
SELECT -> método executeQuery .
INSERT -> método executeUpdate
Criação, modificação, remoção de tabelas ->
executeUpdate
49
25