SQLBURGER: 2011

domingo, 14 de agosto de 2011

Importar um arquivo texto direto para o SQL Server

Imagine que você tem um arquivo texto, delimitado por ponto-e-vírgula (um CSV) e você deseja inserí-lo diretamente dentro de uma tabela. Porém, eu não vou mostrar como fazer isso no Management Studio, que é fácil. Eu vou mostrar como fazer isso via T-SQL! E porque? Bom, para mim isto foi muito útil para automatizar uma rotina de comunicação entre sistemas, rodando automaticamente.

Vamos então para a parte prática. É muito simples, primeiro analise o arquivo texto a ser importado, e identifique todas as colunas do arquivo com seu respectivo tipo de dado (data, texto, número, money, decimal, etc). É muito importante este trabalho de análise, pois será necessário criar uma tabela exatamente com as mesmas especificações.

Vamos ver um exemplo, lançamentos contábeis. Eu tenho aqui um arquivo texto, delimitado por vírgulas gerado a partir de um sistema ERP tosco em que a contabilidade não funciona, e nós precisávamos importar para um que funcionasse, de fato, porém em SQL Server. Este tipo de coisa sempre surge no meu dia a dia, a quantidade de sistemas e a necessidade de comunicação entre eles tem crescido exponencialmente.

Uma sugestão é que sempre (quando possível) abra o arquivo no excel, estude a estrutura das colunas e seus valores, veja se não existem coisas que podem impactar na sua importação (como por exemplo aspas simples ‘ , arquivo delimitado por ponto e vírgula e o infeliz usar ponto-e-vírgula na observação do lançamento e o sistema não tratar isso) entre outras coisas… o excel ajuda muito, nomeie as colunas, insira observações, planeje, para depois partir para parte prática.

*desculpem pela formatação do blog, estou revendo este layout para permitir inserir grandes imagens…

Vamos olhar o arquivo:

Então temos algum punhado de colunas, algumas DATETIME compatível com SQL Server, outros valores texto e money, e assim por diante. Para que possamos executar esta tarefa diretamente via T-SQL, é necessário criar a tabela com precisão, para que não ocorram erros de importação, que podem comprometer a qualidade da ferramenta desenvolvida. Para o exemplo acima, eu criei a seguinte tabela:

 CREATE TABLE [dbo].[ctb_movimento_202011](
    [Coluna 0]  bigint NULL,
    [Coluna 1]  bigint NULL,
    [Coluna 2]  bigint NULL,
    [Coluna 3]  datetime NULL,
    [Coluna 4]  varchar(10) NULL,
    [Coluna 5]  varchar(500) NULL,
    [Coluna 6]  varchar(1) NULL,
    [Coluna 7]  money NULL,
    [Coluna 8]  bigint NULL,
    [Coluna 9]  varchar(50) NULL,
    [Coluna 10] bigint NULL,
    [Coluna 11] varchar(1) NULL,
    [Coluna 12] bigint NULL,
    [Coluna 13] bigint NULL,
    [Coluna 14] bigint NULL,
    [Coluna 15] bigint NULL,
    [Coluna 16] bigint NULL,
    [Coluna 17] bigint NULL,
    [Coluna 18] bigint NULL,
    [Coluna 19] bigint NULL,
    [Coluna 20] bigint NULL
)
 

Coisas de programador… coloquei estes nomes de coluna por que previamente havia feito o mapeamento em outra ferramenta (excel). Enfim, a parte prática. Para importar o arquivo, basta utilizar uma ferramenta muito útil, chamada BULK INSERT (inclusive, muito mais ampla do que este simples exemplo). Bulk significa “grande volume”, e Insert é inserir. Então vamos inserir um grande volume de dados em uma tabela.

Vamos fazer um único exemplo, porém acredito que seja o mais comum deste tipo de aplicação, o arquivo delimitado por um caracter de controle.

 BULK INSERT ctb_movimento_202011 
    FROM 'C:\Users\nms.cores\Desktop\districomp-20052011\Cordilheira\CTBMOV_D.txt' 
    WITH 
    ( 
        FIELDTERMINATOR = ';', 
        ROWTERMINATOR = '\n' 
    )
 

Este comando informa o nome da tabela que deverá ser inserida a informação (no caso, ctb_movimento_202011), a cláusula from (origem dos dados), onde especificamos a localização do arquivo texto, e logo em seguida alguns parâmetros para esta importação (dentro do WITH), como o FIELDTERMINATOR, que especifica qual é o delimitador para cada coluna (no caso, ponto e vírgula), e também qual é o delimitador de cada linha (\n no caso separado por salto de linha, veja no Books Online as opções disponíveis).

