DB V2: Cenários de uso

Saiba mais sobre cenários práticos de uso do conector DB V2, desde a definição de propriedades personalizadas de conexão até o tratamento de tipos de dados personalizados.

O DB V2 é um conector que oferece suporte às operações SELECT, INSERT, DELETE e UPDATE, além da execução de procedures, retornando os resultados em uma estrutura JSON. Para a lista completa de bancos de dados compatíveis, consulte a documentação de Bancos de dados suportados.

Este documento apresenta diversos cenários de uso do conector, como a definição de propriedades personalizadas de conexão e o tratamento de tipos de dados personalizados.

Confira abaixo os cenários de uso compatíveis:

Configurando uma operação QUERY com uma instrução SELECT

Use o DB V2 para executar uma consulta SELECT sempre que precisar recuperar dados de um banco de dados configurado.

Para isso, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, por exemplo: SELECT * FROM CLIENTES.

Após a execução do pipeline, a saída será:

{
  "data": [
    {
      "uf": "SP",
      "codigo": 1,
      "cidade": "Osasco",
      "logradouro": "Rua Laranjeiras",
      "name": "João Bosco",
      "due_date": "2019-03-27",
      "email": "[email protected]",
      "cep": "XXXXX-XXX"
    },
    {
      "uf": "SP",
      "codigo": 2,
      "cidade": "São Paulo",
      "logradouro": "Rua João Bosco",
      "name": "Roberto Carlos",
      "due_date": "2019-03-27",
      "email": "[email protected]",
      "cep": "XXXXX-XXX"
    }
  ],
  "updateCount": 0,
  "rowCount": 2
}

Entendendo a saída

A consulta retorna um objeto JSON com três propriedades principais:

• data: Um array de objetos que representam as linhas retornadas pela consulta. Cada objeto contém propriedades correspondentes às colunas do SELECT. Por exemplo, se a consulta for SELECT name FROM CLIENTES, cada objeto no array terá apenas a propriedade name.

• updateCount: A quantidade de linhas afetadas pela consulta. Neste caso, o valor é 0 porque a operação SELECT não altera a tabela.

• rowCount: A quantidade de linhas retornadas pela consulta.

Configurando a operação QUERY com uma instrução UPDATE

Use o DB V2 para atualizar registros no seu banco de dados executando uma instrução UPDATE.

Para isso, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, por exemplo: UPDATE CLIENTES SET uf='SP' WHERE uf is null.

Após a execução do pipeline, a saída será:

{
  "data": null,
  "updateCount": 5,
  "rowCount": 0
}

Entendendo a saída

• updateCount: Confirma que cinco linhas na tabela foram modificadas neste exemplo.

Configurando a operação QUERY com uma instrução DELETE

Use o DB V2 para remover dados do seu banco de dados com uma instrução DELETE.

Para isso, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, por exemplo: DELETE FROM CLIENTES WHERE codigo='111111.

Após a execução do pipeline, a saída será:

{
  "data": null,
  "updateCount": 1,
  "rowCount": 0
}

Entendendo a saída

• updateCount: Confirma que uma linha foi excluída da tabela neste exemplo.

Configurando a operação QUERY com uma instrução INSERT

Use o DB V2 para inserir novos registros no seu banco de dados com uma instrução INSERT.

Para isso, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, por exemplo: INSERT INTO CLIENTES (name, email) VALUES ('My Name', '[email protected]').

Após a execução do pipeline, a saída será:

{
  "data": null,
  "updateCount": 1,
  "rowCount": 0
}

Entendendo a saída

• updateCount: Confirma que uma linha foi criada na tabela neste exemplo.

Mantendo a conexão ativa para drivers antigos ou instáveis

Se o seu banco de dados for incompatível com JDBC 4.0 ou tiver dificuldades para manter uma conexão estável, você pode usar o DB V2 para executar qualquer query — mesmo que a mesma chamada seja feita novamente após 20 minutos. Isso garante que a conexão com o banco de dados permaneça ativa.

Na aba Advanced Settings, é possível configurar a Connection Test Query. Essa consulta é executada antes da especificada no parâmetro SQL Statement, garantindo que a conexão com o banco de dados esteja ativa e prevenindo erros.

