Como montar instrução SQL para o serviço externo

Na instrução SQL os campos retornados serão automaticamente adicionados no campo “Campos de retornos”, e os parâmetros da instrução serão adicionados automaticamente no campo “Parâmetros” da tela.

Exemplo de SQL montado: 

-- Este SQL retorna o nome e a idade dos alunos:
-- De um curso
-- Ou de um curso no período letivo.
 
SELECT PES_NOME AS nome, 
(YEAR(GETDATE()) - YEAR(PES_DATNAS)) AS idade 
FROM TB_PESSOA 
INNER JOIN TB_INGRESSO ON ING_PESID = PES_ID 
INNER JOIN TB_MESTRE_ALUNO ON MAL_INGID = ING_ID 
INNER JOIN TB_TURMA ON TUR_ID = MAL_TURID 
INNER JOIN TB_PERIODO_LETIVO ON PEL_PERID = TUR_PERID
INNER JOIN TB_CURSO ON CUR_ID = TUR_CURID 
WHERE CUR_ID = :curso AND PEL_PERID = COALESCE(:periodo, PEL_PERID)

Quando adicionado esta instrução SQL no serviço, no campo “Campos de retornos” será adicionado o campo PES_NOME com o alias nome e a operação de calcular a idade com o alias idade.
E no campo “Parâmetro” será adicionado o parâmetros :curso e o parâmetro :periodo.

Declarando parâmetros no SQL

Para declarar um parâmetro na consulta sempre deve ser utilizado “ : “.
Exemplo:

WHERE campo = :parametro

Ao salvar a instrução com um parâmetro, ele é adicionado automaticamente no campo “Parâmetros”. Então, depois de salvar o SQL clique no campo e defina o tipo de dados do mesmo.

Utilizando um parâmetro que não é obrigatório

Para utilizar um parâmetro que não é obrigatório deve ser utilizado COALESCE.
O coalesce trabalha da seguinte forma:

WHERE campo = COALESCE(:parametro, campo)

Ele atribui o primeiro valor ao campo, se não encontrar atribui o segundo.
Exemplo: Se o parâmetro ”:parametro” tiver um valor, esse valor será atribuído ao “campo” e se não tiver valor, atribui “campo” para “campo”.


Tipos de retorno para instruções do tipo “Consultar”

Para Instruções SQL do tipo “Consultar”, abaixo da caixa de texto para inserção da query, é solicitado o parâmetro “Retorna apenas primeiro registro”.

Quando marcado, será retornado apenas o primeiro registro encontrado, e o “json” de retorno não estará no formato de lista, exemplo: 

{
    "resultado": "SUCESSO",
    "pes_id": 1,
    "pes_nome": "Milena Andressa"
}


Atenção: por questões de performance, quando utilizado para retornar apenas o primeiro registro, é altamente recomendável que utilize-se “TOP / ROWNUM” para limitação dos dados, de acordo com o tipo do banco de dados utilizado. Isso porque o sistema irá executar a query exatamente da forma que foi definida, e posteriormente, pegará apenas o primeiro registro retornado. Neste caso, se não for limitado na query pode pesar, e tornar a consulta demorada.



Quando desmarcado, será retornado todos os registros encontrados, e o “json” de retorno estará no formato de lista, exemplo: 

[
    {
        "resultado": "SUCESSO",
        "dados": [
            {
                "pes_id": 1,
                "pes_nome": "Milena Andressa Radtke"
            },
            {
                "pes_id": 8,
                "pes_nome": "Elaine Scoz"
            },
            {
                "pes_id": 10,
                "pes_nome": "Fabiana Beumer Pasqualini"
            },
            {
                "pes_id": 15,
                "pes_nome": "Iara Maria Poffo"
            },
            {
                "pes_id": 19,
                "pes_nome": "Juliana Odorizzi"
            }
        ]
    }
]


Para o tipo “Alteração de dados”, referente à um INSERT

No cadastro de serviço externo, quando o tipo é “Instrução SQL” e a operação é de “Alteração de dados”, onde será informado um “INSERT”, deve-se configurar da seguinte forma:

Na imagem acima, deve-se definir o nome do serviço, e o script que será executado. O nome da consulta é muito importante, pois posteriormente, na chamada deste serviço, ao informar o “body” com os parâmetros, é este nome que deverá ser referenciado na lista. Os parâmetros, assim como nos demais tipos de serviço, devem ser precedidos por “ : ” (dois pontos).

Ao configurar os parâmetros, conforme imagem abaixo, deve-se colocar o parâmetro de lista utilizando no nome o mesmo nome definido anteriormente, e este parâmetro de lista deve ser colocado como “coleção” dos demais parâmetros que serão informados:

Ao realizar a chamada, no json deverá utilizar exatamente o nome dado à coleção, informando os parâmetros desta coleção, conforme exemplo abaixo:

** E quando preciso executar mais de um INSERT ou UPDATE, no mesmo serviço, como fazer?

Para executar mais de um insert/update no mesmo serviço, basta definir um detalhe de comandos. No exemplo abaixo, quero executar dois update alterando o nome dos países. É um exemplo fictício, poderia ter uma tabela envolvida no primeiro script, e outra no segundo, por exemplo. Observe abaixo, que um dos detalhes de execução de sql chama-se “pais”, e o outro detalhe chama-se “outropais”:

Ao acessar os parâmetros, deve-se especificar além do tipo, também a sua respectiva “coleção”:

Para realizar a chamada, deve-se especificar cada um dos detalhes no json (collection), e dentro deles, os seus respectivos parâmetros que estarão sendo utilizados (os quais possuem a collection respectiva como collection no parâmetro). 

{
   "pais":[
      {
         "paisid":156
      }
   ],
   "outropais":[
      {
         "outroid":157
      }
   ]
}
  • (edição externa)