Thread: Dumb Perl-related question

Hi,
This question is so dumb that I am embarassed to ask it, but I can't 
seem to figure it out myself.  I have a select statement with a where clause, 
where I am trying to select all values from the database that match a string 
literal.  Only whenever I try to specify a string literal in the query, it 
gives me a syntax error on the first word in the literal.
What I'm doing looks like this:
  $query   = "select recipe_name, occasion, num_served, prep_time, " .             "freezable, instructions "
                  .             "from recipe "                                          .             "where
recipe_name= '$recipe';";  $result  = $dbconn->exec($query);  $rstatus = $result->resultStatus;  if ($rstatus !=
PGRES_TUPLES_OK&& $rstatus != PGRES_EMPTY_QUERY) {     croak "getRecipe:  Database error during query: " .
$dbconn->errorMessage; }
 
Every time, it prints out the following error:

getRecipeEquipment: Database error during query: ERROR:  parser: parse error at or near "no" at ./recipeDisplay line
33
The recipe name I am using is 'No Such Recipe'.
There would only be one row ever returned, since recipe_name is the 
primary key of the recipe table.
I am using perl 5.005_55, postgreSQL 6.4.2 (with the Pg module which 
is distributed with it).
I have tried every different type of quote that my keyboard will 
allow me to enter, casts, you name it, I think I've tried it.  Well, 
obviously not, since I am certain that this MUST work somehow or another.  
About half of the things I've tried work fine in psql, just not in the Perl 
interface.
Any help would be greatly appreciated.

                        Brendan
-- 
Brendan McKenna    
Technical Director            Phone: +353-(0)61-338177 x4143
W3 Services Ltd.              Fax: +353-(0)61-338065
Innovation Centre            Email: brendan@w3s.ie
National Technological Park
Limerick
Ireland

On Thu, Jun 17, 1999 at 06:33:03PM +0000, Brendan McKenna wrote:
> 
>     What I'm doing looks like this:
> 
>    $query   = "select recipe_name, occasion, num_served, prep_time, " .
>               "freezable, instructions "                              .
>               "from recipe "                                          .
>               "where recipe_name = '$recipe';";

I'm no perl expert, but your problem sounds like the query is being
constructed without any quotes around the constant, so perl must be eat
them. Does this work?
              "where recipe_name = \'$recipe\';";

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Brendan McKenna wrote:
> 
> Hi,
> 
>         This question is so dumb that I am embarassed to ask it, but I can't
> seem to figure it out myself.  I have a select statement with a where clause,
> where I am trying to select all values from the database that match a string
> literal.  Only whenever I try to specify a string literal in the query, it
> gives me a syntax error on the first word in the literal.
> 
>         What I'm doing looks like this:
> 
>    $query   = "select recipe_name, occasion, num_served, prep_time, " .
>               "freezable, instructions "                              .
>               "from recipe "                                          .
>               "where recipe_name = '$recipe';";
>    $result  = $dbconn->exec($query);
>    $rstatus = $result->resultStatus;
>    if ($rstatus != PGRES_TUPLES_OK && $rstatus != PGRES_EMPTY_QUERY) {
>       croak "getRecipe:  Database error during query: " .
>             $dbconn->errorMessage;
>    }
Try changing the $query assignment to:
   $query   = <<QUERY_STOP;   select recipe_name, occasion, num_served, prep_time,           freezable, instructions
     from recipe          where recipe_name = '${recipe}';   QUERY_STOP
 

The two significant changes are to use a here document and put brackets
around your
variable name.  The first change, I believe, simply makes the SQL code
more readable,
and it should cause you to have to escape less characters (although in
your select
query there were no escapes necessary).  The bracketing of the variable
name
will insure that perl will interpret the variable properly.


> 
>         Every time, it prints out the following error:
> 
> getRecipeEquipment: Database error during query: ERROR:  parser: parse error at or near "no" at ./recipeDisplay line
33
> 
>         The recipe name I am using is 'No Such Recipe'.
> 
>         There would only be one row ever returned, since recipe_name is the
> primary key of the recipe table.
> 
>         I am using perl 5.005_55, postgreSQL 6.4.2 (with the Pg module which
> is distributed with it).
> 
>         I have tried every different type of quote that my keyboard will
> allow me to enter, casts, you name it, I think I've tried it.  Well,
> obviously not, since I am certain that this MUST work somehow or another.
> About half of the things I've tried work fine in psql, just not in the Perl
> interface.
Single quotes were what you should use around the SQL literal.  Double
quotes should
be used (if not using a here document) around the whole query, if you
wish your 
variable to be replaced by its contents.

I hope this helps...james
> 
>         Any help would be greatly appreciated.
> 
>                                                         Brendan
> --
> Brendan McKenna
> Technical Director                      Phone: +353-(0)61-338177 x4143
> W3 Services Ltd.                          Fax: +353-(0)61-338065
> Innovation Centre                       Email: brendan@w3s.ie
> National Technological Park
> Limerick
> Ireland