Voilà

Espero que seja útil!

domingo, 26 de junho de 2011

Automação de impressoras térmicas diretamente pelo SQL Server

*Este artigo se aplica a versões 2005 ou superior do SQL Server. Na versão 2000, haverá restrições de 8000 caracteres na geração de arquivos, devido a limitação do varchar(max) do SQL 2000.

O título pode soar um tanto estranho. Mas o fato é que esta se revelou uma grande solução “surpresa” durante o desenvolvimento de um projeto de automação através de coletores de dados.

Para explicar melhor o contexto onde isto foi adotado, tudo começou durante a implantação de um projeto de automação na conferência de mercadorias, confecção de embalagens e expedição de volumes. Adotamos 3 coletores ARGOX PT-60 (fig.1) com Windows CE 5.0 e conexão wireless e 3 impressoras ZEBRA GK420t (fig.2) instaladas em um único computador dedicado, compartilhando as impressoras pela rede.


Fig.1 Coletor Argox PT-60	Fig.2 Impressora Térmica ZEBRA GK420t

O sistema, nomeado ARKAB, foi desenvolvido em vb.net utilizando o MS Compact framework. Basicamente, o sistema atuava de forma on-line com o servidor SQL, buscando dados referente os pedidos de venda, e alimentando tabelas durante a conferência, possibilitando que dois operadores pudessem conferir o mesmo pedido.

Este sistema permite conferir as mercadorias que estão no pedido de venda. O separador pega todos os produtos utilizando o romaneio de separação (fig.3), e entrega em um carrinho para o conferente. Este identifica o pedido no coletor através de código de barras (fig.3), identifica-se através do seu crachá pessoal e também identifica qual impressora irá utilizar (código de barras na impressora, conf. fig.4).


Fig.3 Romaneio de Separação	Fig.4 Identificação da impressora

Mas aí surgiram problemas, que prejudicaram o funcionamento do projeto. Durante o final do mês, a empresa realiza muito processamento no servidor na geração de lotes contábeis, fechamentos fiscais, emissão de DREs pesadas e abarrotadas de dados. E os coletores começaram a ficar muito lentos, prejudicando significativamente a performance da equipe de separação.

Outro problema que ocorria sempre era ao enviar uma impressão pela rede. De alguma forma, o Windows CE exibe uma messagebox com o resultado do comando de impressão ao usuário, o que atrapalha (e muito!) durante a conferência. A mensagem aparece na tela, o usuário não percebe, e ele bipa um produto. Como no final do comando sempre tem um ENTER, a tela de mensagem é fechada, e o usuário nem teria percebido que aquele produto bipado somente fechou a mensagem, e não foi processado pelo software. Isto acontecia por que eu gerava um arquivo texto temporário no coletor, e o copiava pela rede diretamente para a impressora, e o próprio Windows CE gerencia esta mensagem, o que parece não ser possível desativar.

Para resolver isto, modifiquei o sistema do coletor, fazendo com que carregasse em DATASET todos os dados necessários para realizar a conferência, de forma que acessasse o banco de dados duas vezes (na abertura e no fechamento da conferência), e não mais em cada operação. O aumento de performance foi absoluto, o coletor não dependia mais da rede para ter um bom desempenho ou não. Mas ainda restava a etiqueta. Como fazer a impressão de forma que não aparecesse esta mensagem de status de impressão ao usuário?

Resolvi então fazer a impressão da etiqueta térmica utliizando o próprio SQL Server, de forma que o servidor enviasse o comando para a impressora, e não mais o coletor. A solução aparentemente muito simples e funcional me fez perceber que podemos fazer qualquer sistema que utilize base SQL Server imprimir diretamente em impressoras térmicas de linguagem específica (PPLA, PPLB, ZPL, etc).

Para isso, vamos dividir esta informação em três partes fundamentais: geração dos dados da etiqueta, salvar estes dados em um arquivo texto e finalmente copiar este arquivo diretamente à impressora desejada pela rede.

1. Geração dos dados da etiqueta

O primeiro procedimento que montei é o que realiza a busca de todas as informações no banco de dados para a impressão da etiqueta, e monta dinamicamente todos os comandos ZPL necessários para a impressão da etiqueta:

Code Snippet
 -- set nocount on
