stored procedures

Home > Banco de dados (DBWare) > Stored procedures

Stored procedures pelo DBWare

[!IMPORTANT]
Em desenvolvimento (2.0). Ainda não está numa versão lançada e pode mudar.

Delphi e Lazarus · Nível: intermediário

Em uma frase: o TRALDBModule chama as stored procedures do banco pelo nome,
numa rota só (proc/<nome>), acha cada uma no catálogo do próprio banco e responde
no formato que o cliente pedir - para um dataset do RAL ou para qualquer cliente
HTTP, de qualquer linguagem.

Você vai precisar: o DBWare funcionando, um banco com procedures
(Firebird, PostgreSQL ou MySQL; o SQLite não tem) e, no servidor, um storage para a
resposta (RALStoragePackage; o JSON é o padrão).

Ligar no servidor

ProcOptions.	Padrão	O que é
Enabled	False	cria a rota; desligado, ela não existe (nem para os datasets do RAL)
Route	proc	prefixo: /db/proc/<nome>
Filter	vazio	máscaras de nome aceitas, uma por linha (API_*); vazio = todas
Schemas	vazio	esquemas aceitos, um por linha; vazio = o padrão da conexão
CatalogLife	5	minutos que o módulo guarda o que o catálogo disse; 0 = pergunta sempre
Storage	vazio	formato da resposta quando o Accept do cliente não pede outro; vazio = JSON

Nada de procedure fica guardado no módulo nem no .dfm: uma procedure criada ou
alterada no banco aparece sozinha quando o cache vence (RefreshProcedures esquece
na hora). O módulo escolhe a sintaxe pelo banco e pelo tipo da procedure:

Banco	Procedure	Chamada
Firebird	selecionável (SUSPEND)	select * from NOME(...)
Firebird	executável	execute procedure NOME(...); as saídas voltam como parâmetros
PostgreSQL	procedure	call NOME(...); OUT/INOUT voltam como parâmetros
PostgreSQL	function	select * from NOME(...)
MySQL	PROCEDURE	call NOME(...); OUT/INOUT voltam como parâmetros
MySQL	FUNCTION	select NOME(...)

[!WARNING]
Ligar a rota abre todas as procedures que o usuário da conexão pode executar.
O limite de verdade é o banco: use um usuário só para a API, com GRANT EXECUTE
apenas no que ela deve expor. Filter, Schemas e o evento abaixo apertam mais.

OnValidateProc: a parte personalizada

Roda antes de cada chamada (pela rota ou por um dataset do RAL), com o que o catálogo
disse e os parâmetros já no tipo certo:

procedure TForm1.RALDBModule1ValidateProc(ASender: TObject; ARequest: TRALRequest;
  AProc: TRALDBProcInfo; AParams: TParams; var AAllow: Boolean);
begin
  // o vendedor vem do token, não do cliente: o valor posto aqui vence
  if AParams.FindParam('VENDEDOR') <> nil then
    AParams.ParamByName('VENDEDOR').AsString := ARequest.ParamByName('sub').AsString;
  // e uma regra qualquer
  AAllow := not SameText(AProc.Name, 'API_FECHA_MES') or EhGerente(ARequest);
end;

Num cliente RAL

Nos três datasets (TRALDBFDMemTable, TRALDBZMemTable, TRALDBBufDataset):

Membro	O que faz
StoredProcName	a procedure; com ela, Open e ExecProc chamam a procedure em vez de rodar SQL
ExecProc	chama; as saídas voltam em Params, e as linhas, se houver, abrem o dataset
NextRecordSet	passa para o próximo result set da última chamada
ResultSets	liga o 2º, 3º... result set a outros datasets: outro dataset do RAL ou qualquer TDataSet

qrSaldo.StoredProcName := 'API_BAIXA_ESTOQUE';
qrSaldo.Params.CreateParam(ftInteger, 'PRODUTO', ptInput).AsInteger := 10;
qrSaldo.Params.CreateParam(ftInteger, 'QTD', ptInput).AsInteger := 2;
qrSaldo.ExecProc;                                    // levanta exceção se falhar
ShowMessage(qrSaldo.ParamByName('SALDO').AsString);  // saída da procedure

Sem dataset do RAL, direto pela conexão, em quaisquer datasets (pelo storage):

RALDBConnection1.ExecProcRemote('API_PEDIDOS_DO_CLIENTE', vParams,
  [cdsPedidos, cdsItens], RALStorageBINLink1);

O ProcRoute do TRALDBConnection tem de ser igual ao ProcOptions.Route do
servidor (os dois começam em proc).

Vários result sets

Uma procedure do MySQL com dois select, ou do PostgreSQL que devolve dois
refcursor, devolve dois datasets numa chamada. Cada um vai no formato nativo do
componente quando cliente e servidor usam o mesmo (FireDAC com FireDAC, por exemplo)
e pelo storage nos outros casos. Quem lê o servidor é o FireDAC (NextRecordSet); o
SQLDB e o Zeos entregam um result set por chamada.