Se o teste de conexão falhar, uma nova conexão será aberta automaticamente. Isso ajuda a evitar problemas como conexões "travadas" e timeouts causados por falhas anteriores.

Essa opção deve ser considerada apenas para bancos de dados antigos que utilizam versões do JDBC anteriores à 4.0.

Para isso, conecte o DB V2 ao trigger e configure da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do seu banco de dados, como por exemplo jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, por exemplo INSERT INTO CLIENTES (name, email) VALUES ('My Name', '[email protected]').

Aba Advanced Settings:

• Connection Test Query: Insira uma consulta para testar a conexão, como SELECT version().

Após a execução do pipeline, o resultado da consulta será exibido na coluna Saída do Painel de Execução.

Mantendo a conexão ativa por 5 ou 30 minutos

Use o DB V2 para manter a conexão com o banco de dados aberta por 5 ou 30 minutos, dependendo das suas necessidades.

Configure o conector da seguinte forma:

Aba Advanced Settings:

• Keep Connection: Habilite essa opção para estender o tempo padrão de conexão de 5 minutos para 30 minutos, garantindo uma sessão ativa mais longa com o banco de dados.

Interrompendo a execução do pipeline em caso de erro

Use o DB V2 para controlar a execução do pipeline, interrompendo-a quando ocorrer um erro durante uma operação no banco de dados.

Para controlar como os erros impactam a execução, configure o conector da seguinte forma:

Aba General:

• Fail On Error: Habilite essa opção para parar o pipeline se ocorrer um erro ao invocar o DB V2. Se desabilitado, o pipeline continuará executando mesmo que a operação falhe.

Acessando e gravando arquivos no banco de dados

Use o DB V2 para ler ou gravar arquivos em campos BLOB ou CLOB no seu banco de dados. Isso permite que você:

• Grave arquivos do pipeline em colunas BLOB ou CLOB no banco de dados.

• Leia dados BLOB ou CLOB do banco de dados como arquivos dentro do pipeline.

Ao habilitar a opção Blob As File ou Clob As File, você pode especificar um caminho de arquivo para essas operações:

• Ao usar INSERT, fornecer um caminho de arquivo do pipeline armazena o arquivo no banco de dados.

• Ao usar SELECT, o conteúdo BLOB ou CLOB recuperado é convertido em um arquivo dentro do pipeline.

Passo 1: Ler o arquivo

Para ler o arquivo, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Step Name: Nomeie como “Ler Arquivo” ou algo similar.

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do seu banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Insira a instrução SQL, por exemplo SELECT blob FROM CLIENTES WHERE id=78.

• Blob As File / Clob As File: Habilite uma ou ambas as opções. Se Clob As File for habilitado, o parâmetro Charset aparecerá para definir a codificação do arquivo.

Após a execução do pipeline, a saída será:

{
  "data": [
    {
      "blob": "E4G17F.file",
      "clob": "H2K89G.file"
    }
  ],
  "updateCount": 0,
  "rowCount": 1
}

Dois arquivos, E4G17F.file e H2K89G.file, foram gerados e disponibilizados no pipeline.

Passo 2: Gravar o arquivo

Para gravar o arquivo, conecte outro DB V2 ao conector “Ler Arquivo” e configure-o da seguinte forma:

Aba General:

• Step Name: Nomeie como “Gravar Arquivo” ou algo similar.

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Insira a URL do seu banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Insira a instrução SQL, por exemplo INSERT INTO CLIENTES (blob, clob) VALUES ( {{ message.data.blob }}, {{ message.data.clob }} ).

Este exemplo usa expressões Double Braces ({{ message.data.blob }}, {{ message.data.clob }}) para referenciar o resultado da consulta do conector anterior. Essas expressões recuperam os nomes dos arquivos gerados (E4G17F.file, H2K89G.file) e passam seus caminhos para o comando INSERT nos campos blob e clob.

• Blob As File / Clob As File: Habilite uma ou ambas as opções. Se Clob As File for habilitado, o parâmetro Charset aparecerá para definir a codificação do arquivo.

