Banco de Dados I Capítulo 6: Uso de SQL em Aplicações

1. Banco de Dados ICapítulo 6: Uso de SQL em Aplicações UFPB/DSC Bacharelado em Ciência da Computação Cláudio Baptista

2. SQL Embutido • Até então vinhamos usando o SQL interativo, mas como fazer para usarmos SQL numa aplicação? • Porquê SQL não é uma linguagem completa? • Várias formas de conexão a BD: SQL Estático, SQL Dinâmico e Call Level Interface (SQL/CLI, ODBC/JDBC) • Nesta seção estudaremos SQL embutido em linguagem hospedeira. • SQL + Linguagem Hospedeira (Pascal, C, Cobol, Java …) • Geralmente, diferentes sistemas seguem diferentes convenções para embutir comandos SQL

3. SQL Embutido Linguagem Hospedeira (C, Pascal, Fortran, Cobol, Ada, SQLJ (java)) + SQL Embutido Pré-processador Linguagem hospedeira + Chamadas de funções Compilador Linguagem Hospedeira Código objeto

4. SQL Embutido • Problema: “Impedance Mismatch” • SQL trabalha com relações • Linguagens de programação trabalham orientada a registro • Como fazer para ler os dados de um conjunto retornado pelo SQL numa linguagem de programação? • Precisamos ter um mecanismos para associar os valores retornados pelo SGBD em variáveis da aplicação. Faz-se isso usando variáveis hospedeiras. • Obs.: Java tem um tratamento diferenciado pelo fato de se tirar proveito de suas características Orientada a Objetos Existe um padrão para isto SQL/OLB (Object Language Bindings)

5. SQL Embutido • Tratamento de exceções • é realizado pelo comando WHENEVER que permite indicar as ações que devem ser realizadas quando um erro ocorrer. • Isto evita de fazer verificação de exceção para cada comando SQL produzido • Pode-se usar o SQLSTATE, SQLEXCEPTION, SQLWARNING, NOT FOUND (02000) dentre outros. • SQLSTATE é um array de 5 caracteres, cada vez que um comando SQL é executado o SGBD retorna no SQLSTATE informações sobre erros, através de códigos (SQL-89 chamava de SQLCODE) • Ex.: Código ‘00000’ => não houve erro • Código ‘02000’=> tupla não encontrada

6. SQL Embutido • A seção DECLARE • Qualquer declaração SQL (por exemplo a definição de variáveis host) é feita entre os comandos: • EXEC SQL BEGIN DECLARE SECTION • EXEC SQL END DECLARE SECTION • Ex.: Em C EXEC SQL BEGIN DECLARE SECTION; char nomeStudio[50], endStudio[256]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION;

