«

Hi,

I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to the
string variable which in turns become part of the SQL statement, the SQL
does return result.
If I assign a value which is returned from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does not
return any result.

For example:
The following code does not return any result when I use it in SQL
statement:
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"

However, if I use the following hard coded assignment, the SQL statement
returns result:
# my $mycustcode = "07-2-0057";

Here is the SQL statement for MySQL 5.0:
$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 date(t.date) >= "$start_date"
and date(t.date) <= "$end_date"
and (c.type = "EXPORT" or c.type = "LOCAL")
and (c.custcode = "$mycustcode")
group by c.custcode;};

I may be using incorrect way of spliting the tokens, here is the
function to extract a list of the token:
$outlet_str = $in{'outlets'}

Thanks
Jack

Jack wrote:
> Hi,
>
> I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
> I found that if I assign a hard coded string (with double quote) to the
> string variable which in turns become part of the SQL statement, the SQL
> does return result.
> If I assign a value which is returned from an array in perl (eg.
> @array[1]) and use this variable in the SQL statement, the SQL does not
> return any result.
>
> For example:
> The following code does not return any result when I use it in SQL
> statement:
> my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"
>

Are you sure that @outlets[1] is the correct way to access the variable
you want? If it is an array then it should be $outlets[1].

- Ken

Ken wrote:

> Jack wrote:
>
>> Hi,
>>
>> I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
>> I found that if I assign a hard coded string (with double quote) to
>> the string variable which in turns become part of the SQL statement,
>> the SQL does return result.
>> If I assign a value which is returned from an array in perl (eg.
>> @array[1]) and use this variable in the SQL statement, the SQL does
>> not return any result.
>>
>> For example:
>> The following code does not return any result when I use it in SQL
>> statement:
>> my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"
>>
>
> Are you sure that @outlets[1] is the correct way to access the variable
> you want? If it is an array then it should be $outlets[1].
>
OK, I changed it to "$outlets[1]", but the query still does not return
result. I have also checked its value with the print $outlets[1]
statement, it prints out the desire value as I expected. But when it
used in SQL statement, it failed to match with the AND statement.

Another strange issue is if I assigned "$outlets[0]" to the variable,
the SQL statement does return result. There 3 elements in the error,
$outlets[1] is the second element.

Thanks
Sam

Thanks
Sam

> - Ken
>

sam wrote:

> Ken wrote:
>
>> Jack wrote:
>>
>>> Hi,
>>>
>>> I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
>>> I found that if I assign a hard coded string (with double quote) to
>>> the string variable which in turns become part of the SQL statement,
>>> the SQL does return result.
>>> If I assign a value which is returned from an array in perl (eg.
>>> @array[1]) and use this variable in the SQL statement, the SQL does
>>> not return any result.
>>>
>>> For example:
>>> The following code does not return any result when I use it in SQL
>>> statement:
>>> my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"
>>>
>>
>> Are you sure that @outlets[1] is the correct way to access the
>> variable you want? If it is an array then it should be $outlets[1].
>>
> OK, I changed it to "$outlets[1]", but the query still does not return
> result. I have also checked its value with the print $outlets[1]
> statement, it prints out the desire value as I expected. But when it
> used in SQL statement, it failed to match with the AND statement.
>
> Another strange issue is if I assigned "$outlets[0]" to the variable,
> the SQL statement does return result. There 3 elements in the error,
> $outlets[1] is the second element.
>
The value of the custcode is 07-3-0037, when it is used in MySQL
statement, is there a way to force it to be a string rather than a date
format?
In the SQL statement I wrote (in perl), regardless of having double
quote or not, there is not result return from the following statement:
AND c.custcode = "$mycustcode"

Thanks

> Thanks
> Sam

>
>> - Ken
>>

Jack <> wrote:

> my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"


You should always enable warnings when developing Perl code.


--
Tad McClellan SGML consulting
Perl programming
Fort Worth, Texas

sam <> wrote:
> Ken wrote:
>> Jack wrote:


> OK, I changed it


But Ken made that suggestion to Jack and you are sam.

Do you (sam) have access to Jack's code somehow?


--
Tad McClellan SGML consulting
Perl programming
Fort Worth, Texas

Jim Gibson wrote:
> In article <cqs1um$23il$>, Jack <> wrote:
>
>
>>sam wrote:
>
>
>
>>The value of the custcode is 07-3-0037, when it is used in MySQL
>>statement, is there a way to force it to be a string rather than a date
>>format?
>>In the SQL statement I wrote (in perl), regardless of having double
>>quote or not, there is not result return from the following statement:
>>AND c.custcode = "$mycustcode"
>
>
> Perl does not have a primitive, built-in date scalar value. It has only
> numeric, string, and reference scalar values, so '07-3-0037' IS a
> string.
>
> You need to post a complete, working, short-as-possible program that
> anyone can run to get more help. In general, you should print out the
> values of variables to see what they contain, but you seem to be doing
> that.
>
Here is a more complete version of the perl code.
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

Unless the code of $in{} did something wrong behind the scene, 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

"Jack" <> wrote in message
news:cqt7h2$2lso$...
>
> I tried to turn on warninig with -w, but not sure how to see them when
> running in web browser.
>

Sorry, but your post is too convoluted for my poor brain at this hour. I
will just mention that you can resolve the above by adding:

use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;

Matt

Jack wrote:
> Hi,
>
>
Sorry
> However, if I use the following hard coded assignment, the SQL statement
> returns result:
> # my $mycustcode = "07-2-0057";
>
> Here is the SQL statement for MySQL 5.0:
> $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 date(t.date) >= "$start_date"
> and date(t.date) <= "$end_date"
> and (c.type = "EXPORT" or c.type = "LOCAL")
> and (c.custcode = "$mycustcode")
> group by c.custcode;};
>
I just found that the value of $mycustcode contains a leading space,
thus sql failed to match the right record.
This also explained why the first element has no such problem.
Sorry for being so careless.


> Thanks
> Jack

Jack wrote:

> Jack wrote:
>
>> Hi,
>>
>>
> Sorry
>
>> However, if I use the following hard coded assignment, the SQL
>> statement returns result:
>> # my $mycustcode = "07-2-0057";
>>
>> Here is the SQL statement for MySQL 5.0:
>> $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 date(t.date) >= "$start_date"
>> and date(t.date) <= "$end_date"
>> and (c.type = "EXPORT" or c.type = "LOCAL")
>> and (c.custcode = "$mycustcode")
>> group by c.custcode;};
>>
> I just found that the value of $mycustcode contains a leading space,
> thus sql failed to match the right record.
> This also explained why the first element has no such problem.
> Sorry for being so careless.
>
I tried to remove the leading space, but the regular expression failed
to remove it. It may be not a whitespace, what what is it?
Here is the html string being passed to the $in{'outlets'} perl code:
outlets=07-6-0057%2C&outlets=07-3-0051%2C&outlets=07-2-0036
What is %2C here?

The code I used to strip the white space is:
$str =~s/[ ]//g;

Thanks
Sam

>> Thanks
>> Jack