Disparando uma "Stored Procedure" no horário marcado no Firebird/Interbase

Neste artigo vou abordar uma questão muito interessante e muito útil aos desenvolvedores que utilizam o banco de dados Firebird/Interbase, trata-se do agendamento de tarefas rotineiras a serem disparadas por Stored Procedures (vamos chama-las de SP"s) num horario marcado tanto no Windows como no Linux.

Aqueles que já utilizaram (ou utilizam) algum outro banco de dados como o Oracle, Sybase ou MSSQL já estão acostumados a utilizar ferramentas que acompanham esses produtos para realizar tarefas de sistema ou tarefas de rotina num horario previamente calculado. No Firebird/Interbase, no entanto não acompanha nenhum programa com essa finalidade especifica, claro que se voce for procurar na internet voce encontrará programas para essa finalidade.

O problema em especializar-se em utilizar tais ferramentas na internet é que cada uma possui sua própria interface, o que voce for aprender a fazer utilizando um determinado programa terá de ser "esquecido" caso venha a utilizar outra ferramenta de programação para a mesma tarefa ou para ser usado com outro programa. Um outro problema é que a maioria dos servidores já possuem seu próprio agendador de tarefas, então porque não utiliza-los ? Usando outro programa voce apenas estará dificultando as coisas, se já existe um padrão para agendar tarefas em seu sistema operacional então por que ignora-lo ?

Talvez voce diga : "Ok, Gladiston, eu já conheço muito bem o crontab e o agendador de tarefas do Windows, mas com eles eu apenas conseguiria agendar execuções de programas (backup,limpeza,etc...), como eu faria para eu executar SP"s com eles ? Sendo que SP"s não são executáveis e encontram-se dentro do banco de dados ?"

Se voce tiver a mesma pergunta acima, então este artigo é para voce.

A escolha do programa para executar a stored procedure

Uma dica ao usar agendadores é trabalhar com a linguagem script padrão do sistema, ao invés de tentar criar apenas executáveis para tais tarefas.

No Windows a linguagem script mais conhecida (a única por padrão) é chamada de cmd, conhecido tambem como "arquivos batch" cuja extensões são conhecidas como : .bat ou .cmd. No linux existe uma grande lista de linguagens scripts : Python, Perl, Bash, .... em nosso exemplo, iremos criar scripts no Linux no formato Bash que é o mais popular. A grande vantagem de utilizar scripts é que para cria-los voce precisa apenas dum editor de textos simples, nada de compiladores ou engenhosos mecanimos RAD de criação de programas e também quando há necessidade de uma mudança rápida, voce simplesmente edita o arquivo em questão e faz as mudanças sugeridas, nada de compiladores, depuradores, etc....

Ás vezes eu fico me perguntando porque alguns criam aplicativos executáveis para tarefas simples que podem ser resolvidas com um simples script ?

Certa vez, eu ajudei um programador numa lista que já estava por dias criando um programa para efetuar o backup da base de dados, o programa ficaria no system-tray do windows e na hora marcada por ele o sistema automaticamente faria o backup, ele queria saber porque algumas vezes o programa era não era chamado no horario marcado, bem...eu não ajudei-o a consertar o bug em sua aplicação mas chamei a atenção dele que poderia ter criado um arquivo batch com um simples :

copy e agenda-lo por meio do Agendador de Tarefas do próprio Windows, a pessoa ficou chocada com tamanha a simplicidade com que eu resolvi o problema dele. Parece incrivel, mas programadores Windows parecem estar resolvidos a resolver problemas sempre pela pior maneira possivel. Bom, agora chega de bla,bla,bla e vamos para o próximo passo.

Conheça o Interactive SQL (ou Interactive Query)

Para executar uma SP vamos precisar duma ferramenta que permita a execução de scripts SQL. Talvez voce conheça o IBConsole ou IBExpert, no entanto, estas são ferramentas visuais interativas, isto é, precisam da presença dum analista ou programador para dizer-lhes o que devem fazer, às vezes permitem a autoexecução de scripts, no entanto por consumirem muita memoria e serem muito carregados com DLLs existe a possibilidade de falharem ou causar GPF"s.

Para solucionar este problemas, temos de lembrar de usar uma ferramenta chamada dfe Interactive SQL, também conhecido mundialmente como : isql (aposto que voce não sabia quem era) , uma ferramenta homologada pela equipe de desenvolvimento do Firebird/Interbase que permite que se execute scripts sql diretamente no prompt do sistema operacional que possui vantagens : simples, não consome muita memória/recursos e muito, mas muito flexivel. Tem uma única desvantagem : não possui uma GUI (Graphic User Interface) com ícones, grades, listbox, etc... com o qual os programadores visuais já estão acustumados, alias mesmo a TUI (Text User Interface) possui limitações na exibição de longos textos. Mas a intenção do isql não é ser uma ferramenta de programação avançada, é facilitar a execução de serviços de administração da base de dados. Essa ferramenta é tão importante que mesmo outros RDBMS como Oracle, Sybase e MSSQL possuem também uma ferramenta Interactive SQL para uso em prompt, até mesmo com o mesmo nome isql.

O Interactive SQL encontra-se no diretorio interbase\bin\isql.exe, e possui os seguintes argumentos :

Listagem 1: Argumentos do Interactive SQL

isql [<database>] [-e] [-t <terminator>] [-i <inputfile>] [-o <outputfile>]
[-x|-a] [-d <target db>] [-u <user>] [-p <password>]
[-page <pagelength>] [-n] [-m] [-q] [-s <sql_dialect>]
[-r <rolename>] [-c <num cache buffers>] [-z] -nowarnings -noautocommit

A parte que mais nos interessa é a opção : -i que serve para carregar um arquivo de entrada com os comandos sql e a opção -o que permite especificar o arquivo de saída para onde os resultados do que for executado irão ficar. Claro que sem as opções -u para especificar o nome do usuário, -p para especificar a senha, nunca script nenhum funcionaria.

Bom agora que voce já conhece o isql, vamos criar o script que executará uma SP no horario marcado...

Um script .cmd/.bat para Windows com hora marcada

O Windows NT/2000/XP reconhece a extensão .cmd como arquivos script que serão submetidos ao interpretador de comandos do Windows. Porém, para Windows 95/98/ME a extensão .cmd não é válida e deverá ser substituída por .bat (Batch Files), também conhecidos como arquivos de lote. Preferencialmente eu utilizaria a extensão .cmd, no entanto, para este artigo vou utilizar o padrão .bat por ele ser compátivel e compreensivel a todas as versões de Windows.

Vamos criar o arquivo rodar_sp.bat com o seguinte conteúdo :

Listagem 2: Conteúdo do arquivo rodar_sp.bat

@echo off
rem ============================================
rem Variaveis representando o nome de arquivos para o arquivo 
rem SQL (entrada) e para o arquivo de LOG (saida)
rem ============================================
set arq_log="c:\meulog.log"
set arq_sql="c:\isql.sql"
rem ============================================
rem caso o arquivo de exista, então será eliminado
rem ============================================
if exist %arq_log goto apagalog
:inicio
rem ============================================
rem Estou criando um arquivo SQL com as sequencia de comandos 
rem que serão executados no servidor Firebird/Interbase
rem ============================================
echo "/* Estou criando um arquivo SQL vazio */" >%arq_sql
echo set names iso8859_1;>%arq_sql
echo set sql dialect 3;">%arq_sql
echo commit;>>>%arq_sql
echo set transaction;>>>%arq_sql
echo execute procedure SP_VERSION; >>>%arq_sql
echo commit; >>>%arq_sql
echo quit; >>>%arq_sql
rem ============================================
rem Agora vou executar o "isql" e fazer com que execute o SQL
rem ============================================
echo Executando stored procedure...
"c:\arquivos de programas\firebird\bin\isql.exe" localhost:c:\caminho\para\os\dados.fdb -u SYSDBA -p masterkey -page 4096 -i %arq_sql -o %arq_log
rem ============================================
rem Se ocorreu tudo OK, então apaga-se o arquivo de log e finaliza
rem ============================================
if errorlevel 1 goto fim
if errorlevel 0 goto apagalog
:apagalog
del %arq_log
:fim
del %arq_sql
echo Fim

Script pronto, salve-o em algumas pasta importante do seu servidor.

Os comandos SQL executarão uma SP chamada SP_VERSION que criei para teste, os resultados da execução de SP_VERSION serão gravados no arquivo de LOG. Mas atente-se que no meu caso, no final da execução da procedure eu elimino o arquivo LOG caso não haja nenhuma mensagem de erro. Voce poderá modificar isso, caso precise sempre guardar o arquivo de log.

Para agendar a execução desse script, use o próprio Agendador de Tarefas do Windows. Eu creio que o uso desse agendador não seja segredo para ninguém.

Um script bash para Linux/Unix like com hora marcada

Todas as variações de Unix (Linux, *BSD, MacOSX,...) contam com os scripts bash, esses scripts são muito poderosos para executar praticamente o que quizer em seu sistema. Escrever scripts escritos em bash são centena de vezes melhor que o interpretador de linha de comandos do Windows NT/2000/XP e em muitos casos resolve problemas que somente seriam resolvidos criando um aplicativo comum em C, C++, Pascal,...

Todas as variações de Unix (Linux, *BSD, MacOSX,...) também contam com crontab

Vamos criar o arquivo rodar_sp.sh com o seguinte conteúdo :

Listagem 3: Conteúdo do arquivo rodar_sp.sh

#/bin/sh
#################################################
# Variaveis representando o nome de arquivo para :
# SQL (Entrada) e o arquivo de Log (saída)
#################################################
clear
data_ini=`date +%d-%m-%Y`
exec_title="sp-execute-$data_ini"
arq_log="/var/log/backup/$exec_title.log"
arq_sql="/tmp/$exec_title.sql"
#################################################
# caso o arquivo de log já exista, então será eliminado
#################################################
if [ -f $arq_log ] ; then
rm -f $arq_log;
fi;
#################################################
# Estou criando um arquivo SQL com as sequencia de comandos 
# que serão executados no servidor Firebird/Interbase
#################################################
echo "/* Estou criando um arquivo SQL vazio */" >$arq_sql
echo set names iso8859_1;>$arq_sql
echo set sql dialect 3;">$arq_sql
echo commit;>>>$arq_sql
echo set transaction;>>>$arq_sql
echo execute procedure SP_VERSION; >>>$arq_sql
echo commit; >>>$arq_sql
echo quit; >>>$arq_sql
#################################################
# Agora vou executar o "isql" e fazer com que execute o SQL
# Atencao que o path onde o Firebird/Interbase esta 
# instalado podera mudar dependendo de sua instalação
#################################################
echo Executando stored procedure...
/usr/interbase/bin/isql localhost:c:\caminho\para\os\dados.fdb -u SYSDBA -p masterkey -page 4096 -i $arq_sql -o $arq_log
#################################################
# Se quiser acrescentar novas linhas, acrescente abaixo
#################################################

#################################################
# No final apaga-se o arquivo SQL temporario que foi gerado
#################################################
rm -f $arq_sql

Salve o script com o seu editor preferido (eu uso o vi porque gosto de sofrer), e transforme-o em executavel com permissão apenas para o root, pois não é legal ver usuário bisbilhotando nosso script ou até mesmo executa-lo, os comandos seriam os seguintes :

Listagem 4: Comandos do script com permissão apenas para o root

chmod +x rodar_sp.sh (transformando-o em executável)
chomod 700 rodar_sp.sh (somente o root poderá ver e alterar esse script)

Exatamente igual a versão para o Windows só que em bash script, os comandos SQL executarão uma SP chamada SP_VERSION e os resultados da execução de SP_VERSION serão gravados no arquivo de LOG. Eu dei uma incrementada no nome do arquivo de log que agora será gravado como "sp-execute-.log" e ao contrario do script cmd para Windows não será excluído.

Se voce quiser incrementar ainda mais este script acrescente as seguintes linhas na parte indicada do script :

Listagem 5: Incrementando o script

#################################################
# Envia e-mail notificando #
# Atenção que ele utiliza o zip para compactar o arquivo de #
# log antes de enviar a notificacao por e-mail #
#################################################
echo "From: root" >/tmp/message
echo "To: webmaster@dominio.com.br">>/tmp/message
echo "CC: gladisto@bol.com.br">>/tmp/message
echo "Subject: [sp-programada] $backup_title">>/tmp/message
echo " ">>/tmp/message
echo "Segue em anexo o log do backup" >>/tmp/message
echo "=> $backup_title" >>/tmp/message
echo "que foi iniciado as $data_ini" >>/tmp/message
echo "e terminou (incluindo verificacao) as $data_fim" >>/tmp/message
zip /tmp/log.zip $file_log
uuencode /tmp/log.zip tape_list.zip >>/tmp/message
exec cat /tmp/message<<EOF| /usr/lib/sendmail -i -t -B8BITMIME
sleep 5
rm -f /tmp/log.zip
rm -f /tmp/message

Essa é parte importante do bash script nos sistemas unix, é a flexibilidade e o poder da linguagem.

Acrescentando apenas mais algumas linhas, e nosso script inclusive notificará-o por e-mail.

Se tivesse que criar um programa parecido para agendar uma SP e enviar sua execução por e-mail demoraria muito tempo para desenvolve-lo e ainda por cima é muito provavel que com resultados piores.

E para agendar tarefas no Linux (incluindo Unix-like), veja como é simples, digite no prompt :

Listagem 6: Agendado tarefas no Linux

crontrab -e
abrir-se-á o editor "vi", então tecle "I" (inserir) e digite :

# Este script rodará 00:01 de segunda a sexta-feira
# <min> <hora> <dia_do_mes> <nro_mes> <nro_dia_da_semana> nome_do_script
01 00 * * 1-5 /root/scripts/rodar_sp.sh

para salvar a programação e sair do vi : >ESC<:qs

para sair sem salvar : >ESC<:q!

Prontinho! Sua SP rodará no horario marcado, para colocar novos scripts em programação basta acrescentar novas linhas conforme acima.

Nota do Autor :

Como pode ver, não é tão complicado usar o isql e o agendador de tarefas de seu sistema, e usa-los dessa forma é bem mais simples do que vemos às pessoas fazendo por aí.

Referencias:

No linux, use man isql para acessar o manual do Interactive SQL do Firebird/Intebase, já no Windows é preciso ler Operation Guide que está disponivel em PDF na Internet. Este artigo está sob forma de licença GPL (General Public License) e pode ser reproduzido e distribuido livremente em outros tipos de mídia diferentes donde este artigo foi originalmente publicado, no entanto, atente-se para fato de que qualquer produto associado à este artigo também herdará as características GPL e também deverá ser fornecido livremente.

Para maiores esclarecimentos leia sobre a GPL em :

http://www.gnu.org/philosophy/philosophy.pt.html

Autor : Gladiston Santana

Data : 20-Maio-2003

Home Page : www.gladisto.hpg.com.br