• Type Properties: Clique em Add e informe os seguintes dados:

  • Key: 0 (cada expressão com Double Braces tem um índice; 0 se refere à primeira)

  • Type: BLOB

  • Out Parameter Name: (deixe em branco)

  • Parameter Type: IN

Após a execução do pipeline, a saída será:

{
"data": null,
"updateCount": 1,
"rowCount": 0
}

Entendendo a saída

O resultado indica que uma linha da tabela foi afetada. Primeiro, o conector Ler Arquivo recuperou o conteúdo BLOB e CLOB do banco de dados e gerou arquivos. Em seguida, o conector Gravar Arquivo usou esses arquivos do pipeline e os inseriu no banco de dados como um novo registro.

Executando uma procedure

Use o DB V2 para chamar e executar uma procedure armazenada no seu banco de dados e recuperar o resultado.

Exemplo

Neste exemplo, o banco de dados contém a seguinte procedure:

CREATE PROCEDURE `proc_sum_one` (INOUT `num` INTEGER)
BEGIN
    SET `num` = `num` + 1;
END;

Para executar esse procedimento no seu pipeline, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione PROCEDURE.

• Database URL: Informe a URL do seu banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, como call proc_sum_one({{ message.num }}).

Neste exemplo, usamos a expressão com Double Braces {{ message.num }} para acessar um valor do conector anterior. Para mais detalhes, consulte a documentação.

• Type Properties: Clique em Add e informe os seguintes dados:

  • Key: 0 (cada expressão com Double Braces tem um índice; 0 se refere à primeira)

  • Type: INTEGER

  • Out Parameter Name: result (define o nome do parâmetro de saída da procedure)

  • Parameter Type: INOUT

Depois que o conector estiver configurado e salvo, abra o Painel de Execução e insira um valor de entrada na coluna Payload, como:

{ 
  "num": 3 
}

Após executar o pipeline, a saída será:

{
  "data": {
    "result": 4
  },
  "success": true
}

Definindo propriedades personalizadas de conexão

Use o DB V2 para definir propriedades personalizadas de conexão, personalizando como o conector interage com seu banco de dados.

Para isso, conecte o DB V2 ao trigger e configure-o da seguinte forma:

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do seu banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Informe a instrução SQL, como SELECT * FROM CLIENTES.

• Custom Connection Properties: Informe as propriedades personalizadas de conexão. Por exemplo: { "connectTimeout": 1 }

Após executar o pipeline, a saída será:

{
  "success": false,
  "message": "Could not establish connection to the database",
  "error": "java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=xxxxx)(port=XXX)(type=master) : null"
}

Neste caso, a propriedade de conexão connectTimeout foi definida como 1 (um segundo) para demonstrar que foi aplicada e resultou em um erro de conexão.

Lendo tipos de dados personalizados de um banco de dados Oracle

Use o DB V2 para ler tipos de dados personalizados de um banco de dados Oracle quando uma tabela configurada contém esse tipo de dado.

Exemplo:

Suponha que você tenha uma tabela criada com a seguinte estrutura:

CREATE TABLE project (
    name VARCHAR2(50),
    coordinator OWNER
);

E o tipo personalizado OWNER seja definido como:

CREATE TYPE OWNER AS OBJECT (
    name VARCHAR2(50),
    email VARCHAR2(30)
);

Ao executar a seguinte consulta:

SELECT * FROM projeto

O resultado será:

{
  "data": [
    {
      "name": "Example Project",
      "owner": [
        "Coordinator Name",
        "Coordinator Email"
      ]
    }
  ],
  "updateCount": 0,
  "rowCount": 1
}

Se o tipo personalizado incluir outros tipos personalizados em sua estrutura, os dados retornados serão aninhados, como mostrado no exemplo abaixo:

{
  "data": [
    {
      "name": "Example Project",
      "owner": [
        "Coordinator Name",
        "Coordinator Email",
        [
          "Campo de um segundo tipo personalizado",
          [
            "Campo de um terceiro tipo personalizado"
          ]
        ]
      ]
    }
  ],
  "updateCount": 0,
  "rowCount": 1
}