-- exec spArkabEtiqueta 9
create proc  [dbo].[spArkabEtiqueta]
  @id_volume int
, @cd_impressora int
as
 
  /*
  Autor: Fabiano Cores
  Data : 06/2011
  Objetivo: Gera a etiqueta através de comandos ZPL, salva em arquivo
  através de automação OLE e lança o arquivo diretamente para a impressora
  térmica na rede através de simples compartilhamento, somente copiando
  o arquivo para o dispositivo (como \\pc-na-rede\nome_imp_zebra)
  */
 
  declare @t table (texto varchar(8000) null, ordem int not null identity)
  
  declare @cd_empresa     int
  declare @nr_pedido      int
  declare @nr_volume      int
  declare @qt_volume      int
  declare @cd_usuario     int
  declare @qt_pecas       int
  declare @cliente        varchar(60)
  declare @transportadora varchar(60)
  declare @endereco       varchar(200)
  declare @complemento    varchar(200)
  declare @pedido         varchar(20)
  declare @volume         varchar(20)
  declare @responsavel    varchar(50)
  declare @data           varchar(20)
  declare @quantidade     varchar(20)
  declare @cdb            varchar(50)
  declare @vtexto         varchar(8000)
  declare @texto          varchar(max)
  declare @diretorio      varchar(255)
  declare @arquivo        varchar(100)
  declare @origem         varchar(500)
  declare @destino        varchar(500)
  
  select @cd_empresa = cd_empresa
       , @nr_pedido  = nr_pedido
       , @nr_volume  = nr_volume
       , @cd_usuario = cd_usuario
       , @cdb        = cdb
    from ArkabVolume
   where id_volume   = @id_volume
   
  select @qt_pecas = sum(qt_item)
    from arkabvolumeitem
   where id_Volume = @id_volume
  
  select @qt_volume = count(*)
    from ArkabVolume
   where cd_empresa = @cd_empresa
     and nr_pedido  = @nr_pedido
   
  select @cliente = rtrim(cadastro.razao)
       , @transportadora = isnull(rtrim(transp.razao),'')
       , @endereco = rtrim(cadastro.endereco)
       , @complemento = rtrim(ltrim( rtrim(isnull(cadastro.complemento, '')) 
           + ' ' + rtrim(cidade.nome) + '/' + rtrim(cidade.cd_estado) 
           + ' - ' + left(rtrim(cadastro.cep),5) + '-' + right(rtrim(cadastro.cep),3)))
       , @pedido = right('000000' + rtrim(@nr_pedido), 6)
       , @volume = right('000' + rtrim(@nr_volume),3)--  + '/' + right('00' + rtrim(@qt_volume),2)
       , @responsavel = rtrim(usuario.login)
       , @data = convert(char(10), getdate(), 103) + ' ' 
         + right('00' + rtrim(datepart(hour, getdate())),2) + 'h'
       , @quantidade = rtrim(@qt_pecas) + ' PC'
       
    from cadastro, cidade, usuario, pedidovenda, cadastro transp
   where pedidovenda.cd_cadastro = cadastro.cd_cadastro
     and cidade.cd_Cidade = cadastro.cd_cidade
     and pedidovenda.cd_empresa = @cd_empresa
     and pedidovenda.nr_pedido  = @nr_pedido
     and usuario.cd_usuario = @cd_usuario
     and pedidovenda.cd_transportador *= transp.cd_cadastro
     
  select @texto = '