Tudo ou nada

Duas formas, as duas sem guardar nada no servidor entre pedidos:

• No servidor, TRALDBModule.AutoCommit := False: cada pedido é uma transação.
  Se um comando falha, todos os do pedido são desfeitos e voltam com
  Rolled back: another statement of the same request failed. - o que falhou traz o
  erro do banco. Com True (o padrão), cada comando é gravado sozinho, como sempre.

• No cliente, a fila do TRALDBConnection: nada sai pela rede até o Commit, que
  manda tudo num pedido só, gravado inteiro ou nada:

  :::pascal
  RALDBConnection1.StartTransaction;
  mtClientes.ApplyUpdates;
  mtPedidos.ExecSQL;
  RALDBConnection1.ExecProcRemote('BAIXA_ESTOQUE', vParams, []);
  RALDBConnection1.Commit; // um pedido; cada um recebe a sua resposta; levanta exceção se falhar

Rollback descarta a fila (nada foi enviado). Dentro da fila não dá para ler o
resultado de um comando para montar o seguinte, e o Open é recusado: essa lógica
vai numa procedure, que roda inteira no servidor.

Para clientes HTTP de outras linguagens

A rota é POST /<Domain>/<Route>/<nome>, por exemplo POST /db/proc/API_SALDO.

Pedido. Os parâmetros, pelo nome, num objeto JSON no corpo, ou na query string,
ou como campos de formulário. Texto é convertido para o tipo que o catálogo diz:
números com ponto, datas em ISO 8601, booleanos true/false, binário em base64.

curl -X POST http://localhost:8000/db/proc/API_SALDO \
  -H "Content-Type: application/json" -d '{"PRODUTO": 10}'

Resposta.

Caso	Corpo
a procedure não devolve nada	204 No Content
um dataset (as saídas, ou as linhas)	o dataset no storage escolhido
mais de um	multipart/mixed, uma parte por dataset
procedure desconhecida, fora do Filter/Schemas	404
nome inválido	400
OnValidateProc recusou	403
erro do banco	500, com a mensagem (escondida com HideErrorDetails)

O storage é o que o Accept pede (application/json, text/csv, application/xml,
application/octet-stream... entre os que o servidor tem), senão ProcOptions.Storage,
senão JSON. As saídas viram um dataset de uma linha, um campo por parâmetro.

No multipart/mixed, as partes vêm nesta ordem: params (as saídas, quando há),
data (o primeiro result set), data2, data3... Cada parte tem o seu
Content-Type e Content-Disposition: inline; name="...":

HTTP/1.1 200 OK
Content-Type: multipart/mixed; boundary=ral3E571E648F401AB31E2C9C7A

--ral3E571E648F401AB31E2C9C7A
Content-Disposition: inline; name="params"
Content-Type: application/json

{ ...o dataset das saídas... }
--ral3E571E648F401AB31E2C9C7A
Content-Disposition: inline; name="data"
Content-Type: application/json

{ ...as linhas... }
--ral3E571E648F401AB31E2C9C7A--

Em JavaScript, o Response.formData() do navegador só lê multipart/form-data;
para multipart/mixed, separe pelo boundary do Content-Type:

const r = await fetch('/db/proc/API_PEDIDOS', {method: 'POST',
  headers: {'Content-Type': 'application/json'}, body: JSON.stringify({CLIENTE: 7})});
const type = r.headers.get('Content-Type');
if (type.startsWith('multipart/mixed')) {
  const boundary = type.split('boundary=')[1];
  const parts = (await r.text()).split('--' + boundary).slice(1, -1).map(p => {
    const [head, body] = p.split('\r\n\r\n');
    return {name: /name="([^"]+)"/.exec(head)[1], body: body.replace(/\r\n$/, '')};
  });
  // parts[0].name === 'params', parts[1].name === 'data' ...
}

Erros comuns

O que acontece	Causa	O que fazer
404 Stored procedure X not found, or not allowed.	não está no catálogo, fora do Filter ou de Schemas	confira o nome, as máscaras e o esquema
404 em toda chamada	ProcOptions.Enabled desligado (a rota não existe)	ligue
403 Stored procedures are not enabled...	um dataset do RAL chamou com a rota desligada	ligue ProcOptions.Enabled
403 Stored procedure call rejected by OnValidateProc.	a sua regra recusou	confira o evento
500 com permission denied/no permission	o usuário do banco não tem GRANT EXECUTE	é o banco dizendo não, como deve
This database has no stored procedures.	SQLite	use outro banco
Stored procedure X has more than one signature...	sobrecarga no PostgreSQL	mande os nomes de parâmetro que distinguem as versões
RALLegacyWire is on...	o formato antigo não leva chamada de procedure	desligue RALLegacyWire nesse lado

Veja também

• Banco de dados (DBWare)
• Segurança para produção
• Autenticação