[ic] Difficulties grabbing sql data within perl tags.

[Cameron G]

> 
> > > 
> >  
> >
> >>>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
> 

Thanks Ethan, that worked perfectly. The really strange thing is that your
code is (as far as I can tell) precisely the same as what I had already: 

my $results = $Db{orderline}->query( {sql => "SELECT description FROM
orderline WHERE order_number = '$line->{order_number}'"});

Vs. 

my $ordersql = "select description from orderline where order_number =
'$line->{order_number}'";
my $results = $Db{orderline}->query({ sql => $ordersql });

To think that creating a string and using it in the query would break rather
than using it inline. Oh well, perhaps this thread will save someone else
the head scratching I went through. Thanks!