Firebird - Trabalhando com UDFs.

O que é uma UDF?

Nesta matéria abordarei um assunto muito interessante em se tratando do Banco de Dados Firebird, as denominadas UDFs, para quem não conhece UDF(User Defined Function) são funções definidas pelo usuário, uma função escrita em qualquer linguagem de programação capaz de gerar uma biblioteca compartilhada. No Windows são conhecidas pelo nome de Dynamic Link Libraries (as conhecidas DLLs).

Utilizando as UDFs

De primeiro momento estarei demonstrando como utilizar as UDFs vindas do próprio Interbase/Firebird e estarei listando as principais, consequentemente as que são mais utilizadas e em seguida estarei apresentando algumas Bibliotecas de terceiros. As UDFs são armazenadas por padrão no seguinte diretório C:\Arquivos de programas\Firebird\Firebird_x\UDF. Quando o firebird é instalado, como padrão teremos quatro arquivos, um chamado ib_udf.dll outro fbudf.dll (as UDFs) e mais dois denominados ib_udf e fbudf (onde ficam as declarações das respectivas UDFs). 

Primeiramente clique com o botão direito sobre o ib_udf e abra com o bloco de notas. Percebam que aparecerão várias declarações de UDFs que poderão ser utilizadas pelo Firebird.

Iremos utilizar a conhecida ferramenta para gerenciamento de Banco de Dados chamada Ibexpert (caso não possuam,  ela possui uma versão Freeware que poderá ser baixada através do site www.ibexpert.com). Abra o Ibexpert e registre o banco de dados que já vem com o Firebird, a partir do menu Database selecione a opção Register Database e configure o banco de dados EMPLOYEE.FDB como na figura 01.  (Por padrão esta Base de Dados é encontrada no seguinte diretório

C:\Arquivos de programas\Firebird\Firebird_x\examples\EMPLOYEE.FDB onde x é a versão do Firebird utilizado).

            

Figura 01. Registrando o Banco Employee.

Com o Ibexpert aberto clique no ícone   (New Sql Editor) para podermos declarar as nossas UDFs. Neste momento irei utilizar uma UDF explicando como deve ser declarada seguida de um exemplo.

Abra o arquivo ib_udf e copie o seguinte código:

DECLARE EXTERNAL FUNCTION abs

      DOUBLE PRECISION

      RETURNS DOUBLE PRECISION BY VALUE

      ENTRY_POINT 'IB_UDF_abs' MODULE_NAME 'ib_udf';      

Cole no ibexpert em seguida pressione F9 ou clique em Execute e por final dê um Commit Transaction. Vejam que foi adicionado ao Banco de Dados uma UDF chamada ABS (Figura 02).

Figura 02. UDF Criada no Banco Employee.

Esta UDF recebe como parâmetro um valor numérico(double precision) e irá retornar um número absoluto (positivo). Iremos simular uma situação.

Temos a seguinte tabela:

Codigo	Valor
1	-20
2	-30
3	-40

Select codigo,Abs(valor) from tabela Este select irá retornar valores positivos, ou seja, teremos como resultset os seguintes registros.

 

Codigo	Valor
1	20
2	30
3	40

Conhecendo as UDFs

A partir daqui irei apresentar algumas das principais UDFs deste arquivo ib_udf seguida de uma breve explicação sobre elas.

a s c i i _ c h a r

DECLARE EXTERNAL FUNCTION ascii_char

      INTEGER

      RETURNS CSTRING(1) FREE_IT

      ENTRY_POINT 'IB_UDF_ascii_char' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetro um valor inteiro e retorna seu valor em código Ascii.

Exemplo: select ascii_char(cust_no) from sales

a s c i i _ v a l

DECLARE EXTERNAL FUNCTION ascii_val

    CHAR(1)

    RETURNS INTEGER BY VALUE

    ENTRY_POINT 'IB_UDF_ascii_val' MODULE_NAME 'ib_udf';

Utlização: Recebe como parâmetro um valor ascii e retorna um valor inteiro correspondente.

