Hard coded vs. variable assignment using in SQL statement.

Discussion in 'Perl Misc' started by Jack, Dec 28, 2004.

  1. Jack

    Jack Guest

    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, Dec 28, 2004
    #1
    1. Advertising

  2. Jack

    Ken Guest

    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, Dec 28, 2004
    #2
    1. Advertising

  3. Jack

    sam Guest

    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, Dec 28, 2004
    #3
  4. Jack

    Jack Guest

    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, Dec 28, 2004
    #4
  5. 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
     
    Tad McClellan, Dec 28, 2004
    #5
  6. 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
     
    Tad McClellan, Dec 28, 2004
    #6
  7. Jack

    Jack Guest

    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, Dec 29, 2004
    #7
  8. Jack

    Matt Garrish Guest

    "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
     
    Matt Garrish, Dec 29, 2004
    #8
  9. Jack

    Jack Guest

    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, Dec 29, 2004
    #9
  10. Jack

    sam Guest

    Strange leading character

    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
     
    sam, Dec 29, 2004
    #10
  11. Re: Strange leading character

    sam wrote:

    > 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?


    %2C == the comma character.

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


    Better:

    $str =~ s/\s+//g;

    You may also want to try passing the string through Data::Dumper to
    make sure you know exactly what character it is (if it's non-printable,
    Data::Dumper may translate it to printable for you).

    Also, you really should stop putting values in your SQL. As much as
    possible, that is.

    $create_view_stmt = 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) >= ?
    and date(t.date) <= ?
    and c.type in (?,?)
    and c.custcode = ?
    group by c.custcode};


    Then, when you go to execute this:

    $res = $sth->execute($start_date,
    $end_date,
    qw(EXPORT LOCAL),
    $mycustcode);

    Then you don't need to worry about special character as much. (Stray
    characters are a different problem no matter how you do things.)

    I would prefer putting a placeholder in for the view name, too, but I
    doubt many (if any?) database vendors support that. If any do, it's
    unlikely MySQL does.

    Then again, MySQL may not even support this much for a create statement
    - I'm not too sure. Then I presume you're using the DBI 'quote'
    function to hide special characters properly?
     
    Darin McBride, Dec 29, 2004
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    528
    Curt_C [MVP]
    Jan 6, 2005
  2. Luke Airig
    Replies:
    1
    Views:
    591
    Dimitre Novatchev
    Dec 24, 2003
  3. rodchar
    Replies:
    2
    Views:
    371
    rodchar
    Jul 1, 2008
  4. Aidan Gauland

    Refactoring hard-coded values

    Aidan Gauland, Jun 29, 2011, in forum: XML
    Replies:
    1
    Views:
    1,332
    Joe Kesselman
    Jun 30, 2011
  5. Microsoft

    How to avoid accessing row values with hard coded index

    Microsoft, Sep 24, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    2
    Views:
    172
    Perecli Manole
    Oct 3, 2003
Loading...

Share This Page