Júlio Battisti Livros e Cursosa sua sala de aula na internet

Vamos iniciar o nosso estudo, aprendendo a criar consultas baseadas em tabelas do SQL Server 2005, ou seja, criamos uma consulta normalmente no Microsoft Access, porém a consulta acessa dados diretamente em tabelas de um banco de dados do SQL Server 2005. É importante diferenciar que, neste caso, não estamos acessando dados de uma tabela anexada, mas sim diretamente do SQL Server 2005.

Para que uma consulta do Microsoft Access possa acessar tabelas no SQL Server 2005, precisamos configurar a propriedade SourceConnectStr (Seq de conexão da origem). Nesta propriedade informamos uma String para que a consulta consiga estabelecer uma conexão ODBC com o SQL Server 2005. Outro detalhe que devemos observar é que, uma vez estabelecida a conexão, as tabelas do SQL Server serão acessadas com o nome no formato NomeDoSchemaDonoDaTabela.NomeDaTabela. Porém, no Microsoft Access, o ponto (.) não é um caracter permitido como nome de tabela. Por isso, antes de executarmos a consulta, iremos criar “aliases” para as tabelas. Criaremos aliases sem o ponto.

Vamos fazer um exemplo prático.

Exemplo: Criar um novo Banco de Dados chamado BuscaDadosNoSQL.mdb e salvá-lo na pasta C:\Meus documentos. Neste Banco de Dados, criar uma consulta baseada nas tabelas Orders e Order Details do Banco de Dados Northwind da instância SERVIDOR\SQL2005, do SQL Server 2005. Esta consulta deve retornar os campos OrderID, OrderDate, ShipCountry e um campo calculado que forneça o valor total do pedido. Este exercício será composto de dois procedimentos. No Procedimento 1, iremos criar o Banco de Dados BuscaDadosNoSQL.mdb. No Procedimento 2, iremos criar a consulta “Relação de Pedidos a partir do SQL”.

Procedimento 1: Para criar o Banco de Dados BuscaDadosNoSQL.mdb, faça o seguinte:

Passo 1. Abra o Microsoft Access (Iniciar -> Programas -> Microsoft Access).

Passo 2. Na janela que surge, dê um clique na opção Banco de Dados vazio do Access, e depois dê um clique no botão OK.

Passo 3. Surge a janela Novo arquivo de Banco de Dados. Utilize a lista Salvar em, para navegar até a pasta C:\Meus documentos. No campo Nome do arquivo, digite BuscaDadosNoSQL.mdb.

Passo 4. Dê um clique no botão Criar.

O Banco de Dados BuscaDadosNoSQL.mdb é criado e aberto no Microsoft Access 2000. Por padrão, vem a guia Tabelas selecionada. Observe que, como o banco acaba de ser criado, ainda não existe nenhuma tabela.

Com isso, criamos o Banco de Dados BuscaDadosNoSQL.mdb.

Passo 5. Mantenha o Microsoft Access aberto.

Procedimento 2: Para criar a consulta Relação de Pedidos, a partir do SQL, siga os passos indicados a seguir:

Passo 1. Dê um clique na guia Consultas.

Passo 2. Dê um clique na opção Criar consulta no modo Estrutura. Surge a janela Mostrar tabela, indicada na Figura 12.31. Como acabamos de criar o Banco de Dados BuscaDadosNoSQL.mdb, a lista de tabelas está vazia. Neste caso, faremos com que a nossa consulta busque dados no servidor SQL Server 2005.

Figura 12.31 A janela Mostrar tabela, sem nenhuma tabela na lista.

Passo 3. Dê um clique no botão Fechar.

A janela Mostrar tabela será fechada e você estará no modo estrutura para a criação de uma nova consulta.

NOTA: Para maiores informações sobre a criação de Views, consulte o Capítulo 9. Para maiores informações sobre a criação de consultas no Microsoft Access 2000, consulte a documentação do produto.

Para fazer com que a consulta possa acessar tabelas em um Banco de Dados do servidor SQL Server, precisamos configurar as propriedades da consulta. Mais especificamente, temos que definir a propriedade “Seq da conexão da origem”.

Passo 4. Para acessar as propriedades da consulta, selecione o comando Exibir -> Propriedades. Surge a janela Propriedades da consulta, indicada na Figura 12.32. Nesta janela, podemos definir a propriedade Seq da conexão da origem.