Exemplo: select ascii_val(campo) from tabela

div

DECLARE EXTERNAL FUNCTION div

      INTEGER, INTEGER

      RETURNS DOUBLE PRECISION BY VALUE

      ENTRY_POINT 'IB_UDF_div' MODULE_NAME 'ib_udf';

Utilização: Esta UDF recebe como parâmetros dois valores inteiros e retorna a parte inteira da divisão do primeiro pelo segundo.

Exemplo: select div(cust_no,qtyordered) from sales

floor

DECLARE EXTERNAL FUNCTION floor

      DOUBLE PRECISION

      RETURNS DOUBLE PRECISION BY VALUE

      ENTRY_POINT 'IB_UDF_floor' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetro um valor do tipo double precision e retorna a parte inteira deste parâmetro

Exemplo: select floor(new_salary) from salary_history

log

DECLARE EXTERNAL FUNCTION log

      DOUBLE PRECISION, DOUBLE PRECISION

      RETURNS DOUBLE PRECISION BY VALUE

      ENTRY_POINT 'IB_UDF_log' MODULE_NAME 'ib_udf';

Utilização: Útil para operações matemáticas, esta UDF recebe como parâmetro dois valores(x e y) e tem como retorno o logaritmo base x por y

Exemplo: select log(x,y) from tabela

lower

DECLARE EXTERNAL FUNCTION lower

      CSTRING(255)

      RETURNS CSTRING(255) FREE_IT

      ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetro um campo tipo string de até 255 caracteres e como retorno um valor com caixa baixa

Exemplo: select lower(last_name) from employee

lpad

DECLARE EXTERNAL FUNCTION lpad

      CSTRING(255), INTEGER, CSTRING(1)

      RETURNS CSTRING(255) FREE_IT

      ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetros uma string de até 255 caracteres, um inteiro e um caracter.

Exemplo: select lpad(first_name,20,' ') from employee

Entendendo: No exemplo acima, insere espaços em branco à esquerda do campo fisrt_name até obter um total de 20 caracteres (contando os espaços em branco).

ltrim

DECLARE EXTERNAL FUNCTION ltrim

      CSTRING(255)

      RETURNS CSTRING(255) FREE_IT

      ENTRY_POINT 'IB_UDF_ltrim' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetro uma string de até 255 caracteres e tem como retorno esta mesma string com remoção dos espaços em branco da esquerda.

Exemplo: select ltrim(first_name) from employee

mod

DECLARE EXTERNAL FUNCTION mod

      INTEGER, INTEGER

      RETURNS DOUBLE PRECISION BY VALUE

      ENTRY_POINT 'IB_UDF_mod' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetros de entrada dois valores inteiros e como retorno o resto da divisão do primeiro parâmetro pelo segundo.

Exemplo: select mod(campo1,campo2) from tabela

substr

DECLARE EXTERNAL FUNCTION substr

      CSTRING(255), SMALLINT, SMALLINT

      RETURNS CSTRING(255) FREE_IT

      ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetro uma string de até 255 caracteres e dois valores inteiros (o primeiro indica a posição e o segundo a quantidade de caracteres). Útil para pegar pedaços de strings.

Exemplo: select substr(last_name,1,3) from employee .Percebam que este select irá trazer como resultset apenas os três primeiros caracteres do campo last_name.

strlen

DECLARE EXTERNAL FUNCTION strlen

      CSTRING(32767)

      RETURNS INTEGER BY VALUE

      ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';

Utilização: Recebe como parâmetro uma string e tem como retorno o tamnho desta string

Exemplo: select strlen(last_name) from employee

Neste momento iremos verificar algumas UDFs interessantes vindas do arquivo fbudf.dll. Faça o mesmo procedimento citado acima. Abra este arquivo e perceba a quantidade imensa de UDFs para serem utilizadas.

dow

declare external function dow

timestamp,

varchar(15) returns parameter 2

entry_point 'DOW' module_name 'fbudf';

