[SP-pm] DBIx::Class + executando funções no SELECT

Renato Santos renato.cron at gmail.com
Fri Jun 27 14:08:35 PDT 2014


Mais em
http://search.cpan.org/~ribasushi/SQL-Abstract-1.78/lib/SQL/Abstract.pm#Literal_SQL_with_placeholders_and_bind_values_(subqueries)


2014-06-27 17:55 GMT-03:00 Blabos de Blebe <blabos at gmail.com>:

> "DBIx::Class é pra quem joga de mono black devotion"
>
>
> 2014-06-27 17:24 GMT-03:00 Renato Santos <renato.cron at gmail.com>:
>
> ah!
>>
>> Funciona tambem se usar arrayref como bind, no lugar do hash.
>>
>> uso esse codigo aqui em produção, ai fiquei 'curioso'!
>>           '+select' => [
>>                 \[
>>                     '(SELECT x.period_begin FROM f_extract_period_edge(?,
>> me.valid_from) x)',
>>                     [ plain_value => $options{group_by} ]
>>                 ]
>>             ],
>>             '+as' => ['group_from']
>>
>>
>>
>> 2014-06-27 15:51 GMT-03:00 Andre Carneiro <andregarciacarneiro at gmail.com>
>> :
>>
>> Valeu Renato!
>>>
>>> Infelizmente, a maneira como você passa o bind também não funcionou. Mas
>>> a boa notícia é que encontrei a solução, de qualquer forma. Ficou algo
>>> assim:
>>>
>>>
>>>
>>> my $row = $c->model('DB::Tabela')->search(
>>>     undef,
>>>     {
>>>         '+select' => [
>>>             \['randseq((?)::integer)', 123 ],
>>>         ] ,
>>>         '+as' => ['randseq']
>>>     },
>>> );
>>>
>>> O problema depois foi descobrir que o número que estava entrando no
>>> parâmetro do randseq estourava o tamanho do integer... Aí descobri que o
>>> método last_insert_id já retornava o valor sem eu precisar executar o
>>> randseq, diferentemente do DBI 'puro'.
>>>
>>> Mas ajudou bastante! Obrigado!
>>>
>>>
>>>
>>>
>>>
>>> 2014-06-27 12:59 GMT-03:00 Renato Santos <renato.cron at gmail.com>:
>>>
>>> Faça o bind local:
>>>>
>>>>
>>>> my $row = $c->model('DB::Tabela')->search(
>>>>      undef,
>>>>     {
>>>>         '+select' => [
>>>>             \['randseq((?)::integer)', { randseq_param => 123 }],
>>>>         ] ,
>>>>         '+as' => ['randseq']
>>>>     },
>>>> )->next;
>>>>
>>>> # $row->get_column('randseq');
>>>>
>>>>
>>>> http://search.cpan.org/~ribasushi/SQL-Abstract-1.78/lib/SQL/Abstract.pm#Literal_SQL_with_placeholders_and_bind_values_(subqueries)
>>>>
>>>>
>>>>
>>>>
>>>> 2014-06-27 12:47 GMT-03:00 Andre Carneiro <
>>>> andregarciacarneiro at gmail.com>:
>>>>
>>>>> Opa, já descobri! Foi mal!
>>>>>
>>>>> Era só passar 'bind', assim:
>>>>>
>>>>>  $c->model('DB::Tabela)->search(undef, {
>>>>>                                                         select =>
>>>>> ['randseq((?)::integer)  ', ] ,
>>>>>                                                         as =>
>>>>> [qw/randseq/],
>>>>>                                                         bind =>
>>>>> [12345],
>>>>>                                                     }, );
>>>>>
>>>>> O problema agora é que o DBIx::Class me solta o seguinte erro:
>>>>>
>>>>> schema "me" does not exist [for Statement "SELECT
>>>>> me.randseq((?)::integer)  FROM Tabela me" with ParamValues: 1='12345']
>>>>>
>>>>>
>>>>> Aparentemente, DBIx::Class não reconhece o apelido que ele mesmo deu
>>>>> pra tabela... :-p
>>>>>
>>>>>
>>>>> Alguma idéia?
>>>>>
>>>>>
>>>>> 2014-06-27 12:38 GMT-03:00 Andre Carneiro <
>>>>> andregarciacarneiro at gmail.com>:
>>>>>
>>>>> Boa tarde!
>>>>>>
>>>>>> Alguém pode me dizer como eu envio as bind variables para uma
>>>>>> equivalente a query abaixo no DBIx::Class, por gentileza?
>>>>>>
>>>>>>
>>>>>> SELECT randseq((?)::integer) as randseq FROM Tabela;
>>>>>>
>>>>>>
>>>>>> Minha tentativa no DBIx::Class
>>>>>>
>>>>>>
>>>>>> $c->model('DB::Tabela)->search(undef, {
>>>>>>                                                         select =>
>>>>>> ['randseq((?)::integer) as randseq ', ] ,
>>>>>>                                                     }, );
>>>>>>
>>>>>>
>>>>>> Não consegui descobrir em que lugar da estrutura eu preciso passar as
>>>>>> 'bind variables' para o DBIx::Class. Alguém poderia me ajudar, por
>>>>>> gentileza?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Obrigado!
>>>>>>
>>>>>> --
>>>>>> André Garcia Carneiro
>>>>>> Software Engineer
>>>>>> (11)982907780
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Saravá,
>>>> Renato CRON
>>>> http://www.renatocron.com/blog/
>>>> @renato_cron <http://twitter.com/#!/renato_cron>
>>>>
>>>> =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
>>>
>>>
>>
>>
>> --
>> Saravá,
>> Renato CRON
>> http://www.renatocron.com/blog/
>> @renato_cron <http://twitter.com/#!/renato_cron>
>>
>> =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
>
>


-- 
Saravá,
Renato CRON
http://www.renatocron.com/blog/
@renato_cron <http://twitter.com/#!/renato_cron>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/saopaulo-pm/attachments/20140627/8d5f9c7b/attachment.html>


More information about the SaoPaulo-pm mailing list