Figura 12.32 A janela de propriedades da consulta.

Passo 5. Nesta propriedade temos que definir uma String de conexão ODBC para o Banco de Dados Nortwhind, da instância SERVIDOR\SQL2005. Digite a seguinte String:

ODBC;DRIVER=SQL Server;UID=sa;PWD=abc123 LANGUAGE=Português (Brasil);DATABASE=Northwind;WSID=SERVIDOR;APP=Microsoft Open Database Connectivity;SERVER=SERVIDOR\SQL2005;

Uma String de conexão ODBC informa os parâmetros necessários para a conexão. Os parâmetros são informados na forma de pares Nome_parâmetro = valor e os pares são separados por ponto e vírgula (;). A única exceção é o primeiro parâmetro – ODBC –, o qual não é no formato de um par. Este parâmetro informa que o que vem a seguir é a definição de uma String de conexão ODBC.

Na Tabela 12.1, temos a descrição dos parâmetros informados na String anterior.

Tabela 12.1 Parâmetros para a String de conexão ODBC.

Passo 6. Uma vez definida esta propriedade, feche a janela de propriedades da consulta. Para isso clique no botão Fechar (x), da janela de propriedades.

Você estará de volta à consulta. Antes de testar se a nossa conexão ODBC está funcionando, vamos salvar a consulta.

Passo 7. Selecione o comando Arquivo -> Salvar, ou dê um clique no botão Salvar (botão com o desenho de um disquete).

Passo 8. Surge uma janela pedindo que você digite o nome da consulta. Digite Relação de Pedidos a partir do SQL.

Passo 9. Dê um clique no botão OK e pronto, a consulta é salva.

O próximo passo é adicionar as tabelas Order e Order Details, para que possamos criar a consulta proposta.

Passo 10.   Para adicionar tabelas, selecione o comando Consulta -> Mostrar tabela, ou dê um clique no botão Mostrar tabela (botão com um sinal de mais amarelo).

É exibida a janela Mostrar tabela, porém agora com a listagem das tabelas disponíveis no Banco de Dados Northwind da instância SERVIDOR\SQL2005, conforme indicado na Figura 12.33.

Figura 12.33 Consulta no Microsoft Access acessando tabelas do SQL Server 2005.

Passo 11.   Dê um clique duplo na tabela dbo.Orders para adicioná-la à consulta.

Passo 12.   Dê um clique duplo na tabela dbo.Order Details para adicioná-la à consulta.

Passo 13.   Dê um clique no botão Fechar para voltar à consulta. As tabelas dbo.Orders e dbo.Order Details já estarão adicionadas.

Conforme comentado anteriormente, o Microsoft Access não aceita um ponto no nome das tabelas. Se tentarmos criar uma consulta mantendo os nomes como estão, ao executarmos a consulta, receberemos a mensagem de erro indicada na Figura 12.34.

Figura 12.34 Mensagem de erro devido ao ponto (.) no nome das consultas.

Não podemos renomear as tabelas no SQL Server 2005, até porque o dbo. significa o usuário dono da tabela. O que podemos fazer é criar um alias (apelido) para a tabela. Este apelido é válido na consulta.

Passo 14.   Para criar um apelido para a tabela Orders, clique com o botão direito no nome da tabela (dbo.Orders) e, no menu que surge, selecione Propriedades. Na janela que surge digite um alias, no nosso caso, digite Orders, conforme indicado na Figura 12.35. Após digitar o alias, clique no botão Fechar (x), para voltar à consulta.

Figura 12.35 Criando um alias para a tabela Orders.

Passo 15.   Repita o passo 14 e crie o alias OrderDetails para a tabela dbo.Order Details. A sua tela deve estar conforme indicado na Figura 12.36.

Figura 12.36 Já foram definidos aliases para as duas tabelas.

Agora precisamos definir o relacionamento entre as duas tabelas. As tabelas Orders e Orders Details são relacionadas através do campo OrderID. Ao criarmos um relacionamento em uma consulta, o relacionamento somente é válido na consulta, ou seja, não estaremos definindo um relacionamento no Banco de Dados Northwind da instância SERVIDOR\SQL2005. Na verdade, ao criarmos um relacionamento, estamos utilizando uma cláusula Join, no comando T-SQL que define a consulta.

