Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have a bit of code that uses the Mysql module. It is complaining on:
my $query = "SELECT field1,field2 FROM my_table WHERE LEFT(field3) = ' +$var'";
The error returned is:

DBD::mysql::st execute failed: You have an error in your SQL syntax near '' at line 1 at /usr/lib/perl5/site_perl/5.6.1/i386-linux/ line 172.

If I remove the where clause, the error disappears.

edited: Sun Mar 9 14:40:39 2003 by jeffa - code tags

Replies are listed 'Best First'.
Re: Mysql module
by pfaut (Priest) on Mar 07, 2003 at 01:00 UTC

    I believe LEFT() ought to take two parameters; one being a string to extract characters from and two being the number of characters to extract. Maybe you should try something like

    my $l = length($var); my $query = "SELECT field1,field2 FROM my_table WHERE LEFT(field3,$l) += '$var'";
    --- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';
Re: Mysql module
by Heidegger (Hermit) on Mar 07, 2003 at 06:59 UTC
    Take the SQL sentence SELECT field1,field2 FROM my_table WHERE LEFT(field3) = '$var' and execute it in your database client (outside Perl). SQL should be tested in the database. Put a number instead of $var. BTW, I think you should select field3 before you can use it in the function LEFT(field3).
Re: Mysql module
by Cabrion (Friar) on Mar 07, 2003 at 01:58 UTC
    I'm not a Mysql buff, so I don't know about the syntax of Mysql's LEFT(). Typically LEFT() functions take another argument: how many characters to extract from the left side.

    However the " leads me to belive that there is a double quote in $var. Try printing $query to see what's in it.

    If that's the case, do something like this before the my $query... thing.

    $var = $dbh->quote($var);
Re: Mysql module
by Jazz (Curate) on Mar 07, 2003 at 02:26 UTC

    pfaut is correct. LEFT() basically does the same thing as substr (without offset and replacement options).

    LEFT( string, length )
      Does leaving out that parameter yeild an error message saying the problem is near `"'? I don't know. Just asking.
        mysql> select left("abcdef") -> ; ERROR 1064: You have an error in your SQL syntax near ')' at line 1 mysql> select left("abcdef",3); +------------------+ | left("abcdef",3) | +------------------+ | abc | +------------------+ 1 row in set (0.00 sec)
        --- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';
Re: Mysql module
by Anonymous Monk on Mar 07, 2003 at 16:27 UTC
    I corrected the function and selected the field. I removed the function entirely. Either way I get the same error. I can't print the query out because the entire script fails and sends the error to the log files.
      You are not escaping some meta characters in your $var. Do a  $var = $dbh->quote($var); before you build the SQL. See my earlier post.
Re: Mysql module
by Tomte (Priest) on Mar 12, 2003 at 12:12 UTC

    Funny no-one mentioned it so far: You should consider to develop the habit to always use prepared statements, passing in such things as parameters:

    [...] my $queryStatementHandler = $dbh->prepare("SELECT field1,field2 FROM m +y_table WHERE LEFT(field3, ?) = ?"); $query->bind_param(1, $value_1); $query->bind_param(2, $var); $query->execute(); [...]

    Not only is this (and the shorter forms DBI allows for) usually considered good style, but usually the safest form to talk to a DB.