Hi list,
There is problem when with executing the sql statement in mysql 5.0.
I m not sure whether version 4.x has the same problem.
The problem is if I use static (hardcoded) values in the SQL statement, mysql returns result for each element in the array.
If I use dynamically allocated value that assigned to the array, the sql statement only return result for the first element of the array.
To illustrate the problem please see the code below.
The following perl code does return result when execute the SQL statement:
@outlets = ("07-6-0057","07-3-0051","07-2-0036");
my $mycustcode = "$outlets[1]"; # also work for [0] or [2]
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};However if I change the code to be a bit more dynamic like the following:
In html.pl: # the following value (in the OPTION tag) will be submitted to the query1_result.cgi.
while ($aref = $sth->fetchrow_arrayref){
print "<OPTION value=$aref->[0],>$aref->[1]: $aref->[2]</Option>\n";
}In query1_result.cgi:
sub split_outlets_to_array
{
my ($s) = @_;
@outlets = split(',',$s);
return @outlets;
}$outlet_str = $in{'outlets'}; # the outlets contains a list of custcodes that seperated by comma.
@outlets_array = &split_outlets_to_array($outlet_str);
my $mycustcode = "$outlets_array[0]"; # but value of [1] and [2] can't make the following SQL statement return a result.
$sql = qq {
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};Summary:
The problem with the second case is that outlets_array[0] does make the SQL statement return result, but [1] and [2] does not.
With the first case (with hardcoded values), all elements of outlets_array does make the SQL return result.
There may be problem in the code $in{'outlets'}. However from printing each element of the array on the html page, I found nothing wrong with the value, they are all printed in the following format on the html page:
outlets[0]: 07-6-0057
outlets[1]: 07-3-0051
outlets[2]: 07-2-0036
I don't know what caused this error when execute it with the SQL statement.
I tried to turn on warninig with -w, but not sure how to see them when running in web browser.
Thanks Jack
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
