http://qs321.pair.com?node_id=241045

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'";

[download]
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/Mysql.pm 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'";

    [download]
    ---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';

    [download]
[reply]
[d/l]
[select]
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).
[reply]
[d/l]
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);

    [download]
[reply]
[d/l]
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 )

    [download]
[reply]
[d/l]
      Does leaving out that parameter yeild an error message saying the problem is near `"'? I don't know. Just asking.
[reply]
        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)

        [download]
        ---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';

        [download]
[reply]
[d/l]
[select]
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.
[reply]
      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.
[reply]
[d/l]
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();
[...]

    [download]

    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.

    regards,
    tomte

[reply]
[d/l]

Back to Seekers of Perl Wisdom