Tipos personalizados não expõem os nomes dos campos definidos em sua estrutura; apenas a ordem desses campos é preservada.

No exemplo acima, o primeiro elemento do array "owner" corresponde ao campo "name" e o segundo ao campo "email", conforme definido no tipo OWNER.

Ao trabalhar com tipos personalizados em consultas do tipo PROCEDURE, é necessário definir o Out Parameter Name com o mesmo nome do tipo personalizado. Neste caso, seria OWNER.

Enviando tipos de dados personalizados a partir de um banco de dados Oracle

Use o conector DB V2 para inserir tipos de dados personalizados (structs) em uma tabela de um banco de dados Oracle.

O conector DB V2 não oferece suporte às expressões Double Braces para resolver tipos personalizados (struct) ao usar comandos INSERT ou UPDATE.

Exemplo

Os seguintes tipos personalizados e tabela são criados no banco de dados:

• Tipos personalizados:

CREATE OR REPLACE TYPE CONTACT AS OBJECT (
  NAME VARCHAR2(50),
  PHONE VARCHAR2(15)
);

CREATE OR REPLACE TYPE PERSON AS OBJECT (
  ID NUMBER(10,0),
  NAME VARCHAR2(50),
  CONTACT_INFO CONTACT
);

CREATE OR REPLACE TYPE DEPARTMENT AS OBJECT (
  ID NUMBER(10,0),
  NAME VARCHAR2(50)
);

• Tabela:

CREATE TABLE EMPLOYEE (
  PERSON PERSON, 		
  DEPT DEPARTMENT,
  SALARY NUMBER(12,2)
);

Para inserir dados na tabela EMPLOYEE, você pode usar as abordagens a seguir:

• Exemplo de entrada:

{
	"personName": "John A.",
	"contactName": "John",
	"contactPhone": "99999",
	"departmentName": "IT"
}

• Usando um comando INSERT a partir de uma consulta SELECT:

INSERT INTO EMPLOYEE
SELECT 
  PERSON(
    TRUNC(DBMS_RANDOM.VALUE(0,999999)), 
    {{ message.personName }}, 
    CONTACT({{ message.contactName }}, {{ message.contactPhone }})
  ),
  DEPARTMENT(
    TRUNC(DBMS_RANDOM.VALUE(0,999999)), 
    {{ UPPERCASE(message.departmentName) }}
  ),
  3030.67
FROM DUAL;

• Usando uma PROCEDURE previamente criada:

Criação da procedure:

CREATE OR REPLACE PROCEDURE MY_PROC(DEPT IN DEPARTMENT, EMP IN OUT PERSON) IS
  SAL NUMBER(12,2);
BEGIN
  SAL := 4567.89 * 1.10;
  INSERT INTO EMPLOYEE VALUES (EMP, DEPT, SAL);	
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001, 'An error was encountered - ' || SQLCODE || ' - ERROR: ' || SQLERRM);
END;

Chamada da procedure:

DECLARE
  CONT CONTACT;
  EMP PERSON;
  DEPT DEPARTMENT;
BEGIN
  DEPT := DEPARTMENT(
    TRUNC(DBMS_RANDOM.VALUE(0,999999)), 
    {{ UPPERCASE(message.departmentName) }}
  );

  CONT := CONTACT(
    {{ message.contactName }}, 
    {{ message.contactPhone }}
  );

  EMP := PERSON(
    TRUNC(DBMS_RANDOM.VALUE(0,999999)), 
    {{ message.personName }}, 
    CONT
  );

  MY_PROC(DEPT, EMP);
END;

• Usando um script SQL completo:

DECLARE
  CONT CONTACT;
  EMP PERSON;
  DEPT DEPARTMENT;
  SAL EMPLOYEE.SALARY%TYPE;
