[ic] sql queries inside perl tag

[Dan Browning]

At 06:09 PM 8/1/2001 -0700, you wrote:
>At 05:47 PM 8/1/2001 -0400, you wrote:
>>Hello all,
>>         I'm having problems with some code inside of a perl 
>> tag.  Unfortunatley, I have a products table with a column (sun) that 
>> has a list of comma seperated numbers  (ex  5,6,7,8)
>>
>>and a sun table that has the correspondings values
>>
>>1       NO Shade
>>2       Some Shade
>>3       Half shade
>>4       Some sun
>>etc etc
>>
>>I realize that this is a poor db design, but it was written years ago by 
>>a predecessor  ;-)
>>
>>Any ways this is what I'm trying to do...
>>I need to look up the value in products->sun  (5,6,7,8)
>>split the results into an array, then do a lookup for each number in the 
>>array and display the results
>>
>>Here's my code:
>>[perl]
>>  my $suninfo = [sql]SELECT sun FROM products WHERE code = 
>> '[item-code]'[/sql];
>>  my $sunarray = split(/,/, $suninfo);
>>  my $i = 0;
>>  my $numelements = scalar(@sunarray);
>>  while ( $i < $numelements)
>>  {
>>    $textvalue = [sql]SELECT val FROM sun WHERE code = $sunarray[$i][/sql];
>>    print($textvalue);
>>    $i++;
>>  }
>>
>>[/perl]
>>
>>
>>Here's the error log message:
>>
>>207.166.192.127 v8iccWcx:207.166.192.127 - [01/August/2001:17:50:01 
>>-0400] bluestone /cgi-bin/bluestone.cgi/ABDIP.html Safe: syntax error at 
>>(eval 377) line 2, near "]SELECT sun "
>> > Missing right curly or square bracket at (eval 377) line 3, within pattern
>> >
>> >
>> >                               my $suninfo = [sql]SELECT sun FROM 
>> products WHERE code = 'ABDIP'[/sql];
>> >                               my $sunarray = split(/,/, $suninfo);
>> >                               my $i = 0;
>> >                               my $numelements = scalar(@sunarray);
>> >                               while ( $i < $numelements)
>> >                               {
>> >                                       $textvalue = [sql]SELECT val 
>> FROM sun WHERE code = $sunarray[$i][/sql];
>> >                                       print($textvalue);
>> >                                       $i++;
>> >                               }
>>
>>What am I missing???!!!?!?!?!?!?!
>>Brian Kosick
>>Web Programmer
>>New Age Consulting Service, Inc.
>>216-619-2000
>>briank@nacs.net
>
>And it runs OK in '$perl -W'? Just change [sql]... to "5,6,7,8".
>
>Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com

Wait a minute.  Let me give this another shot.  :-)

[perl] does not interpolate tags (like [sql]).  You have to use [calc] for 
that.  Even then, I don't think I've seen the [sql] tag used you did 
above.  Maybe you should try something like one of the following:

$Tag->query({sql => "SELECT ..."});

Also, here's a lot of good example code from 
lib/UI/pages/include/item_option_modular

(also see 
http://interchange.redhat.com/cgi-bin/ic/dev-4.8/ictemplates_15.html )

[perl tables=options]
         my $item_id = $Tag->cgi({name => 'item_id', filter => 'sql'});
         my $sql = "SELECT * FROM options WHERE o_master='$item_id' ORDER 
BY o_group, o_sort";
#Log("sql=$sql");
         my $hash_results = $Tag->query({ sql => $sql, hashref => 
'my_results'});

         my %above;
         my @rows;
         foreach my $row (@{$hash_results}) {
                 my $master = $row->{code};
#Log("master=$master");
                 $above{$CGI->{item_id}} = 1;
                 $above{$master} = 1;
                 my @stack = ( [ $row ] );
                 ARY: for (;;) {
                         my $ary;
                         $ary = pop(@stack)
                                 or last ARY;
                         ROW: for(;;) {
                                 $row = shift @$ary
                                         or last ROW;
                                 $row->{level} = scalar(@stack);
                                 push(@rows, $row);
                                 my $sku = $row->{sku};
                                 next ROW if ! $row->{o_enable};
                                 my $code = $row->{code};
                                 if($above{$sku}) {
                                         $row->{description} = 
errmsg("ENDLESS TREE") . " $row->{description}";
                                         display_template($_);
                                         next ROW;
                                 }
                                 if( $expand->{$code}
                                                 or
                                         ($explode and ! defined 
$expand->{$code})
                                         )
                                 {
                                         push(@stack, $ary);
                                         my $key = $odb->quote($sku, 'sku');
                                         my $q = "SELECT * FROM options 
WHERE o_master = $key ORDER BY o_sort",
                                         $ary = $odb->query(
                                                         {
                                                                 hashref => 1,
                                                                 sql => 
"SELECT * FROM options
                                                                                 WHERE 
o_master = $key
                                                                                 ORDER 
BY o_sort
                                                                         ",
                                                         }
                                         );
                                         $above{$sku} = 1 if $ary and 
scalar @{$ary};

                                 }
                         }  # END ROW
                 }  # END ARY
         }
         my $out;
         for(@rows) {
                 $out .= display_template($_);
         }
         return $out;
[/perl]

HTH,

Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com