^XA
^MD10
^LT0
^JMA^FS
^MUd,200,200
^LH6,0
^FO20,7
^GB780,625,5,,0
^FS
^FO20,70
^GB645,120,5,,0
^FS
^FO20,7
^GB780,70,70,,0
^FS
^FO660,70
^GB140,562,5,,0
^FS
^FO20,275
^GB235,160,5,,0
^FS
^FO30,305
^GB215,120,120,,0
^FS
^FO250,275
^GB180,160,5,,0
^FS
^FO260,305
^GB160,120,120,,0
^FS
^FO425,275
^GB240,85,5,,0
^FS
^FO425,355
^GB240,80,5,,0
^FS
^FO425,430
^GB240,75,5,,0
^FS
^FO425,500
^GB240,132,5,,0
^FS
^FO90,20,0
^ATN,30,20
^FR
^FD
DECORLUX MATERIAL ELETRICO LTDA
^FS
^FO30,80,0
^AQN,20,10
^FD
CLIENTE
^FS
^FO30,103,0
^ATN,30,20
^FD
' + left(rtrim(@cliente), 29) + '
^FS
^FO30,138,0
^ATN,30,20
^FD
' + substring(@cliente, 30, 29) + '
^FS
^FO30,138,0
^ASN,30,20
^FD
^FS
^FO30,200,0
^AQN,20,10
^FD
TRANSPORTADORA
^FS
^FO30,223,0
^ASN,30,20
^FD
' + rtrim(@transportadora) + '
^FS
^FO30,280,0
^AQN,20,10
^FD
PEDIDO DE VENDA
^FS
^FO40,290,0
^AVN,120,100
^FR
^FD
' + rtrim(@pedido) + '
^FS
^FO265,280,0
^AQN,20,10
^FD
VOLUME
^FS
^FO290,290,0
^AVN,120,100
^FR
^FD
' + rtrim(@volume) + '
^FS
^FO435,280,0
^AQN,20,10
^FD
RESPONSAVEL
^FS
^FO435,310,0
^ASN,20,10
^FD
' + rtrim(@responsavel) + '
^FS
 
^FO435,360,0
^AQN,20,10
^FD
DATA
^FS
 
^FO435,385,0
^ASN,30,20
^FD
' + rtrim(@data) + '
^FS
 
^FO435,435,0
^AQN,20,10
^FD
QUANTIDADE
^FS
 
^FO435,460,0
^ASN,30,20
^FD
' + rtrim(@quantidade) + '
^FS
 
^FO30,435,0
^AQN,20,10
^FD
NOTA FISCAL E VISTO
^FS
 
^FO690,97
^BY2
^BCR,100,Y,N,N
^FD
' + rtrim(@cdb) + '
^FS
^XZ'
 
  -- Diretorio para gravar o arquivo temporário
  set @diretorio = 'D:\Arkab\impressoes\'
  
  -- Definição do nome do arquivo, coloca dia, mes, ano,
  -- hora, minuto, segundo e ID do volume.
  select @arquivo = 'tmp' 
                  + right('00' + rtrim(datepart(day, getdate())),2)
                  + right('00' + rtrim(datepart(month, getdate())),2)
                  + rtrim(datepart(year, getdate()))
                  + right('00' + rtrim(datepart(hour, getdate())),2)
                  + right('00' + rtrim(datepart(minute, getdate())),2)
                  + right('00' + rtrim(datepart(second, getdate())),2)
                  + '_vol_' + rtrim(@id_volume) + '.txt'
                  
  -- Gravo o arquivo
  exec spArkabGravaArquivo
    @texto, @diretorio, @arquivo
    
  -- Definição da origem e destino do arquivo salvo.
  select @origem = rtrim(@diretorio) + rtrim(@arquivo)
 
  -- Algo como '\\PC-CONF01\ZEBRA-SEP1'
  select @destino = caminho_de_rede
    from ArkabImpressora
   where cd_impressora = @cd_impressora
  
  -- Envio o arquivo texto pela rede diretamente na impressora térmica.
  exec spArkabCopiaArquivo
    @origem, @destino
 

Este procedimento coleta todos os dados, concatena em um varchar(max), e passa esta string para a segunda etapa, que é gravar este texto em um arquivo em uma pasta específica no servidor.

Exemplo do texto gerado pelo procedimento:

^XA
^MD10
^LT0
^JMA^FS
^MUd,200,200
^LH6,0
^FO20,7
^GB780,625,5,,0
^FS
^FO20,70
^GB645,120,5,,0
^FS
^FO20,7
^GB780,70,70,,0
^FS
^FO660,70
^GB140,562,5,,0
^FS
^FO20,275
^GB235,160,5,,0
^FS
^FO30,305
^GB215,120,120,,0
^FS
^FO250,275
^GB180,160,5,,0
^FS
^FO260,305
^GB160,120,120,,0
^FS
^FO425,275
^GB240,85,5,,0
^FS
^FO425,355
^GB240,80,5,,0
^FS
^FO425,430
^GB240,75,5,,0
^FS
^FO425,500
^GB240,132,5,,0
^FS
^FO90,20,0
^ATN,30,20
^FR
^FD
DECORLUX MATERIAL ELETRICO LTDA
^FS
^FO30,80,0
^AQN,20,10
^FD
CLIENTE
^FS
^FO30,103,0
^ATN,30,20
^FD
TORNADO INDUSTRIA E PECAS LTD
^FS
^FO30,138,0
^ATN,30,20
^FD
A
^FS
^FO30,138,0
^ASN,30,20
^FD
^FS
^FO30,200,0
^AQN,20,10
^FD
TRANSPORTADORA
^FS
^FO30,223,0
^ASN,30,20
^FD
NATALIN DA SILVA RAMOS
^FS
^FO30,280,0
^AQN,20,10
^FD
PEDIDO DE VENDA
^FS
^FO40,290,0
^AVN,120,100
^FR
^FD
056783
^FS
^FO265,280,0
^AQN,20,10
^FD
VOLUME
^FS
^FO290,290,0
^AVN,120,100
^FR
^FD
009
^FS
^FO435,280,0
^AQN,20,10
^FD
RESPONSAVEL
^FS
^FO435,310,0
^ASN,20,10
^FD
AUTOMATICO
^FS