Passo 16.   Para criar o relacionamento entre as duas tabelas, na consulta, clique no campo OrderID na tabela Orders, mantenha o mouse pressionado e arraste o campo OrderID da tabela Orders sobre o campo OrderID da tabela Order Details. Ao largar o mouse, surge uma linha unindo os dois campos. Esta linha é um indicativo do relacionamento, conforme indicado na Figura 12.37.

Figura 12.37 Relacionamento definido na consulta.

De agora em diante é como se estivéssemos construindo uma consulta no Microsoft Access. Vamos adicionar os campos OrderID, OrderDate e ShipCountry, além de um campo calculado.

Passo 17.   Adicione o campo OrderID da tabela Orders. Para fazer isso você pode dar um clique duplo no campo OrderID ou pode arrastá-lo da tabela Orders até a primeira coluna da consulta.Também adicione os campos OrderDate e ShipCountry, da tabela Ordes. A consulta deve estar conforme indicado na Figura 12.38.

Figura 12.38 Adicionando campos à consulta.

Passo 18.   Agora vamos adicionar um campo calculado. Na coluna em branco, ao lado do campo ShipCountry, digite a seguinte fórmula, na primeira linha:

Total: ([Quantity]*[UnitPrice])*(1-[Discount])

A parte antes dos dois pontos é um alias para o campo calculado, e o que vêm depois dos dois pontos é a fórmula para o cálculo.

Esta fórmula calcula o total para cada item do pedido. Para calcular o total do pedido, temos que agrupar os diversos itens de cada pedido e somá-los. Para fazer este agrupamento, temos que exibir a linha de totais, na estrutura da consulta.

Passo 19.   Para exibir a linha de totais, selecione o comando Exibir -> Total. Uma nova linha chamada Total, aparecerá abaixo da linha Tabela.

Passo 20.   Agora vamos às configurações finais. Na linha de totais selecione Agrupar Por, para os campos OrderID, OrderDate e ShipCountry. Para o campo calculado, selecione Soma.

Estamos quase prontos para executar a consulta e observar os resultados obtidos.

Antes de executarmos a consulta, vamos formatar os dados da coluna calculada.

Passo 21.   Clique com o botão direito do mouse em qualquer linha da coluna calculada Total. No menu que surge, selecione Propriedades. É aberta a janela de propriedades desta coluna.

Passo 22.   Defina a propriedade Formato como sendo Moeda e a propriedade Casas decimais, como sendo 2, conforme indicado na Figura 12.39.

Figura 12.39 Definindo as propriedades do campo calculado.

Passo 23.   Clique no botão Fechar (x) da janela de propriedades para voltar à estrutura da consulta. Classifique o campo OrderId em ordem Crescente.

Agora estamos em condições de executar a consulta e observar os resultados obtidos.

Passo 24.   Para executar a consulta, selecione o comando Consulta -> Executar, ou dê um clique no botão Executar (botão com uma exclamação vermelha).

Na Figura 12.40, temos os resultados da consulta.

Figura 12.40 Resultados da consulta.

Passo 25.   Para você ter uma idéia do comando SQL associado à consulta, selecione o comando Exibir -> Modo SQL. Deverá ser exibido o seguinte comando:

SELECT     Orders.OrderID, Orders.OrderDate, Orders.ShipCountry, Sum(([Quantity]*[UnitPrice])*(1-[Discount])) AS Total

Observe a utilização da cláusula IN. Esta cláusula recebe dois parâmetros. O primeiro é um parâmetro vazio (‘ ‘), no caso de estarmos utilizando uma conexão ODBC como o seguindo parâmetro. Observe que a String de conexão ODBC faz parte do comando SQL que retorna os dados, ou seja, a informação para acessar o Banco de Dados Northwind da instância SERVIDOR\SQL2005 é passada no próprio comando SQL.

Passo 26.   Clique no botão Salvar para salvar a consulta.

Passo 27.   Selecione o comando Arquivo -> Fechar para sair da consulta.

Passo 28.   Feche o Microsoft Access.

