A linguagem procedural do Firebird, a PSQL, foi incrementada com novos comandos e outras melhorias. A primeira melhoria que irei apresentar se trata do uso de subqueries como expressões PSQL. Antes, o desenvolvedor era obrigado a criar uma variável e fazer um SELECT INTO para obter um valor e usá-lo posteriormente em outro comando. Agora as subqueries fazem parte do conjunto de expressões PSQL e podem ser usadas da mesma maneira que já podiam ser usadas em comandos SQL. A listagem 15 mostra o uso de uma subquery em uma comparação e em seguida na atribuição de um valor retornado a uma variável.
Listagem 15. Usando subqueries como expressões PSQL.
SET TERM !;
EXECUTE BLOCK RETURNS (COD VARCHAR(10)) AS
BEGIN
IF ((SELECT COUNT(*) FROM DOCUMENTOS) > 3) THEN
BEGIN
COD = (SELECT MAX(CODIGO) FROM DOCUMENTOS);
SUSPEND;
END
END!
SET TERM ;!
Na listagem 15 o parâmetro de retorno COD foi definido com o tipo VARCHAR(10) e recebe um valor lido da coluna CODIGO da tabela DOCUMENTOS. Uma alteração do tipo desta coluna poderia deixar este bloco de código inconsistente ou inválido. No Firebird 2.1 foi adicionado o suporte ao uso de domains em PSQL. Neste caso, o desenvolvedor precisaria criar um domínio e usá-lo na definição da tabela e na definição do parâmetro de retorno do EXECUTE BLOCK para ter um código consistente. Dependendo da situação, a criação de domínios para todas as colunas pode se tornar uma tarefa extremamente burocrática. Pensando nisso, o Firebird 2.5 adiciona a cláusula TYPE OF COLUMN. Com essa cláusula é possível declarar parâmetros e variáveis ou fazer casts usando o tipo definido de uma coluna. Ao alterar o tipo da coluna, a alteração se reflete automaticamente em todas as stored procedures e triggers que façam uso de seu tipo. A listagem 16 mostra o mesmo código da listagem 15 mas usando TYPE OF COLUMN para o parâmetro COD.
Listagem 16. Usando a cláusula TYPE OF COLUMN.
EXECUTE BLOCK RETURNS (COD TYPE OF COLUMN DOCUMENTOS.CODIGO) AS
BEGIN
IF ((SELECT COUNT(*) FROM DOCUMENTOS) > 3) THEN
BEGIN
COD = (SELECT MAX(CODIGO) FROM DOCUMENTOS);
SUSPEND;
END
END!
No Firebird 2.1 foram acrescentados os triggers de eventos de banco de dados, como o evento ON CONNECT. Uma das funcionalidades que podiam ser feitas com este tipo de trigger era a rejeição de uma conexão, permitindo que os desenvolvedores implementassem um sistema de segurança dentro do banco de dados. Para rejeitar uma conexão é necessário lançar uma exceção dentro do trigger. Nota: este tipo de segurança não impede que o usuário SYSDBA tenha acesso ao banco, pois os administradores podem desativar os triggers de banco de dados conectando-se usando a opção -nodbtriggers do ISQL. O problema é que alguns desenvolvedores gostariam de gravar uma entrada em uma tabela de log registrando o evento, mas a exceção causava o rollback da criação deste registro. Esta e outras situações (como comunicação com outros sistemas através de UDFs) agora podem se beneficiar de um novo comando, o IN AUTONOMOUS TRANSACTION. Assim como um IF ou WHILE, este comando aceita um subcomando (ou vários, com o uso de BEGIN END). No início da execução do IN AUTONOMOUS TRANSACTION é aberta uma nova transação e o subcomando é executado dentro desta transação. Caso o subcomando execute por completo, é feito um COMMIT na transação criada. Caso ocorra uma exceção, é feito um rollback. A listagem 17 apresenta um trigger ON CONNECT que aceita apenas conexões dos usuários SYSDBA e ADMIN e grava o nome dos outros usuários que tiverem a conexão rejeitada. Note que a exceção usada para rejeitar a conexão é lançada fora do bloco IN AUTONOMOUS TRANSACTION, que já havia terminado e, consequentemente, inserido o registro e finalizado a transação com COMMIT.
Listagem 17. Usando o comando IN AUTONOMOUS TRANSACTION.
CREATE TABLE CONN_LOG (USUARIO VARCHAR(128), DATA TIMESTAMP);
CREATE EXCEPTION E_CONN 'Acesso negado.';
SET TERM !;
CREATE TRIGGER T_CONN ON CONNECT AS
BEGIN
IF (CURRENT_USER <> 'SYSDBA' AND CURRENT_USER <> 'ADMIN') THEN
BEGIN
IN AUTONOMOUS TRANSACTION DO
BEGIN
BEGIN
INSERT INTO CONN_LOG
VALUES (CURRENT_USER, CURRENT_TIMESTAMP);
END
VALUES (CURRENT_USER, CURRENT_TIMESTAMP);
END
EXCEPTION E_CONN;
END
END!
SET TERM ;!
O comando EXECUTE STATEMENT ganha algumas novidades importantes na versão 2.5. Uma delas é uma das funcionalidades mais pedidas pelos usuários: ler e atualizar dados em outros bancos de dados. Através da cláusula ON EXTERNAL DATA SOURCE é possível especificar o banco de dados em que o comando executará. É permitido também que o desenvolvedor especifique o nome, senha e role do usuário para fazer a conexão, e o funcionamento da transação iniciada para rodar o comando no banco de dados externo. Com WITH COMMON TRANSACTION (opção default), a transação da conexão externa é agrupada à transação interna atual e o comando COMMIT ou ROLLBACK executado na transação atual é propagado às transações externas. Com WITH AUTONOMOUS TRANSACTION o funcionamento se torna similar as transações autônomas, sendo feito o COMMIT ou ROLLBACK logo após a execução sucedida ou não do comando externo. A listagem 18 mostra o uso deste comando para ler e gravar dados em outro banco de dados.
Listagem 18. Executando comandos em outros bancos de dados com EXECUTE STATEMENT.
SET TERM !;
EXECUTE BLOCK RETURNS (NOME VARCHAR(60)) AS
DECLARE BANCO VARCHAR(60) = 'localhost:/db/pessoas.fdb';
DECLARE USUARIO VARCHAR(10) = 'SYSDBA';
DECLARE SENHA VARCHAR(10) = 'masterke';
DECLARE NOME_INSERIR VARCHAR(20) = 'Sicrano';
BEGIN
FOR EXECUTE STATEMENT 'SELECT NOME FROM PESSOAS'
ON EXTERNAL DATA SOURCE BANCO
AS USER USUARIO PASSWORD SENHA
INTO NOME
DO
BEGIN
SUSPEND;
END
EXECUTE STATEMENT
'INSERT INTO PESSOAS (NOME) VALUES (''' || NOME_INSERIR || ''')'
ON EXTERNAL DATA SOURCE BANCO
AS USER USUARIO PASSWORD SENHA
WITH AUTONOMOUS TRANSACTION;
END!
SET TERM ;!
A listagem 18 mostra como era feita a criação de strings de SQL dinâmico, usando concatenação de strings. A criação de comandos com concatenação é insegura, pois um usuário mal-intencionado pode gerar comandos não previstos pelo desenvolvedor, causando o ataque conhecido como injeção de SQL [1]. No novo EXECUTE STATEMENT é possível passar parâmetros de duas formas diferentes. A listagem 19 mostra o uso de parâmetros anônimos, com o símbolo de interrogação.
Listagem 19. Usando EXECUTE STATEMENT com parâmetros anônimos.
SET TERM !;
EXECUTE BLOCK AS
DECLARE NOME VARCHAR(20) = 'Sicrano';
DECLARE DATA_NASCIMENTO DATE = DATE '2000-01-01';
BEGIN
EXECUTE STATEMENT
('INSERT INTO PESSOAS (NOME, DATA_NASCIMENTO) VALUES (?, ?)')
(NOME, DATA_NASCIMENTO);
END!
SET TERM ;!
Além da passagem de parâmetros anônimos, o comando EXECUTE STATEMENT agora suporta o uso de parâmetros nomeados. As vantagens dos parâmetros nomeados são que o desenvolvedor não precisa se preocupar com a ordem e também pode usar um mesmo parâmetro mais de uma vez sem a necessidade de criação de variáveis. O uso dos parâmetros dentro da string é similar a passagem de parâmetros feita com o símbolo dois-pontos em bibliotecas Delphi, como expliquei na parte referente à construção “? IS NULL”. Os valores são associados aos nomes dos parâmetros usando-se o operador “:=” (dois pontos, igual). A listagem 20 mostra o uso de parâmetros nomeados.
Listagem 20. Usando EXECUTE STATEMENT com parâmetros nomeados.
SET TERM !;
EXECUTE BLOCK AS
DECLARE NOME VARCHAR(20) = 'Sicrano';
DECLARE DATA_NASCIMENTO DATE = DATE '2000-01-01';
BEGIN
EXECUTE STATEMENT
('INSERT INTO PESSOAS (NOME, DATA_NASCIMENTO)
VALUES (:P_NOME, :P_DATA_NASCIMENTO)')
(P_DATA_NASCIMENTO := DATA_NASCIMENTO, P_NOME := NOME);
END!
SET TERM ;!
Nota
[1] Injeção de SQL é o nome de um ataque em que o usuário mal-intencionado preenche dados de um formulário de forma que um comando gerado no servidor através de concatenação de strings seja alterado. Geralmente o ataque é feito usando-se aspas simples e um restante de código que termine o comando iniciado pelo desenvolvedor, seguindo com outro comando completo e o símbolo de início de comentário (--) para invalidar o restante do comando.
Posts
