[SP-pm] Encontrar valor em regex armazenada em banco de dados
Italo Gonçales
italo.goncales at gmail.com
Wed Sep 30 06:10:57 PDT 2015
Enão, eu não tenho a regex como valor, e sim o valor completo da string. A
regex está armazenada no banco de dados:
mysql> select * from card_atr where atr RLIKE
'3B7F1300008031C0529999640569937083819000';
Empty set (0,00 sec)
mysql> select * from card_atr where
'3B7F1300008031C0529999640569937083819000' rlike atr;
+--------+----------------------------------------------------------------------+-------------------------------------------------------+
| id_atr | atr
| name
|
+--------+----------------------------------------------------------------------+-------------------------------------------------------+
| 69 | 3B7F1300008031C052[0-9]{4}640569937083819000 | S12-265
|
+--------+----------------------------------------------+-----------------------+------------------------------------------------------+
Seguindo o exemplo do email do Hernan, a saída com DBIC_TRACE ativado foi:
[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 (
3B7F1300008031C0529999640569937083819000 RLIKE ? ): 'atr'
DBI Exception: DBD::mysql::st execute failed: Unknown column
'3B7F1300008031C0529999640569937083819000' in 'where clause' [for Statement
"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 ? )" with ParamValues:
0='atr'] at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Schema.pm
line 1081.
DBIx::Class::Schema::throw_exception(SmartCardWebAtr::Schema=HASH(0x2415cf0),
"DBI Exception: DBD::mysql::st execute failed: Unknown column "...) called
at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage.pm
line 112
DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x24164a0),
"DBI Exception: DBD::mysql::st execute failed: Unknown column "...) called
at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI.pm
line 1466
DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::st execute failed:
Unknown column '3B7F1300008031"..., DBI::st=HASH(0x2a573c0), undef) called
at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI.pm
line 1834
DBIx::Class::Storage::DBI::_dbh_execute(DBIx::Class::Storage::DBI::mysql=HASH(0x24164a0),
DBI::db=HASH(0x2a089e0), "SELECT me.id_atr, me.atr, me.name,
me.eletric_test, me.code, "..., ARRAY(0x2a31338), ARRAY(0x265e6c0)) called
at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI.pm
line 849
DBIx::Class::Storage::DBI::__ANON__() called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/BlockRunner.pm
line 136
DBIx::Class::Storage::BlockRunner::try {...} () called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/Try/Tiny.pm
line 76
eval {...} called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/Try/Tiny.pm
line 72
Try::Tiny::try(CODE(0x2a3c558), Try::Tiny::Catch=REF(0x28c0020))
called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/BlockRunner.pm
line 140
DBIx::Class::Storage::BlockRunner::__ANON__() called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/Context/Preserve.pm
line 32
Context::Preserve::preserve_context(CODE(0x2a097c0), "replace",
CODE(0x2a35788)) called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/BlockRunner.pm
line 219
DBIx::Class::Storage::BlockRunner::_run(DBIx::Class::Storage::BlockRunner=HASH(0x2a2d3c0),
CODE(0x27bcdf8)) called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/BlockRunner.pm
line 111
DBIx::Class::Storage::BlockRunner::run(DBIx::Class::Storage::BlockRunner=HASH(0x2a2d3c0),
CODE(0x27bcdf8)) called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI.pm
line 850
DBIx::Class::Storage::DBI::dbh_do(undef, undef, "SELECT me.id_atr,
me.atr, me.name, me.eletric_test, me.code, "..., ARRAY(0x2a31338),
ARRAY(0x265e6c0)) called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI.pm
line 1815
DBIx::Class::Storage::DBI::_execute(DBIx::Class::Storage::DBI::mysql=HASH(0x24164a0),
"select", ARRAY(0x1a53ea8), ARRAY(0x1ba6f30), HASH(0xb8c038),
HASH(0x28e1458)) called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI.pm
line 2376
DBIx::Class::Storage::DBI::_select(DBIx::Class::Storage::DBI::mysql=HASH(0x24164a0),
ARRAY(0x1a53ea8), ARRAY(0x1ba6f30), HASH(0xb8c038), HASH(0x1be6788)) called
at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/Storage/DBI/Cursor.pm
line 178
DBIx::Class::Storage::DBI::Cursor::all(DBIx::Class::Storage::DBI::Cursor=HASH(0x26d2068))
called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/ResultSet.pm
line 1311
DBIx::Class::ResultSet::_construct_results(DBIx::Class::ResultSet=HASH(0x1be6770),
"fetch_all") called at
/home/devel/perl5/perlbrew/perls/perl-5.14.4/lib/site_perl/5.14.4/DBIx/Class/ResultSet.pm
line 1827
DBIx::Class::ResultSet::all(DBIx::Class::ResultSet=HASH(0x1be6770))
called at findatr.pl line 19
Em 30 de setembro de 2015 10:01, Daniel Vinciguerra <
daniel.vinciguerra at bivee.com.br> escreveu:
> Fala Italo,
>
> Sugiro que vc dê uma habilitada no DBIC_TRACE para ver a saída antes de
> tudo...
>
> Não consigo simular o seu cenário mas testei com um banco meu e em outro
> contexto e...
>
> ->search({ email => {-rlike => 'foo at .*'} })->single;
> Query: SELECT me.id, me.name, me.email, me.password, me.active,
> me.change, me.token, me.last_access, me.updated, me.created FROM user me
> WHERE ( email RLIKE ? ): 'foo at .*'
>
> Isso me diz que aparentemente sua query com -rlike esta OK
>
> Você também pode fazer o seguinte:
>
> ->search({ email => \["RLIKE 'foo at .*'"] })->single;
> Query: SELECT me.id, me.name, me.email, me.password, me.active,
> me.change, me.token, me.last_access, me.updated, me.created FROM user me
> WHERE ( email RLIKE 'foo at .*' ):
>
>
> Abs,
>
>
>
>
> *Daniel Vinciguerra*
> Web Solutions Architect and founder at Bivee
> Cel: +55 (47) 9270-6171
>
> *ATENÇÃO/ATTENTION:*
> Este e-mail contém informações confidenciais e seu conteúdo é dirigido ao
> uso do indivíduo ou da entidade mencionados acima. Se você recebeu esta
> mensagem por engano, por favor, notifique o remetente e remova-o
> imediatamente.
>
>
> This e-mail contains confidential information intended only for the use of
> the individual or entity named above. If you are not the intended
> recipient, please notify the sender and delete it immediately.
>
> 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
>>
>>
>
> =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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/saopaulo-pm/attachments/20150930/8dd36487/attachment-0001.html>
More information about the SaoPaulo-pm
mailing list