BEGIN
  DEPT := DEPARTMENT(
    TRUNC(DBMS_RANDOM.VALUE(0,999999)), 
    {{ UPPERCASE(message.departmentName) }}
  );

  CONT := CONTACT(
    {{ message.contactName }}, 
    {{ message.contactPhone }}
  );

  EMP := PERSON(
    TRUNC(DBMS_RANDOM.VALUE(0,999999)), 
    {{ message.personName }}, 
    CONT
  );

  SAL := 4567.89;

  INSERT INTO EMPLOYEE VALUES (EMP, DEPT, SAL);	
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001, 'An error was encountered - ' || SQLCODE || ' - ERROR: ' || SQLERRM);
END;

Usando instrução SQL bruta para lidar com consultas condicionais

Suponha que seu fluxo de integração precise executar duas consultas SQL diferentes, dependendo da lógica de negócio:

Consulta 1:

SELECT * FROM TABLE_1
WHERE ID = 123

Consulta 2:

SELECT * FROM TABLE_2
WHERE CODE = 'ABC'

O conector DB V2 oferece suporte ao recurso Raw SQL Statement, permitindo que você referencie dinamicamente uma consulta inteira usando Double Braces. Isso significa que você pode definir a consulta completa em um conector anterior e passá-la como uma variável para o DB V2, adaptando-a em tempo de execução.

Para usar esse recurso, você deve configurar a consulta SQL em um conector como o Template Transformer e então referenciá-la no DB V2.

Exemplo passo a passo

Passo 1: Configure o pipeline

Adicione um Template Transformer e um conector DB V2 ao seu pipeline.

Passo 2: Configure o conector Template Transformer

• Preserve Original: Ative esta opção.

• Body: Defina o seguinte corpo:

SELECT * FROM 
${table}
WHERE 
<#if id?? > 
    ID = {{ message._query.id }} 
</#if>

<#if code??>
    CODE = {{ message._query.code }} 
</#if>

Passo 3: Configure o conector DB V2

Aba General:

• Account Type: Selecione o tipo de conta configurado.

• Account: Escolha a conta usada para autenticar no banco de dados.

Aba Operation:

• Type: Selecione QUERY.

• Database URL: Informe a URL do seu banco de dados, como jdbc:mysql://myserver/mydb.

• SQL Statement: Defina como: {{ message.query }}

Aba Advanced Settings:

• Raw SQL Statement: Ative essa opção.

Passo 4: Teste a execução

No Painel de execução, teste os seguintes payloads de entrada:

Payload para a Consulta 1:

{
    "query": {
        "table": "TABLE_1",
        "id": 123
    }
}

Payload para a Consulta 2:

{
    "query": {
        "table": "TABLE_2",
        "code": "ABC"
    }
}

Os resultados esperados para cada consulta são:

Resultado para a Consulta 1:

{
    "data": [
        {
            "id": 123,
            "name": "John",
            "role": "Developer",
            ...
        }
    ],
    "updateCount": 0,
    "rowCount": 1
}

Resultado para a Consulta 2:

{
    "data": [
        {
            "code": "ABC",
            "fullName": "Paul Smith",
            "zipCode": "99999",
            ...
        }
    ],
    "updateCount": 0,
    "rowCount": 1
}

Entendendo a saída

O resultado da consulta é retornado como um JSON com três propriedades principais:

• data: Um array de objetos representando cada linha retornada. Cada objeto contém as colunas selecionadas pela consulta.

  • Exemplo: Se sua consulta for SELECT name FROM TABLE_1 WHERE ID = 123, cada objeto no array data conterá apenas a propriedade name.

• updateCount: Número de linhas afetadas pelo comando SQL. Para operações SELECT, este valor é sempre 0, já que nenhuma tabela é alterada.

• rowCount: Número de linhas retornadas pela consulta.

Embora outros conectores como JSON Generator ou Transformer (JOLT) também possam ser usados para definir a string de consulta, o Template Transformer oferece algumas vantagens importantes:

• Usa a tecnologia FreeMarker para validar os dados antes de executar a consulta.

• Ajuda a evitar SQL Injection: a declaração com Double Braces não é resolvida pelo próprio Template Transformer, mas sim pelo DB V2, que configura e valida os parâmetros da consulta com antecedência usando Prepared Statements.

• Fornece uma maneira mais segura e fácil de criar consultas no seu pipeline.