Literal SQL with placeholders in update_objects

[Philip Garrett]

Hello,

I'm trying to get Rose to accept a literal SQL string with
placeholders in the "set" clause of update_objects(). The literal is
easy. It's the placeholders that are a problem.

For example, this:
Product::Manager->update_products(
set => { price => [ \'ceil(price + ?)', 5 ] },
...
produces this SET clause:
SET price IN (ceil(price + ?), ?)

I managed to make it work by passing an array reference to set:
Product::Manager->update_products(
set => [ [ \'price = ceil(price + ?)', 5 ] ],
...

Unfortunately, the docs say it requires a hash reference, and I don't
want to depend on an undocumented feature. Is there an officially
blessed way to do it, other than injecting the placeholder values
myself?

Thanks,
Philip

[John Siracusa]

On Thu, Mar 1, 2012 at 9:13 PM, Philip Garrett <phil...@gmail.com> wrote:
> I managed to make it work by passing an array reference to set:
>  Product::Manager->update_products(
>    set => [ [ \'price = ceil(price + ?)', 5 ] ],
>    ...
>
> Unfortunately, the docs say it requires a hash reference, and I don't
> want to depend on an undocumented feature. Is there an officially
> blessed way to do it, other than injecting the placeholder values
> myself?

Actually, I think that should probably work, so I think the
documentation should be changed. For now, you can just put a comment
next to your code to remind yourself that it's currently undocumented.
In the next release, I'll make a note to update the docs and add some
tests for this technique.

-John

[Philip Garrett]

On Mar 2, 7:34 am, John Siracusa <sirac...@gmail.com> wrote:

> On Thu, Mar 1, 2012 at 9:13 PM, Philip Garrett <philg...@gmail.com> wrote:
> > I managed to make it work by passing an array reference to set:
> >  Product::Manager->update_products(
> >    set => [ [ \'price = ceil(price + ?)', 5 ] ],
> >    ...
>

> Actually, I think that should probably work, so I think the
> documentation should be changed.  For now, you can just put a comment
> next to your code to remind yourself that it's currently undocumented.
>  In the next release, I'll make a note to update the docs and add some
> tests for this technique.

Thanks. Will do.

- Philip

[Philip Garrett]

On Mar 2, 8:34 am, John Siracusa <sirac...@gmail.com> wrote:
>> I managed to make it work by passing an array reference to set:
>> Product::Manager->update_products(
>> set => [ [ \'price = ceil(price + ?)', 5 ] ],
>> ...
>

> Actually, I think that should probably work, so I think the
> documentation should be changed.

Is it possible to accomplish something similar with the sort_by
clause? I want to use a placeholder in sort_by, but RDBO interprets:
sort_by => [ \'some_func(?)' => 5 ]
as
ORDER BY some_func(?), t1.5

Thanks,
Philip

[John Siracusa]

On Wed, Mar 21, 2012 at 7:00 PM, Philip Garrett <phil...@gmail.com> wrote:
> Is it possible to accomplish something similar with the sort_by
> clause?  I want to use a placeholder in sort_by, but RDBO interprets:
>  sort_by => [ \'some_func(?)' => 5 ]
> as
>  ORDER BY some_func(?), t1.5

No, bind values are not supported in sort_by. (Are they supported in
many DBD::* modules?)

-John

[Philip Garrett]

On Mar 21, 7:12 pm, John Siracusa <sirac...@gmail.com> wrote:

> On Wed, Mar 21, 2012 at 7:00 PM, Philip Garrett <philg...@gmail.com> wrote:
> > Is it possible to accomplish something similar with the sort_by
> > clause?  I want to use a placeholder in sort_by, but RDBO interprets:
> >  sort_by => [ \'some_func(?)' => 5 ]
> > as
> >  ORDER BY some_func(?), t1.5
>
> No, bind values are not supported in sort_by.  (Are they supported in
> many DBD::* modules?)

Yes, I think they're supported in most DBDs. I know mysql and Oracle
for sure. Probably Postgres, too.

To be clear, I am not trying to put a column name in the placeholder.
I am trying to pass an argument to a function, or really just replace
any literal value, for example:
select * from product
where match(description) against (? in boolean mode)
order by match(description) against (? in boolean mode) desc

- Philip