7. SQL Embutido • Variáveis host: • são compartilhadas entre a aplicação e o BD • são definidas na aplicação e precedidas por dois_pontos (:) • o comando EXEC SQL • Quando queremos invocar um comando SQL de dentro de uma aplicação simplesmente usamos o comando EXEC SQL ou outra diretiva(por exemplo $ ou #)

8. SQL Embutido Exemplo de cadastro de cliente em C: void cadastraCliente() { EXEC SQL BEGIN DECLARE SECTION; char nome[50], endereco[256]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; printf(“Entre o nome e endereço\n”); gets(nome); gets(endereco); insert into Cliente (nome, endereco); values (:nome, :endereco); }

9. SQL Embutido Exemplo de consulta que retorna uma única tupla: void printCliente() { EXEC SQL BEGIN DECLARE SECTION; char nome[25], endereco[256]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; printf(“Entre com o nome do cliente\n”); gets(nome); EXEC SQL select nome, endereco into :nome, :endereco from Cliente where nome = :nome if (strcmp(SQLSTATE, “00000”)) printf (“Nome = %s\tendereco = %s\n”, nome, endereco); else printf (“ERRO no ACESSO AO BD = %s”, SQLSTATE); }

11. SQL Embutido • No programa anterior uma única tupla é selecionada pelo SQL embutido. Geralmente, uma query resulta em várias tuplas. • Problema: SQL processa um conjunto de tuplas, enquanto que C e Pascal (ou outra linguagem host) processa um registro por vez. • Solução: Introduziu-se o conceito de cursor para permitir processar uma tupla por vez nas linguagens hospedeiras.

12. SQL Embutido • Um cursor pode ser visto como um ponteiro que aponta para uma única tupla(linha) do resultado da query. • Cada cursor possui uma pesquisa associada, especificada como parte da operação que define o cursor. • A pesquisa é executada quando o cursor for aberto. • Numa mesma transação um cursor pode ser aberto ou fechado qualquer número de vezes. Pode-se ter vários cursores abertos ao mesmo tempo.

13. SQL Embutido • Sintaxe da especificação de um cursor: EXEC SQL DECLARE nome-cursor CURSOR FOR cláusula-select • Um cursor possui as seguintes operações: • OPEN: executa a query especificada e pões o cursos para apontar para uma posição anterior a primeira tupla do resultado da consulta • FETCH: move o cursor para apontar para próxima linha no resultado da consulta. Tornando-a a tupla corrente e copiando todos os valores dos atributos para as variáveis da linguagem hospedeira usada. • CLOSE: fecha o cursor.

14. SQL Embutido • UPDATE … CURRENT OF: realiza a atualização dos atributos da tupla que está sendo apontada pelo cursor (linha corrente). Sintaxe: • UPDATE tabela • SET lista de atribuições • WHERE CURRENT OF cursor • DELETE ... CURRENT OF: elimina a tupla que está sendo apontada pelo cursor. Sintaxe: • DELETE • FROM tabela • WHERE CURRENT OF curso

16. Exemplo usando Delete e Update // Se empregado ganha mais de 10000 é demitido se não tem seu // salário reduzido em 20% void reducaodeFolhadePagamento() { EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; float salario; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE salCursor CURSOR FOR SELECT salario FROM Empregado ; EXEC SQL OPEN salCursor; while(1) { EXEC SQL FETCH FROM salCursor INTO :salario; // Verifica se não há mais tuplas if (strcmp(SQLSTATE, “02000”)) break; if (salario > 10000) EXEC SQL DELETE FROM CLIENTE WHERE CURRENT OF salCursor; else EXEC SQL UPDATE CLIENTE SET salario = salario - salario * 0.2; WHERE CURRENT OF salCursor; } EXEC SQL CLOSE salCursor; }

17. SQL Embutido • Scrolling cursors • cursores movem-se por default do inicio do result set para frente (forward) • podemos, entretanto, movê-los também para trás e/ou para qualquer posição no result set, • para tanto, devemos acrescentar SCROLL na definição do cursor • EX. EXEC DECLARE meuCursor SCROLL CURSOR FOR Empregado;

18. SQL Embutido • Scrolling cursors • Num FETCH, podemos adicionar as seguintes opções: • NEXT ou PRIOR: pega o próximo ou anterior • FIRST ou LAST: obtém o primeiro ou último • RELATIVE seguido de um inteiro: indica quantas tuplas mover para frente (se positivo) ou para trás (se negativo) • ABSOLUTE seguido de um inteiro: indica a posição da tupla contando do início (se positivo) ou do final (se negativo)

19. SQL Embutido • Exemplo em SQLJ: • #sql { CREATE TABLE EMPREGADO ( matricula int not null, nome varchar(30), Primary key(matricula) )};

20. SQL Dinâmico Motivação: • SQL em uma linguagem hospedeira é bom em aplicações estáticas, p.e., um programa de reserva de passagens aéreas. • Não serve para escrever um programa como sqlplus, porque não se sabe de antemão qual o código que segue um sinal de prontidão SQL>. • Para resolver, precisamos de dois comandos: • PREPARE converte uma cadeia de caracteres em um comando SQL. • EXECUTE executa o comando.

21. Exemplo: Versão Simplificada do Sqlplus EXEC SQL BEGIN DECLARE SECTION; char query[MAX_QUERY_LENGTH]; EXEC SQL END DECLARE SECTION; /* issue SQL> prompt */ /* read user's text into array query */ EXEC SQL PREPARE q FROM :query; EXEC SQL EXECUTE q; /* go back to reissue prompt */ • Uma vez preparada, uma consulta pode ser executada muitas vezes. • “Prepare” = otimiza a consulta, i.e., encontra um meio de executá-la com um mínimo de I/O’s. • Alternativamente, PREPARE e EXECUTE podem ser combinadas em: EXEC SQL EXECUTE IMMEDIATE :query;

22. Desvantagens da Abordagem 'Linguagens Hospedeira' • Nem é C e nem é SQL, nem é Java e nem é SQL, … • O programa é executado no lado cliente da arquitetura cliente-servidor • Problema de desempenho

23. Interfaces “Call-Level” (SQL/CLI) Nesta abordagem, C (ou outra linguagem) cria comandos SQL como cadeias de caracteres passados como argumentos de funções que são parte de uma biblioteca. • SQL/CLI (ODBC = open database connectivity) e JDBC (Java database connectivity). • Grande vantagem em relação ao enfoque Linguagem Hospedeira: o programa continua C puro

24. SQL-CLI • Em C, necessita-se do include sqlcli.h • O programa pode então usar 4 tipos de estruturas: • 1. Environment: prepara para conexão • 2. Connections: conecta a aplicação ao SGBD • 3. Statements: contém os comandos a serem processados • 4. Descriptions: metadados

25. SQL-CLI • Em C, a biblioteca permite que você crie um statement • handle = estrutura em que você coloca um comando SQL. • Os handles são: • 1. SQLHENV: para setar environment • 2. SQLHDBC: para conexão • 3. SQLHSTMT: para statements • 4. SQLHDESC: para descriptions

26. SQL-CLI Exemplo: #include <sqlcli.h> SQLHENV ambiente; SQLHDBC conexao; SQLHSTMT comando; SQLHRETURN erro1, erro2, erro3; erro1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ambiente); if (!erro1) { erro2 = SQLAllocHandle(SQL_HANDLE_DBC, ambiente, &conexao); if(!erro2) erro3 = SQLAllocHandle(SQL_HANDLE_SMT, conexao, &comando); }

27. SQL-CLI • Use SQLPrepare(myHandle, <statement>,length) para fazer myHandle representar o comando SQL no segundo argumento. • Length é o tamanho do string. Pode ser usado SQL_NTS (Null Terminating String) que o próprio SQLPrepare descobre automaticamente. • Use SQLExecute(myHandle) para executar o comando. Exemplo SQLPrepare(comando, "SELECT nome, salario FROM Empregado WHERE depto = 10", SQL_NTS); SQLExecute(comando); • Podemos combinar estes dois comandos num único: • SQLExecDirect(comando, "SELECT nome, salario • FROM Empregado WHERE depto = 10", SQL_NTS);

28. Recuperando Dados Para obter os dados retornados por uma consulta, usa-se: • Variáveis de ligação (“bind”) para os componentes das tuplas retornadas. • SQLBindCol aplica-se a um handle, o número da coluna, e a variável de ligação, mais outros argumentos. • Comando “Fetch”, usando o handle. • SQLFetch aplica-se a um handle. Exemplo SQLBindCol(handle1, 1, SQL_CHAR, &coluna1, size(coluna1), &col1Info) SQLBindCol(handle1, 2, SQL_REAL, &coluna2, size(coluna2), &col2Info) SQLExecute(handle1); ... while(SQLFetch(handle1) != SQL_NO_DATA) { ... }

29. SQL-CLI – Passando Parâmetros para Queries • Use: • 1. SQLPrepare(Comando) • 2. SQLBindParameter() • 3. SQLExecute() Exemplo: SQLPrepare(comando, “insert into projeto(codigo, nome) values (?,?)”, SQL_NTS); SQLBindParameter(comando, 1,..., codProj, ...); SQLBindParameter(comando, 2, ..., nomeProj, ...)~; SQLExecute(comando);

30. ODBC • ODBC tem as mesmas idéias de SQL/CLI, entretando com pequenas modificações • A seguir veremos um exemplo de ODBC

31. int ODBCexample() { RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, “buchada.dsc.ufcg.edu.br”, SQL_NTS,“baptista”, “password”, SQL_NTS); { char banco[80]; float saldo; int lenOut1, lenOut2; HSTMT stmt; SQLAllocStmt(conn, &stmt); char * query = “select banco, sum(saldo) from contas group by banco” error = SQLExecDirect(stmt, query, SQL_NTS); if (error == SQL_SUCCESS) { SQLBindCol(stmt, 1, SQL_C_CHAR, banco, 80, &lenOut1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &saldo, 0, &lenOut2); while (SQLFetch(stmt) == SQL_SUCCESS) { printf(“%s %g\n”, banco, saldo); } } } SQLFreeStmt(stmt, SQL_DROP); SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env); }