^FO435,360,0
^AQN,20,10
^FD
DATA
^FS

^FO435,385,0
^ASN,30,20
^FD
26/06/2011 21h
^FS

^FO435,435,0
^AQN,20,10
^FD
QUANTIDADE
^FS

^FO435,460,0
^ASN,30,20
^FD
1 PC
^FS

^FO30,435,0
^AQN,20,10
^FD
NOTA FISCAL E VISTO
^FS

^FO690,97
^BY2
^BCR,100,Y,N,N
^FD
00020567830099699335
^FS
^XZ

Veja que depois defino qual o destino final do arquivo (no caso, uma impressora zebra na rede). No caso do sistema Arkab, a definição da impressora é dinâmica, no momento da abertura da conferência o usuário seleciona qual irá utilizar. Ao imprimir a etiqueta, o sistema passa para o procedimento como parâmetro (@cd_impressora) qual será a impressora que deverá enviar o comando. Uma simples tabela permite referenciar o código desta impressora ao seu caminho de rede.

E logo no final, temos o procedimento spArkabCopiaArquivo, que faz a cópia do arquivo do servidor para a impressora utilizando a rede. Vamos então falar de cada um deles agora.

2. spArkabGravaArquivo - Este procedimento grava o texto desejado em um arquivo em disco:

Code Snippet
 create proc spArkabGravaArquivo
  @texto varchar(max), --8000 in SQL Server 2000
  @diretorio varchar(255),
  @arquivo varchar(100)
 
as 
 
  /*
  Autor: Fabiano Cores
  Data : 06/2011
  Objetivo: Permite gravar arquivos de texto em disco diretamente
  através do T-SQL.
 
  Observação importante: pode haver a necessidade de ativar a
  opção 'Ole Automation Procedures' no SQL Server através do comando
  sp_reconfigure. Para isto, utilize o script abaixo:
  
  -- Mostrar opções avançadas.
  exec sp_configure 'show advanced options', 1
  GO
  -- Ativar a opção anterior
  reconfigure
  GO
  -- Ativa a automação OLE
  exec sp_configure 'Ole Automation Procedures', 1
  GO
  -- Reconfigura para aceitar a modificação
  reconfigure
  GO
  */
 
  declare @objFileSystem   int
  declare @objTextStream   int
  declare @hr              int
  declare @fileAndPath     varchar(80)
 
  exec @hr = sp_OACreate 'Scripting.FileSystemObject'
                       , @objFileSystem output
 
  select @FileAndPath = @diretorio + '\' + @arquivo
  
  if @hr = 0 
    exec @hr = sp_OAMethod @objFileSystem
                         , 'CreateTextFile'
                         , @objTextStream output
                         , @FileAndPath
                         , 2
                         , True
 
  if @hr=0
    exec @hr = sp_OAMethod @objTextStream
                         , 'Write'
                         , Null
                         , @texto
 
  if @hr = 0
    exec @hr = sp_OAMethod @objTextStream, 'Close'
 
  exec sp_OADestroy @objTextStream
  exec sp_OADestroy @objTextStream
 

3. spArkabCopiaArquivo – Realiza a cópia do arquivo da etiqueta diretamente para a impressora:

 /*
-- Para testar, copia o arquivo direto para impressora SEP1.
exec spArkabCopiaArquivo 
    'D:\Arkab\Impressoes\tmp26062011030828_vol_4.txt'
  , '\\PC-CONF01\ZEBRA-SEP1'
*/
create proc spArkabCopiaArquivo
  @origem varchar(200)
, @destino varchar(200)
 