Utilização: Recebe como parâmetro um campo tipo timestamp e retorna o dia da semana correspondente

Exemplo: select dow('05.07') from employee ou

    select dow('05.07.2007') from employee ou

    select dow('05.07.2007 10:25:32') from employee          

addDay

declare external function addDay

timestamp, int

returns timestamp

entry_point 'addDay' module_name 'fbudf';

Utilização: Recebe como parâmetro um campo tipo timestamp e um inteiro. Ela serve para adicionar dias a data informada.

Exemplo: select addday('06.07.2007', 20) from employee

Obs: Esta biblioteca de Funções possui várias UDFs relacionadas às datas e horas. Todas seguem a mesma linha de raciocínio, veja abaixo:

AddWeek – Adiciona Semanas a uma data.

AddMonth – Adiciona meses a uma data.

Addyear – Adiciona ano a uma data.

AddMillisecond – Adiciona milisegundos a uma hora.

AddSecond – Adiciona segundos a uma hora.

AddMinute – Adiciona minutos a uma hora.

AddHour – Adiciona horas a uma hora.

sright

declare external function sright

varchar(100) by descriptor, smallint,

varchar(100) by descriptor returns parameter 3

entry_point 'right' module_name 'fbudf';

 

Utilização: Recebe como parâmetro um campo do tipo varchar(100) e um inteiro. O segundo parâmetro determina quantos caracteres são mostrados da direita para a esquerda do campo informado no primeiro parâmetro.

Exemplo: select sright(first_name, 5) from employee

round

declare external function Round

int by descriptor, int by descriptor

returns parameter 2

entry_point 'fbround' module_name 'fbudf';

Utilização: Recebe como parâmetro um valor a ser arredondado. Ela serve para arredondar valores.

Exemplo: select round(25.99) from tabela

dPower

declare external function dPower

double precision by descriptor, double precision by descriptor,

double precision by descriptor

returns parameter 3

entry_point 'power' module_name 'fbudf';

Utilização: Recebe como parâmetro dois valores que podem ser do tipo double precision, sendo o primeiro o número a ser elevado pelo segundo. É uma UDF de exponenciação.

Exemplo: select dPower(2.36, 2.58) from tabela

Utilizando UDFs de terceiros

            Depois de conhecermos algumas UDFs vindas do próprio Firebird agora iremos adicionar algumas de terceiros no nosso Banco de Dados. Iremos utilizar uma chamada FreeUdfLib do seguinte autor Greg Deatz e alterada  por Cláudio Valderrama. Baixe esta biblioteca através do seguinte link http://www.ibphoenix.com/downloads/freeudflib.zip . Descompacte no diretório de sua escolha e copie os seguintes arquivos: ext_funcs e FreeUDFLib.dll para o diretório C:\Arquivos de programas\Firebird\Firebird_x\UDF.

            Abra o Arquivo ext_funcs e observem a quantidade enorme de UDFs. Encontraremos tratando de campos do tipo Blob, Data e Hora, Funções Matemáticas entre outras. Abaixo mostrarei algumas que considero mais interessantes.

f_blobsize

declare external function f_BlobSize

blob

returns integer by value

entry_point 'BlobSize' module_name 'FreeUDFLib.dll'; 

Utilização: Recebe como parâmetro um valor do tipo blob e retorna um inteiro.Como o próprio nome já diz, tem como retorno o tamanho do campo Blob.

Exemplo: select f_blobsize(job_requirement) from job

f_strblob

declare external function f_StrBlob

cstring(254),

blob

returns parameter 2

entry_point 'StrBlob' module_name 'FreeUDFLib.dll';

Utilização: Recebe como parâmetro uma string e tem como retorno um campo do tipo Blob.

Exemplo: select f_strblob(job_country) from job

f_AddMonth

declare external function f_AddMonth

date,

integer

returns

date /* free_it */

entry_point 'AddMonth' module_name 'FreeUDFLib.dll';

