[ic] Difficulties grabbing sql data within perl tags.

[Cameron G]

> > > 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'".
> > > >
> > > > So far, I've tried this:
> > > >
> > > > [perl tables="store orderline"]
> > > >         return <<EOF unless $Tmp->{main};
> > > >         <TR class=rnorm>
> > > >         <TD VALIGN=top>
> > > >         <H2>Bad query specified, caused error.</H2>
> > > >         </TD>
> > > > EOF
> > > >         $out = '';
> > > >         foreach $line (@{$Tmp->{main}}) {
> > > >                 $total_sales    += $line->{total_cost};
> > > >                 $amount = sprintf '%.2f', $line->{total_cost};
> > > >             $line->{status} = $Tag->loc('', $line->{status});
> > > >                 $url = $Tag->area('affiliate/order_view',
> > > > $line->{order_number});
> > > >                 my $ordersql = "select * from orderline where
> > > > order_number = '$line->{order_number}'";    ****** Here 
> are the two
> > > > lines
> > > >                 my $results = $Tag->query( { sql => 
> $ordersql, } 
> > > > );
> > > > ******
> > > >                 $out .= <<EOF;
> > > >
> > > >
> > > > And so on till the end of the code. The problem is, I'm
> > > getting this
> > > > in the error logs:
> > > >
> > > > Safe: no access for database products at 
> > > > /usr/lib/interchange/lib/Vend/Data.pm line 1064.
> > > >
> > > > Even though I'm quite clearly not accessing the products
> > > table (even
> > > > grepping for "products" on the whole document turns up
> > > nothing, it's
> > > > simply not there). Adding "products" to the perl tag causes
> > > this error:
> > > >
> > > > Query rerouted from table products failed: Can't locate
> > > object method "new"
> > > > via package "SQL::Parser" (perhaps you for got to load
> > > "SQL::Parser"?)
> > > > at /usr/lib/interchange/lib/Vend/Scan.pm line 592.
> > > >
> > > > And this is where I'm stuck. I'm pretty sure I'm doing the
> > > right thing
> > > > here, and I've tried various quoting schemes around the
> > > order number
> > > > too, all to no avail. Can anyone see where I've gone wrong?
> > >
> > > Use [perl tables="products store orderline"] or set the base 
> > > attribute for query to orderline.
> > >
> > > Ciao
> > >       Racke
> > >
> >
> >
> >I had tried that already, and it errors:
> >
> >Query rerouted from table products failed: Can't locate 
> object method "new"
> >via package "SQL::Parser" (perhaps you forgot to load 
> "SQL::Parser"?) 
> >at /usr/lib/interchange/lib/Vend/Scan.pm line 592.
> > >
> > > Query was: select * from orderline where order_number = 
> '(the actual 
> > > order
> >number in here)'
> 
> 
> 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'
> 

Yeah, I tried this: 

my $ordersql = "select * from orderline where order_number =
'$line->{order_number}'";
my $results = $Tag->query( { sql => $ordersql, base => 'orderline' } );

And it errors out like this: 

Query rerouted from table orderline failed: Can't locate object method "new"
via package "SQL::Parser" (perhaps you forgot to load "SQL::Parser"?) at
/usr/lib/interchange/lib/Vend/Scan.pm line 592.
> 
> Query was: select * from orderline where order_number = '(order number in
here)'

Same error, just with the orderline table specified. The order number is
definitely being inserted in to the sql correctly too, I've dropped the
statement in to phpmyadmin and sure enough, I get a result.