32. JDBC • JDBC é uma Call-level Interface que permite acesso externo a banco de dados SQL • Difere de SQL Embutido, pois possui uma API que é chamada na própria linguagem de progração para acessar o BD • Implementa o modelo cliente-servidor

33. JDBC Estabelecendo uma Conexão • 1) Precisa-se carregar um driver JDBC para o SGBD que se está usando. • No Oracle, isto pode ser feito da seguinte forma: • Class.forName("oracle.jdbc.driver.OracleDriver") • 2) Fazer uma conexão • Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@oracle-prod:1521:OPROD", username, passwd);

34. Criando JDBC Statement • JDBC Statement é um objeto usado para mandar um comando SQL para o SGBD • Está associado a uma conexão aberta • O método createStatement() retorna um objeto da classe Statement (se não houver argumento) ou PreparedStatement se houver um comando SQL como argumento ("overload" de métodos). Exemplo Statement stat1 = myCon.createStatement(); PreparedStatement stat2 = myCon.createStatement( "SELECT nome, salario" + "FROM Empregado" + "WHERE salario > 5000'" ); • myCon é uma conexão, stat1 é um objeto “vazio” da classe Statement, e stat2 é um objeto da classe PreparedStatement que tem um comando SQL associado.

35. Executando Comandos • JDBC distingue consultas (comandos que retornam dados) de updates (comandos que somente afetam o BD). • Os métodos executeQuery() e executeUpdate() são usados para executar essas duas espécies de comandos. • Eles devem ter um argumento se aplicados a Statement, nunca se aplicados a PreparedStatement. • Quando uma consulta é executada, retorna um objeto da classe ResultSet. Exemplo stat1.executeUpdate( "INSERT INTO Empregado" + "VALUES(‘’Ana Maria, ‘Engenheira', 3000.00)" ); ResultSet rs = stat2.executeQuery();