NOTA: Neste tópico procurei mostrar algumas maneiras de acessar os dados do SQL Server 2005, de dentro do Microsoft Access. Falar sobre o desenvolvimento de aplicações utilizando o Microsoft Access como Front End e o SQL Server 2005 como Back End, é assunto para um livro inteiro. Aliás é um dos projetos nos quais estou pensando com carinho.

Desenvolvimento em: Acessando Dados do Servidor

Pré-Requisitos

®          Noções sobre os modelos de desenvolvimento.

®          Conhecimento básico de ODBC e OLE DB

®          Noções sobre os comandos básicos da linguagem T-SQL.

®          Saber utilizar o SQL Server Management Studio.

Metodologia

®          Apresentação dos passos necessários para a configuração e acesso aos dados do servidor SQL Server 2005 através do navegador.

Técnica

®          Utilização do SQL Server Management Studio para configurar o acesso via navegador, aos dados do SQL Server 2005 e utilização do navegador para testar o acesso.

Uma das grandes novidades do SQL Server 2000 e que também está disponível no SQL Server 2005 é a possibilidade de acessarmos dados do SQL Server 2005, utilizando apenas um navegador. Antes que isto seja possível, precisamos fazer algumas configurações para habilitar o acesso aos dados, através do navegador. Por padrão, os dados são retornados no formato XML. Falaremos um pouco sobre XML na parte final deste capítulo.

Para configurar a acesso a um Banco de Dados do SQL Server 2005, através do navegador, utilizamos o utilitário “IIS Virtual Directory Management for SQL Server”. Este utilitário permite a criação de um diretório virtual no servidor IIS, pasta esta que faz conexão com o servidor SQL Server. Para que possamos configurar esta pasta virtual, as seguintes condições devem ser atendidas:

• ®  O servidor deve estar rodando Windows NT 4.0, Windows 2000 Server ou Windows Server 2003.

• ®  Para o caso do Windows NT, temos algumas condições adicionais. Deve estar instalado o IIS 4.0 ou superior ou o PWS (Personal Web Server), para o caso do Windows NT Workstation 4.0. Também deve estar instalado o MMC (Microsoft Management Console) 1.2. Esta versão do MMC é instalada com o Option Pack 4.0 ou com o SQL Server 2005.

• ®  Para computadores rodando Windows 2000 Professional, deve ser instalado o pacote de ferramentas administrativas – Adminpak.msi. Este arquivo pode ser encontrado na pasta %Windir%\System32 de computadores onde está instalado o Windows 2000 Server. Onde %Windir% deve ser substituído pela pasta onde está instalado o Windows 2000 Server. Este arquivo também pode ser encontrado na pasta I386 do CD de instalação do Windows 2000 Server. Para instalar este pacote de ferramentas administrativas, é só localizar o arquivo Adminpak.msi, clicar com o botão direito do mouse no arquivo e, no menu de opções que surge, clicar em Instalar.

Conforme citado anteriormente, precisamos usar o utilitário IIS Virtual Directory Management for SQL Server, para criar um diretório virtual no IIS. Este utilitário cria uma associação entre o diretório virtual do IIS e um Banco de Dados de uma instância do SQL Server 2005. Para acessar este utilitário utilizamos o seguinte caminho: Iniciar -> Programas -> SQLXML 4.0 -> Configure IIS Support.

O nome do servidor IIS e o nome do diretório virtual devem ser informados como parte da URL de acesso. As informações definidas para o diretório virtual (como login, senha e permissões de acesso) são utilizadas para estabelecer uma conexão com um Banco de Dados de uma instância do servidor SQL Server 2005 e executar uma consulta.

Através do navegador, fazer os seguintes tipos de acessos:

• ®  Acessar diretamente uma tabela do Banco de Dados. Neste caso, a URL deve incluir um nome virtual do tipo dbobject. Veremos exemplos mais adiante.

NOTA: URL – Uniform Resource Locattor, nada mais é do que um endereço da Web. Por exemplo: http:www.microsoft.com/windows2000/library/default.asp é uma URL.

• ®  Executar um arquivo de modelo. Um arquivo de modelo é um documento no formato XML, no qual temos um ou mais comandos SQL. Quando um arquivo de modelo é especificado em uma URL, os comandos SQL armazenados no arquivo de modelo são executados. Também podemos especificar uma consulta SQL (normalmente um comando SELECT), diretamente na URL, porém esta não é uma prática recomendada por questões de segurança.