[SP-pm] Encontrar valor em regex armazenada em banco de dados

Italo Gonçales italo.goncales at gmail.com
Wed Sep 30 07:10:01 PDT 2015


Após uma ajuda do Mantovani e do Ilmari no canal #dbix-class, obtive a
seguinte solução:
$card_rs->search(\['? RLIKE atr',
'3B7F1300008031C0521234640569937083819000' ]);

Segundo o ilmari: geralmente se você precisa de alguma coisa além do
identificador ( column name )  no lado esquerdo, você precisa usar \[] ou \"

A saída ficou a seguinte:

SELECT me.id_atr, me.atr, me.name, me.eletric_test, me.code, me.status,
me.microsiga1, me.microsiga2, me.observation FROM card_atr me WHERE ( ?
RLIKE atr ): '3B7F1300008031C0521234640569937083819000'

E encontrou os dados que eu precisava.

Obrigado a todos pela atenção!

Em 30 de setembro de 2015 10:55, Italo Gonçales <italo.goncales at gmail.com>
escreveu:

> Utilizando a estrutura: use DDP; p $_ for $card_rs->search({ "atr" => {
> "rlike" => '3B7F1300008031C0529999640569937083819000' } });
>
> Obtive:
> [devel at develvm bin]$ DBIC_TRACE=1 perl findatr.pl
> SELECT me.id_atr, me.atr, me.name, me.eletric_test, me.code, me.status,
> me.microsiga1, me.microsiga2, me.observation FROM card_atr me WHERE ( atr
> RLIKE ? ): '3B7F1300008031C0529999640569937083819000'
>
> Em 30 de setembro de 2015 10:42, Daniel de Oliveira Mantovani <
> daniel.oliveira.mantovani at gmail.com> escreveu:
>
>> Me falaram no canal para você tentar isso,
>> search({ "atr" => { "rlike" => '3B7F1300008031C0529999640569937083819000'
>> } })
>>
>> 2015-09-30 10:30 GMT-03:00 Italo Gonçales <italo.goncales at gmail.com>:
>>
>>> O código está assim:
>>>
>>> my $regex = '3B7F1300008031C0529999640569937083819000';
>>> use DDP; p $_ for $card_rs->search({ $regex => { RLIKE => 'atr' }
>>> })->all;
>>>
>>> Porém, invertendo a ordem da estrutura para ` use DDP; p $_ for
>>> $card_rs->search({ atr => { RLIKE => $regex } })->all; `, a saída fica:
>>>
>>> [devel at develvm bin]$ DBIC_TRACE=1 perl findatr.pl
>>> SELECT me.id_atr, me.atr, me.name, me.eletric_test, me.code, me.status,
>>> me.microsiga1, me.microsiga2, me.observation FROM card_atr me WHERE ( atr
>>> RLIKE ? ): '3B7F1300008031C0529999640569937083819000'
>>>
>>>
>>>
>>> Em 30 de setembro de 2015 10:26, Daniel de Oliveira Mantovani <
>>> daniel.oliveira.mantovani at gmail.com> escreveu:
>>>
>>>> Italo,
>>>>
>>>> qual estrutura no dbic vc está usando que o resultado é "SELECT
>>>> me.id_atr, me.atr, me.name, me.eletric_test, me.code, me.status,
>>>> me.microsiga1, me.microsiga2, me.observation FROM card_atr me WHERE (
>>>> 3B7F1300008031C0529999640569937083819000 RLIKE ? ): 'atr'" ?
>>>>
>>>> 2015-09-30 10:14 GMT-03:00 Italo Gonçales <italo.goncales at gmail.com>:
>>>>
>>>>> olá Carneiro,
>>>>>
>>>>> É porque a atr, é um dado que vem do chip do cartão, estou lendo o
>>>>> mesmo a partir de uma leitora de smartcard, e buscando as informações sobre
>>>>> esta atr no banco.
>>>>>
>>>>> Em 30 de setembro de 2015 10:09, Andre Carneiro <
>>>>> andregarciacarneiro at gmail.com> escreveu:
>>>>>
>>>>>> Estou tentando entender porque voce está tentando buscar pelo
>>>>>> conteúdo de 'atr' e nao pelo conteúdo de 'name'. De qq forma, creio que
>>>>>> voce pode tentar usar RLIKE do próprio Mysql como o Vinciguerra sugeriu(ele
>>>>>> foi mais rápido que eu... :P). Dependendo do tamanho dessa tabela,
>>>>>> podería-se pensar em usar FTS, embora não pareça ser o caso.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Atenciosamente
>>>>>>
>>>>>> 2015-09-30 8:38 GMT-03:00 Italo Gonçales <italo.goncales at gmail.com>:
>>>>>>
>>>>>>> Bom dia!
>>>>>>>
>>>>>>> Estou migrando uma aplicação java stand alone para WebService usando
>>>>>>> Catalyst e MySQL, quando me deparei com um caso atípico do meu dia a dia. A
>>>>>>> antiga base de dado ( em txt ) da aplicação java possui expressões
>>>>>>> regulares nas chaves que estou utilizando para a busca ( e que é a única
>>>>>>> que eu tenho ).
>>>>>>>
>>>>>>> Gostaria de saber se há alguma maneira de buscar um valor que
>>>>>>> corresponda à regex armazenada no banco de dados?
>>>>>>>
>>>>>>> Testei pelo MySQL e funcionou, mas não consigo fazer o mesmo select
>>>>>>> no perl.
>>>>>>>
>>>>>>> Segue busca de exemplo:
>>>>>>>
>>>>>>> mysql> SELECT * FROM card where '3B45978551FE119864' RLIKE atr;
>>>>>>>
>>>>>>> +----------------------------------+----------------------------------------+
>>>>>>> | atr                                      | name
>>>>>>>                     |
>>>>>>>
>>>>>>> +-----------------------------------+---------------------------------------+
>>>>>>> | 3B[0-9]{4}8551FE119864  | S12-468
>>>>>>>  |
>>>>>>>
>>>>>>> Tentei usar o search_like, search com '-rlike' e search com '-like',
>>>>>>> mas nada funcionou. Tentei inverter na query do search também, mas não deu
>>>>>>> muito certo.
>>>>>>>
>>>>>>> Tentativas:
>>>>>>> $card_rs->search({ atr => { -like => $atr } })->single;
>>>>>>>
>>>>>>> $card_rs->search({ atr => { -rlike => $atr } })->single;
>>>>>>>
>>>>>>> $card_rs->search({ $atr => { -rlike => atr } })->single;
>>>>>>>
>>>>>>> Gostaria de saber se há alguma outra maneira de fazer essa busca no
>>>>>>> perl.
>>>>>>>
>>>>>>> Obrigado!
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Att,
>>>>>>>
>>>>>>> Italo Gonçales
>>>>>>> Perl Developer at Morpho
>>>>>>> +55 12 98134 0318
>>>>>>>
>>>>>>> =begin disclaimer
>>>>>>>    Sao Paulo Perl Mongers: http://sao-paulo.pm.org/
>>>>>>>  SaoPaulo-pm mailing list: SaoPaulo-pm at pm.org
>>>>>>>  L<http://mail.pm.org/mailman/listinfo/saopaulo-pm>
>>>>>>> =end disclaimer
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> André Garcia Carneiro
>>>>>> Software Engineer
>>>>>> (11)982907780
>>>>>>
>>>>>> =begin disclaimer
>>>>>>    Sao Paulo Perl Mongers: http://sao-paulo.pm.org/
>>>>>>  SaoPaulo-pm mailing list: SaoPaulo-pm at pm.org
>>>>>>  L<http://mail.pm.org/mailman/listinfo/saopaulo-pm>
>>>>>> =end disclaimer
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>>
>>>>> Att,
>>>>>
>>>>> Italo Gonçales
>>>>> Perl Developer at Morpho
>>>>> +55 12 98134 0318
>>>>>
>>>>> =begin disclaimer
>>>>>    Sao Paulo Perl Mongers: http://sao-paulo.pm.org/
>>>>>  SaoPaulo-pm mailing list: SaoPaulo-pm at pm.org
>>>>>  L<http://mail.pm.org/mailman/listinfo/saopaulo-pm>
>>>>> =end disclaimer
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Thank you very much!
>>>>
>>>> -dom
>>>>
>>>> --
>>>>
>>>> Daniel de Oliveira Mantovani
>>>> Business Analytic Specialist
>>>> Perl Evangelist /Astrophysics hobbyist.
>>>> +55 11 9 8538-9897
>>>> XOXO
>>>>
>>>> =begin disclaimer
>>>>    Sao Paulo Perl Mongers: http://sao-paulo.pm.org/
>>>>  SaoPaulo-pm mailing list: SaoPaulo-pm at pm.org
>>>>  L<http://mail.pm.org/mailman/listinfo/saopaulo-pm>
>>>> =end disclaimer
>>>>
>>>>
>>>
>>>
>>> --
>>>
>>>
>>> Att,
>>>
>>> Italo Gonçales
>>> Perl Developer at Morpho
>>> +55 12 98134 0318
>>>
>>> =begin disclaimer
>>>    Sao Paulo Perl Mongers: http://sao-paulo.pm.org/
>>>  SaoPaulo-pm mailing list: SaoPaulo-pm at pm.org
>>>  L<http://mail.pm.org/mailman/listinfo/saopaulo-pm>
>>> =end disclaimer
>>>
>>>
>>
>>
>> --
>> Thank you very much!
>>
>> -dom
>>
>> --
>>
>> Daniel de Oliveira Mantovani
>> Business Analytic Specialist
>> Perl Evangelist /Astrophysics hobbyist.
>> +55 11 9 8538-9897
>> XOXO
>>
>> =begin disclaimer
>>    Sao Paulo Perl Mongers: http://sao-paulo.pm.org/
>>  SaoPaulo-pm mailing list: SaoPaulo-pm at pm.org
>>  L<http://mail.pm.org/mailman/listinfo/saopaulo-pm>
>> =end disclaimer
>>
>>
>
>
> --
>
>
> Att,
>
> Italo Gonçales
> Perl Developer at Morpho
> +55 12 98134 0318
>



-- 


Att,

Italo Gonçales
Perl Developer at Morpho
+55 12 98134 0318
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/saopaulo-pm/attachments/20150930/8cf4a323/attachment.html>


More information about the SaoPaulo-pm mailing list