[ic] Difficulties grabbing sql data within perl tags.

[Ethan Rowe]

Cameron G wrote:

> > 
>  
>
>>>On Wed, 5 Jan 2005 19:02:41 +0800
>>>"Cameron G" <ritontor at icenet.com.au> wrote:
>>>
>>>      
>>>
>>>>>>>On Wed, 5 Jan 2005 17:14:41 +0800 "Cameron G" 
>>>>>>><ritontor at icenet.com.au> wrote:
>>>>>>>
>>>>>>>              
>>>>>>>
>>>>>>>>I'm positive I'm missing something terribly obvious here, 
>>>>>>>>but right now I'm stumped. All I'm trying to do 
>>>>>>>>                
>>>>>>>>
>>is hack the
>>    
>>
>>>>>Detailed
>>>>>          
>>>>>
>>>>>>>>reports page to display a list of the item descriptions
>>>>>>>>                
>>>>>>>>
>>>>>that were
>>>>>          
>>>>>
>>>>>>>purchased -
>>>>>>>              
>>>>>>>
>>>>>>>>seemingly a simple task, merely a query that says "select
>>>>>>>>                
>>>>>>>>
>>>>>>>description
>>>>>>>              
>>>>>>>
>>>>>>>>from orderline where order_number = 'the order number'".
>>>>>>>>                
>>>>>>>>
>>>>>>>> my $results = $Tag->query( { sql => $ordersql, }
>>>>>>>>                
>>>>>>>>
>>>>>>>Use [perl tables="products store orderline"] or set 
>>>>>>>              
>>>>>>>
>>the base 
>>    
>>
>>>>>>>attribute for query to orderline.
>>>>>>>              
>>>>>>>
>>>>>Cameron, have you tried setting the base attribute for 
>>>>>          
>>>>>
>>the query 
>>    
>>
>>>>>tag to orderline as Racke proposed?
>>>>>E.g. add to query tag:
>>>>>base='orderline'
>>>>>          
>>>>>
>>>Isn't his problem that he is using $Tag->query() instead of
>>>
>>>  $Db{products}->query({ sql => $ordersql })  ?
>>>      
>>>
>>This appears to be a reasonable objection. Cameron, please try
>>
>>$Db{orderline}->query({ sql => $ordersql })
>>
>>Ciao
>>	Racke
>>
>>    
>>
>
>Oh, that seems to work a little better. It's seems to be attempting the
>query now. Of course, nothing is ever that simple. Ideally, all I want to
>return is the description field, so basically all I need is an array, or a
>reference, but when I do this: 
>
>my $ordersql = "select description from orderline where order_number =
>'$line->{order_number}'"; 
>my $results = $Db{orderline}->query({ sql => $ordersql });
>
>It complains: 
>
>Safe: Can't coerce array into hash at (tag 'perl') line 17
>
>Which is interesting, as I was under the assumption it'd default to giving
>me a reference to whatever clunky ol' data structure that DBI spat out. It's
>the same result if I try to select *. I saw the arrayref => 'foo' stuff in
>the query tag docs, but that doesn't really look like what I need to do
>here, or am I once again being stupid? Sigh. It's days like this that IC
>makes me feel really dumb. 
>  
>
If memory serves, the $Db object's query sub will return an arrayref of 
arrayrefs.  Thus, you can work with the description field via:
my $results = $Db{orderline}->query( {sql => "SELECT description FROM 
orderline WHERE order_number = '$line->{order_number}'";
for my $record (@$results) {
    print "Description: $record->[0]";
}

If you want an arrayref of hashrefs, I think you need to pass "hashref 
=> 1" as one of the options in your call to $Db->query().

Hope this helps.
    - Ethan

-- 
Ethan Rowe
End Point Corporation
ethan at endpoint.com