![Fechar [x]](http://dw1.s81c.com/i/c.gif){kind=small}
A validação e qualidade dos dados mantidos por uma aplicação podem ser tratadas diretamente na camada do banco de dados, e este artigo irá, através de exemplos práticos, demonstrar essa funcionalidade.
Uma das características que o DB2 9.7 LUW oferece é a criação de UDFs (User Defined Functions) em diversas linguagens, entre elas Java. Este artigo irá mostrar como criar funções para validação de dados utilizando UDFs Java e o pacote java.util.regex, e posteriormente, demonstrar como criar triggers para disparar as UDFs criadas validando se os dados informados são válidos e podem ser inseridos/alterados na tabela destino. O conjunto UDF/regex/Trigger permite uma alta flexibilidade na contribuição para uma manutenção da qualidade dos dados mantidos na base.
No DB2 9.7 LUW, UDF pode ser descrita como uma função externa do DBMS que pode ser escrita em uma linguagem totalmente diferente da linguagem padrão do mesmo para realizar atividades que o banco de dados não está apto a fazer, seja por performance ou até mesmo por arquitetura. Alguns exemplos de UDFs podem estar associados a utilização de uma linguagem orientada a objetos, tal como Java ou C++, para tratar funcionalidades que requerem essa característica.
O que são Regular Expressions (regex)?
Regular Expressions são uma forma eficiente e flexível de busca e seleção de características de uma String utilizando operadores e literais comuns. Utilizando patterns (padrões) quase que como uma linguagem de programação, possibilita a localização de uma simples cadeia de caracteres como "vamos pular" até sequências matemáticas complexas como "qualquer número seguido de sequência de 4 caracteres entre "a e Z" seguido de um caracter literal ":" ou a remoção de comentários de programas C (// ou /* */) antes de serem compilados e disponibilizados em produção.
O exemplo abaixo utiliza um pattern ([0-4]) para validar apenas números entre 0 e 4. Observe as classes Pattern e Matcher sendo instanciadas e o método matcher sendo invocado para validar se o parâmetro de entrada varchar é realmente um número de 0 a 4.
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class validarZeroAQuatro
{
public static int validarZeroAQuatro (String varchar)
{
Pattern p = Pattern.compile("[0-4]");
Matcher m = p.matcher(varchar);
if (m.matches())
{
return 1;
}
return 0;
}
}
|
Alguns patterns básicos de regex:
| Operador | Definição |
| . (ponto) | Corresponde a um caracter alfanumérico |
| ^ | Corresponde a negação do operador seguinte |
| F | Caracter "F" (maiúsculo) |
| k | Caracter "k" (minúsculo) |
| \d | Numéricos de 0 a 9 |
| \D | Caracter não numérico |
| \w | Caracter alfanumérico |
| [4-9] | Caracter numérico de 4 a 9 (4, 5, 6, 7, 8 e 9) |
| [B-D] | Intervalo válido de "B" a "D" ("B", "C" e "D") |
| [^B-D] | Intervalo inválido de "B" a "D" ("B", "C" e "D") |
| \d{n,m} | Mínimo "n" e no máximo "m" caracteres numéricos |
| \\ | Escape de barra invertida (requer para compilar UDFs Java para DB2) |
| P|F | Caracter "P" ou "F" |
| PF | Caracter "P" seguido imediamente do caracter "F" |
Verificar se o JDK está instalado e configurado no DB2:
db2 get dbm config
Figura 1
Veja uma versão ampliada da Figura 1
Teste da primeira UDF:
* Utilize seu editor favorito e salve como "minhaPrimeiraUDF.java":
public class minhaPrimeiraUDF
{
public static String minhaPrimeiraUDF (String t) throws SQLException
{
return "Ola "+t;
}
}
|
* Compile o programa Java:
javac minhaPrimeiraUDF.java
Copie o arquivo gerado minhaPrimeiraUDF.class para o diretório 'x:\DB2InstallPath\functions', onde "x:\DB2InstallPath" é o diretório de instalação do DB2.
Para associar o arquivo java compilado a uma função externa do DB2, devemos criar uma FUNCTION de linguagem Java (language java) que direcione para o arquivo minhaPrimeiraUDF.class utilizando o método minhaPrimeiraUDF(String sText).
Sintaxe básica do Create Function:
>>-CREATE--+------------+--FUNCTION--function-name-------------->
'-OR REPLACE-'
>--(--+-------------------------------+--)--●------------------->
| .-,-------------------------. |
| V | |
'---| parameter-declaration |-+-'
>--RETURNS--+-| data-type2 |--+------------+----------------------------+-->
| '-AS LOCATOR-' |
'-| data-type3 |--CAST FROM--| data-type4 |--+------------+-'
'-AS LOCATOR-'
>--| option-list |---------------------------------------------><
parameter-declaration
|--+-----------------------------+------------------------------>
| .-IN------. |
'-+---------+--parameter-name-'
| (1) |
+-OUT-----+
'-INOUT---'
>--| data-type1 |--+--------------------+--+------------+-------|
'-| default-clause |-' '-AS LOCATOR-'
|--●--LANGUAGE--+-C----+------●--+-------------------------+---->
+-JAVA-+ '-SPECIFIC--specific-name-'
+-CLR--+
'-OLE--'
>--●--EXTERNAL--+----------------------+--●--------------------->
'-NAME--+-'string'---+-'
'-identifier-'
>--PARAMETER STYLE--+-DB2GENERAL-+--●--------------------------->
+-JAVA-------+
'-SQL--------'
.-NOT DETERMINISTIC-.
>--+------------------------------+--●--+-------------------+--->
'-PARAMETER CCSID--+-ASCII---+-' '-DETERMINISTIC-----'
'-UNICODE-'
.-FENCED------------------------.
>--●--+-------------------------------+--●---------------------->
+-FENCED--●--+-THREADSAFE-----+-+
| '-NOT THREADSAFE-' |
| .-THREADSAFE-. |
'-NOT FENCED--●--+------------+-'
|
| Atributo | Definição |
| Language | Linguagem externa utilizada pela UDF |
| External Name | Nome do arquivo e função que será utilizada pela UDF |
| Parameter Style | Indica qual a forma de passagem do parâmetro será utilizada pela UDF |
| Fenced | Se o DB2 irá executar a UDF em modo protegido ou não |
Utilizando seu editor favorito, salve o código abaixo como "minhaPrimeiraUDF.sql":
drop function minhaPrimeiraUDF (VARCHAR(255)); CREATE or replace FUNCTION minhaPrimeiraUDF (VARCHAR(255)) RETURNS varchar(255) FENCED EXTERNAL NAME 'minhaPrimeiraUDF!minhaPrimeiraUDF' NOT VARIANT NO SQL PARAMETER STYLE java LANGUAGE java NO EXTERNAL ACTION; |
Execute o comando abaixo utilizando o parâmetro -tvf para executar o script SQL salvo anteriormente:
db2 -tvf minhaPrimeiraUDF.sql
Para verificar se a UDF foi compilada e corretamente criada no DB2, execute a seguinte linha de comando:
db2 "select minhaPrimeiraUdf('Ricardo Boaretto') from sysibm.sysdummy1"
O resultado deve ser algo parecido como:
Figura 2
Veja uma versão ampliada da Figura 2
Primeira UDF com Regular Expression
Primeiramente, crie a seguinte tabela:
CREATE TABLE CADASTRO( NOME CHAR(50), IP CHAR(15), EMAIL CHAR(50), CEP CHAR(9), IDADE INTEGER) ; |
Popule a tabela cadastro com os seguintes valores:
INSERT INTO CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES
('BIANCA 26', '192.168.0.26', 'BIANCA@26.COM.BR', '04433-000', 26);
INSERT INTO CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES
('RICARDO 25', '192.168.0.25', 'RICARDO@25.COM.BR', '04453-000', 25);
INSERT INTO CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES
('JOAO 24', '192.168.0.24', 'JOAO@24.COM.BR', '04463-000', 24);
INSERT INTO CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES
('JOSE 23', '192.168.0.23', 'JOSE@23.COM.BR', '04473-000', 23);
|
Agora, o objetivo é usarmos o esqueleto de nossa primeira udf e incrementar as funcionalidades usando o package java.util.regex, responsável pelos objetos Pattern e Matcher que serão instanciados e parametrizados de acordo com o pattern que queremos validar. Nesse primeiro exemplo iremos instanciar a classe Pattern com um pattern de regex que verifica se um endereço IP é válido.
Utilize seu editor favorito e salve o arquivo como isValidIp.java:
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class isValidIp
{
public static int isValidIp (String varchar)
{
Pattern p = Pattern.compile("\\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)
\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)
\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)
\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\b");
Matcher m = p.matcher(varchar);
if (m.matches())
{
return 1;
}
return 0;
}
}
|
O pattern "\\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\b" valida se a String de entrada possui 4 sequências separadas por ponto com no máximo 3 dígitos e no mínimo 1 dígito, além de verificar se o intervalo de cada sequência está entre 0 e 255.
Compile o programa Java:
javac isValidIp.java
Copie o arquivo gerado isValidIp.class para o diretório 'x:\DB2InstallPath\functions'
Utilize seu editor favorito e salve como "isValidIp.sql":
CREATE or replace FUNCTION isValidIp (VARCHAR(255)) RETURNS int FENCED EXTERNAL NAME 'isValidIp!isValidIp' NOT VARIANT NO SQL PARAMETER STYLE java LANGUAGE java NO EXTERNAL ACTION; |
Execute:
db2 -tvf "isValidIp.sql"
Para verificar se a UDF para validação de IPs foi compilada corretamente e foi corretamente criada no DB2, execute a seguinte linha de comando:
db2 "select ip, isValidIp(TRIM(IP)) as isValidIp from CADASTRO"
O resultado deve ser algo parecido como:
Figuera 3
Veja uma versão ampliada da Figura 3
Pode-se observar que 2 endereços IPs não são válidos, ou por não terem os 4 conjuntos de 3 dígitos ou por possuir algum caracter inválido em algum dos conjuntos.
O segundo exemplo de UDF com regular expression será criado para verificar se um endereço email é válido ou não de acordo com nossa tabela de Cadastro.
Utilize seu editor favorito e salve o arquivo como isValidEmail.java:
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class isValidEmail
{
public static int isValidEmail (String varchar) throws SQLException
{
Pattern p = Pattern.compile("\\b[A-Z0-9._%+-]+@(?:[A-Z0-9-]+\\.)+[A-Z]{2,4}\\b");
Matcher m = p.matcher(varchar);
if (m.matches())
{
return 1;
}
return 0;
}
}
|
O pattern "\\b[A-Z0-9._%+-]+@(?:[A-Z0-9-]+\\.)+[A-Z]{2,4}\\b" valida se a String de entrada possui as características de um email válido, contendo o literal '@' e a definição de um domínio.
* Compile o programa Java:
javac isValidEmail.java
Copie o arquivo gerado isValidEmail.class para o diretório 'x:\DB2InstallPath\functions'
Utilize seu editor favorito e salve como "isValidEmail.sql":
drop function isValidEmail (VARCHAR(255)); CREATE or replace FUNCTION isValidEmail (VARCHAR(255)) RETURNS int FENCED EXTERNAL NAME 'isValidEmail!isValidEmail' NOT VARIANT NO SQL PARAMETER STYLE java LANGUAGE java NO EXTERNAL ACTION; |
Execute:
db2 -tvf "isValidEmail.sql"
Para verificar se a UDF para validação de e-mails foi compilada corretamente e foi corretamente criada no DB2, execute a seguinte linha de comando:
db2 "select email, isValidEmail(TRIM(email)) as isValidemail from CADASTRO"
O resultado deve ser algo parecido como:
Figura 4
Veja uma versão ampliada da Figura 4
Pode-se observar que 2 endereços de email não são válidos, ou por possuir um domínio inválido ("BRXXX") ou por não ter o literal "@".
O terceiro exemplo de UDF com regular expression será criado para verificar se o CEP cadastrado em nossa na tabela de CADASTRO é válido ou não.
Utilize seu editor favorito e salve o arquivo como isValidCEP.java:
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class isValidCEP
{
public static int isValidCEP (String varchar) throws SQLException
{
Pattern p = Pattern.compile("\\b\\d{5}\\-\\d{3}\\b");
Matcher m = p.matcher(varchar);
if (m.matches())
{
return 1;
}
return 0;
}
}
|
O pattern "\\b\\d{5}\\-\\d{3}\\b" valida se a String de entrada possui as características de um CEP válido, contendo 5 dígitos, o caracter literal "-" e por último, 3 dígitos.
* Compile o programa Java:
javac isValidCEP.java
Copie o arquivo gerado isValidCEP.class para o diretório 'x:\DB2InstallPath\functions'
Utilize seu editor favorito e salve como "isValidCEP.sql":
drop function isValidCEP (VARCHAR(255)); CREATE or replace FUNCTION isValidCEP (VARCHAR(255)) RETURNS int FENCED EXTERNAL NAME 'isValidCEP!isValidCEP' NOT VARIANT NO SQL PARAMETER STYLE java LANGUAGE java NO EXTERNAL ACTION; |
Execute:
db2 -tvf "isValidCEP.sql"
Para verificar se a UDF para validação de CEPs foi compilada corretamente e criada no DB2, execute a seguinte linha de comando:
db2 "select cep, isValidCep(TRIM(cep)) as isValidCep from CADASTRO"
O resultado deve ser algo parecido como:
Figura 5
Veja uma versão ampliada da Figura 5
Apenas um dos CEPs cadastrados é válido, os restantes não atenderam a regra de possuir o caracter literal "-", ou não possuir 5 dígitos antes do caracter "-" ou por não ter 3 dígitos após o caracter "-".
Para finalizar nossa introdução a validação de campos utilizando UDFs, iremos criar duas triggers que serão disparadas assim a tabela CADASTRO sofrer uma operação de INSERT ou de UPDATE validando se o CEP informado é válido ou não, permitindo ou não a inserção/update do registro. Um SQLSTATE com a mensagem de "CEP inválido" será retornado para a aplicação que tentar alterar um registro utilizando um CEP fora dos padrões descritos na function "isValidCEP".
Utilize seu editor favorito e salve o arquivo como "createTriggerUpdateCadastro.sql":
CREATE OR REPLACE TRIGGER VALIDARCEPUPDATE
BEFORE UPDATE OF CEP ON CADASTRO
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (ISVALIDCEP(TRIM(NEW.CEP))=0)
SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT='CEP INVALIDO'
|
A trigger irá validar, para cada linha a ser alterada (FOR EACH ROW), se o CEP informado é válido, no caso de um valor inválido, um SQLSTATE 75001 será retornado ao usuário.
Execute:
db2 -tvf "createTriggerUpdateCadastro.sql"
Para verificarmos se nossa trigger está funcionando corretamente, execute os seguintes comandos:
DB2 "UPDATE CADASTRO SET CEP = '04444-040' WHERE IDADE = 25" DB2 "UPDATE CADASTRO SET CEP = '04444 00' WHERE IDADE = 25" DB2 "UPDATE CADASTRO SET CEP = '04444-00' WHERE IDADE = 25" |
O resultado deve ser algo como na imagem abaixo:
Figura 6
Veja uma versão ampliada da Figura 6
Apenas o primeiro update foi efetuado com sucesso, observe que os CEPs informados nos dois últimos updates são inválidos, e a trigger VALIDARCEP não permitiu a atualização do registro pois a UDF "isValidCep" criada anteriormente invalida o CEP informado.
Utilize seu editor favorito e salve como "createTriggerInsertCadastro.sql":
CREATE TRIGGER VALIDARCEPINSERT
before INSERT ON CADASTRO
REFERENCING NEW AS NEWX
FOR EACH ROW
WHEN (ISVALIDCEP(TRIM(NEWX.CEP))=0)
SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT='CEP INVALIDO'
|
Execute:
db2 -tvf "createTriggerInsertCadastro.sql"
Para verificarmos se nossa trigger está funcionando corretamente, execute os seguintes comandos:
DB2 "INSERT INTO OPTIM.CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES( 'JERIVALDO 64', '192.168.1.x', 'JERIVALDO@64.COM.BR', '03363-008', 24)" DB2 "INSERT INTO OPTIM.CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES( 'CLEONICE 34', '192.168.4.x', 'CLEONICE@34.COM.BR', '0263-008', 24)" DB2 "INSERT INTO OPTIM.CADASTRO (NOME, IP, EMAIL, CEP, IDADE) VALUES( 'NEUSA 44', '192.168.5.x', 'NEUSA@44.COM.BR', '01163-08', 24)" |
O resultado deve ser parecido com o da imagem abaixo:
Figura 7
Veja uma versão ampliada da Figura 7
Apenas o primeiro registro foi inserido na tabela CADASTRO, os dois últimos registros possuem CEPs inválidos.
A facilidade de construção e utilização de UDFs no DB2 9.7 LUW facilita a criação de rotinas para tratamento de dados para atender a qualquer requisito de negócio da organização, facilitando a manutenção da qualidade dos dados que são mantidos na base de dados. O uso de triggers em conjunto com UDFs/regex tornam o DB2 9.7 LUW altamente flexível para atender a qualquer requisito de negócio.
Fonte:
Mastering Regular Expressions [Paperback] Jeffrey E.F. Friedl http://www.amazon.com/Mastering-Regular-Expressions-Jeffrey-Friedl/dp/0596528124/ref=sr_1_1?ie=UTF8&qid=1310578684&sr=8-1
DB2 9 Fundamentals Certification Study Guide [Paperback]
Roger E. Sanders
http://www.amazon.com/DB2-Fundamentals-Certification-Study-Guide/dp/1583470727/ref=sr_1_8?ie=UTF8&qid=1310578733&sr=8-8
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
Ricardo Boaretto é especialista de vendas técnicas de Information Management, com foco em DB2 distribuído para plataformas Linux, Unix e Windows.
Conheça a IBM da sua cidade
Tags
Use a barra de rolagem para ver mais ou menos tags.
Tags populares mostra as principais tags para esta zona de conteúdo em particular (por exemplo, Java technology, Linux, WebSphere).
Minhas tags mostra suas tags para esta zona de conteúdo em particular (por exemplo, Java technology, Linux, WebSphere).
Tags de artigo populares |
Minhas tags de artigoIr para lista de tags
Tags de artigo populares |
Minhas tags de artigo