[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