Atualizando Tabelas no DB

De Qknow
Ir para: navegação, pesquisa

Introdução

InsertDatabase3.png

Como uma ferramenta de Data Discovery o QlikView atua na leitura de dados a partir de diferentes formatos, incluindo tabelas em banco de dados, arquivos gravados em formato Excel, arquivos textuais, serviços web (Web Services) e tabelas HTML. Sua capacidade de reunir rapidamente todas estas fontes em memória tem sido um grande diferencial para usuários e desenvolvedores de painéis de tomada de decisão. Por padrão, todos os acessos aos dados disponibilizados são realizados em modo de leitura, o que quer dizer que o QlikView não efetua alterações nas tabelas ou arquivos originais. Há, porém, uma série de motivos para os quais se deseja permitir que o QlikView possa fazer algum nível de modificação nos dados lidos, tal como para realizar uma pesquisa de satisfação do usuário quanto aos dados, obter uma pontuação sobre uma consulta de opinião ou mesmo gravar estimativas de alterações propostas pelo usuário em valores de reajustes. Enfim, a partir da perspectiva do negócio há diversos motivos para que uma informação possa retornar ao banco de dados fruto de uma atualização (ou inserção) a partir de um painel.

Lembre-se que o script de carga é o meio pelo qual os dados chegam até o QlikView (ou QlikSense), enquanto o editor de módulos no QlikView é a forma como conexões e instruções podem ser encaminhadas a um banco de dados para atualização ou inserção de registros em tabelas. Existem diferentes métodos de conexão que permitem leitura e modificação, dentre eles o padrão aberto chamado ODBC e o padrão Microsoft OLEDB. Há ainda provedores nativos de conexão para Microsoft Access (JET Engine) e outros meios que interpretam a comunicação com as plataformas e arquivos.

Para algumas plataformas será necessário adicionar componentes ao sistema operacional, típicamente chamados de drivers de banco de dados. Antes que uma conexão possa ser estabelecida entre o QlikView® e um banco de dados, os drivers nativos daquele banco devem estar presentes, da mesma forma que para realizar a impressão de um documento em uma determinada impressora, o driver correspondente deve estar instalado no sistema operacional.

OLEDB (também conhecida como OLE-DB) é uma API (Application Program Interface) desenvolvida pela Microsoft© para acessar dados em uma grande variedade de fontes de informação, de uma maneira uniforme. Esta API fornece um conjunto de interfaces programadas implementadas com uso do COM (Component Object Model) e busca ser um sucessor do uso do padrão ODBC. Com OLEDB é possível acessar uma grande variedade de bases de dados utilizando um padrão de comandos que abstrai as particularidades de cada plataforma. Por isso, com o mesmo conjunto de instruções QlikView® é possível obter dados de diferentes fontes sem preocupar-se com os comandos específicos de cada fabricante.

Conexão a Base de Dados via Módulo

InsertDatabase4.png

Com a integração do QlikView com o ambiente Windows, a plataforma Qlik pode fazer uso de componentes de conexão em nível de módulo para um banco de dados com fins a realizar a atualização ou inserção de dados em tabelas. O editor de módulos (as vezes chamado de macros) é acessado pelo menu Ferramentas do QlikView Desktop ou pressionando as teclas CTRL + M. No editor de módulos rotinas são criadas semelhante ao processo de escrita de instruções em nível de script de carga.

Por padrão, a linguagem utilizada é um pseudo visual basic for application. Rotinas são definidas pelas instruções SUB...END SUB, muito semelhante ao que também pode ser feito em nível de script de carga (vide exemplo de rotina de gravação de arquivos qvd).

A página branca do editor de módulos é a área de digitação das instruções de rotina que são úteis para conexão com bases de dados e outros fins. Para iniciar a construção de uma rotina basta digitar na área vazia do editor. O exemplo seguinte faz a criação de uma rotina que será utilizada posteriormente por um botão de acionamento na interface gráfica do painel para invocar (executar) as instruções definidas. Por enquanto, preocupe-se em digitar apenas as instruções Sub, seguido de um nome para a rotina (no caso foi explicitado UpdateDB, uma linha branca (<enter>) e a instrução End Sub).

Sub UpdateDB ()

End Sub

Todas as instruções que fazem a conexão com o banco de dados devem ser inserida entre os comandos SUB..END SUB. Neste exemplo será utilizado o método de conexão via ADO.NET, não abordado tecnicamente neste artigo. Para tanto, é preciso definir uma variável de ambiente que atuará na hospedagem da conexão com o banco de dados. No exemplo, esta variável será definida com o nome cn. Portanto, inclua as seguintes instruções entre os comandos SUB..END SUB.