Utilização: Recebe como parâmetros: um campo do tipo Date e um valor inteiro. Adiciona meses à data informada.

Exemplo: select F_addmonth(’10.05.2007’,5) from tabela

Importante: A Biblioteca fbudf também contém UDFs para trabalhar com Datas, mas observem que o tipo de campo é timestamp. Abaixo verifiquem a quantidade de funções interessantes sobre datas, estarei dando uma breve explicação sobre elas.

f_year: Retorna o ano de uma data.

f_month: Retorna o mês de uma data.

f_AddYear: Adiciona anos a uma data.

f_AgeInDays: Verifica a quantidade de dias no intervalo entre duas datas.

f_CdowShort: retorna o dia da semana.

f_CmonthLong: retorna o mês da data informada.

f_dayofmonth: retorna o dia do mês da data informada.

f_lrTrim

declare external function f_lrTrim

      cstring(254)

      returns

      cstring(254) /* free_it */

      entry_point 'lrTrim' module_name 'FreeUDFLib.dll';

Utilização: Recebe e retorna como parâmetro uma string. Sua função é de remover espações em branco localizado na esquerda e na direita de uma string.

Exemplo: Select f_lrTrim(‘  The Club - O Maior clube de programadores  ‘) from tabela

f_propercase

declare external function f_ProperCase

      cstring(254)

returns

      cstring(254) /* free_it */

      entry_point 'ProperCase' module_name 'FreeUDFLib.dll';

Utilização: Recebe e retorna como parâmetro uma string. O retorno desta UDF será a primeira letra em maiúscula e o resto em minúscula.

Exemplo: select f_propercase('thiago') from tabela

f_ibpassword

declare external function f_IBPassword

cstring(32)

returns

cstring(32) /* free_it */

entry_point 'IBPassword' module_name 'FreeUDFLib.dll';

Utilização: Recebe como parâmetro uma string e retorna esta string criptografada.

Exemplo: Select f_IBPassword(job_country) from job

            Além desta biblioteca de Funções existem outras (também ditas open-source) que poderão ser úteis para os senhores. Eu particularmente recomendo uma chamada tbudf (Tecnobyte UDF para Firebird) do autor Daniel Pereira Guimarães que poderá ser baixada no seguinte link http://www.tecnobyte.com.br/tbudf2_2.zip. Esta biblioteca além de ser tão boa quanto a FreeUDFLib possui uma ótima documentação em português. Para utilizá-la siga os mesmos passos ditos anteriormente.

Criando uma UDF

Quando iremos criar uma UDF não podemos esquecer uma regra básica, é recomendável ser uma função simples e rápida. Significa que uma função que manipula strings, realiza cálculos matemáticos, manipula datas como nos exemplos citados acima são ótimas candidatas a virarem uma UDF.

A ferramenta para criarmos nossa UDF será o delphi 7. Abra o Delphi, vá em File/New/Other. Na aba New escolha DLL Wizard, veja figura 03.

 Figura 03. Criando uma UDF.

Logo em seguida clique em ok. Aparecerá os seguintes códigos.

library Project1;

{ Important note about DLL memory management: ShareMem must be the

  first unit in your library's USES clause AND your project's (select

  Project-View Source) USES clause if your DLL exports any procedures or

  functions that pass strings as parameters or function results. This

  applies to all strings passed to and from your DLL--even those that

  are nested in records and classes. ShareMem is the interface unit to

  the BORLNDMM.DLL shared memory manager, which must be deployed along

  with your DLL. To avoid using BORLNDMM.DLL, pass string information

  using PChar or ShortString parameters. }

uses

  SysUtils,

  Classes;

{$R *.res}

begin

end.

            Logo após Library iremos colocar o nome da Biblioteca. Defina como quiser, neste caso específico iremos definir como THECLUB. Em uses iremos utilizar apenas a unit chamada SysUtils e por final remova a cláusula {$R *.res} que não iremos utilizar.Veja abaixo como está ficando nosso código:

library THECLUB;

uses SysUtils;