36. Executando comandos • Comandos DDL (criar tabelas) e updates são realizados com o método: executeUpdate() Statement stmt = con.createStatement(); stmt.executeUpdate("CREATE TABLE Sells " + "(bar VARCHAR2(40), beer VARCHAR2(40), price REAL)" ); stmt.executeUpdate("INSERT INTO Sells " + "VALUES ('Bar', 'BudLite', 2.00)" ); String sqlString = "CREATE TABLE Bars " + "(name VARCHAR2(40), address VARCHAR2(80), license INT)" ; stmt.executeUpdate(sqlString);

37. Obtendo as Tuplas de um ResultSet • O método next() se aplica a um ResultSet e move um “cursor” para a próxima tupla do conjunto. • Aplique next() para obter a primeira tupla. • next() returna FALSE se não houver mais tuplas. • Para a tupla corrente do cursor, você pode obter seu i-ésimo componente aplicando o método getX(i), onde X é o nome para o tipo do argumento. Exemplo while(rs.next()) { nome = rs.getString(1); salario = rs.getFloat(2); ... }

38. Executando SELECT • Usa o método executeQuery() que retorna um objeto ResultSet String bar, beer ; float price ; ResultSet rs = stmt.executeQuery("SELECT * FROM Sells"); while ( rs.next() ) { bar = rs.getString("bar"); beer = rs.getString("beer"); price = rs.getFloat("price"); System.out.println(bar + " sells " + beer + " for " + price + " Dollars."); }