as
 
  /*
  Autor: Fabiano Cores
  Data : 06/2011
  Objetivo: Permite copiar arquivos, informando origem e destino.
 
  Observação importante: pode haver a necessidade de ativar a
  opção 'xp_cmdshell' no SQL Server através do comando
  sp_reconfigure. Para isto, utilize o script abaixo:
  
  -- Mostrar opções avançadas.
  exec sp_configure 'show advanced options', 1
  GO
  -- Ativar a opção anterior
  reconfigure
  GO
  -- Ativa o comando
  exec sp_configure 'xp_cmdshell', 1
  GO
  -- Reconfigura para aceitar a modificação
  reconfigure
  GO
  */
 
  declare @string varchar(400)
  
  select @string = 'copy ' + rtrim(@origem) + ' ' + rtrim(@destino)
 
  exec master.dbo.xp_cmdshell @string, no_output
 

O resultado, é uma etiqueta saindo pela impressora, como esta daqui de exemplo (exatamente a mesma do procedimento acima):

24062011686 24062011683

24062011685 24062011684

Uma solução interessante…

sexta-feira, 17 de junho de 2011

SPLIT STRING – Função para dividir uma string por um caracter de controle

Imagine a seguinte situação: você possui uma string, separada por ponto e vírgula, e você deseja separar estas strings para realizar um tratamento. Vamos ver um exemplo abaixo:

string1; string2; "string 3 com ; no meio"; string4

Então nós temos quatro valores concatenados em uma única string. Observe que o terceiro valor utiliza o ponto e vírgula como texto da própria string, porém a sentença toda está entre aspas (“), o que determina que o ponto e virgula desta frase faz parte da string, e não é um caracter separador.

Para resolver esta questão, eu desenvolvi o seguinte procedimento:

 -- exec spSplit 'string1; string2; "string3 com ; no meio"; string4', ';'
alter proc spSplit
  @grupo varchar(8000),
  @caracter char(1) = NULL
as
 
begin
 
  -- ***************
  -- SQL Burger 2011
  -- Função SPLIT, separa string dividida por
  -- caracter de controle. Esta função só pode ser
  -- utilizada e distribuída caso os créditos ao autor
  -- sejam citados.
  -- ***************
  
  if @caracter is null set @caracter = ','
 
  declare @pos int
  declare @buffer varchar(8000)
  declare @buffer2 varchar(8000)
 
  create table #ret (texto varchar(8000) null, ordem int not null identity(1,1))
 
  while charindex('"', @grupo) > 0
  begin
 
    select @pos = charindex('"', @grupo)
 
    select @buffer = left(@grupo, @pos)
 
    select @grupo = substring(@grupo, @pos + 1, len(@grupo))
 
    select @pos = charindex('"', @grupo)
 
    select @buffer2 = left(@grupo, @pos)
 
    select @grupo = substring(@grupo, @pos + 1, len(@grupo))
 
    select @buffer = replace(@buffer, '"','')
         , @buffer2 = replace(@buffer2, '"', '')
 
    select @buffer2 = replace(@buffer2, @caracter, char(254))
    
    select @buffer2 = replace(@buffer2, ';', char(253))
 
    select @grupo = rtrim(@buffer) + rtrim(@buffer2) + rtrim(@grupo)
 
  end
  
  while charindex(@caracter,@grupo) > 0
  begin
    select @pos = charindex(@caracter,@grupo)
    select @buffer = left(@grupo,@pos - 1)
    select @grupo = right(@grupo,len(rtrim(@grupo)) - @pos)
    
    select @buffer = replace(@buffer, char(254), @caracter)
    select @buffer = replace(@buffer, char(253), ';')
    
    insert into #ret (texto)
      select rtrim(ltrim(@buffer))
  end
 
  if @grupo <> ''
  begin
    select @buffer =replace(@grupo,@caracter,'')
 
    insert into #ret (texto)
      select rtrim(ltrim(@buffer))
  end
 
  select texto from #ret order by ordem
 
end
 

O procedimento recebe dois parâmetros: @grupo (que é string a ser tratada) e @caracter (o caracter de controle a ser utilizado para separar a string). O resultado é o seguinte:

 exec spSplit 'string1; string2; "string3 com ; no meio"; string4', '!'
 

A procedure irá retornar cada string em uma linha, permitindo tratá-las da maneira como quiser. Uma dica, utilize uma tabela temporária com uma coluna auto-numérica, e você saberá exatamente qual linha ler de acordo com a posição desejada:

 set nocount on
 