Sub UpdateDB ()
    dim cn
    set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa; Password=YourPass@123!; Initial Catalog=TSQL2012;Data Source=192.168.63.138;"
End Sub

A conexão acima apresenta a string correspondente a um servidor de banco de dados SQL Server 2012R2, motivo pelo qual o parâmetro Provider está configurado para SQLOLEDB.1. Para conexão ao SQL Server outros parâmetros são necessários, incluindo User ID e Password, que na configuração acima foi atribuído a sa e YourPass@123!, respectivamente. O endereço do servidor de banco de dados está definido pelo parâmetro Data Source seguido do endereço IP do servidor. Já o banco de dados (em algumas plataformas seria o schema) foi atribuído ao parâmetro Initial Catalog como TSQL2012. Os demais parâmetros são opcionais. A quantidade de atributos e parâmetros utilizados pela conexão irá variar de acordo com o tipo de SGBD em uso. Por exemplo, uma conexão para Oracle Database ou PostgreSQL terão parãmetros diferentes e devem ser fornecidos de acordo com a plataforma em uso.

Dica: Utilize a string de conexão do Editor de Scripts copiando o mesmo comando sem os colchetes. 


Variáveis

InsertDatabase5.png

Variáveis são ponteiros de memória para armazenar temporariamente valores. Podem ser definidas a partir do menu Configuração seguido do comando Visão Geral das Variáveis (CTRL + ALT + V). Há também a possibilidade de definir variáveis por meio do editor de scripts de carga através dos comandos LET ou SET. Consulte este artigo para maiores informções sobre como definir variáveis em nível de script ou pela interface do QlikView Desktop. Para fins de exemplo duas variáveis foram definida neste artigo:

  • vCompanyName
  • vPhone

As variáveis acima defindias podem ser utilizadas para armazenar valores de entrada de dados inseridas por um usuário através do componente Caixa de Entrada. No exemplo deste artigo o usuário poderá inserir valores para estas variáveis por meio do componente de entrada. Para adicionar este objeto a interface do QlikView Desktop, basta um clique com o botão da direita na área vazia do painel e escolher Novo Objecto de Pasta seguido de Caixa de Entrada. Nas propriedades gerais do objeto, basta adicionar as duas variáveis para a lista da direita (Variáveis Mostradas). O resultado será algo semelhante ao objeto abaixo.

InsertDatabase8.png

Estas variáveis serão preenchidas quando o usuário digitar (entrar) com os valores para cada uma delas. Digamos, por exemplo, que estes valores são possíveis de entrada para cadastro de novas empresas de logística armazenadas no banco de dados. O usuário será capaz de fornecer o nome da empresa e o telefone de contato. De volta ao editor de módulos, veja como é possível fazer a leitura das variáveis de interface gráfica do QlikView por meio de comandos na rotina previamente montada. A nova versão do script no Editor de Módulos deve ser semelhante a apresentada a seguir.

Sub UpdateDB ()
    dim cn

    dim vC      'Variável para armazenar o nome da companhia digitada pelo usuário.
    dim vP      'Variável para armazenar o telefone da companhia digitada pelo usuário.

    set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa; Password=YourPass@123!; Initial Catalog=TSQL2012;Data Source=192.168.63.138;"

    vC = ActiveDocument.Variables("vCompanyName").GetContent().String
    vP = ActiveDocument.Variables("vPhone").GetContent().String

End Sub

Variáveis também podem ser utilizadas em nível de editor de módulos para armazenar valores e objetos. Por exemplo, a variável cn é utilizada para manter aberta uma conexão com o banco de dados, enquanto as variáveis vC e vP armazenarão as entradas feitas pelo usuário por meio do objeto Caixa de Entrada. É importante destacar que as variáveis criadas na janela do QlikView Desktop não são vistas pelo editor de módulos naturalmente e, ao mesmo tempo, as variáveis criadas dentro do editor de módulos não são vistas pela janela de Visão Geral das Variáveis.