begin

end.

Iremos construir uma UDF simples, receberá uma string como parâmetro e retornará esta mesma string com espaço em branco entre seus caracateres. Veja abaixo nossa função:

     Function ESPACO(var1:pchar): pchar; cdecl; export;

var i:integer;

    var2, var3: string;

begin

 for i:=1 to Length(var1) do

  begin

   var2 := copy(var1,i,1) + ' ';

   var3 := var3 + var2;

  end;

 result := pchar(var3);

end;

Fica uma dúvida, porque utilizar pchar ao invés de string? Para quem não sabe pchar é um ponteiro para caracter. Ponteiro é uma variável que contém um endereço de memória. Este endereço é a posição de outra variável ou de um determinado dado na memória. Tratando-se de UDFs (criação de DLLs propriamente ditas) não seria recomendável a utilização de strings como variáveis (seja de retorno ou de entrada) e sim do tipo pchar ou shortstring.

Outra dúvida, o que significa cdecl e export?

Cdecl: Significa o tipo de chamada, determina a ordem na qual os parâmetros são passados para a rotina. Ele passa os parâmetros da direita para a esquerda. Na forma cdecl o chamador é responsável por remover da pilha os parâmetros de retorno das funções.

Export: indica que a função poderá ser chamada por outros aplicativos.(No nosso caso o Firebird)

Mas não iremos nos aprofundar muito neste assunto, para quem se interessar veja nossa matéria chamada Trabalhando com Procedures e Funções encontrada no seguinte link http://www.theclub.com.br/revista/trab1203.aspx do mês de Dezembro de 2003.

 

Veja abaixo como ficou a UDF criada pelo Delphi.

library THECLUB;

uses SysUtils;

Function ESPACO(var1:pchar): pchar; cdecl; export;

var i:integer;

    var2, var3: string;

begin

 for i:=1 to Length(var1) do

  begin

   var2 := copy(var1,i,1) + ' ';

   var3 := var3 + var2;

  end;

 result := pchar(var3);

end;

  exports

  ESPACO;

begin

end.

Após construirmos nossa UDF iremos salvá-la em File/Save All em seguida compilá-la. Fiquem atentos, não podemos executar uma DLL pelo Delphi, através do RUN do Menu Run, pois não se trata de uma aplicação. Clique em Project/ Compile THECLUB.

Percebam que ao compilar nosso projeto será criado um arquivo chamado THECLUB.dll, ou seja, estamos terminando o processo de criação de nossa UDF, basta apenas declará-la no nosso Banco de Dados.

Copie o arquivo THECLUB.dll para a seguinte diretório C:\Arquivos de programas\Firebird\Firebird_x\UDF (que é o diretório onde ficam armazenadas as UDFs do Firebird e as de terceiros). Em seguida abra o Ibexpert para declararmos nossa UDF. Observe abaixo como fica:

DECLARE EXTERNAL FUNCTION UDF_ESPACO

CSTRING(32)

RETURNS CSTRING(32)

ENTRY_POINT 'ESPACO' MODULE_NAME 'TESTE.dll'

DECLARE EXTERNAL FUNCTION UDF_ESPACO: coloque o nome da UDF.

ENTRY_POINT 'ESPACO' MODULE_NAME 'TESTE.dll': ESPACO seria o nome da UDF criada no Delphi e TESTE.dll o nome da biblioteca.

Clique em Execute e logo após Commit Transaction, veja na figura 04 a nossa UDF criada com sucesso no banco de dados.

Figura 04. UDF criada no Banco de Dados.

Utilizando nossa UDF chamada udf_espaco:

select udf_espaco(last_name) as LAST_NAME from employee

Veja o resultado(Figura 05)

Figura 05. Resultado de nossa instrução SQL.

  

Conclusão

            Vimos neste artigo como se trabalhar com UDFs, desde sua definição até a criação de uma pelo Delphi. Espero que tenha ajudado e gostado da matéria. Um forte abraço e até o mês que vem.