create table #split (
  texto varchar(100) null
, ordem int not null identity )
 
insert into #split (texto)
  exec spSplit 'string1; string2; "string3 com ; no meio"; string4', ';'
  
select texto as 'Primeiro parametro' from #split where ordem = 1
select texto as 'Segundo parametro' from #split where ordem = 2
select texto as 'Terceiro parametro' from #split where ordem = 3
select texto as 'Quarto parametro' from #split where ordem = 4
 
 
drop table #split
 

Desta forma, o número da linha corresponderá ao parâmetro:

Outros caracteres de controle poderão ser utilizados, trazendo o mesmo resultado:

 exec spSplit 'string1! string2! "string3 com ; no meio"! string4', '!'
 

 exec spSplit 'string1! string2! "string3 com ! no meio"! string4', '!'
 

Espero que seja útil!

quarta-feira, 25 de maio de 2011

row_number : Use o número da linha em suas consultas

Durante o desenvolvimento de um layout de exportação de dados por arquivo texto, utilizei uma ferramenta muito prática do Transact-SQL, que gostaria de compartilhar aqui no meu blog. A função ROW_NUMBER é utilizada para retornar o número da linha dentro da consulta, de acordo com o “partition_by” e o “order_by” informados. Vamos ver a estrutura da função:

 ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )
 

[ <partition_by_clause> ]

Este parâmetro não é obrigatório. Permite especificar o particionamento da numeração da linha. É um agrupamento, que permitirá que a numeração seja sempre iniciada a cada mudança do agrupamento / particionamento.

<order_by_clause>

Determina qual será a ordenação das linhas do select na atribuição da numeração. A ordenação da função row_number não precisa necessariamente ser a mesma ordenação do select principal onde a função ROW_NUMBER foi aplicada.

A função irá retornar um BIGINT com o respectivo número da linha. Vamos então para a parte prática!

Crie a seguinte tabela e execute uma carga de exemplo:

 create table exemplo_rn (
  funcao char(15) null
, nome char(15) null
)
 
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'JOAO' )
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'MARIA' )
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'MARCOS' )
insert into exemplo_rn ( funcao, nome ) values ( 'PROGRAMADOR' , 'VERIDIANA' )
 
insert into exemplo_rn ( funcao, nome ) values ( 'ANALISTA' , 'IGOR' )
insert into exemplo_rn ( funcao, nome ) values ( 'ANALISTA' , 'PEDRO' )
 
insert into exemplo_rn ( funcao, nome ) values ( 'COORDENADOR' , 'OSVALDO' )
 

Nós vamos utilizar como exemplo uma pequena tabela, que possui função e nome de colaboradores. Claro, reduzimos apenas às colunas que nos interessa para exemplificarmos.

Vamos então consultar nossa tabela de exemplo:

 select * from exemplo_rn
 

Então nós temos 7 linhas no total. Vamos agora retornar uma terceira coluna, que trará automaticamente o número da linha:

 select funcao, nome, row_number() over ( order by nome ) as 'ID'
  from exemplo_rn
 order by nome
 

A coluna ID é resultado da função row_number(). Neste primeiro exemplo, utilizamos a ordenação alfabética da coluna “nome” como condição de numeração das linhas. Observe que a ordenação dentro da cláusula OVER () e a ordenação do select principal não estão diretamente interligadas, porém a ordenação dentro de OVER() é obrigatória:

 select funcao, nome, row_number() over ( order by funcao, nome ) as 'ID'
  from exemplo_rn
 order by funcao, nome
 

Neste segundo exemplo, a numeração foi feita através da ordenação da função e nome, na respectiva ordem. O próximo exemplo mostra a utilização de PARTITION_BY, condicionando a numeração das linhas ao agrupamento de respectivos campos:

 select funcao
     , nome
     , row_number() over ( partition by funcao order by nome ) as 'ID'
  from exemplo_rn
 order by funcao
 

Observe no resultado que a numeração da linha foi feita para cada agrupamento utilizado.

quinta-feira, 17 de março de 2011

Crystal Reports – Como buscar uma imagem de arquivo para ser exibida no relatório

Olá caros sqlnautas, gostaria de retornar aqui com mais uma dica importante sobre imagens no crystal reports. Imagine o seguinte cenário: você está montando um relatório em crystal reports que deverá exibir imagens que, ao invés de estarem armazenadas no banco de dados, estão em arquivos no disco rígido do servidor, e no banco de dados você só tem a localização deste arquivo, o que é muito comum, pois armazenar arquivos em banco de dados é extremamente desaconselhável quando se pensa em performance.

