[ic] Difficulties grabbing sql data within perl tags.

[Ton Verhagen]

Quoting Cameron G <ritontor at icenet.com.au>:
> > -----Original Message-----
> > From: interchange-users-bounces at icdevgroup.org
> > [mailto:interchange-users-bounces at icdevgroup.org] On Behalf
> > Of Stefan Hornburg
> > Sent: Wednesday, 5 January 2005 5:27 PM
> > To: interchange-users at icdevgroup.org
> > Cc: racke at linuxia.de
> > Subject: Re: [ic] Difficulties grabbing sql data within perl tags.
> >
> > 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'


Best regards,


Ton

--
Alamerce Nederland --- Cornerstone of eBusiness
Phone +31-40-2374381    info at alamerce.nl    http://www.alamerce.nl

eCommerce Solutions -- Consulting -- Website & Interchange Hosting