Nota:  Comentários são definidos no editor de módulos por meio do caractere aspas simples (').

Observe que os valores das variáveis vCompanyName e vPhone são lidas por meio da instrução ActiveDocument.Variables("vCompanyName").GetContent() em formato de texto (string). Cada uma destas é armazenada nas respectivas variáveis vC e vP definidas em nível de módulo. Ou seja, no final das contas as variáveis vC e vP possuem o texto digitado pelo usuário para o nome e telefone da empresa, respectivamente.

Considerações importantes:

  • Variáveis definidas no script de carga não são visíveis naturalmente pelo editor de módulos.
  • Para ler variáveis do QlikView no editor de módulo, utilize o método GetContent() do objeto ActiveDocument.Variables.
  • Variáveis podem ser definidas em nível de editor de módulo por meio da instrução dim.


Modificando Tabelas do Banco de Dados

InsertDatabase9.png

Para efetivar alterações no banco de dados, seja para inserir novos registros ou para alterar registros existentes, é preciso que o módulo (rotina) esteja configurada com permissões de acesso a equipamentos externos. Além disso, a rotina precisa ser completada com a instrução que equivale a operação que se deseja realizar. Esta ação é preparada via uma instrução SQL (structured query language) que pode ser um insert, update ou delete. No exemplo em questão a instrução será um comando insert. Ou seja, mediante o preenchimento das variáveis o usuário poderá disparar a inserção de dados em uma tabela do banco de dados a partir do QlikView. Mas, primeiramente, esteja certo de configurar o módulo para Permitir Acesso ao Sistema na opção Segurança Local Atual no editor de módulos (CTRL + M).

Além da configuração mencionada, o módulo precisa ser completado com as instruções restantes que farão a atualização da tabela. Falta, portanto, definir o comando que irá inserir os registros desejados. Neste exemplo, há uma tabela no banco de dados chamada Shippers que pertence ao schema Sales contendo três colunas, sendo uma delas de numeração automática (IDENTITY). O código abaixo completa o script no editor de módulos.

Sub UpdateDB ()
    dim cn

    dim vC      'Variável para armazenar o nome da companhia digitada pelo usuário.
    dim vP      'Variável para armazenar o telefone da companhia digitada pelo usuário.
    dim vSQL    'Variável para armazenar a instrução SQL que fará a operação na tabela do banco.

    set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa; Password=YourPass@123!; Initial Catalog=TSQL2012;Data Source=192.168.63.138;"

    vC = ActiveDocument.Variables("vCompanyName").GetContent().String
    vP = ActiveDocument.Variables("vPhone").GetContent().String

    vSQL = "INSERT Sales.Shippers Values ('" & vC & "','" & vP & "')"
    cn.Execute vSQL

End Sub


Para fins de inserção de novos registros na tabela a rotina está completa e a janela do editor de módulos pode ser fechada. De volta a interface geral do QlikView Desktop é possível acrescentar um botão de comando que fará o acionamento do módulo. Botões são adicionados com um clique com o botão da direita na área vazia do painel escolhendo a opção Novo Objeto de Pasta seguido de Botão. No exemplo, o texto Insert New foi adicionado a propriedade Texto do botão. Em seguida, na guia Ações um disparador foi incluído, conforme imagem a seguir.


InsertDatabase10.png

Para inserir uma ação no botão, siga os seguintes passos:

1. Nas propriedades do botão, ative a guia Ações.

2. Clique no botão Inserir para apresentar a janela Adicionar Ação.

3. Escolha o item Externo e em seguida, Executar Macro.

4. Clique em Ok para voltar a janela anterior e insira o nome da rotina.

5. Caso não lembre do nome da rotina, clique no botão Editar Módulo.

6. Copie o nome da rotina para a área de transferência (CTRL + C) e feche o editor.

7. Cole ou escreva o nome da rotina no campo Nome da Macro, conforme ilustração ao lado.

Após fechar a janela de propriedades do botão, ajuste a interface de maneira a ter o acionador ao lado da caixa de entrada. Para que a rotina seja executada basta adicionar os dados desejados e clicar no botão.

InsertDatabase11.png

O resultado é o acionamento da rotina que faz a conexão com o banco de dados, obtém o conteúdo digitado pelo usuário através das variáveis definidas no objeto caixa de entrada, prepara a instrução de inserção e executa o comando na tabela Sales. Obviamente uma série de comandos adicionais pode ser utilizado para tratar erros, verificar se os dados foram inseridos, etc. Porém, este artigo não é um guia completo de visual basic for application ou mesmo de conexão via ADO.NET, mas um guia para demonstrar como o QlikView pode devolver dados a tabelas do banco de dados.

Para avaliar se o resultado foi bem sucedido este artigo carrega a tabela a partir do editor de scripts (CTRL + E). O processo de carga é regular, sem nenhuma necessidade de ajustes ou configurações avançadas. Trata-se, portanto de um processo de carga de tabela normal. O resultado é apresentado em um objeto tabela.

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=TSQL2012;Data Source=192.168.63.138] (XPassword is IEGOZCNOULZEXZcGGLIWC);

Shippers:
LOAD companyname, phone, shipperid;
SQL SELECT * FROM TSQL2012.Sales.Shippers;

InsertDatabase12.png



Download


Assuntos Relacionados


Outros Assuntos


Envelope01.jpg
Procurando Algo? Fale Conosco!

Voltar | Página Principal