39. Executando SELECT • Alternativamente, podemos usar bar = rs.getString(1); price = rs.getFloat(3); beer = rs.getString(2);

40. ResultSet • Contém métodos getRow, isFirst, isBeforeFirst, isLast, isAfterLast que indicam aonde o cursor está • Pode-se criar cursores scrollable que podem se movimentar em qualquer sentido no ResultSet • Com isso podem ser usados: • rs.absolute(3); // move para a terceira tupla rs.previous(); // move para trás 1 tupla rs.relative(2); // move para frente 2 tuplas rs.relative(-3); // move para trás 3 tuplas

41. Prepared Statement • Usado quando se quer usar a mesma query com diferentes parâmetros várias vezes • o comando é compilado e otimizado pelo SGBD apenas uma vez • PreparedStatement prepareUpdatePrice = con.prepareStatement( "UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?"); • Então precisamos preencher os parâmetros: • prepareUpdatePrice.setInt(1, 3); prepareUpdatePrice.setString(2, "Bar Of Foo"); prepareUpdatePrice.setString(3, "BudLite");

42. Desvantagens de ODBC/JDBC • Os programas C, Java, … , ainda são executados no lado cliente

43. Integração Estreita com SGBDs • O uso de SQL/PSM (Stored Procedures) tais como PL/SQL, SQLJ, TransactSQL, … , são extensões da SQL • Processadas no lado servidor da arquitetura cliente - servidor • Isto é muito bom para o desempenho

44. 6.3 Stored Procedures • É um conjunto de comandos SQL definidos pelo usuário que ficam armazenados num BD como um procedimento/função, para eventuais processamentos. • São processamentos de tarefas da aplicação que residem no SGBD ao invés de no código da aplicação (cliente).

45. 6.3 Stored Procedures • Vantagens: • 1.Desempenho • Ex.: Seja a consulta SELECT codigop, nome, COUNT(*) FROM Projeto p, Alocacao a WHERE p.codproj = a.codigop GROUP BY p.codproj, p.nome

46. 6.3 Stored Procedures • Se vários usuários realizarem esta consulta o tráfego de rede será alto. • se criarmos uma stored procedure para executar esta consulta, os usuários necessitarão apenas de um comando para executar a consulta anterior: EXEC nomeProcedimento; • Outro ponto é a compilação, a consulta anterior seria compilada a cada chamada, enquanto o procedimento contendo a consulta seria compilado uma única vez

47. 6.3 Stored Procedures • 2. Facilita o gerenciamento do BD, pois a consulta é escrita em um único lugar, portanto a manutenção desta torna-se mais eficaz e segura.

48. 6.3 Stored Procedures • 3. Segurança, como veremos no próximo capítulo, podemos usar stored procedures para limitar o acesso de alguns usuários ao BD. Desta forma, a maneira em que o BD pode ser modificado é estritamente definida.

49. Stored Procedures • SQL/PSM - Persistent Stored Modules • No momento cada SGBD oferece sua própria linguagem (Oracle PL/SQL, Microsoft Transact/SQL, etc) • Em PSM, definimos módulos que são coleções de definições defunções ou procedimentos, declarações de tabelas temporárias, dentre outros.

50. Stored Procedures -SQL/PSM • Criando Funções e Procedimentos • CREATE PROCEDURE <NOME> (<parâmetros>) declarações locais corpo do procedimento; • CREATE FUNCTION <NOME> RETURNS <tipo> declarações locais corpo da função; • obs.: parâmetros são do tipo modo-nome-tipo (onde modo indica IN, OUT ou INOUT) • Parâmetros em funções devem ter modo IN