[ic] Re: select widget with lookup_select and sql

[Kevin Walsh]

"Aaron Berg" <ir.gath at gmail.com> wrote:
> This is now solved with some help from Mike Heins.
> 
> I had been trying to set the query using meta data and had also tried
> something like:
> 
> [table-editor
> 	widget.room_id=select
> 	lookup_query.room_id="SELECT room_id, room_name
> 			    FROM listing_rooms
> 			    WHERE listing_id = [scratch listing_item_id]
> 				"
> 	no_top=1
> ]
> 
> This method was incorrectly escaping the query and passing it
> literally to MySQL.
> 
> Mike gave me this solution:
> 
> [table-editor
> 	widget.room_id=select
> 	lookup_query.room_id=`
> 				$Scratch->{listing_item_id} =~ s/\D+//g;
> 				return qq{
> 			    SELECT room_id, room_name
> 			    FROM listing_rooms
> 			    WHERE listing_id = $Scratch->{listing_item_id}
> 				};
> 			`
> 	no_top=1
> ]
> 
> With the query correctly escaped it works much better. :)
>
I think you may have misunderstood the solution.

The problem doesn't appear to have been solved by altering the quotes
or with a query value escape mechanism.  The problem appears to have
been solved by removing all non-digit characters from the "listing_item_id"
scratchpad value and ensuring that the scratchpad value is actually
used as part of the query, instead of the [scratch] tag being passed
verbatim (without interpolation).

You would probably get the same effect with the following code:

    [pragma interpolate_itl_references 1]

    [table-editor
        no_top=1
        widget.room_id=select
        lookup_query.room_id=|
            SELECT  room_id, room_name
            FROM    listing_rooms
            WHERE   listing_id = [scratch name=listing_item_id filter=digits]
    |]

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin at cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/