O que precisamos fazer, é utilizar uma ferramenta muito simples, chamada BULK INSERT. Este comando é utilizado para importar dados em massa provenientes de arquivos para o banco de dados. Mas nós não vamos armazenar esta imagens, e sim converter os arquivos de imagem em formato BLOB, ou image. O conceito é simples: realizo o bulk insert do arquivo dentro de uma tabela temporária, que retorna um select ao crystal reports com a coluna image da tabela:

Code Snippet
 -- exec spcAmplaPedidoCompraImagem 3, 2685
alter proc spcAmplaPedidoCompraImagem  
  @cd_empresa int
, @nr_pedido int
 
as
 
  declare @nr_documento int
  declare @revisao int
  declare @vcd_item char(13)
  declare @vdocumento varchar(4000)
  declare @sql varchar(8000)
  
  -- ***************
  -- SQL Burger 2011
  -- Procedure que retorna as imagens provenientes de arquivos
  -- para exibição em relatório crystal reports.
  -- ***************
  
  -- Inicio a montagem de uma query dinâmica, necessária para a execução
  -- do comando bulkinsert com parâmetros dinâmicos, provenientes da tabela
  -- que possui a localização dos arquivos.
  select @sql = '
 
  create table #tmpImagem (imagem image)
  
  '
  
  -- Insiro em uma tabela auxiliar todos os itens do pedido de compra
  -- para verificar quais possuem documentos que deverão ser impressos
  -- junto com o pedido.
  create table #tmpItem (cd_item char(13) collate database_Default null)
  
  insert into #tmpItem
    select cd_item from pedidocitem
     where cd_empresa = @cd_empresa
       and nr_pedido = @nr_pedido
       
  insert into #tmpItem
    select cd_item from AdhEPTRomaneioPedidoCompra
     where cd_empresa = @cd_empresa
       and nr_pedido = @nr_pedido
       
  -- Faço join da tabela temporária de itens com a de documentos
  -- configurados a serem impressos nos pedidos de compra, e valido
  -- se o arquivo está ativo e dentro da validade.
  declare cp1 cursor local fast_forward read_only for
    select distinct a.cd_item, b.documento
      from #tmpItem a, itemdocumentorev b
     where a.cd_item = b.cd_item
       and b.imprime_pc = 'S'
       and b.cd_situacao = 'A' -- ativos apenas
       and b.dt_validade >= convert(char(10), getdate(), 120)
     order by a.cd_Item
       
  open cp1
  
  while 1 = 1
  begin
    fetch next from cp1 into @vcd_item, @vdocumento
    if @@fetch_status <> 0 break
    
    -- Para cada arquivo retornado, monto na query dinâmica o comando bulk insert
    -- que irá inserir o stream da imagem na tabela #tmpImagem, criada no começo
    -- do procedimento.
    select @sql = @sql + '  insert into #tmpImagem (imagem)
      select Bulkcolumn 
        from OPENROWSET(
               BULK N''' + rtrim(@vdocumento) + ''', SINGLE_BLOB
                       ) as picture      
    '
  
  end
  close cp1
  deallocate cp1
  
  -- No final, retorno as imagens.
  select @sql = @sql + '
  
    select imagem from #tmpImagem
    
  '
  
  -- Executo a query dinâmica
  exec (@sql)
 

A string que contem a query dinâmica (@sql) foi executada da seguinte forma:

Code Snippet
 create table #tmpImagem (imagem image)
 
  insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4002-109 Base Fixação Encoder0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-057 Suporte Sensor Ótico0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-061 Suporte Direito Bandeja0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-064 Suporte Esquerdo Bandeja0001.jpg', SINGLE_BLOB
                     ) as picture      
    insert into #tmpImagem (imagem)
    select Bulkcolumn 
      from OPENROWSET(
             BULK N'C:\MSSQL\Novo\4004-119B Placa Fixação Servo0001.jpg', SINGLE_BLOB
                     ) as picture      
  
 
  select imagem from #tmpImagem
 

Para cada arquivo, ele montou a string executando o bulk insert de cada imagem. No final, o select retornando as imagens da temporária. O resultado do procedimento, via query no SQL Server:

Já no relatório crystal reports, ao importar esta coluna do tipo IMAGE, aparecerá no seu relatório:

Easy, don’t you think ? =)

Até a próxima!!

Pesquisar este blog